深入理解partition by order by

发布时间：2023-05-21

一、基本概念

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时，我们也需要注意保持正确的语法和正确的数据分组。

推荐文章