sql - generate_series 用于创建月份列表以获取缺失日期

Question

我有一个按月出勤的 View (日期按月截断):

----------------------------------------------------
| Student_ID    | Date             | Attendance    |
----------------------------------------------------
| 1             | 2017-01-01       | 4             |
| 1             | 2017-02-01       | 3             |
| 1             | 2017-04-01       | 4             |
| 1             | 2017-06-01       | 2             |
| 2             | 2017-03-01       | 5             |
| 2             | 2017-05-01       | 2             |
----------------------------------------------------

如您所见，并非每个 ID 都显示每个月，因为此 View 对实际出勤表中出现的每个日期进行计数和分组。不过，我需要每个月都显示每个 ID，即使是 0。

----------------------------------------------------
| Student_ID    | Date             | Attendance    |
----------------------------------------------------
| 1             | 2017-01-01       | 4             |
| 1             | 2017-02-01       | 3             |
| 1             | 2017-03-01       | 0             |
| 1             | 2017-04-01       | 4             |
| 1             | 2017-05-01       | 0             |
| 1             | 2017-06-01       | 2             |
| 2             | 2017-01-01       | 0             |
| 2             | 2017-02-01       | 0             |
| 2             | 2017-03-01       | 5             |
| 1             | 2017-04-01       | 0             |
| 2             | 2017-05-01       | 2             |
| 1             | 2017-06-01       | 0             |
----------------------------------------------------

我尝试调用此 View 并进行完全连接，但未成功

SELECT
    CAST(CAST('2017-01-01' AS DATE) + (interval '1' month * generate_series(0,11)) AS DATE) AS month,
    0 AS attendance

但它不起作用，但我觉得有点接近实际解决方案。感谢帮助。

Answer 1

使用交叉连接生成每个 student_id 的所有日期组合，然后左连接原始表以获取带有0的缺失行值(value)观。

select i.student_id,m.mth,coalesce(t.attendance,0)
from (select distinct student_id from tbl) i
cross join generate_series('2017-01-01'::date,'2017-12-01'::date, '1 MONTH') m(mth)
left join tbl t on t.student_id=i.student_id and t.dt=m.mth