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

Question

我有两个表

table1 (p double precesion, t timestamp without zone) 

和

table2 (v double precision, t timestamp without zone) 

每分钟的值。

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

有些值可能不存在，在这种情况下相应的返回值应该是 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