By topcat


2019-07-11 20:35:46 8 Comments

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date to each station. I'm working with PostgreSQL 10.9

This is my table:

CREATE TABLE stations (station_id text, value integer, date date);
INSERT INTO stations (station_id, value, date) VALUES 
('001', 10, '2014-01-01'),
('001', 30, '2014-01-03'),
('002', 40, '2014-01-01'),
('002', 50, '2015-01-02'),
('003', 20, '2014-01-01'),
('003', 10, '2015-01-02'),
('003', 70, '2015-01-04');

I also have a table holding unique stations with identifiers.

And I want something like this:

| station | value | date       |
|---------|-------|------------|
| 001     | 10    | 2014-01-01 |
| 001     | NULL  | 2014-01-02 |
| 001     | 30    | 2014-01-03 |
| 001     | NULL  | 2014-01-04 |
| 002     | 40    | 2014-01-01 |
| 002     | 50    | 2014-01-02 |
| 002     | NULL  | 2014-01-03 |
| 002     | NULL  | 2014-01-04 |
| 003     | 20    | 2014-01-01 |
| 003     | 10    | 2014-01-02 |
| 003     | NULL  | 2014-01-03 |
| 003     | 70    | 2014-01-04 |

Following some DBA Exchange (questions)1, I tried a combination of a LEFT JOIN with a LATERAL JOIN:

WITH complete_dates_station AS (
    select station_id,
           generate_series(DATE '2014-01-01', DATE '2014-12-31', INTERVAL '1 day')::DATE as dt
    FROM stations
    GROUP by station_id
    ), temp_join AS (
        SELECT station_id,
               dt,
               s.value
        FROM complete_dates_station
            LEFT JOIN LATERAL (
                SELECT s.value
                FROM stations s
                WHERE s.station_id = complete_dates_station.station_id
                AND s.date = complete_dates_station.dt
                ORDER by s.station_id, date desc
                LIMIT 1) as s on TRUE
             ORDER BY station_id, dt
         ) SELECT * from temp_join

This works like a charm, but this join is really slow for my complete table, which has more than 2M rows and the date range goes over 18 years (I stopped after 4 hrs of running). I tried a simpler approach by using a regular LEFT JOIN, but the table outputs the not-joined groups as missings:

WITH complete_dates_station AS (
    SELECT station_id,
           generate_series(date '2014-01-01', date '2014-12-31', interval '1 day')::date as dt
    from stations
    GROUP BY station_id)
SELECT s.station_id,
       c.dt,
       s.value
FROM complete_dates_station c
    left outer join stations s
    on c.station_id = s.station_id and
    c.dt = s.date;

which yields the following:

| station | value | date       |
|---------|-------|------------|
| 001     | 10    | 2014-01-01 |
| NULL    | NULL  | 2014-01-02 |
| 001     | 30    | 2014-01-03 |
| NULL    | NULL  | 2014-01-04 |
| 002     | 40    | 2014-01-01 |
| 002     | 50    | 2014-01-02 |
| NULL    | NULL  | 2014-01-03 |
| NULL    | NULL  | 2014-01-04 |
| 003     | 20    | 2014-01-01 |
| 003     | 10    | 2014-01-02 |
| NULL    | NULL  | 2014-01-03 |
| 003     | 70    | 2014-01-04 |

There is any way to optimize the first query, or use a simpler approach to fill my station gaps in the second query? I tried already using multicolumn indexes in my source table, but the query is still taking a lot of time.

1 comments

@Erwin Brandstetter 2019-07-11 22:01:59

You also have a table holding unique stations with identifiers, Could look like this:

CREATE TABLE uniq_stations (station_id text);
INSERT INTO uniq_stations VALUES
('001'),
('002'),
('003');

There will be more columns, which are irrelevant for us.

This should be much faster then:

SELECT station_id, s.value, date
FROM   uniq_stations u
CROSS  JOIN (
   SELECT generate_series (timestamp '2014-01-01'
                         , timestamp '2014-01-04'
                         , interval  '1 day')::date
   ) d(date)
LEFT   JOIN stations s USING (station_id, date)
ORDER  BY station_id, date;  -- optional

db<>fiddle here

You do not need a LATERAL join at all, the date series is the same for every station. Only a CROSS JOIN to build the complete Cartesian product of stations and days, then a LEFT [OUTER] JOIN to existing combinations in table stations (an unfortunate table name for its content, btw.). LATERAL joins are great, when needed. But plain joins are faster.

Also, this fills in stations with all days missing, which would not work at all without uniq_stations. You may or may not have such cases.

One of the expensive pieces in this puzzle is identifying unique stations. A task we can skip completely if we can use the added uniq_stations providing what we need. Else we might use DISTINCT ON or a recursive CTE to make use of a matching index. See:

Still more expensive than reading unique rows from a table, but much faster already than what you had. Which was a grand waste of CPU cycles, frankly.

Finally, a multicolumn index on stations (station_id, date) should deliver top notch performance even with a big table stations. (Using a higher percentage of rows from that table makes the index less important.)

There is a reason I use generate_series (timestamp, timestamp, interval):

Related:

Aside: Your station_id should probably type integer. Faster than text, and smaller, too if numbers go beyond 999.

@Erwin Brandstetter 2019-07-11 22:33:03

About CTEs: It's typically faster to use subqueries instead of CTEs. Only use CTEs where you actually need them. This changes with the upcoming Postgres 12 where CTEs can be inlined. Similar for LATERAL: use it when you need it. A plain join is typically cheaper.

Related Questions

Sponsored Content

2 Answered Questions

changing from inner joins to left joins to include null values?

  • 2017-12-29 16:51:27
  • joshua ortiz
  • 135 View
  • 0 Score
  • 2 Answer
  • Tags:   sql-server join

2 Answered Questions

2 Answered Questions

[SOLVED] Insert missing dates within table

1 Answered Questions

[SOLVED] Fill in the gaps between rows using the previous row's values

3 Answered Questions

[SOLVED] Record doesn't show if selecting earlier date interval

  • 2017-05-25 06:27:27
  • Klemkas
  • 59 View
  • 1 Score
  • 3 Answer
  • Tags:   mysql date record

1 Answered Questions

[SOLVED] Include NULL row on join

2 Answered Questions

1 Answered Questions

2 Answered Questions

[SOLVED] LEFT JOIN conversion to INNER JOIN, can't change FROM clause

Sponsored Content