文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Oracle语法迁移/ 系统函数/ 分析函数
更新时间：2024-10-26 GMT+08:00
查看PDF
分享

分析函数

分析函数根据一组行计算一个聚合值。它与聚集函数的不同之处在于，它为每个组返回多行。分析函数通常用于计算累积值，数据移动值，中间值和报告聚合值。DSC支持分析函数，包括RATIO_TO_REPORT函数。

输入：分析函数

1
2
3
4
5
 
SELECT empno, ename,  deptno
  , COUNT(*) OVER() AS cnt
  , AVG(DISTINCT empno) OVER (PARTITION BY deptno) AS cnt_dst
  FROM emp
ORDER BY empno;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
WITH aggDistQuery1 AS (
     SELECT
               deptno
               ,AVG (
                    DISTINCT empno
               ) aggDistAlias1
          FROM
               emp
          GROUP BY
               deptno
) SELECT
          empno
          ,ename
          ,deptno
          ,COUNT( * ) OVER( ) AS cnt
          ,(
               SELECT
                         aggDistAlias1
                    FROM
                         aggDistQuery1
                    WHERE
                         deptno = MigTblAlias.deptno
          ) AS cnt_dst
     FROM
          emp MigTblAlias
     ORDER BY
         empno
;

RATIO_TO_REPORT

RATIO_TO_REPORT是个分析函数，它会返回一个值与一组值的比例。

输入：RATIO_TO_REPORT

1
2
3
4
 
SELECT last_name, salary
           , RATIO_TO_REPORT(salary) OVER () AS rr
  FROM employees
WHERE job_id = 'PU_CLERK';

输出

1
2
3
4
 
SELECT last_name, salary
     , salary / NULLIF( SUM (salary) OVER( ), 0 ) AS rr
  FROM employees
WHERE job_id = 'PU_CLERK';

输入：RATIO_TO_REPORT，在SELECT中使用AGGREGATE列

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
SELECT
          Ename
          ,Deptno
          ,Empno
          ,SUM (salary)
          ,RATIO_TO_REPORT (
               COUNT( DISTINCT Salary )
          ) OVER( PARTITION BY Deptno ) RATIO
     FROM
          emp1
     ORDER BY
          Ename
          ,Deptno
          ,Empno
;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
SELECT
          Ename
          ,Deptno
          ,Empno
          ,SUM (salary)
          ,COUNT( DISTINCT Salary ) / NULLIF( SUM ( COUNT( DISTINCT Salary ) ) OVER( PARTITION BY Deptno ) ,0 ) RATIO
     FROM
          emp1
     ORDER BY
          Ename
          ,Deptno
          ,Empno
;

输入：RATIO_TO_REPORT，且AGGREGATE列使用扩展分组功能，但RATIO TO REPORT列的COUNT（Salary）不在SELECT字段列表中

可以使用extendedGroupByClause参数来配置扩展GROUP BY子句的迁移。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT
          Ename
          ,Deptno
          ,Empno
          ,SUM (salary)
          ,RATIO_TO_REPORT (
               COUNT( Salary )
          ) OVER( PARTITION BY Deptno ) RATIO
     FROM
          emp1
     GROUP BY
          GROUPING SETS (
               Ename
               ,Deptno
               ,Empno
          )
     ORDER BY
          Ename
          ,Deptno
          ,Empno
;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
SELECT
          Ename
          ,Deptno
          ,Empno
          ,ColumnAlias1
          ,aggColumnalias1 / NULLIF( SUM ( aggColumnalias1 ) OVER( PARTITION BY Deptno ) ,0 ) RATIO
     FROM
          (
               SELECT
                         SUM (salary) AS ColumnAlias1
                         ,COUNT( Salary ) aggColumnalias1
                         ,NULL AS Deptno
                         ,NULL AS Empno
                         ,Ename
                    FROM
                         emp1
                    GROUP BY
                         Ename
               UNION
               ALL SELECT
                         SUM (salary) AS ColumnAlias1
                         ,COUNT( Salary ) aggColumnalias1
                         ,Deptno
                         ,NULL AS Empno
                         ,NULL AS Ename
                    FROM
                         emp1
                    GROUP BY
                         Deptno
               UNION
               ALL SELECT
                         SUM (salary) AS ColumnAlias1
                         ,COUNT( Salary ) aggColumnalias1
                         ,NULL AS Deptno
                         ,Empno
                         ,NULL AS Ename
                    FROM
                         emp1
                    GROUP BY
                         Empno
          )
     ORDER BY
          Ename
          ,Deptno
          ,Empno
;
父主题： 系统函数