分析函数

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

输入：分析函数

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

输出

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

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

输出

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

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

SELECT
          Ename
          ,Deptno
          ,Empno
          ,SUM (salary)
          ,RATIO_TO_REPORT (
               COUNT( DISTINCT Salary )
          ) OVER( PARTITION BY Deptno ) RATIO
     FROM
          emp1
     ORDER BY
          Ename
          ,Deptno
          ,Empno
;

输出

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子句的迁移。

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
;

输出

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
;