CODEWITHSUNDEEP
sqlpostgresql
Oleg
Oleg

Reputation: 61

Select a fixed number of rows from two tables in sql

I have two tables 

table1 (p double precesion, t timestamp without zone)

and 

table2 (v double precision, t timestamp without zone)

with values for every minute.

How do I select 60 rows of the form (t, v, p) for every minute in a specified hour?

Some values may not be present, in such case the corresponding returned value should be NULL.

There must be exactly 60 rows for every hour.

I use PostgreSQL.

Upvotes: 3

Views: 367

Answers (1)

Quassnoi
Quassnoi

Reputation: 425683

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

This assumes the records are unique within each minute (though the seconds may vary).

If they are not, you will be receiving duplicates.

In this case, you may want to aggregate them (say, sum):

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

Upvotes: 1

Related Questions