文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle语法迁移> 系统函数> 分析函数

分析函数

分享
更新时间: 2019/08/09 GMT+08:00

分析函数根据一组行计算一个聚合值。它与聚集函数的不同之处在于,它为每个组返回多行。分析函数通常用于计算累积值,数据移动值,中间值和报告聚合值。Migration Tool支持分析函数,包括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

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

输入: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
;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区