在PostgreSQL中，如何用零填补缺失的日期？

Question

我在PostgreSQL中有如下查询：

select count(id_student) students, date_beginning_course from 
data.sessions_courses
left join my_schema.students on id_session_course=id_sesion 
where course_name='First course'
group by date_beginning_course

我通过此查询得到的是参加了多个日期“第一课程”会话的学生人数，例如：

Students  Date_beginning_course
____________________________________

5         2019-06-26
1         2019-06-28
5         2019-06-30
6         2019-07-01
2         2019-07-02

我希望填充这个表格中缺失的日期值，并且对于每个缺失的值，在“学生”列中分配一个“0”，因为该日期没有学生。 例如：

Students  Date_beginning_course
____________________________________

5         2019-06-26
0         2019-06-27  <--new row
1         2019-06-28
0         2019-06-29  <--new row
5         2019-06-30
6         2019-07-01
2         2019-07-02

你能帮我吗？谢谢！:)

- belfastcowboy24

---

请使用日历表（可能使用generate-series()函数）进行左连接。 - wildplasser

确实。加入到一个子查询中，该子查询使用generate_series()创建日期范围，如@wildplassers 答案所述。 - Bohemian

我尝试使用generate_series查询，但没有成功，我认为我做错了什么。 - belfastcowboy24

Answer 1

你可以使用实用的Postgres集合返回函数generate_series()生成日期列表，并将其与sessions_courses和students表进行LEFT JOIN：

SELECT
    COUNT(s.id_student) students, 
    d.dt
FROM 
    (
        SELECT dt::date 
        FROM generate_series('2019-06-26', '2019-07-02', '1 day'::interval) dt
    ) d
    LEFT JOIN data.sessions_courses c 
        ON c.date_beginning_course  = d.dt 
        AND c.course_name='First course'
    LEFT JOIN my_schema.students s 
        ON s.id_session_course = c.id_session 
GROUP BY d.dt 

你可以通过修改 generate_series() 的前两个参数来更改日期范围。

注意：在查询中，将列名与相关表名（或表别名）索引是一种通用的良好实践，以便明确每个列属于哪个表。我相应地更改了你的查询，并且不得不做出一些假设，你可能需要进行调整。

- GMB

---

很抱歉，这个好像不太行，因为当我在WHERE子句中添加更多的过滤器时，我的表会返回所有结果，而不是被过滤的结果 :( - belfastcowboy24

@belfastcowboy24：不太确定你的意思... 无论如何，查询中有一个错误：我刚刚删除了WHERE子句；所有过滤条件都应该放在相关LEFT JOIN的ON子句中。 - GMB

@belfastcowboy24……这似乎回答了你所问的问题。你应该接受这个答案。如果你有不同的问题，那么请提出一个 新 的问题。 - Gordon Linoff