从sql中的两个表中选择固定行数

Question

我有两张表

table1 (p double precesion, t timestamp without zone) 

，

table2 (v double precision, t timestamp without zone) 

每分钟都有值。

如何为指定小时内的每分钟选择 (t, v, p) 形式的 60 行？

有些值可能不存在，在这种情况下，相应的返回值应该为 NULL。

每小时必须正好有 60 行。

我使用 PostgreSQL。

Answer 1

SELECT  '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL, v, p
FROM    generate_series(0, 59) m
LEFT JOIN
        table1 t1
ON      t1.t >= '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL
        AND t1.t < '2010-01-01 01:00:00'::DATE + (m + 1 || ' minute')::INTERVAL
LEFT JOIN
        table2 t2
ON      t2.t >= '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL
        AND t2.t < '2010-01-01 01:00:00'::DATE + (m + 1 || ' minute')::INTERVAL

这假设每分钟内的记录都是唯一的（尽管秒数可能会有所不同）。

如果不是，您将收到重复的邮件。

在这种情况下，您可能想要聚合它们（例如求和）：

SELECT  '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL,
        COALESCE
        (
        SELECT  SUM(p)
        FROM    table1 t1
        WHERE   t1.t >= '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL
                AND t1.t < '2010-01-01 01:00:00'::DATE + (m + 1 || ' minute')::INTERVAL
        ), 0) p,
        COALESCE
        (
        SELECT  SUM(v)
        FROM    table2 t2
        WHERE   t2.t >= '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL
                AND t2.t < '2010-01-01 01:00:00'::DATE + (m + 1 || ' minute')::INTERVAL
        ), 0) v
FROM    generate_series(0, 59) m