基于季度日期范围聚合学生分数的SQL实现问询

现有表T1结构及数据 #

STUDENT SCORE   DATE
1   6   2022-02-01
1   0   2022-03-12
1   5   2022-04-30
1   1   2022-04-30
1   1   2022-05-14
1   1   2022-05-19
1   8   2022-05-26
2   9   2022-01-02
2   10  2022-04-11
2   2   2022-04-12
2   0   2022-04-17
2   7   2022-05-08
2   4   2022-05-12
3   10  2022-01-09
3   2   2022-02-11
3   6   2022-03-16
3   3   2022-03-18
3   2   2022-04-02
3   9   2022-04-27
4   4   2022-02-24
4   0   2022-02-26
4   9   2022-02-28
4   2   2022-03-27
4   8   2022-04-02
4   4   2022-04-14
5   3   2022-01-28
5   5   2022-02-12
5   6   2022-02-18
5   0   2022-02-21
5   4   2022-04-05
XX  0.711094564 2022-02-28
XX  0.60584994  2022-03-31
XX  0.087965016 2022-04-30
YY  0.497937992 2022-02-28
YY  0.727796963 2022-03-31
YY  0.974471085 2022-04-30
YY  0.780187398 2022-05-30

需求 #

• 提取STUDENT为'XX'或'YY'的唯一DATE值，这些是区间结束日期；
• 根据这些日期划分区间（如2022-01-01至2022-02-28等），对每个学生的SCORE进行SUM聚合，生成目标表T2：

STUDENT SCORE   DATE.START  DATE.END
1   6   2022-01-01  2022-02-28
1   0   2022-03-01  2022-03-31
1   6   2022-04-01  2022-04-30
1   10  2022-05-01  2022-05-30
2   9   2022-01-01  2022-02-28
2   NA  2022-03-01  2022-03-31
2   12  2022-04-01  2022-04-30
2   11  2022-05-01  2022-05-30
3   12  2022-01-01  2022-02-28
3   9   2022-03-01  2022-03-31
3   11  2022-04-01  2022-04-30
3   NA  2022-05-01  2022-05-30
4   13  2022-01-01  2022-02-28
4   2   2022-03-01  2022-03-31
4   12  2022-04-01  2022-04-30
4   NA  2022-05-01  2022-05-30
5   14  2022-01-01  2022-02-28
5   NA  2022-03-01  2022-03-31
5   4   2022-04-01  2022-04-30
5   NA  2022-05-01  2022-05-30

尝试的错误SQL #

WITH T2 AS(
SELECT DISTINCT(DATE),
COALESCE(LEAD(DATE) OVER(ORDER BY DATE) AS DATE.START,
ROW_NUMBER() OVER (ORDER BY DATE) AS DATE.END
FROM T1 WHERE (STUDENT = 'XX' OR STUDENT = 'YY')
)
SELECT STUDENT, SUM(SCORE), DATE.START, DATE.END
FROM T1 JOIN T2 ON T1.DATE.START BETWEEN DATE.START AND DATE.END

你的SQL存在语法错误（COALESCE使用不当、含点列名未加引号、JOIN条件逻辑错误），且未生成正确的区间起始日期，也未处理无数据时的NA值。以下是正确实现：

WITH date_ranges AS (
    -- 提取XX/YY的唯一结束日期，生成区间起始和结束
    SELECT 
        CASE 
            WHEN ROW_NUMBER() OVER (ORDER BY end_date) = 1 THEN '2022-01-01'::DATE
            ELSE (LAG(end_date) OVER (ORDER BY end_date) + INTERVAL '1 day')::DATE
        END AS date_start,
        end_date AS date_end
    FROM (
        -- 获取XX/YY的唯一DATE值作为区间结束日期
        SELECT DISTINCT "DATE" AS end_date
        FROM T1
        WHERE STUDENT IN ('XX', 'YY')
    ) AS unique_ends
),
all_students AS (
    -- 提取需要统计的学生（排除XX/YY）
    SELECT DISTINCT STUDENT
    FROM T1
    WHERE STUDENT NOT IN ('XX', 'YY')
)
-- 生成学生与区间的全组合，左连接原表统计分数
SELECT 
    s.STUDENT,
    COALESCE(SUM(t.SCORE)::TEXT, 'NA') AS SCORE,
    dr.date_start AS "DATE.START",
    dr.date_end AS "DATE.END"
FROM all_students s
CROSS JOIN date_ranges dr
LEFT JOIN T1 t 
    ON s.STUDENT = t.STUDENT
    AND t."DATE" BETWEEN dr.date_start AND dr.date_end
GROUP BY s.STUDENT, dr.date_start, dr.date_end
ORDER BY s.STUDENT, dr.date_start;

关键说明 #

1. date_ranges CTE：先提取XX/YY的唯一结束日期，再通过LAG窗口函数生成每个区间的起始日期，第一个区间起始固定为2022-01-01。
2. all_students CTE：筛选出需要统计的学生群体（排除XX/YY）。
3. 主查询：用CROSS JOIN生成所有学生与区间的组合，左连接原表统计分数，无数据时用COALESCE返回NA，最后按学生和区间起始排序。

注：不同SQL方言（如MySQL、PostgreSQL）在日期函数、类型转换语法上可能有细微差异，可根据实际数据库调整。

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