SQL计算疫苗接种日期间隔时，如何避免lag函数生成重复行？

问题原因分析 #

1. distinct的作用误解：你写的distinct(fie.event_id)是误导性写法，SQL中distinct是对所有选中列的组合去重，而非仅针对括号内的event_id。当新增diff_between_doses列后，只要该列值存在差异，整行的组合就会被判定为不同，distinct无法合并重复行。
2. 窗口函数执行顺序问题：SQL的执行逻辑是先执行窗口函数（如lag），再执行distinct。如果你的子查询实际返回了4行数据，仅在未加lag列时，event_id + vaccination_date的组合只有2种，所以distinct后得到2行；但加了lag计算列后，4行的diff_between_doses值存在差异，导致整行组合变为4种，最终返回4行。

解决方案 #

要实现仅返回2行并展示间隔的需求，需要先对基础数据去重，再在去重后的结果集上计算lag：

基础版（保留第一行的null间隔）

select
  event_id,
  vaccination_date,
  vaccination_date - lag(vaccination_date) over (order by vaccination_date) as diff_between_doses
from (
  -- 先对原始数据去重，得到你需要的2行记录
  select distinct
    fie.event_id,
    dvd.sql_date as vaccination_date
  from (Removed for privacy reasons)
) as deduplicated_data
order by vaccination_date;

优化版（将第一行的null替换为0天）

如果希望第一行直接显示0天间隔，可使用coalesce函数处理null值：

select
  event_id,
  vaccination_date,
  coalesce(vaccination_date - lag(vaccination_date) over (order by vaccination_date), 0) as diff_between_doses
from (
  select distinct
    fie.event_id,
    dvd.sql_date as vaccination_date
  from (Removed for privacy reasons)
) as deduplicated_data
order by vaccination_date;

这样执行后，会返回你需要的2行数据，分别对应0天和86天的接种日期间隔。

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