如何用SQL按APT分组提取首周startday与末周endday

原始数据 #

apt   week     startday       endday
10    1        07.03.2023     07.07.2023
10    2        07.10.2023     07.15.2023
10    3        07.17.2023     07.22.2023
10    4        07.24.2023     07.25.2023
15    3        05.05.2023     05.10.2023
15    4        05.12.2023     05.18.2023
25    1        09.12.2023     09.15.2023
25    2        09.17.2023     09.19.2023

需求 #

将相同apt的记录分组，提取该组第一周的startday和最后一周的endday，合并为单条记录，期望结果：

apt       startday      endday
    10       07.03.2023    07.25.2023
    15       05.05.2023    05.18.2023
    25       09.12.2023    09.19.2023 

解决方案 #

方法1：子查询+关联筛选

先通过子查询获取每个apt的最小/最大周数，再关联原表匹配对应日期：

SELECT 
    t.apt,
    MIN(CASE WHEN t.week = sub.min_week THEN t.startday END) AS startday,
    MAX(CASE WHEN t.week = sub.max_week THEN t.endday END) AS endday
FROM 
    your_table t
JOIN (
    SELECT 
        apt,
        MIN(week) AS min_week,
        MAX(week) AS max_week
    FROM your_table
    GROUP BY apt
) sub ON t.apt = sub.apt
GROUP BY t.apt;

方法2：窗口函数标记首末行

用ROW_NUMBER()窗口函数标记每组的首行（按week升序）和末行（按week降序），再聚合取值：

WITH ranked_data AS (
    SELECT 
        apt,
        startday,
        endday,
        ROW_NUMBER() OVER (PARTITION BY apt ORDER BY week) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY apt ORDER BY week DESC) AS rn_desc
    FROM your_table
)
SELECT 
    apt,
    MAX(CASE WHEN rn_asc = 1 THEN startday END) AS startday,
    MAX(CASE WHEN rn_desc = 1 THEN endday END) AS endday
FROM ranked_data
GROUP BY apt;

注：将your_table替换为实际表名即可。

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