合并要连接的2个表

Question

我使用Postgres 9.1，我有一个查询，返回2列，我想复制，即使没有数据存在。我的查询的重要部分是

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id

返回给我所有的机构和所有的interval_times，就像我期望的那样。现在我还想要没有数据的行。我可以执行以下操作来每次获取其中一个

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN generate_series('2012-02-03', '2013-02-18', interval '1 week')
ON DATE_TRUNC('week', generate_series) = interval_time

或

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN unnest(array[1, 3, 4, 5, 7, 9]) AS all_institution_id
ON all_institution_id = institution_id

然而，我想把它们组合起来，这样我就能得到所有机构的所有interval_times。似乎最好的办法是创建某种中间表，其中包含每个interval_time重复的每个institution_id，然后RIGHT JOIN，但我不确定如何做到这一点。如有任何帮助，不胜感激。

编辑

在稍微摆弄了一下之后，看起来我想做的是下面的事情，但是我的语法是错误的

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN unnest(array[1, 3, 4, 5, 7, 9]) AS all_institution_id,
generate_series('2012-02-03', '2013-02-18', interval '1 week')
ON DATE_TRUNC('week', generate_series) = dose_hsts.interval_time
AND all_institution_id = institution_id

Answer 1

我认为你想要一个CROSS JOIN之前，你做LEFT JOIN(取代RIGHT JOIN到同样的效果在这里):

SELECT interval_time::date , institution_id, ...
FROM   generate_series(date_trunc('week', timestamp '2012-02-03')
                     , timestamp '2013-02-18'
                     , interval '1 week')   AS d(interval_time)
CROSS  JOIN unnest(array[1, 3, 4, 5, 7, 9]) AS i(institution_id)
LEFT   JOIN (
   SELECT date_trunc('week', tx_dttm) AS interval_time, institution_id
   FROM   ...
   GROUP  BY 1, 2
   ) x USING (interval_time, institution_id);

关于generate_series():

• 在PostgreSQL中生成两个日期之间的时间序列

Answer 2

我认为你想要的是联合两个查询。试试这个:

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
UNION
SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN generate_series('2012-02-03', '2013-02-18', interval '1 week')
ON DATE_TRUNC('week', generate_series) = interval_time

Answer 3

我使用下面的语法

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN (SELECT * FROM unnest(array[1, 3, 4, 5, 7, 9]) AS all_institution_id,
generate_series('2012-02-03', '2013-02-18', interval '1 week'))
ON DATE_TRUNC('week', generate_series) = dose_hsts.interval_time
AND all_institution_id = institution_id