从数据库表中不存在的数据中向查询中添加新行

Question

我有以下样本表：

+----------+------+-------+
|   DATE   | NAME | HOURS |
+----------+------+-------+
| 2018-5-3 | JOHN |   8   |
+----------+------+-------+
| 2018-5-9 | JOHN |   5   |
+----------+------+-------+

如何生成将新行填充到现有数据(例如，示例查询结果)的查询：

+-----------+------+-------+
|   DATE    | NAME | HOURS |
+-----------+------+-------+
| 2018-5-1  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-2  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-3  | JOHN |   8   |
+-----------+------+-------+
| 2018-5-4  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-5  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-6  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-7  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-8  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-9  | JOHN |   5   |
+-----------+------+-------+
| 2018-5-10 | JOHN |   0   |
+-----------+------+-------+

检查我是否将0添加到HOURS列中，因为JOHN没有在指定的日期出现时数(仅在2018-5-3和2018-5-8中)。我目前正试图得到这个结果。这只是我需要处理的一个大表的开始，所以我需要为每个用户生成这个固定的值。我试着用以前生成的日期来使用左/右连接，但是它没有工作。

你能建议我怎样才能做到这一点吗？谢谢。

Answer 1

使用generate_series()和left join

select g.dte, t.name, coalesce(t.hours, 0) as hours
from generate_series('2018-05-01'::date, '2018-05-10'::date, interval '1 day') g(dte) left join
     t 
     on g.dte = t.date;

对于多个用户，您需要为所有用户生成所有行，然后生成left join。

select g.dte, n.name, coalesce(t.hours, 0) as hours
from generate_series('2018-05-01'::date, '2018-05-10'::date, interval '1 day'
                    ) g(dte) cross join
     (select distinct name from t) n left join
     t 
     on g.dte = t.date and n.name = t.name;