怎样在PostgreSQL中利用窗口函数替代复杂的自连接分组

自连接易错因依赖join条件完整性，漏写条件会导致逻辑错误；窗口函数天然按partition by分组、order by排序，无笛卡尔积，边界清晰，容错性更强。

怎样在postgresql中利用窗口函数替代复杂的自连接分组_提升代码可读性

为什么自连接一写就错，而窗口函数反而更稳

因为自连接依赖JOIN条件的完整性与准确性，漏一个AND e2.salary > e1.salary，排名就全乱；而窗口函数天然按PARTITION BY分组、按ORDER BY排序，不产生中间笛卡尔积，逻辑边界清晰。你改错一个字段，顶多是结果偏移，不会像自连接那样直接查出重复行或漏掉整组数据。

查每个部门工资最高的员工：别用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()一行搞定前值对比：

WebDesk

一款超级好用的浏览器网址管理工具，以APP图标自定义浏览器主页和个性化Tab标签页。

下载

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。