深入理解partition by order by

一、基本概念

partition by order by是SQL語言中的一個用於分組排序的語法。它結合了partition by和order by兩個子句的功能，可以讓我們在對一組數據進行排序的同時，對每個分組內的數據進行排序和分組計算。

partition by用於將數據分成多個分組，而order by用於對每個分組內的數據進行排序。最終，我們所得到的結果集就是按照指定的排序順序組成的，每個分組內的數據也被排序了。

舉個例子，我們可以使用以下語句來查詢每個部門中工資最高的員工：

SELECT ename, deptno, sal
FROM (
   SELECT ename,deptno,sal, ROW_NUMBER()
       OVER(PARTITION BY deptno ORDER BY sal DESC) rn
   FROM emp
)
WHERE rn = 1;

在這個語句中，我們使用partition by將emp表中的數據按照deptno進行分組，然後使用order by對每個分組的數據按照sal進行排序。最後，我們得到的結果是每個部門中工資最高的員工。

二、應用場景

partition by order by在實際應用中非常靈活，可以用於各種各樣的數據計算和分析場景。下面，我們列舉幾個常見的應用場景：

1. 分組內獲取前N個數據

有時我們需要獲取每個分組內排序後的前N個數據。這時我們可以使用row_number()和partition by order by來實現。

SELECT *
FROM (
   SELECT ename,deptno,sal, ROW_NUMBER()
      OVER(PARTITION BY deptno ORDER BY sal DESC) rn
   FROM emp
)
WHERE rn <= 3;

在這個例子中，我們獲取了每個部門中工資最高的前3個員工。

2. 分組內計算佔比

有時我們需要計算每個分組內某個值的佔比。這時我們可以使用sum() over和partition by order by來計算：

SELECT ename,deptno,sal, 
   ROUND(sal / SUM(sal) OVER(PARTITION BY deptno) * 100, 2) AS pct
FROM emp;

在這個例子中，我們計算了每個部門中每個員工工資金額占該部門工資總額的百分比。

3. 計算年月的增長率

有時我們需要計算年月的增長率，這時我們可以使用partition by order by和lag()函數來計算：

SELECT sales_yearmonth, sales, 
  ROUND( 
    (sales - LAG(sales, 1) OVER (PARTITION BY sales_yearmonth ORDER BY sales_yearmonth)) 
    / LAG(sales, 1) OVER (PARTITION BY sales_yearmonth ORDER BY sales_yearmonth) 
    * 100, 2
  ) AS growth_rate 
FROM sales_data;

在這個例子中，我們計算了每個年月數據的增長率。

三、注意事項

在使用partition by order by時，我們需要注意以下幾點：

1. order by子句必須放在partition by子句之後

如果我們交換order by和partition by子句的順序，則會出現語法錯誤。我們需要始終記得保持正確的子句順序。

-- 錯誤實例：
SELECT ename,deptno,sal
FROM (
   SELECT ename,sal, ROW_NUMBER()
       OVER(ORDER BY sal DESC PARTITION BY deptno) rn
   FROM emp
)
WHERE rn = 1;

-- 正確實例：
SELECT ename,deptno,sal
FROM (
   SELECT ename,deptno,sal, ROW_NUMBER()
       OVER(PARTITION BY deptno ORDER BY sal DESC) rn
   FROM emp
)
WHERE rn = 1;

2. 分組排序必須指定排序列

在使用partition by order by時，我們需要確保指定了排序列。否則，查詢結果將不可預知。

-- 錯誤實例：
SELECT ename,deptno
FROM (
   SELECT ename,deptno, ROW_NUMBER()
   OVER(PARTITION BY deptno ORDER BY ?????) rn
   FROM emp
)
WHERE rn = 1;

-- 正確實例：
SELECT ename,deptno
FROM (
   SELECT ename,deptno, ROW_NUMBER()
   OVER(PARTITION BY deptno ORDER BY sal DESC) rn
   FROM emp
)
WHERE rn = 1;

3. 分組必須相鄰

在使用partition by order by時，我們需要確保相鄰行的排序列值相同才能正確地分組。

-- 錯誤實例：
SELECT ename,deptno,sal
FROM (
   SELECT ename,deptno,sal, ROW_NUMBER()
       OVER(PARTITION BY deptno ORDER BY deptno, sal DESC) rn
   FROM emp
)
WHERE rn = 1;

-- 正確實例：
SELECT ename,deptno,sal
FROM (
   SELECT ename,deptno,sal, ROW_NUMBER()
       OVER(PARTITION BY deptno ORDER BY sal DESC) rn
   FROM emp
)
WHERE rn = 1;

四、總結

partition by order by是一個非常強大的SQL語言功能，可以幫助我們在數據計算和分析時更加靈活地進行處理。同時，在使用partition by order by時，我們也需要注意保持正確的語法和正確的數據分組。