填写查询中缺少的值

Question

假设我们有这样的数据集:两个学生注册了两个不同的课程，每个student都有出勤率。我感兴趣的是找到它们中的一个或两个都错过了各自的subject的students的attendance_dates。对于subject和attendance_dates，students未显示并返回NULL。例如：Chris错过了2/5/2021上的English类，Chris和Joseph错过了2/6/2021上的Chemistry和English。

我正在使用postgreSQL！

subject            attendance_dates        student 
Chemistry             2/4/2021              Chris
Chemistry             2/4/2021              Joseph
English               2/4/2021              Chris
English               2/4/2021              Joseph
Chemistry             2/5/2021              Chris
Chemistry             2/5/2021              Joseph
English               2/5/2021              Joseph
Chemistry             2/7/2021              Chris
Chemistry             2/7/2021              Joseph
English               2/7/2021              Chris
English               2/7/2021              Joseph

我尝试过的：

with cte as (
select subject,attendance_dates,student from my_table
)
,
dates as (
select * from generate_series('2021-02-04', '2021-02-07', interval '1 day')  AS more_dates
)

select subject_id,cast(more_dates as date)more_dates,student
from cte c
right join dates d 
on cast(more_date as date) = attendance_dates

结果：

subject            attendance_dates        student 
Chemistry             2/4/2021              Chris
Chemistry             2/4/2021              Joseph
English               2/4/2021              Chris
English               2/4/2021              Joseph
Chemistry             2/5/2021              Chris
Chemistry             2/5/2021              Joseph
English               2/5/2021              Joseph
                      2/6/2021                           <----------
Chemistry             2/7/2021              Chris
Chemistry             2/7/2021              Joseph
English               2/7/2021              Chris
English               2/7/2021              Joseph

请求：

subject           attendance_dates          student 
Chemistry             2/4/2021               Chris
Chemistry             2/4/2021               Joseph
English               2/4/2021               Chris
English               2/4/2021               Joseph
Chemistry             2/5/2021               Chris
Chemistry             2/5/2021               Joseph
English               2/5/2021                         <------------
English               2/5/2021               Joseph
Chemistry             2/6/2021                         <-----------
Chemistry             2/6/2021                         <-----------
English               2/6/2021                         <-----------
English               2/6/2021                         <-----------
Chemistry             2/7/2021               Chris
Chemistry             2/7/2021               Joseph
English               2/7/2021               Chris
English               2/7/2021               Joseph

Answer 1

我认为你真的希望每天为每个学生和科目都挂上一面旗帜，表明该学生是否参加了考试。

使用cross join为所有日期、主题和学生创建行。然后带上一面旗帜，表明该学生当天是否参加了会议：

select d.date, su.subject, st.student,
       (t.student is not null) as attended_flag
from generate_series('2021-02-04', '2021-02-07', interval '1 day') d(dte) cross join
     (select distinct subject from my_table) su cross join
     (select distinct student from my_table) st left join
     my_table t
     on t.student = st.student and t.subject = su.subject and
        t.attendance_date = d.dte;

Here是一个db<>fiddle。