SQL用法总结

窗口函数

窗口函数（Window Function）是在查询结果的“窗口”内 对行做计算，不会把行合并成一条（不像 GROUP BY）。

1
2
3
4
5
函数名(...) OVER (
    [PARTITION BY 列1, 列2, ...]  -- 分区，相当于每个组内单独计算
    [ORDER BY 列3, 列4, ...]      -- 窗口内行的顺序
    [ROWS|RANGE BETWEEN 起点 AND 终点]  -- 窗口框架
)

其中ROWS的关键词有：

  • UNBOUNDED PRECEDING:从分区的第一行开始
  • N PRECEDING:从当前行前N行开始
  • CURRENT ROW:从当前行开始
  • N FOLLOWING:从当前行后N行开始
  • UNBOUNDED FOLLOWING:分区最后一行

聚合类

1
2
3
4
5
SUM(salary) OVER (PARTITION BY dept)    -- 部门工资总和
COUNT(*) OVER (PARTITION BY dept)       -- 部门人数
AVG(salary) OVER (PARTITION BY dept)    -- 部门平均工资
MIN(salary) OVER (PARTITION BY dept)    -- 部门最小工资
MAX(salary) OVER (PARTITION BY dept)    -- 部门最大工资

排序类

1
2
3
4
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) -- 排名1,2,3...
RANK() OVER (PARTITION BY dept ORDER BY salary DESC)       -- 并列名次
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) -- 并列不空缺
NTILE(n) OVER (PARTITION BY dept ORDER BY salary DESC)     -- 分成n份

值引用类

1
2
3
4
LAG(salary, 1) OVER (PARTITION BY dept ORDER BY salary) -- 前一行工资
LEAD(salary, 1) OVER (PARTITION BY dept ORDER BY salary) -- 下一行工资
FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary) -- 窗口第一行
LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -- 窗口最后一行

累计类

1
2
SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- 累计工资
AVG(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)       -- 滑动平均

时间函数

递归RECURSIVE CTE

可以用于递归查询，比如文件夹结构，祖先关系等，也可用于生成一连串数字。

1
2
3
4
5
6
7
8
9
10
11
WITH RECURSIVE cte_name AS (
    -- 1. 基础查询 (Anchor member)
    SELECT ... FROM table WHERE ... 

    UNION [ALL]

    -- 2. 递归查询 (Recursive member)
    SELECT ... FROM table
    JOIN cte_name ON ...
)
SELECT * FROM cte_name;
1
2
3
4
5
6
7
8
9
/*用于生成1-20的subtask_id*/
WITH RECURSIVE numbers AS (
    SELECT 1 AS subtask_id
    UNION ALL
    SELECT subtask_id + 1
    FROM numbers
    WHERE subtask_id < 20
)

封面

封面