函数——开窗函数

/*
分析函数：
function(value_expression[,offset] [,default]) over ([query_partition_clase] order_by_clause)
value_expression：可以是一个字段或一个内建函数;
offset：是正整数，代表偏移量，默认为1，指往前或往后几点记录;
default：用于处理溢出边界的行。因组内第一个条记录没有之前的行，最后一行没有之后的行，default就是用于处理这样的信息，默认为空;
query_partition_clase：代表分区。结果根据分区计算；
order_partition_clase：代表排序。结果按照排序计算。

移动求平均：
移动平均(moving average)以当前行，向前、向后推移固定行，随着当前行的变化，指定框架像窗口一样移动。
ROWS n PRECEDING：截至到前N行（共：前N行+当前行=N+1）
ROWS n FOLLOWING：截至到后N行（共：后N行+当前行=N+1）
ROWS BWTWEEN n PRECEDING AND n FOLLOWING：前N行至后N行（共：前N行+后N行+当前行=2N+1）
如：SUM(COL1) OVER (PARTITION BY COL1 ORDER BY COL2 ROWS BETWEEN 2 PRECEDING AND 2 FOLLWOING)--5行
主要的开窗函数：
1.排序
①row_number()over() ：返回值唯一，不同的连续位次（1，2，3，……）
②rank()over()：跳跃排序，相同位次跳过之后位次（1，2，2，4，……）
③dense_rank()over() ：连续排序，相同位次不跳过之后位次（1，2，2，3，……）
2.前/后N行
①lag() over ()；（取出前n行数据）
②lead() over ()；（取出后N行数据）
3.聚合函数
①sum()over(）
②count() over()
③max() over()
④min() over()
⑥avg() over()
⑦
4.首个/最后一个值
first_value() over()
last_value() over()
*/

WITH TEMP AS(
SELECT 'cfe' AS NAME, 2 AS CLASS,74 AS SCORE FROM DUAL UNION ALL
SELECT 'dss ' AS NAME, 1 AS CLASS,95 AS SCORE FROM DUAL UNION ALL
SELECT 'ffd ' AS NAME, 1 AS CLASS,95 AS SCORE FROM DUAL UNION ALL
SELECT 'fda ' AS NAME, 1 AS CLASS,80 AS SCORE FROM DUAL UNION ALL
SELECT 'gds ' AS NAME, 2 AS CLASS,92 AS SCORE FROM DUAL UNION ALL
SELECT 'gf ' AS NAME, 3 AS CLASS,99 AS SCORE FROM DUAL UNION ALL
SELECT 'ddd ' AS NAME, 3 AS CLASS,99 AS SCORE FROM DUAL UNION ALL
SELECT 'adf ' AS NAME, 3 AS CLASS,45 AS SCORE FROM DUAL UNION ALL
SELECT 'asdf ' AS NAME, 1 AS CLASS,55 AS SCORE FROM DUAL UNION ALL
SELECT '3dd ' AS NAME, 1 AS CLASS,95 AS SCORE FROM DUAL
)

SELECT A.*
,RANK()OVER(PARTITION BY CLASS ORDER BY SCORE DESC) AS FLG
FROM TEMP A;

--SELECT A.*
-- ,ROW_NUMBER()OVER(PARTITION BY CLASS ORDER BY SCORE DESC) AS FLG
-- FROM TEMP A;

--SELECT A.*
-- ,DENSE_RANK()OVER(PARTITION BY CLASS ORDER BY SCORE DESC) AS FLG
-- FROM TEMP A;

--SELECT A.*
-- ,SUM(SCORE)OVER(PARTITION BY CLASS ORDER BY SCORE DESC) AS FLG
-- FROM TEMP A;

WITH A AS (
SELECT 1 ID,'a' NAME FROM DUAL UNION
SELECT 2 ID,'b' NAME FROM DUAL UNION
SELECT 3 ID,'c' NAME FROM DUAL UNION
SELECT 4 ID,'d' NAME FROM DUAL UNION
SELECT 5 ID,'e' NAME FROM DUAL
)
--SELECT ID,NAME,LAG(ID,1,'')OVER(ORDER BY NAME) FLG FROM A;

SELECT ID,NAME,LEAD(ID,1,'')OVER(ORDER BY NAME) FLG FROM A;

---------------------------------------------------------------------------------------------------------

/*
分析函数 ：
keep(dense_rank first/last)
在业务数据中可能遇到这样的需求。希望获取部门内年龄最小的人中，工资最高的记录。
此时就需要使用KEEP(DENSE_RANK FIRST/LAST)来处理数据了。
FIRST可以理解是取小值，LAST取大值。而前面的MIN或者MAX则是在KEEP的结果集中取某一字段的最大值或最小值。
*/

WITH WORKERS AS(
SELECT 'CIM' DEPT, 'zhangsan' NAMES , 23 AGE, 4000 SALARIES FROM DUAL UNION ALL
SELECT 'CIM' DEPT, 'lisi' NAMES , 35 AGE, 9000 SALARIES FROM DUAL UNION ALL
SELECT '自动化' DEPT, 'wangwu' NAMES , 26 AGE, 6500 SALARIES FROM DUAL UNION ALL
SELECT '自动化' DEPT, 'maliu' NAMES , 28 AGE, 6000 SALARIES FROM DUAL UNION ALL
SELECT '自动化' DEPT, 'zhaoqi' NAMES , 26 AGE, 5000 SALARIES FROM DUAL UNION ALL
SELECT 'CIM' DEPT, 'liba' NAMES , 23 AGE, 3000 SALARIES FROM DUAL
)
--希望获取部门内年龄最小的人中，工资最高的记录
--SELECT W.DEPT,
-- MAX(W.SALARIES) KEEP(DENSE_RANK FIRST ORDER BY W.AGE) MAX_SALARY
-- FROM WORKERS W
-- WHERE 1=1
-- GROUP BY DEPT;

---年龄最大人中的最低工资查询如下
SELECT W.DEPT,
MIN(W.SALARIES) KEEP(DENSE_RANK FIRST ORDER BY W.AGE) MIN_SALARY
FROM WORKERS W
WHERE 1=1
GROUP BY DEPT;

WITH T AS
(
SELECT 'A' GB1, '01' GB2, '20190601' DT, 10 V FROM DUAL
UNION ALL SELECT 'A', '01', '20190604', 20 FROM DUAL
UNION ALL SELECT 'A', '02', '20190603', 30 FROM DUAL
UNION ALL SELECT 'A', '03', '20190602', 40 FROM DUAL
UNION ALL SELECT 'B', '01', '20190601', 50 FROM DUAL
UNION ALL SELECT 'B', '02', '20190605', 60 FROM DUAL
UNION ALL SELECT 'B', '03', '20190603', 70 FROM DUAL
UNION ALL SELECT 'B', '04', '20190602', 80 FROM DUAL
)
SELECT GB1
, GB2
, MAX(DT) DT
, SUM(V) V
, MAX(MAX(DT)) OVER(PARTITION BY GB1) MAX_DT
, MAX(GB2) KEEP(DENSE_RANK LAST ORDER BY MAX(DT)) OVER(PARTITION BY GB1) MAX_DT_GB2
, MAX(SUM(V)) KEEP(DENSE_RANK LAST ORDER BY MAX(DT)) OVER(PARTITION BY GB1) MAX_DT_V
FROM T
GROUP BY GB1, GB2
ORDER BY GB1, GB2;