如何用单条SQL实现学生成绩全维度追踪的多组合分析？

背景 #

现有学生成绩表结构及数据如下：

CREATE TABLE myt 
(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    letter CHAR(1),
    date DATE
);

INSERT INTO myt (id, name, letter, date) 
VALUES
(1, 'Alice', 'C', '2023-01-28'),
(2, 'Alice', 'B', '2023-02-28'),
(3, 'Alice', 'A', '2023-03-28'),
(4, 'Bob', 'B', '2023-01-09'),
(5, 'Bob', 'C', '2023-02-09'),
(6, 'Bob', 'B', '2023-03-09'),
(7, 'Charlie', 'B', '2023-01-19'),
(8, 'Charlie', 'A', '2023-02-19'),
(9, 'Charlie', 'A', '2023-03-19'),
(10, 'Charlie', 'A', '2023-04-19'),
(11, 'Charlie', 'A', '2023-05-19'),
(12, 'David', 'B', '2023-01-05'),
(13, 'David', 'C', '2023-02-05'),
(14, 'David', 'A', '2023-03-05'),
(15, 'David', 'B', '2023-04-05'),
(16, 'David', 'B', '2023-05-05'),
(17, 'David', 'A', '2023-06-05'),
(18, 'Emma', 'B', '2023-01-15'),
(19, 'Emma', 'A', '2023-02-15'),
(20, 'Emma', 'A', '2023-03-15'),
(21, 'Emma', 'A', '2023-04-15'),
(22, 'Emma', 'A', '2023-05-15'),
(23, 'Frank', 'B', '2023-01-06'),
(24, 'Frank', 'A', '2023-02-06'),
(25, 'Frank', 'A', '2023-03-06'),
(26, 'Frank', 'A', '2023-04-06'),
(27, 'Grace', 'A', '2023-01-27'),
(28, 'Grace', 'B', '2023-02-27'),
(29, 'Grace', 'B', '2023-03-27'),
(30, 'Henry', 'B', '2023-01-31'),
(31, 'Henry', 'A', '2023-03-03'),
(32, 'Henry', 'A', '2023-03-31'),
(33, 'Henry', 'A', '2023-05-01'),
(34, 'Henry', 'A', '2023-05-31'),
(35, 'Isabel', 'B', '2023-01-20'),
(36, 'Isabel', 'C', '2023-02-20'),
(37, 'Isabel', 'A', '2023-03-20'),
(38, 'Isabel', 'A', '2023-04-20'),
(39, 'Isabel', 'C', '2023-05-20'),
(40, 'Jack', 'B', '2023-01-30'),
(41, 'Jack', 'C', '2023-03-02'),
(42, 'Jack', 'A', '2023-03-30'),
(43, 'Jack', 'B', '2023-04-30'),
(44, 'Jack', 'B', '2023-05-30'),
(45, 'Jack', 'C', '2023-06-30');

已实现针对起始成绩为B的学生首次取得A后后续是否全为A的统计查询：

WITH RankedGrades AS 
(
    SELECT 
        name,
        letter,
        date,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) as grade_sequence
    FROM 
        myt
),
BStarters AS 
(
    SELECT DISTINCT name
    FROM RankedGrades
    WHERE grade_sequence = 1 AND letter = 'B'
),
FirstASequence AS 
(
    SELECT 
        r.name,
        MIN(r.grade_sequence) as first_a_sequence
    FROM 
        RankedGrades r
    JOIN
        BStarters b ON r.name = b.name
    WHERE 
        r.letter = 'A'
    GROUP BY 
        r.name
),
FirstADetails AS 
(
    SELECT 
        r.name,
        r.date as first_a_date,
        fas.first_a_sequence as a_sequence_number
    FROM 
        RankedGrades r
    JOIN 
        FirstASequence fas ON r.name = fas.name
    WHERE 
        r.grade_sequence = fas.first_a_sequence
),
SubsequentPerformance AS 
(
    SELECT 
        f.name,
        CASE WHEN MAX(CASE WHEN r.letter != 'A' THEN 1 ELSE 0 END) = 0 
             THEN 'Pure A'
             ELSE 'Mixed Grades'
        END as grade_pattern
    FROM 
        FirstADetails f
    JOIN 
        RankedGrades r ON f.name = r.name
    WHERE 
        r.grade_sequence > f.a_sequence_number
    GROUP BY 
        f.name
),
Summary AS 
(
    SELECT 
        grade_pattern,
        COUNT(*) as student_count,
        SUM(COUNT(*)) OVER () as total_students
    FROM 
        SubsequentPerformance
    GROUP BY 
        grade_pattern
)
SELECT 
    grade_pattern,
    student_count,
    ROUND(CAST(student_count AS FLOAT) / total_students * 100, 2) as percentage
FROM 
    Summary
ORDER BY 
    student_count DESC;

查询结果：

grade_pattern student_count percentage
  --------------------------------------
        Pure A             4      57.14
  Mixed Grades             3      42.86

问题 #

需要将查询扩展至所有成绩组合场景，例如：

• 起始成绩为B的学生首次取得C后是否后续全为C
• 起始成绩为A的学生首次取得C后是否后续全为C
• 其他所有起始成绩（A/B/C）与首次达成目标成绩（A/B/C）的组合

能否在单条SQL中实现全组合分析，还是需要编写多条独立查询？

答案 #

完全可以在单条SQL语句中实现所有成绩组合的分析，无需拆分多条查询。核心思路是将原查询中固定的起始成绩和目标成绩参数化，通过生成所有可能的组合，再关联数据进行批量统计。

实现SQL #

WITH RankedGrades AS (
    SELECT 
        name,
        letter,
        date,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) AS grade_sequence,
        -- 获取每个学生的起始成绩
        FIRST_VALUE(letter) OVER (PARTITION BY name ORDER BY date) AS starting_grade
    FROM myt
),
-- 生成所有可能的起始成绩与目标成绩组合
GradeCombinations AS (
    SELECT DISTINCT starting_grade, target_grade
    FROM (SELECT DISTINCT letter AS starting_grade FROM myt) s
    CROSS JOIN (SELECT DISTINCT letter AS target_grade FROM myt) t
),
-- 找出每个学生首次取得目标成绩的序列位置（仅针对对应起始成绩的学生）
FirstTargetSequence AS (
    SELECT 
        rg.starting_grade,
        gc.target_grade,
        rg.name,
        MIN(rg.grade_sequence) AS first_target_sequence
    FROM RankedGrades rg
    JOIN GradeCombinations gc 
        ON rg.starting_grade = gc.starting_grade
    WHERE rg.letter = gc.target_grade
    GROUP BY rg.starting_grade, gc.target_grade, rg.name
),
-- 统计每个组合下，学生首次取得目标成绩后的后续表现
SubsequentPerformance AS (
    SELECT 
        fts.starting_grade,
        fts.target_grade,
        fts.name,
        CASE 
            WHEN MAX(CASE WHEN rg.letter != fts.target_grade THEN 1 ELSE 0 END) = 0 
            THEN 'Pure ' || fts.target_grade
            ELSE 'Mixed Grades'
        END AS grade_pattern
    FROM FirstTargetSequence fts
    JOIN RankedGrades rg 
        ON fts.name = rg.name 
        AND rg.grade_sequence > fts.first_target_sequence
    GROUP BY fts.starting_grade, fts.target_grade, fts.name
),
-- 按组合汇总统计数据
Summary AS (
    SELECT 
        starting_grade,
        target_grade,
        grade_pattern,
        COUNT(*) AS student_count,
        SUM(COUNT(*)) OVER (PARTITION BY starting_grade, target_grade) AS total_students_in_combination
    FROM SubsequentPerformance
    GROUP BY starting_grade, target_grade, grade_pattern
)
-- 最终输出各组合的统计结果
SELECT 
    starting_grade AS '起始成绩',
    target_grade AS '目标成绩',
    grade_pattern AS '后续成绩模式',
    student_count AS '学生人数',
    ROUND(CAST(student_count AS FLOAT) / total_students_in_combination * 100, 2) AS '占比(%)'
FROM Summary
ORDER BY starting_grade, target_grade, student_count DESC;

关键说明 #

1. 生成所有成绩组合：通过CROSS JOIN生成起始成绩（A/B/C）与目标成绩（A/B/C）的全部9种组合
2. 批量获取首次目标成绩位置：不再针对单一目标筛选，而是按组合批量统计每个学生首次达成目标成绩的时间点
3. 统一后续表现判断逻辑：复用原有的后续成绩是否纯目标成绩的判断逻辑，动态关联当前组合的目标成绩
4. 按组合汇总占比：针对每个起始-目标组合单独计算占比，确保统计维度准确

示例输出片段 #

起始成绩 目标成绩 后续成绩模式 学生人数 占比(%)
------- ------- ----------- ------- -------
A       A       Pure A          1    100.00
A       B       Pure B          1    100.00
B       A       Pure A          4     57.14
B       A       Mixed Grades    3     42.86
B       B       Pure B          1     100.00
...（其余组合结果略）

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