第 5 周 · 窗口函数

阶段 1 · SQL 核心 · 第 5 周（共 26 周）· 建议起始 2026-10-12 · 预计 7 到 8 小时

本周目标：理解窗口函数与 GROUP BY 的根本区别；会用排名类、偏移类、聚合类三组窗口函数；能写出一组真实的房源特征查询，这组查询在阶段 3 会直接变成模型特征。

这周没有对应讲义：课程不教 SQL 本身，聚合、子查询、CTE、窗口函数全靠下面「补充」里的免费资源。这正是路线要补的最大空白。

练习在网站的「SQL 练习台」里做：双击 启动.command 打开，三张表和数据都预置好了，本周的题在题目下拉里，写完点判题会告诉你对不对、差在哪。

教材：~/Desktop/de/labor/lab1 任务 3 步骤 7（LAG(revenue) OVER (ORDER BY month) 算环比），任务 3 的说明框"LAG 一条查询即可算环比，无需 self-JOIN"；~/Desktop/de/ppt/week03-lecture pre-ea.pdf p.45（Demo 6 提到 ROLLUP / CUBE 与 window functions）；~/Desktop/de/ppt/w02.pdf p.24（提到用 row_number + window 去重）补充：PostgreSQL 文档 Window Functions Tutorial（三页，是最好的免费入门）；LeetCode SQL 50 里标 Window Function 的题

Day 1 · OVER 与 PARTITION BY · 60 分钟

读 15 分钟：PostgreSQL 文档 Window Functions Tutorial 第一节，重点看那个"部门平均工资"的例子
做 45 分钟：
先对比感受根本区别。写两条：
- A：SELECT city_id, AVG(price_huf) FROM listings GROUP BY city_id（15 行）
- B：SELECT listing_id, city_id, price_huf, AVG(price_huf) OVER (PARTITION BY city_id) FROM listings（50 万行）
- 在 notes/sql.md 里写一句话：为什么 B 的行数没有减少
用 B 那个模式写：每条房源带上它所在城市的平均价、以及它和城市均价的差额
不加 PARTITION BY 试一次：AVG(price_huf) OVER ()，理解空窗口等于全表
加 PARTITION BY city_id, status，理解多列分区

产出：sql/w05_over.sql；notes/sql.md 里那句解释 卡住时：一句话记法，GROUP BY 把一组压成一行，窗口函数让每一行看到它所在组的信息

Day 2 · 排名：ROW_NUMBER、RANK、DENSE_RANK · 60 分钟

读 15 分钟：PostgreSQL 文档 Window Functions Tutorial 第二节
做 45 分钟：
三个函数写在同一条查询里对比：按价格给每个城市的房源排名，ROW_NUMBER()、RANK()、DENSE_RANK() 三列并排
手动插入两条同价格的房源，再跑一次，观察三者在并列时的差别，写进 notes/sql.md
补上 W04 Day 4 留空的第四种写法：用 ROW_NUMBER() OVER (PARTITION BY listing_id ORDER BY observed_at DESC) 加外层 WHERE rn = 1 取每个房源最新价格。和上周三种写法对比行数和耗时
写：每个城市价格最高的 3 套 active 房源（这是评估第二批第 3 题的原题）

产出：sql/w05_ranking.sql，含 W04 那题的窗口函数版 卡住时：WHERE rn = 1 直接写在同一层会报错，想想窗口函数在执行顺序里排在 WHERE 之后（回顾 W03 Day 2 的执行顺序图）

Day 3 · 偏移：LAG 与 LEAD · 60 分钟

读 15 分钟：~/Desktop/de/labor/lab1 任务 3 步骤 7 的完整 SQL 和它下面的说明框
做 45 分钟：
仿照那个例子写月度环比：每月房源数、总价值、上月总价值、环比百分比
在 price_history 上写：每条价格记录带上它的上一次价格，和变化额、变化百分比
用这个算出：每个房源的降价次数（上一次价格比当前高的记录数）。这比 W04 Day 2 用相关子查询做的版本干净得多，对比一下两版
用 LEAD 写：每条价格记录到下一次变价之间隔了多少天
注意 LAG 的第一行是 NULL，用 COALESCE 或者在外层过滤处理

产出：sql/w05_lag_lead.sql 卡住时：环比算出来全是 NULL，检查 OVER 里有没有写 ORDER BY，以及 PARTITION BY 是不是分得太细了

Day 4 · 聚合窗口与累计 · 60 分钟

读 15 分钟：PostgreSQL 文档 Window Functions Tutorial 第三节（窗口帧 frame 的部分）
做 45 分钟：
累计求和：按挂牌日期排序，算出累计房源数（COUNT(*) OVER (ORDER BY listed_at)）
理解默认帧：ORDER BY 存在时默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，所以是累计而不是全部
显式写帧，做 7 日移动平均价：AVG(price_huf) OVER (ORDER BY listed_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
对比 ROWS 和 RANGE 的差别（提示：同一天有多条记录时两者不同）
写一条：每个城市内，每条房源的价格在该城市的百分位（PERCENT_RANK() 或 CUME_DIST()）

产出：sql/w05_frames.sql 卡住时：移动平均的结果看起来不对，先只取一个城市、按日期排序、把窗口列和原始列并排打印出来，一行一行手算验证前 10 行

Day 5 · 房源特征查询集 · 60 分钟

今天产出的东西会在阶段 3 变成真正的模型特征，认真做。

读 15 分钟：回顾 roadmap.md 里阶段 3 任务 3 那一条，看要做哪些特征
做 45 分钟，写一个 sql/w05_features.sql，每条房源一行，包含这些列：
基础：listing_id、city_name、area_m2、rooms、price_huf、每平米单价
城市对比：所在城市的中位单价、本房源单价相对城市中位数的偏离百分比
价格历史：首次观测价、最新观测价、总降价幅度、最近 30 天降价次数
时间：在市天数（从 listed_at 到今天或到成交日）
排名：在所在城市内按价格的百分位
全部用 CTE 组织，一层一个主题，最后一个 SELECT 拼起来

产出：sql/w05_features.sql，一条能跑出完整特征表的查询 卡住时：不要试图一条 SELECT 写完。先写五个独立的 CTE，每个只算一组特征，最后 JOIN 起来。分步调试

周末 · 整合与 LeetCode · 2 到 3 小时

LeetCode SQL 50 里所有标 Window Function 的题做完，卡住的记进 notes/sql.md
把 sql/w05_features.sql 的结果导出成 Parquet（用 DuckDB 的 COPY ... TO ... (FORMAT PARQUET)），存到 data/features/。用 pandas 读回来看一眼形状
用这份特征表在 notebook 里跑一个最简单的 LightGBM，目标是预测 price_huf。不追求精度，目的是确认这份表真的能喂给模型（这是阶段 2 的预演）
git commit
闭卷 10 分钟：写出"每个城市里价格最高的 3 套 active 房源，输出 city_name、listing_id、price_huf"。这是评估第二批第 3 题的原题，上次的回答是"不会"

产出：data/features/ 下的 Parquet；一个跑通的 LightGBM notebook；闭卷那条查询

本周验收

能用一句话说清窗口函数和 GROUP BY 的区别，并举出行数差异
ROW_NUMBER、RANK、DENSE_RANK 在并列时的差别能说清
LAG 算环比能不看参考写出来
sql/w05_features.sql 能跑出完整特征表，并成功喂给 LightGBM
闭卷写对评估第二批第 3 题

给 Claude 的会话提示

本周对应待复习清单第 7 条（窗口函数），这是评估里完全空白的一块，他当时用"三层 MAX"替代 top-3。

Day 1 的 A / B 对比必须让他自己跑，看到 15 行对 50 万行的差异，这个直观冲击是理解窗口函数的关键。不要先讲定义。

Day 2 补上 W04 留空的第四种写法，让他对比四种写法，这是设计好的闭环。他应该能感受到窗口函数版最通用。

Day 5 的特征查询集是本周的真正产出，也是他最有动力的部分（直接对应他实习在做的事）。如果时间紧，宁可压缩 Day 4 的帧语法也要保住 Day 5。帧语法在实践中用得少，遇到再查即可。

周末的 LightGBM 只是打通链路，不要让他陷进调参。他是 ML 方向的学生，容易在这里花掉两小时。明确告诉他：跑通就行，精度无所谓，这周的主题是 SQL。

常见错误：WHERE rn = 1 写在同一层（必须套一层子查询或 CTE）；OVER 里忘了 ORDER BY 导致 LAG 返回 NULL；PARTITION BY 和 GROUP BY 混用导致行数不对。

结束时在 PROGRESS.md 里记：闭卷题结果、特征查询是否跑通、LeetCode 窗口题进度。清掉待复习清单第 7 条（如果掌握了）。

参考答案（自己写完之后再展开）

第 5 周答案 · 窗口函数

这份答案在你自己写完之后再看。先写、再对，顺序反了就没有效果。

每题给的是参考写法，不是唯一正确写法。你的写法只要结果对、可读性不差，就算对。

Day 1 · OVER 与 PARTITION BY

题目：写 A、B 两条对比查询，解释为什么 B 的行数没有减少

-- A：15 行（每个城市一行）
SELECT l.city_id,
       AVG(l.price_huf) AS avg_price_huf
FROM listings AS l
GROUP BY l.city_id;

-- B：50 万行（每条房源一行）
SELECT l.listing_id,
       l.city_id,
       l.price_huf,
       AVG(l.price_huf) OVER (PARTITION BY l.city_id) AS city_avg_price
FROM listings AS l;

解释：GROUP BY 把一组多行折叠成一行，组内的明细信息就丢了。窗口函数不折叠任何行，它只是给每一行多算一列，这一列的值来自"这一行所属的窗口"。PARTITION BY city_id 定义窗口是"同城市的所有行"，所以每一行都能看到自己城市的均价，同时保留自己的明细。一句话：GROUP BY 是压缩，窗口函数是广播。

常见错法：以为可以用 GROUP BY 达到同样效果，写成 SELECT listing_id, AVG(price_huf) FROM listings GROUP BY city_id。报 column "listings.listing_id" must appear in the GROUP BY clause（待复习清单第 3 条）。这个报错本身就是提示：你想同时要明细和聚合，而 GROUP BY 只能给你一个，需要的正是窗口函数。

题目：每条房源带上它所在城市的平均价，以及它和城市均价的差额

SELECT l.listing_id,
       c.city_name,
       l.price_huf,
       ROUND(AVG(l.price_huf) OVER (PARTITION BY l.city_id))               AS city_avg_price,
       l.price_huf - ROUND(AVG(l.price_huf) OVER (PARTITION BY l.city_id)) AS diff_from_city_avg
FROM listings AS l
JOIN cities AS c ON c.city_id = l.city_id
WHERE l.status = 'active'
ORDER BY diff_from_city_avg DESC;

窗口写两遍很啰嗦，用命名窗口（WINDOW 子句）更干净：

SELECT l.listing_id,
       c.city_name,
       l.price_huf,
       ROUND(AVG(l.price_huf) OVER w)               AS city_avg_price,
       l.price_huf - ROUND(AVG(l.price_huf) OVER w) AS diff_from_city_avg
FROM listings AS l
JOIN cities AS c ON c.city_id = l.city_id
WHERE l.status = 'active'
WINDOW w AS (PARTITION BY l.city_id)
ORDER BY diff_from_city_avg DESC;

为什么这样写：WINDOW w AS (...) 定义一次，OVER w 复用，改窗口定义只需改一处。WINDOW 子句写在 HAVING 之后、ORDER BY 之前。

常见错法：在同一层想给 diff_from_city_avg 起别名再用它算别的，比如 WHERE diff_from_city_avg > 0。别名在 WHERE 里不存在（W03 Day 2），而且窗口函数的执行位置在 WHERE 之后，所以窗口函数的结果无论如何都不能在同层的 WHERE 里过滤，必须套一层 CTE 或子查询。明天会正面碰到这一点。

注意这里的 WHERE l.status = 'active' 会影响窗口：窗口只看 WHERE 之后剩下的行，所以 city_avg_price 是"该城市 active 房源的均价"，不是全部房源的均价。这是对的语义，但要有意识。

题目：不加 `PARTITION BY` 试一次，`AVG(price_huf) OVER ()`

SELECT l.listing_id,
       l.price_huf,
       ROUND(AVG(l.price_huf) OVER ()) AS global_avg_price,
       ROUND(100.0 * l.price_huf / NULLIF((AVG(l.price_huf) OVER ())::NUMERIC, 0), 1) AS pct_of_global_avg
FROM listings AS l;

为什么这样写：空窗口 OVER () 的意思是"整个结果集是一个窗口"，等价于把全表当一组。OVER 后面的括号不能省，AVG(price_huf) OVER 是语法错误。AVG 对 bigint 返回 numeric，这里显式 ::NUMERIC 只是为了让 NULLIF 和 ROUND(..., 1) 的类型清晰。

常见错法：以为 OVER () 和不写窗口函数（普通 AVG）一样。普通 AVG(price_huf) 是聚合，会把结果压成一行；AVG(price_huf) OVER () 保留全部行，只是每行都拿到同一个全局值。

题目：加 `PARTITION BY city_id, status`，理解多列分区

SELECT l.listing_id,
       c.city_name,
       l.status,
       l.price_huf,
       COUNT(*)         OVER (PARTITION BY l.city_id, l.status) AS peer_count,
       ROUND(AVG(l.price_huf) OVER (PARTITION BY l.city_id, l.status)) AS peer_avg_price
FROM listings AS l
JOIN cities AS c ON c.city_id = l.city_id
ORDER BY c.city_name, l.status, l.price_huf DESC;

为什么这样写：多列分区就是"这几列的组合相同的行算同一个窗口"，和 GROUP BY 多列的分组规则一致。这里每一行看到的是"同城市同状态"的同伴数量和均价，粒度比上一题细。

常见错法：把 PARTITION BY 和 GROUP BY 写在同一条查询里而不想清楚先后。它们可以共存（窗口函数在分组之后执行，此时"行"已经是分组结果），但那时窗口看到的是组，不是原始行。比如 SELECT city_id, COUNT(*) AS cnt, SUM(COUNT(*)) OVER () AS total FROM listings GROUP BY city_id 是合法且常用的（算占比），但 SELECT city_id, AVG(price_huf) OVER (PARTITION BY city_id) FROM listings GROUP BY city_id 会报错，因为 price_huf 在分组后不再是可用的单值。

Day 2 · 排名：ROW_NUMBER、RANK、DENSE_RANK

题目：三个函数写在同一条查询里对比，按价格给每个城市的房源排名

SELECT c.city_name,
       l.listing_id,
       l.price_huf,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense_rnk
FROM listings AS l
JOIN cities AS c ON c.city_id = l.city_id
WHERE l.status = 'active'
WINDOW w AS (PARTITION BY l.city_id ORDER BY l.price_huf DESC)
ORDER BY c.city_name, rn
LIMIT 60;

为什么这样写：三个函数共用一个窗口定义，所以用 WINDOW w，保证三列的排序规则绝对一致（分开写很容易某一列漏个 DESC，然后对比毫无意义）。排名类函数的 OVER 里必须有 ORDER BY，没有排序就没有"第几名"这个概念。

常见错法：ROW_NUMBER() OVER (PARTITION BY l.city_id) 不写 ORDER BY。这条能跑，但编号是任意顺序的，两次执行可能不同，用它取 top-1 就变成了"随机取一行"。这是待复习清单里最隐蔽的一条：不报错，结果不稳定。

题目：手动插入两条同价格的房源，观察三者在并列时的差别

INSERT INTO listings (listing_id, city_id, area_m2, rooms, price_huf, listed_at, status)
VALUES (9990001, 1, 60, 2, 99000000, DATE '2025-07-01', 'active'),
       (9990002, 1, 62, 2, 99000000, DATE '2025-07-01', 'active');

结论（假设城市 1 里这两条并列第 2 高）：

函数	并列时的编号	下一名的编号	用途
`ROW_NUMBER()`	2, 3（强行分先后）	4	每组取一行、分页、去重
`RANK()`	2, 2	4（跳号）	竞赛式排名，"并列第 2 没有第 3 名"
`DENSE_RANK()`	2, 2	3（不跳号）	取"前 N 个不同的值"（前 3 个价位）

为什么这个实验重要：ROW_NUMBER 是唯一保证"每组恰好 N 行"的函数，所以取 top-1 或 top-N 且要求行数确定时用它；但它在并列时的取舍是任意的，除非你在 ORDER BY 里加第二个键（比如 ORDER BY price_huf DESC, listing_id）把并列打破。RANK 会让 top-3 可能返回 4 行甚至更多，这不一定是错，取决于需求。做完实验删掉这两条：

DELETE FROM listings WHERE listing_id IN (9990001, 9990002);

常见错法：用 RANK() <= 3 实现"每城市恰好 3 套"，然后在下游看到某城市 5 行时以为查询错了。函数选错不是 bug，是需求没定义清楚。

题目：补上 W04 Day 4 留空的第四种写法，用 ROW_NUMBER 取每个房源最新价格

WITH ranked AS (
    SELECT ph.listing_id,
           ph.observed_at,
           ph.price_huf,
           ROW_NUMBER() OVER (PARTITION BY ph.listing_id
                              ORDER BY ph.observed_at DESC, ph.price_huf DESC) AS rn
    FROM price_history AS ph
)
SELECT r.listing_id,
       r.observed_at,
       r.price_huf
FROM ranked AS r
WHERE r.rn = 1;

为什么这样写：WHERE rn = 1 必须在外层。窗口函数在逻辑执行顺序里位于 WHERE 之后（回看 W03 Day 2 那张表），所以同一层的 WHERE 看不到 rn。套一层 CTE 或子查询，让窗口函数先算完，外层才能过滤。ORDER BY 里的第二个键 price_huf DESC 是打破并列用的，有了它，即使同一天两条记录也只返回一行。

和另外三种写法对比：这一版和 DISTINCT ON 一样保证每组一行，但它是标准 SQL（DISTINCT ON 只有 PostgreSQL 有），而且改成 top-3 只要把 = 1 改成 <= 3，扩展性最好。代价是它需要对整个分区排序，在没有合适索引时通常比 DISTINCT ON 略慢一点。四种写法里，面试答这一种最稳。

常见错法：

-- 报错：column "rn" does not exist
SELECT ph.listing_id, ph.observed_at, ph.price_huf,
       ROW_NUMBER() OVER (PARTITION BY ph.listing_id ORDER BY ph.observed_at DESC) AS rn
FROM price_history ph
WHERE rn = 1;

这是待复习清单第 7 条最常见的翻车方式。另一个变体是把它写进 HAVING，同样不行，HAVING 也在窗口函数之前。

题目：每个城市价格最高的 3 套 active 房源

（这是 2026-09-10 评估第二批第 3 题的原题，当时的回答是"不会"，也是阶段 1 复测第 5 题。）

WITH ranked AS (
    SELECT c.city_name,
           l.listing_id,
           l.price_huf,
           ROW_NUMBER() OVER (PARTITION BY l.city_id
                              ORDER BY l.price_huf DESC, l.listing_id) AS rn
    FROM listings AS l
    JOIN cities AS c ON c.city_id = l.city_id
    WHERE l.status = 'active'
)
SELECT r.city_name,
       r.listing_id,
       r.price_huf
FROM ranked AS r
WHERE r.rn <= 3
ORDER BY r.city_name, r.price_huf DESC;

为什么这样写：三个关键点。WHERE l.status = 'active' 放在内层（先过滤再排名，否则排名里混着 sold 房源，第 3 名可能是个已成交的）。PARTITION BY l.city_id 而不是 city_name（同名城市不合并，理由同 W03 双列分组）。WHERE r.rn <= 3 放外层。评估里用"三层 MAX"替代这个模式，那种写法在第 4 名开始就无法扩展，而这一版把 3 改成 10 就是 top-10。

常见错法：把 status 过滤写在外层：FROM ranked r WHERE r.rn <= 3 AND r.status = 'active'。这时排名是在全部房源上算的，某城市前 3 名如果都已成交，这个城市在结果里就是 0 行，而正确答案应该给出该城市 active 房源里最高的 3 套。过滤和排名的先后顺序会改答案，这类问题在实际工作里比语法错更常见。

Day 3 · 偏移：LAG 与 LEAD

题目：月度环比，每月房源数、总价值、上月总价值、环比百分比

WITH monthly AS (
    SELECT DATE_TRUNC('month', l.listed_at)::DATE AS month,
           COUNT(*)                               AS listing_count,
           SUM(l.price_huf)                       AS total_value_huf
    FROM listings AS l
    WHERE l.listed_at IS NOT NULL
    GROUP BY DATE_TRUNC('month', l.listed_at)
)
SELECT m.month,
       m.listing_count,
       m.total_value_huf,
       LAG(m.total_value_huf) OVER (ORDER BY m.month) AS prev_total_value_huf,
       ROUND(100.0 * (m.total_value_huf - LAG(m.total_value_huf) OVER (ORDER BY m.month))
             / NULLIF(LAG(m.total_value_huf) OVER (ORDER BY m.month), 0), 2) AS value_mom_pct
FROM monthly AS m
ORDER BY m.month;

同一个 LAG 写了三遍，拆成两层更好读：

WITH monthly AS (
    SELECT DATE_TRUNC('month', l.listed_at)::DATE AS month,
           COUNT(*)         AS listing_count,
           SUM(l.price_huf) AS total_value_huf
    FROM listings AS l
    WHERE l.listed_at IS NOT NULL
    GROUP BY DATE_TRUNC('month', l.listed_at)
),
with_prev AS (
    SELECT m.month,
           m.listing_count,
           m.total_value_huf,
           LAG(m.total_value_huf) OVER (ORDER BY m.month) AS prev_total_value_huf,
           LAG(m.listing_count)   OVER (ORDER BY m.month) AS prev_listing_count
    FROM monthly AS m
)
SELECT wp.month,
       wp.listing_count,
       ROUND(100.0 * (wp.listing_count - wp.prev_listing_count)
             / NULLIF(wp.prev_listing_count, 0), 2) AS count_mom_pct,
       wp.total_value_huf,
       ROUND(100.0 * (wp.total_value_huf - wp.prev_total_value_huf)
             / NULLIF(wp.prev_total_value_huf, 0), 2) AS value_mom_pct
FROM with_prev AS wp
ORDER BY wp.month;

为什么这样写：和 W04 Day 3 的自连接版本比，LAG 版少了一次 JOIN，也不用操心 INTERVAL '1 month' 的类型转换，PostgreSQL 只需要对 monthly 排一次序。Lab1 任务 3 的说明框说的就是这件事：一条查询即可算环比，无需 self-JOIN。

有一个语义差别值得注意：自连接版本按"日期减一个月"找上月，如果某个月完全没有数据，那一行会是 NULL；LAG 版本按"结果集里的前一行"取，缺月会被跳过，2 月的上月可能是去年 12 月。要严格按日历月，就得先用 generate_series 造出完整的月份序列再左连接。做时间序列时这个坑很常见。

常见错法：LAG(m.total_value_huf) OVER ()，OVER 里没有 ORDER BY。这时"前一行"没有定义，结果不稳定，通常全是 NULL 或者顺序乱掉。本周计划里"环比全是 NULL 就检查 OVER 里有没有 ORDER BY"说的就是这个。

题目：在 price_history 上，每条价格记录带上它的上一次价格、变化额、变化百分比

SELECT ph.listing_id,
       ph.observed_at,
       ph.price_huf,
       LAG(ph.price_huf) OVER w                                AS prev_price_huf,
       ph.price_huf - LAG(ph.price_huf) OVER w                  AS change_huf,
       ROUND(100.0 * (ph.price_huf - LAG(ph.price_huf) OVER w)
             / NULLIF(LAG(ph.price_huf) OVER w, 0), 2)          AS change_pct
FROM price_history AS ph
WINDOW w AS (PARTITION BY ph.listing_id ORDER BY ph.observed_at)
ORDER BY ph.listing_id, ph.observed_at
LIMIT 100;

为什么这样写：PARTITION BY listing_id 是必须的，否则"上一行"会跨到别的房源上，算出毫无意义的价格变化。ORDER BY observed_at 用升序，因为"上一次"指时间更早的那次。

常见错法：ORDER BY ph.observed_at DESC 配 LAG。这时 LAG 取到的是时间上更晚的记录，change_huf 的符号全反了，而结果看起来完全正常。写完先挑一个房源把 observed_at、price_huf、prev_price_huf 三列并排打印，人工核对两三行，这个习惯能省掉很多返工。

题目：用 LAG 算出每个房源的降价次数，和 W04 Day 2 的相关子查询版本对比

WITH changes AS (
    SELECT ph.listing_id,
           ph.observed_at,
           ph.price_huf,
           LAG(ph.price_huf) OVER (PARTITION BY ph.listing_id
                                   ORDER BY ph.observed_at) AS prev_price_huf
    FROM price_history AS ph
)
SELECT ch.listing_id,
       COUNT(*) FILTER (WHERE ch.price_huf < ch.prev_price_huf) AS price_drop_count,
       COUNT(*) FILTER (WHERE ch.price_huf > ch.prev_price_huf) AS price_rise_count
FROM changes AS ch
GROUP BY ch.listing_id
HAVING COUNT(*) FILTER (WHERE ch.price_huf < ch.prev_price_huf) > 0
ORDER BY price_drop_count DESC;

为什么这样写：LAG 只需要一次分区排序就能给每一行配上"上一次价格"，之后就是普通的条件聚合。W04 Day 2 那版对每一行都要跑一次带 ORDER BY ... LIMIT 1 的相关子查询，150 万行就是 150 万次。两版都跑一次 EXPLAIN ANALYZE，把耗时记进 notes/sql.md，这是本周最有说服力的一组数字。

注意第一行的 prev_price_huf 是 NULL，而 NULL < NULL 的结果是 NULL，FILTER (WHERE ...) 只计入结果为 true 的行，所以第一行自动被排除，正好符合"第一次观测不算降价"的语义。这里不需要 COALESCE，NULL 帮了忙。

常见错法：COALESCE(LAG(ph.price_huf) OVER w, 0)，把第一行的上一次价格填成 0。这样每个房源的第一条记录都会被判成"涨价"（当前价大于 0），price_rise_count 全部虚高 1。填默认值之前先想清楚 NULL 在这里代表什么。

题目：用 LEAD 写出每条价格记录到下一次变价之间隔了多少天

SELECT ph.listing_id,
       ph.observed_at,
       ph.price_huf,
       LEAD(ph.observed_at) OVER w                     AS next_observed_at,
       LEAD(ph.observed_at) OVER w - ph.observed_at    AS days_until_next,
       COALESCE(LEAD(ph.observed_at) OVER w, CURRENT_DATE) - ph.observed_at AS days_held
FROM price_history AS ph
WINDOW w AS (PARTITION BY ph.listing_id ORDER BY ph.observed_at)
ORDER BY ph.listing_id, ph.observed_at
LIMIT 100;

为什么这样写：LEAD 是 LAG 的镜像，取窗口内后一行。DATE - DATE 返回整数天数。最后一列演示了 COALESCE 的正确用法：最后一条记录没有"下一次"，但"这个价格挂了多少天"仍然有意义，用今天当右端点是合理的假设（前提是这个房源还在市）。这一列在阶段 3 会变成"当前价格已持续天数"这个特征。

常见错法：LEAD(ph.observed_at, 1, CURRENT_DATE) OVER w 想用第三个参数给默认值。语法是对的（LEAD(表达式, 偏移量, 默认值)），但要注意默认值的类型必须和表达式一致，而且 CURRENT_DATE 在这里会让"最后一行"和"真的有下一行且恰好是今天"变得无法区分。要区分就保留 NULL，另外加一列布尔标记。

Day 4 · 聚合窗口与累计

题目：累计求和，按挂牌日期排序算出累计房源数

SELECT l.listed_at,
       COUNT(*)                                     AS daily_count,
       SUM(COUNT(*)) OVER (ORDER BY l.listed_at)    AS cumulative_count
FROM listings AS l
WHERE l.listed_at IS NOT NULL
GROUP BY l.listed_at
ORDER BY l.listed_at;

为什么这样写：这里 GROUP BY 和窗口函数共存：先按天分组算出每天的量，窗口函数再在"分组结果"上累加，所以是 SUM(COUNT(*))，读作"对每日计数求累计和"。这个嵌套看起来奇怪，但它是合法且常用的，因为窗口函数在 GROUP BY 之后执行。

不分组的版本（每条房源一行，带累计）：

SELECT l.listing_id,
       l.listed_at,
       COUNT(*) OVER (ORDER BY l.listed_at) AS cumulative_count
FROM listings AS l
WHERE l.listed_at IS NOT NULL
ORDER BY l.listed_at, l.listing_id;

常见错法：SUM(COUNT(*)) 不加 OVER 就变成聚合嵌套聚合，报 aggregate function calls cannot be nested。加了 OVER 之后外层就不是聚合而是窗口函数，所以合法。这个区别值得记：OVER 是"这是窗口函数"的标记。

题目：理解默认帧

ORDER BY 存在时，默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是"从分区开头到当前行"，所以是累计而不是全部。ORDER BY 不存在时（比如 OVER (PARTITION BY city_id)），默认帧是整个分区，所以 Day 1 那个 AVG(...) OVER (PARTITION BY city_id) 拿到的是全城市均价而不是累计均价。

验证一下：

SELECT l.listed_at,
       COUNT(*)                                   AS daily_count,
       SUM(COUNT(*)) OVER (ORDER BY l.listed_at)  AS cumulative_default_frame,
       SUM(COUNT(*)) OVER ()                      AS grand_total
FROM listings AS l
WHERE l.listed_at IS NOT NULL
GROUP BY l.listed_at
ORDER BY l.listed_at;

为什么值得记：很多人第一次写 AVG(x) OVER (ORDER BY d) 想要"全期均值"，拿到的却是"累计均值"，然后怀疑数据有问题。只要 OVER 里出现了 ORDER BY，就默认变成了累计视角。想要"全分区"又要排序，得显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

常见错法：默认帧是 RANGE 而不是 ROWS，两者在有并列值时不同（下面一题）。RANGE UNBOUNDED PRECEDING AND CURRENT ROW 会把和当前行排序键相同的所有行都算进来，所以同一天的多条房源拿到的累计值是一样的，都是"到这一天为止的总数"。这通常正是你想要的，但要知道它就是这个原因。

题目：显式写帧，做 7 日移动平均价

WITH daily AS (
    SELECT l.listed_at            AS day,
           AVG(l.price_huf)       AS daily_avg_price,
           COUNT(*)               AS daily_count
    FROM listings AS l
    WHERE l.city_id = 1
      AND l.listed_at IS NOT NULL
    GROUP BY l.listed_at
)
SELECT d.day,
       d.daily_count,
       ROUND(d.daily_avg_price)   AS daily_avg_price,
       ROUND(AVG(d.daily_avg_price) OVER (
                 ORDER BY d.day
                 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
             )) AS ma7_by_rows
FROM daily AS d
ORDER BY d.day;

为什么这样写：移动平均必须先把数据降到"一天一行"的粒度，否则 ROWS BETWEEN 6 PRECEDING 数的是前 6 条房源，不是前 6 天。先按天聚合再开窗，这是时间序列特征的标准做法。

ROWS 版本还有一个隐患：如果中间有几天完全没有房源（周末、爬虫故障），那几天在结果里不存在，6 PRECEDING 就跨到更早的日期上，窗口实际覆盖 10 天。真的要"日历上的 7 天"，用带 INTERVAL 的 RANGE（PostgreSQL 11 起支持）：

SELECT l.listed_at,
       ROUND(AVG(l.price_huf) OVER (
                 ORDER BY l.listed_at
                 RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
             )) AS ma7_by_range
FROM listings AS l
WHERE l.city_id = 1
  AND l.listed_at IS NOT NULL
ORDER BY l.listed_at
LIMIT 50;

常见错法：ROWS BETWEEN 7 PRECEDING AND CURRENT ROW 想做 7 日均值。这个窗口包含 8 行（前 7 加当前）。"N 日移动平均"的帧是 N-1 PRECEDING AND CURRENT ROW，差一错在这里特别容易犯，验证办法是手工核对前 10 行。

题目：对比 `ROWS` 和 `RANGE` 的差别

SELECT l.listing_id,
       l.listed_at,
       l.price_huf,
       COUNT(*) OVER (ORDER BY l.listed_at
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS cum_rows,
       COUNT(*) OVER (ORDER BY l.listed_at
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_range
FROM listings AS l
WHERE l.city_id = 1
  AND l.listed_at IS NOT NULL
ORDER BY l.listed_at, l.listing_id
LIMIT 30;

结论：ROWS 按物理行数计算帧，所以同一天的三条房源会得到 1、2、3 三个不同的累计值（谁在前取决于排序的不确定部分）。RANGE 按排序键的值计算帧，同一天的三条都被视为"当前行的同伴"，三行拿到同一个累计值。要做"截至某日累计"就用 RANGE，要做"最近 N 条"就用 ROWS。

常见错法：做累计报表时用 ROWS，然后发现同一天的几行数字不一样，把它当成数据错误去查。这不是错误，是帧类型选错了。

题目：每个城市内，每条房源的价格在该城市的百分位

SELECT c.city_name,
       l.listing_id,
       l.price_huf,
       ROUND((PERCENT_RANK() OVER w)::NUMERIC, 4) AS pct_rank,
       ROUND((CUME_DIST()    OVER w)::NUMERIC, 4) AS cume_dist,
       NTILE(4)             OVER w              AS price_quartile
FROM listings AS l
JOIN cities AS c ON c.city_id = l.city_id
WHERE l.status = 'active'
WINDOW w AS (PARTITION BY l.city_id ORDER BY l.price_huf)
ORDER BY c.city_name, l.price_huf
LIMIT 60;

为什么这样写：三个函数的定义不同，别混用。PERCENT_RANK() 等于 (rank - 1) / (总行数 - 1)，最便宜的那套是 0，最贵的是 1。CUME_DIST() 等于"值小于等于当前行的行数 / 总行数"，最贵的是 1，最便宜的不是 0。NTILE(4) 把分区切成 4 个尽量等大的桶，返回 1 到 4，做分位数特征时最直接。这三个都返回 double precision（NTILE 返回整数），要用两参数 ROUND 得先转 NUMERIC。

常见错法：拿 PERCENT_RANK 当"比多少百分比的房源贵"来解释。它的分母是"总行数减 1"，语义是排名的相对位置，不是分布的累积概率。要后者用 CUME_DIST。给模型做特征时这两个都能用，但报告里写文字描述时必须说对。

Day 5 · 房源特征查询集

题目：写一条 `sql/w05_features.sql`，每条房源一行，包含基础、城市对比、价格历史、时间、排名五组特征

WITH base AS (
    SELECT l.listing_id,
           l.city_id,
           c.city_name,
           c.county,
           l.area_m2,
           l.rooms,
           l.price_huf,
           l.listed_at,
           l.status,
           l.price_huf / NULLIF(l.area_m2, 0) AS price_per_m2
    FROM listings AS l
    JOIN cities AS c ON c.city_id = l.city_id
),
city_stats AS (
    SELECT b.city_id,
           (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY b.price_per_m2))::NUMERIC
               AS city_median_per_m2
    FROM base AS b
    WHERE b.price_per_m2 IS NOT NULL
    GROUP BY b.city_id
),
hist AS (
    SELECT ph.listing_id,
           ph.observed_at,
           ph.price_huf,
           LAG(ph.price_huf) OVER (PARTITION BY ph.listing_id
                                   ORDER BY ph.observed_at)         AS prev_price_huf,
           FIRST_VALUE(ph.price_huf) OVER (PARTITION BY ph.listing_id
                                           ORDER BY ph.observed_at) AS first_price_huf,
           ROW_NUMBER() OVER (PARTITION BY ph.listing_id
                              ORDER BY ph.observed_at DESC)         AS rn_latest
    FROM price_history AS ph
),
hist_agg AS (
    SELECT h.listing_id,
           MAX(h.first_price_huf)                        AS first_price_huf,
           MAX(h.price_huf) FILTER (WHERE h.rn_latest = 1) AS latest_price_huf,
           MAX(h.observed_at)                            AS last_observed_at,
           COUNT(*) FILTER (WHERE h.price_huf < h.prev_price_huf) AS drop_count_total,
           COUNT(*) FILTER (WHERE h.price_huf < h.prev_price_huf
                              AND h.observed_at >= CURRENT_DATE - 30) AS drop_count_30d
    FROM hist AS h
    GROUP BY h.listing_id
),
ranked AS (
    SELECT b.listing_id,
           PERCENT_RANK() OVER (PARTITION BY b.city_id
                                ORDER BY b.price_huf) AS city_price_pct_rank
    FROM base AS b
    WHERE b.status = 'active'
)
SELECT b.listing_id,
       b.city_name,
       b.area_m2,
       b.rooms,
       b.price_huf,
       ROUND(b.price_per_m2)                                     AS price_per_m2,
       ROUND(cs.city_median_per_m2)                              AS city_median_per_m2,
       ROUND(100.0 * (b.price_per_m2 - cs.city_median_per_m2)
             / NULLIF(cs.city_median_per_m2, 0), 1)              AS deviation_from_median_pct,
       ha.first_price_huf,
       ha.latest_price_huf,
       ha.first_price_huf - ha.latest_price_huf                  AS total_drop_huf,
       COALESCE(ha.drop_count_30d, 0)                            AS drop_count_30d,
       CASE
           WHEN b.status = 'sold'
               THEN COALESCE(ha.last_observed_at, b.listed_at) - b.listed_at
           ELSE CURRENT_DATE - b.listed_at
       END                                                       AS days_on_market,
       ROUND(r.city_price_pct_rank::NUMERIC, 4)                  AS city_price_pct_rank,
       b.status
FROM base AS b
LEFT JOIN city_stats AS cs ON cs.city_id   = b.city_id
LEFT JOIN hist_agg   AS ha ON ha.listing_id = b.listing_id
LEFT JOIN ranked     AS r  ON r.listing_id  = b.listing_id
ORDER BY b.listing_id;

为什么这样写：五个决定值得记住。

第一，每个 CTE 只算一组特征，最后一个 SELECT 只做拼装。调试时可以把 SELECT * FROM hist_agg LIMIT 10 单独跑，确认这一层对了再往下。

第二，全部用 LEFT JOIN 拼回 base。特征表的粒度是"每条房源一行"，用 JOIN 会让没有价格历史的房源整条消失，行数和 listings 不一致，喂给模型时你会以为数据少了。特征缺失应该表现为 NULL，不是表现为少一行。

第三，hist 层用了三个窗口函数（LAG、FIRST_VALUE、ROW_NUMBER）算在同一次扫描里，然后 hist_agg 层把它压成每房源一行。MAX(h.first_price_huf) 看起来奇怪，但 FIRST_VALUE 在整个分区里是同一个值，用 MAX 只是为了让它通过 GROUP BY 的检查。MAX(price_huf) FILTER (WHERE rn_latest = 1) 是"取最新那一行的价格"，这是把每组取一行嵌进聚合的紧凑写法。

第四，30 天降价次数的 LAG 是在全部历史上算的，然后才用 observed_at >= CURRENT_DATE - 30 过滤。顺序反了会出错：先过滤再 LAG，窗口里最早那一行的 prev_price_huf 就变成 NULL，30 天窗口的第一次降价被漏掉。过滤和窗口的先后顺序改变结果，这是本周第二次遇到这个主题。

第五，PERCENTILE_CONT 返回 double precision，在 CTE 里就 ::NUMERIC 转好，避免后面 ROUND(..., 1) 报 function round(double precision, integer) does not exist。类型问题在源头解决，比在使用处到处加转换干净。

常见错法：把所有东西塞进一条 SELECT，靠嵌套子查询组织。这样任何一处报错都要通读整条查询才能定位，而且同一个中间结果会被算好几遍。本周计划的"卡住时"已经给了正确做法：先写五个独立的 CTE，每个只算一组特征，最后 JOIN 起来，分步调试。

另一个常见错法是用 CURRENT_DATE 当"今天"直接算 days_on_market，然后把这份特征表存下来当训练数据。这一列的值取决于跑查询的日期，明天跑就变了，做时间序列回测时会造成数据泄漏。生产上应该传入一个显式的"计算基准日"参数，阶段 2 做分区管线时会处理这件事。

周末 · 整合与 LeetCode

题目：把 `w05_features.sql` 的结果导出成 Parquet，存到 `data/features/`

DuckDB 里直接读 PostgreSQL（推荐，不用 CSV 中转）：

-- 在 DuckDB 里
INSTALL postgres;
LOAD postgres;
ATTACH 'host=localhost port=5432 dbname=ingatlan user=postgres password=postgres'
    AS pg (TYPE POSTGRES, READ_ONLY);

COPY (
    SELECT * FROM postgres_query('pg', 'SELECT ... 这里粘贴 w05_features.sql 的整条查询 ...')
) TO 'data/features/listing_features.parquet' (FORMAT PARQUET);

不装扩展的做法，从 psql 导出 CSV 再让 DuckDB 转换：

-- psql 里（客户端 COPY，不需要超级用户）
\copy (SELECT ... w05_features.sql ...) TO 'data/features/listing_features.csv' WITH (FORMAT CSV, HEADER)

-- DuckDB 里
COPY (
    SELECT * REPLACE (price_per_m2::NUMERIC(12,2) AS price_per_m2)
    FROM read_csv('data/features/listing_features.csv', header = true)
) TO 'data/features/listing_features.parquet' (FORMAT PARQUET);

为什么这样写：postgres_query 把整条 SQL 原样交给 PostgreSQL 执行，窗口函数和 PERCENTILE_CONT 都由 Postgres 算，DuckDB 只负责写 Parquet。COPY (查询) TO ... (FORMAT PARQUET) 是 DuckDB 的写法，PostgreSQL 的 COPY 不支持 Parquet，只有 CSV、text、binary，别把两边的语法记混。::NUMERIC(12,2) 是 DuckDB 里给金额定精度的常用写法，Parquet 会把它存成定点小数而不是浮点，金额列这样存是对的（W06 Day 1 会讲为什么金额不能用浮点）。

* REPLACE (...) 是 DuckDB 的 Friendly SQL 语法，意思是"其他列照抄，只替换这一列"，PostgreSQL 没有。

常见错法：在 PostgreSQL 里写 COPY ... TO 'file.parquet' (FORMAT PARQUET)，报 unrecognized COPY format "parquet"。另一个是用服务端 COPY TO '/path'，在 Docker 里那个路径是容器内的路径而不是你的 Mac 上的路径，文件"消失"了。用 psql 的 \copy（客户端）就没这个问题。

题目：闭卷写出每个城市里价格最高的 3 套 active 房源

（这是 2026-09-10 评估第二批第 3 题的原题，答案见本周 Day 2 最后一题。闭卷时的自评标准：用了窗口函数、PARTITION BY 正确、WHERE rn <= 3 在外层、status 过滤在内层，四条都对才算满分。）

其他周末任务

LeetCode SQL 50 的窗口函数题：LeetCode 自带官方解答和讨论区，这里不重复。卡住的题号记进 notes/sql.md。
用特征表跑一个最简单的 LightGBM：这是 Python 任务，没有标准 SQL 答案。目的只是确认这份表能喂给模型（列类型对、没有全 NULL 的列、行数和 listings 一致），跑通即止，不要调参。
git commit：没有标准答案。

本周的三个要点

GROUP BY 压缩行，窗口函数广播值。OVER 后面的括号不能省，排名类和偏移类函数的 OVER 里必须有 ORDER BY，否则结果不稳定或全是 NULL。
窗口函数在逻辑执行顺序里位于 WHERE、GROUP BY、HAVING 之后，SELECT 之内，所以过滤窗口函数的结果（WHERE rn = 1）必须套一层 CTE 或子查询。同理，写在同层 WHERE 里的过滤条件会改变窗口能看到的行，过滤和排名的先后顺序会改答案。
并列时的行为要主动想清楚：ROW_NUMBER 强行分先后（每组行数确定），RANK 并列后跳号，DENSE_RANK 并列后不跳号。帧类型同理：ROWS 按行数，RANGE 按排序键的值，同一天多行时两者结果不同。