SQL：找出每个组每个 ID 的所有可能的年份组合

我正在使用 Netezza SQL。

我有以下关于学生（2010-2015 年）的数据集（“my_table”）、他们当前就读的学位专业、参加考试的日期以及考试结果：

    student_id current_major year exam_result
1            1       Science 2010           0
2            1          Arts 2013           1
3            1          Arts 2013           0
4            2       Science 2010           1
5            2          Arts 2011           1
6            2       Science 2013           1
7            3          Arts 2010           1
8            3          Arts 2015           1
9            4          Arts 2010           0
10           4       Science 2013           1
11           5          Arts 2010           0
12           5          Arts 2011           0
13           5       Science 2012           1

我的问题：我想知道是否initial学生开始获得的学位会影响学生在大学学习的年限。 （分析期间为 2010 年至 2015 年——学生可以在 2010 年至 2015 年期间的任何时间加入大学。）

为了回答这个问题，我首先想做一个表格，显示每个年份组合就读该大学的学生人数：

select year_2010,
 year_2011,
year_2012,
 year_2013,
 year_2014,
 year_2015, 
count(*) 
from
(
select student_id,
max(case when (year = 2010) then 1 else 0 end) as 'year_2010',
max(case when (year = 2011) then 1 else 0 end) as 'year_2011',
max(case when (year = 2012) then 1 else 0 end) as 'year_2012',
max(case when (year = 2013) then 1 else 0 end) as 'year_2013',
max(case when (year = 2014) then 1 else 0 end) as 'year_2014',
max(case when (year = 2015) then 1 else 0 end) as 'year_2015',
from my_table
group by student_id)a
group by year_2010,
 year_2011,
 year_2012,
 year_2013,
 year_2014,
 year_2015;


   year_2010 year_2011 year_2012 year_2013 year_2014 year_2015 count_star()
1          0         1         0         1         0         0           24
2          0         0         0         0         1         1           17
3          0         0         1         1         0         0           22
4          1         1         1         0         0         0           12
5          0         1         1         1         1         0           12
6          1         0         0         0         1         0           23
7          0         0         1         0         0         0           49

我的问题： 现在，我想使用每个学生最早可用的专业对该表进行“分组”（即学生在大学的第一年学习什么）。为了解决这个问题，我们假设学生在第一年不能改变专业，必须至少等到第二年才能改变专业。然而，从第二年开始，学生可以在同一年内多次转换学位专业。

我想回答以下问题：

• 在 2010 年入学并开始学习科学的学生中，有多少人连续学习了 5 年？

• 在 2011 年入读大学并最初开始学习艺术的学生中，有多少学生在 2011 年至 2015 年间学习了至少 2 年？

我认为此类问题可以这样回答：

• 步骤 1：使用 PARTITION 函数找出最早的年份（通过 CTE）
• 第 2 步：使用现有查询
• 步骤 3：将步骤 1 和步骤 2 的结果连接在一起

这是我的尝试：

WITH earliest_major AS (
    SELECT student_id, current_major AS earliest_major
    FROM (
        SELECT student_id, current_major, year,
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY year) AS rn
        FROM my_table
    ) sub
    WHERE rn = 1
)
SELECT em.earliest_major,
       year_2010,
       year_2011,
       year_2012,
       year_2013,
       year_2014,
       year_2015, 
       COUNT(*) 
FROM (
    SELECT student_id,
           MAX(CASE WHEN (year = 2010) THEN 1 ELSE 0 END) AS year_2010,
           MAX(CASE WHEN (year = 2011) THEN 1 ELSE 0 END) AS year_2011,
           MAX(CASE WHEN (year = 2012) THEN 1 ELSE 0 END) AS year_2012,
           MAX(CASE WHEN (year = 2013) THEN 1 ELSE 0 END) AS year_2013,
           MAX(CASE WHEN (year = 2014) THEN 1 ELSE 0 END) AS year_2014,
           MAX(CASE WHEN (year = 2015) THEN 1 ELSE 0 END) AS year_2015
    FROM my_table
    GROUP BY student_id
) a
JOIN earliest_major em ON a.student_id = em.student_id
GROUP BY em.earliest_major, 
         year_2010, 
         year_2011, 
         year_2012, 
         year_2013, 
         year_2014, 
         year_2015;

该查询似乎运行并以所需的格式生成结果：

  earliest_major year_2010 year_2011 year_2012 year_2013 year_2014 year_2015 count_star()
1         Science         0         1         0         1         0         0           15
2            Arts         0         0         0         0         1         1           11
3            Arts         0         0         1         1         0         0           13
4            Arts         1         1         1         0         0         0            8
5         Science         0         1         1         1         1         0            7

但我不确定我的逻辑是否正确 - 有人可以帮助我吗？

Thanks!

我认为你应该放弃你的柱状格式和你的案例。您实际上需要一个直方图。最好采用“长”格式；如果需要，应用层可以重新格式化。

create table major_exams(
  student_id int not null,
  current_major text not null,
  year smallint not null check (year between 1900 and 2200),
  exam_result boolean not null
);

insert into major_exams(student_id, current_major, year, exam_result) values
( 1, 'Science', 2010, false),
( 1,    'Arts', 2013, true),
( 1,    'Arts', 2013, false),
( 2, 'Science', 2010, true),
( 2,    'Arts', 2011, true),
( 2, 'Science', 2013, true),
( 3,    'Arts', 2010, true),
( 3,    'Arts', 2015, true),
( 4,    'Arts', 2010, false),
( 4, 'Science', 2013, true),
( 5,    'Arts', 2010, false),
( 5,    'Arts', 2011, false),
( 5, 'Science', 2012, true);

select major, duration, count(*) as n
from (
    select first_majors.major,
        last_majors.year - first_majors.year + 1 as duration
    from (
        select distinct on (student_id) student_id, current_major as major, year
        from major_exams
        order by student_id, year
    ) first_majors
    join (
        select student_id, max(year) as year
        from major_exams
        group by student_id
    ) last_majors on last_majors.student_id = first_majors.student_id
) major_bounds
group by major, duration
order by major, duration;

major       duration    n
Arts        3           1
Arts        4           1
Arts        6           1
Science     4           2