如何通过PostgreSQL窗口函数简化复杂自连接查询，增强代码易读性？

本文共计660个文字，预计阅读时间需要3分钟。

由于自连接依赖JOIN条件的完整性及准确性，遗漏了一个AND e2.salary。

查每个部门工资最高的员工：别用LEFT JOIN子查询

常见错误是写两层嵌套子查询找max(salary)，再JOIN回来匹配——既难读又慢。直接用ROW_NUMBER()压平逻辑：

SELECT dept_id, name, salary FROM ( SELECT dept_id, name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn FROM employees ) t WHERE rn = 1;

• PARTITION BY dept_id确保每组独立编号
• ORDER BY salary DESC, id解决同薪并列时序号不确定问题（id兜底）
• 必须加id或其它唯一字段，否则PostgreSQL可能每次返回不同员工
• 如果允许并列第一，换用RANK()，但要注意WHERE rn = 1仍能命中所有并列者

算用户连续登录天数：LAG()比自连接少写8行

传统做法是自连接ON u1.user_id = u2.user_id AND u2.login_date = u1.login_date + INTERVAL '1 day'，再GROUP BY用户+日期差做断点识别。用LAG()一行搞定前值对比：

SELECT user_id, login_date, login_date - LAG(login_date) OVER ( PARTITION BY user_id ORDER BY login_date ) AS gap_days FROM user_logins;

• 结果里gap_days = 1就是连续登录，gap_days > 1或NULL（首日）就是断点
• 注意ORDER BY login_date必须存在，且字段精度要够——如果只存到日，没问题；若含时分秒，建议先login_date::date转换再排序
• 别在LAG()里写复杂表达式如LAG(login_date + INTERVAL '1 day')，会干扰优化器索引使用

性能翻车最常发生在没索引的ORDER BY字段上

窗口函数不是银弹。当OVER (PARTITION BY dept_id ORDER BY hire_date)中hire_date无索引，PostgreSQL会触发磁盘排序（Sort节点出现在EXPLAIN里），比带索引的自连接还慢。验证方法很简单：

• 执行EXPLAIN (ANALYZE, BUFFERS) SELECT ...，看是否有Sort且Buffers: shared read=xxx巨大
• 建复合索引：CREATE INDEX idx_dept_hire ON employees(dept_id, hire_date)
• 如果业务只要分组聚合不依赖顺序（比如只算部门人数），可省略ORDER BY，避免无谓排序
• MySQL 8.0+ 和 PostgreSQL 都支持索引跳扫，但SQL Server需要显式提示，这点容易被忽略

真正卡住人的从来不是语法，而是忘了ORDER BY字段有没有索引，以及是否用ROWS BETWEEN替代了更慢的RANGE BETWEEN。