DM-分析函数

分析函数

主要用户计算基于组的某种聚合值。这是一种更加简便的方式，否则必须使用连接查询，子查询或视图，存储过程实现。

分析函数对每组返回多行数据，多行形成的组称为窗口，窗口决定当前执行的计算范围。
分析函数分为11类：

• COUNT(*)；
• 完全分析函数 AVG|MAX|MIN| COUNT|SUM
• 方差函数 VAR_POP、 VAR_SAMP、 VARIANCE、 STDDEV_POP、 STDDEV_SAMP、
  STDDEV；
• 协方差函数 COVAR_POP、 COVAR_SAMP、 CORR；
• 首尾函数 FIRST_VALUE、 LAST_VALUE；
• 相邻函数 LAG 和 LEAD；
• 分组函数 NTILE；
• 排序函数 RANK、 DENSE_RANK、 ROW_NUMBER；
• 百分比函数 PERCENT_RANK、 CUME_DIST、 RATIO_TO_REPORT、PERCENTILE_CONT、 NTH_VALUE；
• 字符串函数 LISTAGG；
• 指定行函数 NTH_VALUE。

使用限制

• 分析函数只能出现在选择项或ORDER BY子句。
• 有DISTINCE时不允许ORDER BY 一起使用。
• 不允许嵌套使用。分析函数参数中，PARATITION BY，ORDER BY 项中不允许使用分析函数。
• 只有 MIN、 MAX、 COUNT、 SUM、 AVG、 STDDEV、 VARIANCE 的参数支持 DISTINCT，其他分析函数的参数不允许为 DISTINCT；
• FIRST_VALUE 分析函数返回组中数据窗口的第一个值， LAST_VALUE 表示返回
• 组中数据窗口 ORDER BY 项相同的最后一个值；
• FIRST_VALUE/LAST_VALUE/LAG/LEAD/NTH_VALUE 函 数 支 持RESPECT|IGNORE NULLS 子句，该子句用来指定计算中是否跳过 NULL 值；

具体使用
一般分析函数
语法格式：

<分析函数>::=<函数名>(<参数>) OVER (<分析子句>)
<分析子句>::= [<PARTITION BY 项>] [<ORDER BY 项> [<窗口子句>]]
<PARTITION BY 项>::= PARTITION BY <<常量表达式>| <列名>>
<ORDER BY 项>::= ORDER BY <<常量表达式>| <列名>>
<窗口子句>::=<ROWS | RANGE > < <范围子句 1>|<范围子句 2> >
<范围子句 1>::=

使用示例

创建测试表STU

CREATE TABLE "SYSDBA"."STU"
(
"ID" INT NOT NULL,
"NAME" VARCHAR(50),
"SCORE" FLOAT,
"CLASSES" VARCHAR(50),
CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;

插入测试数据

查询每个班的平均成绩

SELECT CLASSES, NAME, SCORE, AVG(SCORE) OVER(partition by CLASSES) as AVG from STU;

查询按成绩排名的学生姓名和排名

SELECT NAME, RANK() OVER(ORDER BY SCORE DESC) AS RANK FROM STU;

查询成绩90以上的班级和学生个数

SELECT CLASSES, COUNT(*) OVER (PARTITION BY SCORE ) AS NUM FROM STU WHERE SCORE > 90;

排名函数RANK, DENSE_RANK和ROW_NUMBER

查看当前数据：有两个78和两个92.5

SELECT *FROM STU;

当使用RANK函数排名时,值相同，排名相同，但后续排名根据前面排名的相同个数顺延。

SELECT NAME, RANK() OVER(ORDER BY SCORE DESC) AS RANK FROM STU;

使用DENSE_RANK函数，值相同，排名相同，但后续排名和相同排名之间连续。

SELECT NAME, DENSE_RANK () OVER(ORDER BY SCORE DESC) AS RANK FROM STU;

使用ROW_NUMBER函数排名，值相同时的排名也不同，且每个值的排名连续。

SELECT NAME, ROW_NUMBER () OVER(ORDER BY SCORE DESC) AS RANK FROM STU;

FIRST, LAST

查询每个班级的第一名和最后一名

SELECT DISTINCT CLASSES, 
MAX(SCORE) KEEP(DENSE_RANK FIRST ORDER BY SCORE DESC) OVER(PARTITION BY CLASSES) MAX_VAL,
MIN(SCORE) KEEP(DENSE_RANK FIRST ORDER BY SCORE ASC) OVER(PARTITION BY CLASSES) MIN_VAL
FROM STU;

1.2.3 分组NTILE
根据分数将学生分为三组

SELECT NAME, NTILE(3) OVER(ORDER BY SCORE DESC) AS ZU FROM STU;

排列百分比PERCENT_RANK()

计算每个分数所占的百分比

SELECT SCORE, PERCENT_RANK() OVER(ORDER BY SCORE DESC) AS BAI FROM STU;

RATIO_TO_REPORT

计算某一样本值所占百分比
计算每个分数在所属班级中的百分比

SELECT CLASSES, RATIO_TO_REPORT(SCORE) OVER(PARTITION BY CLASSES) AS RAT FROM STU;

情况表达式

查询分数，90以上返回优秀，80-90返回合格，80以下返回还需努力。

SELECT NAME, CASE WHEN SCORE > 90 THEN '优秀' WHEN SCORE <= 90 AND SCORE >=80 THEN '合格' ELSE '还需努力' END AS "成绩" FROM STU;