SQL中按条件填充列且每个唯一客户仅生成一行的实现方法

需求：编写SQL查询，展示客户加入项目后12周内每周是否发生特定事件，要求每个客户仅生成一行数据，将对应周的事件日期填充至「Week 1」至「Week 12」列中。

当前问题：每个事件日期会为同一客户生成新行，无法将日期填充到对应列。

实际得到的结果 #

期望得到的结果 #

当前尝试的代码片段 #

With cssrs as (
Select people_id, event_name, event_date
from event_table
Where event_name in ('event_one', 'event_two', 'event_three')
)

Select cc.agency_id_number, cc.full_name, cc.program_name, cc.program_start_date,

CASE
    WHEN cc.people_id in (cssrs.people_id) and cssrs.actual_date between cc.program_start_date and   DATEADD(d, 7, cc.program_start_date) THEN cssrs.actual_date
END AS 'CSSRS Service Week 1',

CASE
    WHEN cc.people_id in (cssrs.people_id) and cssrs.actual_date between DATEADD(d, 7, cc.program_start_date) and   DATEADD(d, 14, cc.program_start_date) THEN cssrs.actual_date
END AS 'CSSRS Service Week 2'

From people_table cc

Left Join cssrs
On cc.people_id = cssrs.people_id and cssrs.actual_date >= cc.program_start_date

解决方案 #

核心问题是未对客户进行分组，且未用聚合函数合并同一客户的多行数据。需通过GROUP BY按客户维度分组，配合MAX()聚合函数提取每个周对应的事件日期，实现一行对应一个客户的需求。

修改后的SQL示例 #

WITH cssrs AS (
    SELECT 
        people_id, 
        event_date
    FROM event_table
    WHERE event_name IN ('event_one', 'event_two', 'event_three')
)
SELECT 
    cc.agency_id_number, 
    cc.full_name, 
    cc.program_name, 
    cc.program_start_date,
    -- 提取第1周的事件日期
    MAX(CASE 
        WHEN cssrs.event_date BETWEEN cc.program_start_date AND DATEADD(d, 7, cc.program_start_date) 
        THEN cssrs.event_date 
    END) AS 'CSSRS Service Week 1',
    -- 提取第2周的事件日期
    MAX(CASE 
        WHEN cssrs.event_date BETWEEN DATEADD(d, 7, cc.program_start_date) AND DATEADD(d, 14, cc.program_start_date) 
        THEN cssrs.event_date 
    END) AS 'CSSRS Service Week 2',
    -- 依次类推，写出Week3到Week12的CASE语句
    MAX(CASE 
        WHEN cssrs.event_date BETWEEN DATEADD(d, 77, cc.program_start_date) AND DATEADD(d, 84, cc.program_start_date) 
        THEN cssrs.event_date 
    END) AS 'CSSRS Service Week 12'
FROM people_table cc
LEFT JOIN cssrs 
    ON cc.people_id = cssrs.people_id 
    AND cssrs.event_date BETWEEN cc.program_start_date AND DATEADD(d, 84, cc.program_start_date) -- 限制12周内的事件
GROUP BY cc.agency_id_number, cc.full_name, cc.program_name, cc.program_start_date;

关键说明 #

1. GROUP BY分组：按客户的唯一标识（及其他需要展示的字段）分组，确保同一客户仅生成一行数据。
2. MAX()聚合函数：同一客户同一周若有多个事件，MAX()会取最新的日期；若无事件则返回NULL，正好填充对应列的空值。
3. 简化条件：通过LEFT JOIN的条件提前过滤出12周内的事件，避免在CASE语句中重复判断范围。

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