SQL 在另一个查询中使用 UNION 查询的结果

Question

我怎样才能完成这个查询？

现在，我正在处理的查询是这样的，但它没有生成正确的数据。

SELECT date, coalesce(count,0) AS count 
FROM 
    generate_series(
        '2014-12-13 12:00:00'::timestamp, 
        '2015-01-06 11:00:00'::timestamp, 
        '1 hour'::interval
    ) AS date 
    LEFT OUTER JOIN (
        SELECT 
            date_trunc('day', TABLE1.created_at) as day, 
            count(DISTINCT TABLE1.user) as count 
        FROM TABLE1 
        WHERE org_id = 1 
        GROUP BY day
    ) results ON (date = results.day);

我需要为查询提供来自另一个查询的数据，而不是 TABLE1，如下所示:

SELECT TABLE2.user_a as userid, TABLE2.created_at as createdat from TABLE2 
UNION ALL 
SELECT TABLE3.user_b as userid, TABLE3.created_at as createdat from TABLE3 
UNION ALL 
SELECT TABLE4.sender as userid, TABLE4.created_at as createdat from TABLE4;

我该怎么做？

Answer 1

接收表的 select 查询的任何部分(例如，from 子句、join 子句等)都可以接收括号中的查询 - 这称为子查询。请注意，在 Postgres 中，此子查询必须 被赋予一个别名(即，它可以被引用的名称)。所以在你的情况下:

SELECT date, coalesce(count,0) AS count 
FROM 
    generate_series(
        '2014-12-13 12:00:00'::timestamp, 
        '2015-01-06 11:00:00'::timestamp, 
        '1 hour'::interval
    ) AS date 
    LEFT OUTER JOIN (
        SELECT 
            date_trunc('day', subquery.created_at) as day, 
            count(DISTINCT subquery.user) as count 
              -- Subquery here:
        FROM (SELECT TABLE2.user_a as userid, TABLE2.created_at as createdat from TABLE2 
              UNION ALL 
              SELECT TABLE3.user_b as userid, TABLE3.created_at as createdat from TABLE3 
              UNION ALL 
              SELECT TABLE4.sender as userid, TABLE4.created_at as createdat from TABLE4) 
             subquery 
        WHERE org_id = 1 
        GROUP BY day
    ) results ON (date = results.day);