如何通过窗口函数实现无关联子查询的最后值变更日期计算？

现有一张记录val值变更情况的表，需要计算val最后一次发生变更的日期（示例中结果为5）。目前已通过带关联子查询的SQL实现需求，现希望仅使用窗口函数完成计算，无需子查询，同时疑惑聚合函数是否支持传入条件。

原实现SQL（带关联子查询）：

with part_table as(
select src_table.* 
, lag(val) over (partition by id order by nday) prev_val
, lead(val) over (partition by id order by nday) nex_val
from 
  (
  select 1 id, 'value1' val, 1 nday from dual union all
  select 1 id, 'value2' val, 2 nday from dual union all
  select 1 id, 'value1' val, 3 nday from dual union all
  select 1 id, 'value2' val, 4 nday from dual union all
  select 1 id, 'value1' val, 5 nday from dual union all
  select 1 id, 'value1' val, 6 nday from dual union all
  select 1 id, 'value1' val, 7 nday from dual 
  ) src_table
) select 
  t.* 
  ,(select max(nday) from part_table t2 where t2.id=t.id and t2.val <> t2.prev_val) max_nday
from part_table t
order by id, nday

当前尝试的窗口函数写法（需实现max_nday为5的效果）：

with part_table as(
select src_table.* 
, lag(val) over (partition by id order by nday) prev_val
, lead(val) over (partition by id order by nday) nex_val
, max(nday) over (partition by id order by nday) max_nday --- how to get 5?
from 
  (
  select 1 id, 'value1' val, 1 nday from dual union all
  select 1 id, 'value2' val, 2 nday from dual union all
  select 1 id, 'value1' val, 3 nday from dual union all
  select 1 id, 'value2' val, 4 nday from dual union all
  select 1 id, 'value1' val, 5 nday from dual union all
  select 1 id, 'value1' val, 6 nday from dual union all
  select 1 id, 'value1' val, 7 nday from dual 
  ) src_table
) select 
  t.* 
from part_table t
order by id, nday

1. 仅用窗口函数实现需求 #

核心思路：先通过lag()标记出所有发生val变更的行，再用条件聚合窗口函数提取这些变更行中的最大nday，即为最后一次变更日期。

完整SQL代码：

with part_table as(
select src_table.* 
, lag(val) over (partition by id order by nday) prev_val
, lead(val) over (partition by id order by nday) nex_val
-- 用case筛选出变更行的nday，再取窗口内的最大值
, max(case when val <> prev_val then nday else null end) over (partition by id) as max_nday
from 
  (
  select 1 id, 'value1' val, 1 nday from dual union all
  select 1 id, 'value2' val, 2 nday from dual union all
  select 1 id, 'value1' val, 3 nday from dual union all
  select 1 id, 'value2' val, 4 nday from dual union all
  select 1 id, 'value1' val, 5 nday from dual union all
  select 1 id, 'value1' val, 6 nday from dual union all
  select 1 id, 'value1' val, 7 nday from dual 
  ) src_table
) select 
  t.* 
from part_table t
order by id, nday

执行后所有行的max_nday都会返回5，符合需求。

2. 关于聚合函数传入条件的问题 #

聚合函数本身不直接支持传入筛选条件，但可以通过**case表达式**间接实现条件筛选：

• 在聚合函数内部用case when 条件 then 字段 else null end，聚合函数会自动忽略null值，从而只对满足条件的行进行聚合计算。
• 这种方式不仅适用于窗口函数中的聚合，普通聚合场景也同样适用。

内容的提问来源于stack exchange，提问作者clipper1995