- 和group by 的差別
- 分析的搭配（聚合，排序，位移）
-
ORDER BY 累計求和屬性
Q1: 和group by 分組有什麼差別？
A1:Over（Partition by）它可以在資料中進行分組然後
計算基于組的某種統計值,并且每一組的每一行都可以傳回一個統計值
SELECT E.DEPTNO,AVG(SAL) FROM EMP E
GROUP BY DEPTNO;
SELECT E.*, AVG(SAL)OVER(PARTITION BY DEPTNO) AS 平均工資
FROM EMP E;
1.
函數為聚合函數（AVG,SUM,COUNT)
1.FUNCTION_NAME(<參數>,…) OVER (PARTITION BY 表達式,… ) -- 分組求值
2.FUNCTION_NAME(<參數>,…) OVER (ORDER BY 表達式 <ASC DESC> ) -- 整體資料未做分組,先排序,在求累計求值
3.FUNCTION_NAME(<參數>,…) OVER (PARTITION BY 表達式,… ORDER BY 表達式 <ASC DESC> ) -- 先分組,按組内排序,對組内求累計求值
-- PARTITION BY 1.檢視員工表中員工資訊以及對應部門的總人數
/*
SELECT * FROM EMP;
SELECT DEPTNO, COUNT(1) FROM EMP GROUP BY DEPTNO;
SELECT E.*, T.CNT
FROM EMP E
JOIN (SELECT DEPTNO, COUNT(1) AS CNT FROM EMP GROUP BY DEPTNO) T
ON E.DEPTNO = T.DEPTNO;
*/
SELECT E.*,
COUNT(EMPNO) OVER (PARTITION BY DEPTNO) AS CNT
FROM EMP E;
-- ORDER BY 2.對員工表的工資從低到高排序,求累計應發放薪資
SELECT E.*,
SUM(SAL) OVER (ORDER BY SAL)
FROM EMP E;
差別
SELECT E.*,
SUM(SAL) OVER (ORDER BY SAL,EMPNO)
FROM EMP E;
---- 按照每個部門的工資從高到低排序,計算每個部門的
累計工資-- PARTITION BY + ORDER BY 3.按照每個部門的工資從高到低排序,計算每個部門的累計工資
SELECT E.*,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)
FROM EMP E;
但如上圖所示，有些累積的金額應該要跳轉累加的，但依舊保留原值。
原因在分組同一個部門後，員工的工資相等，函數默合并為一類。
出現這種情況，我們要
添加多一條字段進行排序，
如果薪酬相同的，
再按員工姓名排序！
SELECT E.*,SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL,EMPNO)
FROM EMP E;
2.函數為排序函數（ROW_NUMBER(),RANK(),DENSE_RANK())情況下：
①ROW_NUMBER： ROW_NUMBER函數傳回一個唯一的值,當碰到相同資料時,排名按照記錄集中記錄的順序依次遞增。[順序排序]
②RANK： RANK函數傳回一個唯一的值,當碰到相同的資料時,此時所有相同資料的排名是一樣的,同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名。[跳躍排序]
③DENSE_RANK： DENSE_RANK函數傳回一個唯一的值,當碰到相同資料時,此時所有相同資料的排名都是一樣的。 同時會在最後一條相同記錄和下一條不同記錄的排名之間不空出排名。[同為合并，連續排序]
經典案例1.将emp表按工資從高到低排序,并給出排名（排名不并列）
SELECT E.*,ROW_NUMBER() OVER (ORDER BY SAL DESC) AS 排名
FROM EMP E;
：按照員工部門分組, 給出工資從高到低的排名（排名不并列）
SELECT E.*, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS 排名
FROM EMP E;
：找出員工表每個部門的一條員工資訊
SELECT *
FROM (
SELECT E.*,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY EMPNO) AS RN
FROM EMP E
) T
WHERE T.RN = 1;
3.
位移函數（LEAD上移(列,參數), LAG下移(列,參數) )
經典案例：員工表按入職先後排序, 求出每兩個員工的入職時間差
SELECT E.*,
LAG(HIREDATE,1) OVER (ORDER BY HIREDATE) AS 下移,
LEAD(HIREDATE,1) OVER (ORDER BY HIREDATE) AS 上移,
LEAD(HIREDATE,1) OVER (ORDER BY HIREDATE) - HIREDATE AS 入職時間差
FROM EMP E;