窗口函数

本章目标：掌握 OVER、排名、分组 TopN、累计/占比/移动平均、LAG/LEAD 和连续区间，能独立手写这些高频题。 前置知识：SELECT / WHERE / GROUP BY / HAVING、多表 JOIN、子查询与 CTE。 预计用时：70 分钟。

1. 为什么需要窗口函数

需求一：每个品类销售额前 3 的商品。GROUP BY category 只能得到品类汇总，商品明细被压没了。

需求二：每个订单的金额，以及它占该客户总消费的比例。同一个结果里既要订单粒度，又要客户粒度；GROUP BY 只能二选一。

窗口函数解决的就是这件事：保留每一行明细，同时给每行附加一个"它所在分组"的计算结果。

GROUP BY 把 100 行明细压成 5 行汇总；窗口函数则保留 100 行明细，只多一列分组值。可以把它理解成"开窗"：当前行能看到同组的其他行，但不会删掉其他行。

提示：本章 SQL 都基于玩具库 project/sql_demo.duckdb，先运行 python scripts/make_sql_demo_db.py。这里只有九张小表，不涉及电商项目数据。

2. 核心语法

函数名() OVER (
    PARTITION BY 分组列
    ORDER BY    排序键
    ROWS BETWEEN ... AND ...
)

一句话：PARTITION BY 决定"和谁比"，ORDER BY 决定"按什么顺序比"，帧决定"比到哪一行"。

SELECT i.product_id, p.category, i.unit_price,
       ROUND(AVG(i.unit_price) OVER (PARTITION BY p.category), 2) AS 品类平均价,
       ROUND(i.unit_price - AVG(i.unit_price) OVER (PARTITION BY p.category), 2) AS 与均价差
FROM demo_order_items i
JOIN demo_products p ON p.product_id = i.product_id
LIMIT 10;

结果仍是 15 行明细，只多了两列。OVER () 括号为空则表示全表一个分区，每行拿到同一个全局值。

执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> 窗口函数 -> SELECT -> DISTINCT -> ORDER BY -> LIMIT。窗口函数在 WHERE 之后才计算，这就是第 8 节那个坑的根源。

3. 排名三兄弟

WITH t AS (
    SELECT * FROM (VALUES (999), (999), (888), (777)) AS v(price)
)
SELECT price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS 行号,
       RANK()       OVER (ORDER BY price DESC) AS 排名,
       DENSE_RANK() OVER (ORDER BY price DESC) AS 密集排名
FROM t;

• ROW_NUMBER：不重复、不跳号（1,2,3,4）；
• RANK：并列同名次，后续跳号（1,1,3,4）；
• DENSE_RANK：并列同名次，后续不跳号（1,1,2,3）。

只取固定 N 行用 ROW_NUMBER；并列都要保留用 RANK 或 DENSE_RANK。用 ROW_NUMBER 时加第二排序键（如 product_id），并列场景的结果才可复现。

4. 分组 TopN

需求：电子品类销售额前 3 的商品。

WITH prod_gmv AS (
    SELECT i.product_id, SUM(i.quantity * i.unit_price) AS gmv
    FROM demo_orders o
    JOIN demo_order_items i ON i.order_id = o.order_id
    WHERE o.status = 'paid'
    GROUP BY i.product_id
),
ranked AS (
    SELECT p.category, p.product_name, pg.gmv,
           ROW_NUMBER() OVER (
               PARTITION BY p.category ORDER BY pg.gmv DESC, pg.product_id
           ) AS rn
    FROM prod_gmv pg
    JOIN demo_products p ON p.product_id = pg.product_id
)
SELECT category AS 品类, product_name AS 商品,
       ROUND(gmv, 2) AS 销售额, rn AS 排名
FROM ranked
WHERE category = '电子' AND rn <= 3
ORDER BY 排名;

结果：显示器 2,598.00、耳机 598.00、机械键盘 499.00。

模板：先聚合到"分组 + 对象"粒度，再在组内编号，最后在外层按编号过滤。

5. 累计、占比、移动平均

5.1 累计求和

WITH daily AS (
    SELECT login_date, COUNT(*) AS 登录次数
    FROM demo_logins
    GROUP BY login_date
)
SELECT login_date AS 日期, 登录次数,
       SUM(登录次数) OVER (
           ORDER BY login_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS 累计登录次数
FROM daily
ORDER BY 日期;

3 月前三天每天都是 4 次，累计为 4、8、12。不写 PARTITION BY 就是全表累计；帧明确写"从分区第一行到当前行"。

5.2 占比

WITH cat_gmv AS (
    SELECT p.category, SUM(i.quantity * i.unit_price) AS gmv
    FROM demo_order_items i
    JOIN demo_orders o ON o.order_id = i.order_id
    JOIN demo_products p ON p.product_id = i.product_id
    WHERE o.status = 'paid'
    GROUP BY p.category
)
SELECT category AS 品类, ROUND(gmv, 2) AS 销售额,
       ROUND(gmv / SUM(gmv) OVER () * 100, 2) AS 占比百分比
FROM cat_gmv
ORDER BY gmv DESC;

电子 92.37%、家居 4.03%、文具 3.60%。SUM(gmv) OVER () 的分母是整张结果表的总和；要算"每月各品类占当月比例"，改成 SUM(gmv) OVER (PARTITION BY 月份)。分子分母必须在同一粒度。

5.3 7 日移动平均

SELECT login_date AS 日期, COUNT(*) AS 日登录次数,
       ROUND(AVG(COUNT(*)) OVER (
           ORDER BY login_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 1) AS 七日平均
FROM demo_logins
GROUP BY login_date
ORDER BY 日期;

6 PRECEDING AND CURRENT ROW 是当前行加前 6 行，共 7 行。玩具数据只有 7 天，窗口逐日扩大；前三天都是 4.0，最后两天降到 3.2、2.9。

注意：ROWS 按物理行移动。日期有缺口时，前 6 行可能跨了不止 7 天；生产上先补全日期，或用 RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW。

6. LAG / LEAD 与同环比

LAG(列, n, 默认值) 取分区内往上数第 n 行（默认 1），LEAD 往下数。首行没有上一行，返回 NULL；做比率时保留 NULL 表示"无法计算"，硬写 0 会让业务误以为"零增长"。

6.1 环比

WITH monthly AS (
    SELECT date_trunc('month', o.created_at) AS 月份,
           SUM(i.quantity * i.unit_price) AS gmv
    FROM demo_orders o
    JOIN demo_order_items i ON i.order_id = o.order_id
    WHERE o.status = 'paid'
    GROUP BY date_trunc('month', o.created_at)
),
lagged AS (
    SELECT 月份, gmv, LAG(gmv) OVER (ORDER BY 月份) AS 上月_gmv
    FROM monthly
)
SELECT 月份, ROUND(gmv, 2) AS GMV, ROUND(上月_gmv, 2) AS 上月GMV,
       ROUND((gmv - 上月_gmv) * 100.0 / 上月_gmv, 2) AS 环比百分比
FROM lagged
ORDER BY 月份;

3 月销售额 2,393.40，4 月 2,025.80，环比 -15.36%；3 月是首月，上月为 NULL，环比也为 NULL。公式：（本期 - 上期）/ 上期 * 100%。

6.2 同比

WITH monthly AS (
    SELECT date_trunc('month', o.created_at) AS 月份,
           SUM(i.quantity * i.unit_price) AS gmv
    FROM demo_orders o
    JOIN demo_order_items i ON i.order_id = o.order_id
    WHERE o.status = 'paid'
    GROUP BY date_trunc('month', o.created_at)
),
lagged AS (
    SELECT 月份, gmv, LAG(gmv, 12) OVER (ORDER BY 月份) AS 去年同月_gmv
    FROM monthly
)
SELECT 月份, ROUND(gmv, 2) AS GMV,
       ROUND((gmv - 去年同月_gmv) * 100.0 / 去年同月_gmv, 2) AS 同比百分比
FROM lagged
ORDER BY 月份;

玩具数据只有两个月，LAG(..., 12) 取不到值，所以同比全部是 NULL。真实数据要先保证月份连续，缺月会取错行；分母为 0 也要单独处理。

7. 连续区间：Gap and Islands

思路叫"差值分组法"：

1. 行为去重成"用户 + 日期"；
2. 同一用户按日期排序、编号；
3. 用日期减去编号；
4. 连续日期的差值相同，这个差值就是连续段编号；
5. 按"用户 + 段编号"分组计数。

日期：      01-01  01-02  01-03  01-05  01-06
行号：         1      2      3      4      5
日期-行号：   -1     -1     -1     -3     -3
                    └─同一段─┘      └─同一段─┘

日期每加一天，行号也加一，连续段差值不变；断档时差值跳变。

用登录日期演示"最长连续登录"：

WITH user_day AS (
    SELECT DISTINCT user_id, login_date
    FROM demo_logins
),
marked AS (
    SELECT user_id, login_date,
           ROW_NUMBER() OVER (
               PARTITION BY user_id ORDER BY login_date
           ) AS rn
    FROM user_day
),
runs AS (
    SELECT user_id,
           login_date - CAST(rn AS INTEGER) AS grp,
           COUNT(*) AS 连续天数
    FROM marked
    GROUP BY user_id, login_date - CAST(rn AS INTEGER)
),
per_user AS (
    SELECT user_id, MAX(连续天数) AS 最长连续
    FROM runs
    GROUP BY user_id
)
SELECT COUNT(*) FILTER (WHERE 最长连续 >= 2) AS "连续>=2天用户数",
       MAX(最长连续) AS "最长连续天数"
FROM per_user;

结果：4 个用户连续登录至少 2 天，最长连续 5 天（用户 6）。

注意：login_date - CAST(rn AS INTEGER) 是 DuckDB 写法；Hive/Spark 用 date_sub(login_date, rn)，MySQL 用 DATE_SUB(login_date, INTERVAL rn DAY)。

8. 为什么窗口函数不能写在 WHERE

-- 错误：WHERE 执行时 rn 还不存在
SELECT i.product_id, i.unit_price,
       ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY i.unit_price DESC) AS rn
FROM demo_order_items i
JOIN demo_products p ON p.product_id = i.product_id
WHERE rn <= 3;

执行顺序中 WHERE 在窗口函数之前。标准解法是套一层 CTE 或子查询：

WITH ranked AS (
    SELECT i.product_id, p.category, i.unit_price,
           ROW_NUMBER() OVER (
               PARTITION BY p.category ORDER BY i.unit_price DESC, i.product_id
           ) AS rn
    FROM demo_order_items i
    JOIN demo_products p ON p.product_id = i.product_id
)
SELECT * FROM ranked WHERE rn <= 3;

DuckDB、Snowflake、BigQuery 还支持 QUALIFY rn <= 3，可以少套一层；但 Hive、Spark SQL、MySQL 不支持，面试请写通用版本。

面试高频：WHERE 筛行，HAVING 筛组，QUALIFY 筛窗口结果，执行位置不同。

9. 大厂变体

9.1 连续 N 个月有下单

同样的模板换成"客户 + 月份"粒度：

WITH customer_month AS (
    SELECT DISTINCT customer_id, date_trunc('month', created_at) AS m
    FROM demo_orders
    WHERE status = 'paid'
),
marked AS (
    SELECT customer_id, m,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY m) AS rn
    FROM customer_month
),
runs AS (
    SELECT customer_id,
           m - CAST(rn AS INTEGER) * INTERVAL 1 MONTH AS grp,
           COUNT(*) AS 连续月数
    FROM marked
    GROUP BY customer_id, m - CAST(rn AS INTEGER) * INTERVAL 1 MONTH
),
per_customer AS (
    SELECT customer_id, MAX(连续月数) AS 最长连续
    FROM runs
    GROUP BY customer_id
)
SELECT COUNT(*) FILTER (WHERE 最长连续 >= 2) AS "连续>=2月客户数",
       MAX(最长连续) AS "最长连续月数"
FROM per_customer;

玩具数据只有 3 月和 4 月，结果是 1 位客户连续 2 个月下单。"连续 3 天登录""连续 3 个月下单"只是换事件粒度和日期单位，模板不变。

9.2 首购月 cohort 回购

按客户首购月份分组，看后续月份是否再次下单：

WITH first_order AS (
    SELECT customer_id, MIN(date_trunc('month', created_at)) AS first_month
    FROM demo_orders
    WHERE status = 'paid'
    GROUP BY customer_id
),
customer_month AS (
    SELECT DISTINCT o.customer_id, f.first_month,
           date_trunc('month', o.created_at) AS order_month
    FROM demo_orders o
    JOIN first_order f ON f.customer_id = o.customer_id
    WHERE o.status = 'paid'
)
SELECT first_month,
       COUNT(DISTINCT customer_id) AS cohort_buyers,
       COUNT(DISTINCT customer_id) FILTER (
           WHERE order_month > first_month
       ) AS repeat_buyers,
       ROUND(COUNT(DISTINCT customer_id) FILTER (
           WHERE order_month > first_month
       ) * 100.0 / COUNT(DISTINCT customer_id), 2) AS repeat_rate_pct
FROM customer_month
GROUP BY first_month
ORDER BY first_month;

2024-03 首购的 3 位客户里，有 1 人在 4 月再次下单，回购率 33.33%。

注意：分母是"首购月 cohort 买家"，分子是同一批买家在后续月份的再次下单。口径写清楚，才叫留存分析。

9.3 履约漏斗

订单时间戳可以做一个粗略漏斗：下单 → 支付 → 签收。

SELECT COUNT(*) AS 下单,
       COUNT(paid_at) AS 已支付,
       COUNT(delivered_at) AS 已签收,
       ROUND(COUNT(paid_at) * 100.0 / COUNT(*), 2) AS 支付率,
       ROUND(COUNT(delivered_at) * 100.0 / COUNT(paid_at), 2) AS 签收率,
       ROUND(COUNT(delivered_at) * 100.0 / COUNT(*), 2) AS 整体履约率
FROM demo_orders;

下单 12、已支付 11、已签收 9；支付率 91.67%、签收率 81.82%、整体履约率 75.00%。这是按时间戳非空判断的粗漏斗，不校验时间先后；严格漏斗需要判断每步时间是否依次递增。

9.4 中位数

WITH ranked AS (
    SELECT amt,
           ROW_NUMBER() OVER (ORDER BY amt) AS rn,
           COUNT(*) OVER () AS n
    FROM (
        SELECT o.order_id, SUM(i.quantity * i.unit_price) AS amt
        FROM demo_orders o
        JOIN demo_order_items i ON i.order_id = o.order_id
        WHERE o.status = 'paid'
        GROUP BY o.order_id
    ) t
)
SELECT ROUND(AVG(amt), 2) AS 订单金额中位数
FROM ranked
WHERE rn IN (FLOOR((n + 1) / 2.0), CEIL((n + 1) / 2.0));

中位数 278.50，平均客单价 441.92。平均值被大额订单拉高，中位数更接近"典型订单"。DuckDB 可直接 median(amt)，其他库常用 PERCENTILE_CONT(0.5)。

10. 在线练习

自测：

• 能解释 ROW_NUMBER、RANK、DENSE_RANK 的差异。
• 能写出“按用户分区、按时间排序”的累计指标。
• 能区分窗口函数和 GROUP BY 的使用场景。

11. 面试延伸

面试题：窗口函数和 GROUP BY 有什么区别？

是什么：GROUP BY 把多行压成每组一行；窗口函数保留原行数，把组内计算结果追加成列。 解决什么问题：需要"明细 + 组内排名/占比/累计/上下行对比"时用窗口；只需要每组一个汇总值时用 GROUP BY。 我的实操边界：项目里用窗口做过分组 TopN、销售额环比和连续月份统计；超大数据量下的窗口性能调优没有生产经验，只知道它会带来 shuffle 和排序开销。

面试题：ROW_NUMBER、RANK、DENSE_RANK 怎么选？

是什么：三者都在组内编号，区别在并列时是否重复、是否跳号。 解决什么问题：只取固定 N 行用 ROW_NUMBER；并列都保留用 RANK / DENSE_RANK；要求稳定结果时给 ROW_NUMBER 加第二排序键。 我的实操边界：分组 TopN 默认用 ROW_NUMBER，并主动说明"并列会被截断"，这是需要和业务确认的口径。

面试题：连续区间的"日期减行号"为什么有效？

是什么：同一用户按日期排序后，连续日期的"日期 - 行号"是同一个常数，断档时常数改变。 解决什么问题：把"找连续区间"转成"按常数分组计数"，避免自关联找相邻行。 我的实操边界：在玩具数据上验证过"连续登录 2 天以上"和"连续下单月份"两种写法；换 Hive 要把日期减法换成 date_sub。

面试题：留存率的口径怎么定？

是什么：留存率 = 同一批新增用户中，D+N 仍有行为的用户数 / 新增用户数。 解决什么问题：衡量产品是否留住用户。必须先定义"新增"（注册还是首次活跃）、"留存"（当天活跃还是 N 日内活跃）和时间窗。 我的实操边界：没有真实产品的登录日志，练习时用"首购月 cohort 的后续月回购率"近似留存（玩具数据里 2024-03 cohort 为 33.33%）；没有生产环境的留存分析经验。

12. 本章验收

• 能说清窗口函数和 GROUP BY 的区别，并举出"明细 + 组内排名"的例子
• 能默写分组 TopN 模板，并解释为什么窗口函数不能直接写在 WHERE 里
• 能独立写出占比、累计求和、7 日移动平均
• 能用 LAG 写出环比和同比，说明首行 NULL 和分母为 0 怎么处理
• 能默写连续区间模板，说出 4 个连续登录用户、1 个连续下单客户分别代表什么
• 能写出连续月、首购月 cohort、履约漏斗、中位数四个变体题
• 7 道在线练习至少独立完成 5 道，并能用嘴讲一遍思路

下一章

SQL 在线刷题路线：去 LeetCode 和牛客按主题刷题，本章的窗口函数对应题单独列了一组。