为什么在SQL中窗口函数结合CASE WHEN威力巨大

CASE WHEN 与窗口函数的三种嵌套方式：外部套用先计算后筛选，参数内嵌过滤输入影响计算基数，OVER子句中动态分区；需警惕类型兼容、NULL处理及ELSE逻辑断点风险。

CASE WHEN 和窗口函数不是简单拼凑，而是让“条件逻辑”真正嵌入到“计算流”里。它解决的不是“怎么写”，而是“在哪一刻做判断”——这个时机决定了结果是否可信。

窗口函数外部套CASE WHEN：先算完再筛，适合结果分级

这是最直观也最容易误用的位置。比如你想要「只给高分学生排名，其他人标为‘未参与’」：

CASE WHEN score >= 90 THEN RANK() OVER (ORDER BY score DESC) ELSE '未参与' END

注意：这里 RANK() 会为所有行计算，再由外层 CASE WHEN 替换掉非高分者的值。问题在于——如果表有 100 万行，引擎仍要完成全部排序，性能浪费明显。

• 适用场景：需要保留原始窗口计算上下文（比如后续还要用 ROWS BETWEEN），仅对展示层做映射
• 风险点：ELSE 返回的类型必须和窗口函数返回值兼容，否则报 ORA-00932 或类似类型错误
• 别写成 CASE WHEN RANK() > 10 THEN ... —— 窗口函数不能出现在 WHEN 的条件表达式中（语法不合法）

窗口函数参数里用CASE WHEN：过滤输入，影响计算基数

这才是真正“按需计算”的写法。例如「只对已支付订单统计累计金额」：

SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) OVER (ORDER BY create_time)

关键在 CASE WHEN ... ELSE 0：它把非已支付订单的 amount 变成 0，而不是 NULL。用 0 能保证求和连续，用 NULL 则会导致该行累计值为 NULL（因为 SUM 遇 NULL 会跳过，但若整段都是 NULL 就没值了）。

• 必须显式写 ELSE 0 或 ELSE NULL，不能省略——否则默认为 NULL，可能破坏聚合连续性
• 慎用 NULL 作为 ELSE：像 AVG()、COUNT() 对 NULL 敏感，行为易偏离预期
• 不适用于 RANK()、ROW_NUMBER() 这类序号函数——它们不接受 CASE WHEN 作为参数（会报错 Window function xxx does not support filtering in argument）

在OVER子句里用CASE WHEN：动态分区，业务规则即SQL结构

当分区逻辑本身是条件驱动的，比如「按用户等级分组排名，等级由当前积分实时判定」：

RANK() OVER (  PARTITION BY CASE     WHEN points >= 10000 THEN 'VIP'    WHEN points >= 1000  THEN 'Gold'    ELSE 'Normal'   END   ORDER BY order_count DESC)

这里 PARTITION BY 不再是固定字段，而是一段可执行逻辑。数据库会在运行时为每行计算出所属分区标签，再分组计算。

• 所有 WHEN 分支的返回值类型必须一致（比如全为字符串），否则报类型不匹配错误
• CASE WHEN 中不能引用窗口函数（如 ROW_NUMBER()），也不能引用本层 OVER 的排序字段（会提示 invalid reference to FROM-clause entry）
• 注意 NULL 处理：如果某行 points 是 NULL，整个 CASE 返回 NULL，这些行会被归入同一个隐式分区（不是丢弃！），容易造成“神秘的额外排名组”

最容易被忽略的细节：ELSE不是兜底，是逻辑断点

很多线上问题源于把 ELSE 当成“保底安全网”。实际上，一旦漏掉某个业务状态（比如订单状态新增了 'refunding'，但 CASE WHEN 没覆盖），它就会滑入 ELSE 分支——而你写的 ELSE '其他' 可能正混着灰度数据、测试脏数据、甚至系统异常码。更危险的是，有些团队习惯写 ELSE NULL，结果下游报表直接丢弃整行，导致统计口径无声漂移。

真正稳妥的做法是：明确枚举所有已知状态，用注释标出版本和生效时间，并在 ELSE 里抛出可识别的占位符（如 'UNEXPECTED_STATUS_' || status），让问题暴露在日志或监控里，而不是沉默吞掉。