Skip to content

窗口函数与 CTE

中等 约 40 分钟

你将构建什么

一组使用窗口函数进行排名、累计求和、滞后/超前比较的分析 SQL 查询,以及用 CTE 编写可读的多步查询。

学习目标

  • 使用 ROW_NUMBER、RANK 和 DENSE_RANK
  • 用窗口框架计算累计总和
  • 用 LAG 和 LEAD 比较行
  • 用 CTE 构建可读的查询

前置要求

  • GROUP BY 和聚合
  • JOIN 基础
  • ORDER BY

步骤

  1. 1

    建立销售数据

    创建一个 sales 表,包含用于排名和趋势分析的月度数据。

    代码
    CREATE TABLE sales (
      id       SERIAL PRIMARY KEY,
      region   TEXT NOT NULL,
      month    TEXT NOT NULL,
      amount   INTEGER NOT NULL
    );
    
    INSERT INTO sales (region, month, amount) VALUES
      ('North', '2024-01', 10000), ('North', '2024-02', 12000), ('North', '2024-03', 9000),
      ('South', '2024-01', 8000),  ('South', '2024-02', 15000), ('South', '2024-03', 11000),
      ('East',  '2024-01', 7000),  ('East',  '2024-02', 9000),  ('East',  '2024-03', 13000);

    预期结果

    三个区域、三个月共九行销售数据。

  2. 2

    用 ROW_NUMBER 和 RANK 排名

    使用 ROW_NUMBER() 和 RANK() 配合 PARTITION BY,按销售额对每个区域内的月份排名。

    代码
    SELECT region, month, amount,
      ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
      RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
    FROM sales
    ORDER BY region, rn;

    预期结果

    每个区域的月份按金额降序编号 1-3;并列时共享排名。

  3. 3

    用窗口框架计算累计总和

    使用窗口框架子句计算每个区域随时间累积的销售额。

    代码
    SELECT region, month, amount,
      SUM(amount) OVER (
        PARTITION BY region
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS running_total
    FROM sales
    ORDER BY region, month;

    预期结果

    running_total 在每个区域内逐月累加。

  4. 4

    用 LAG 计算环比

    使用 LAG() 将每个月的销售额与上月比较,计算百分比变化。

    代码
    SELECT region, month, amount,
      LAG(amount) OVER (PARTITION BY region ORDER BY month) AS prev_month,
      ROUND(
        (amount - LAG(amount) OVER (PARTITION BY region ORDER BY month)) * 100.0
        / LAG(amount) OVER (PARTITION BY region ORDER BY month),
        1
      ) AS pct_change
    FROM sales
    ORDER BY region, month;

    预期结果

    每行显示上月金额和百分比变化。

  5. 5

    用 CTE 查找每个区域的最佳表现

    使用 CTE(WITH 子句)先对行排名,再过滤出每个区域的最佳表现。CTE 让复杂查询更易读。

    代码
    WITH ranked AS (
      SELECT region, month, amount,
        ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
      FROM sales
    )
    SELECT region, month AS best_month, amount AS best_amount
    FROM ranked
    WHERE rn = 1
    ORDER BY region;

    预期结果

    返回每个区域销售额最高的月份。

自己动手试试

你学到了什么

你掌握了窗口函数和 CTE —— 分析 SQL 的核心工具。窗口函数在不折叠行的情况下跨行计算,CTE 让复杂查询更易读、更易维护。

相关资源

相关课程

相关术语

相关术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误

← 返回主题