SQL子查询分组求和问题：如何合并TotalDays结果

原SQL查询 #

select *
from(
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_date desc) rn,
        sum(date_diff('day', start_date, end_date)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = false
)
where rn = 1
union  
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_of_life desc) rn,
        sum(date_diff('day', start_date, end_of_life)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = true
)
where rn = 1)
where record_id = 146 and TotalDays <= 60 and TotalDays >= 0
group by record_id, hood_id, city_id, TotalDays

当前输出结果 #

期望输出结果 #

修改方案 #

原查询通过UNION分别返回了del_col=false和del_col=true分支的TotalDays，导致输出两行独立结果。要合并这两个值，需在外层对相同维度分组后求和：

修改后的SQL：

select record_id, hood_id, city_id, sum(TotalDays) as TotalDays
from(
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_date desc) rn,
        sum(date_diff('day', start_date, end_date)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = false
)
where rn = 1
union all  -- 用UNION ALL替代UNION，避免不必要的去重开销
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_of_life desc) rn,
        sum(date_diff('day', start_date, end_of_life)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = true
)
where rn = 1)
where record_id = 146 and TotalDays <= 60 and TotalDays >= 0
group by record_id, hood_id, city_id

关键调整说明 #

1. 外层查询改为对TotalDays求和，而非直接返回原分支的独立值
2. 分组条件去掉TotalDays，仅保留record_id, hood_id, city_id，确保同一维度的数值被合并
3. 替换UNION为UNION ALL，因两个分支分别对应不同del_col状态的记录，不会产生重复行，提升查询性能

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