Fill the table with data for missing date (postgresql, redshift)

Question

I'm trying to fill daily data for missing dates and can not find an answer, please help.

My daily_table example:

      url          | timestamp_gmt | visitors | hits  | other.. 
-------------------+---------------+----------+-------+-------
 www.domain.com/1  | 2016-04-12    |   1231   | 23423 |
 www.domain.com/1  | 2016-04-13    |   1374   | 26482 |
 www.domain.com/1  | 2016-04-17    |   1262   | 21493 |
 www.domain.com/2  | 2016-05-09    |   2345   | 35471 |          

Expected result: I wand to fill this table with data for every domain and every day which just copy data from previous date:

      url          | timestamp_gmt | visitors | hits  | other.. 
-------------------+---------------+----------+-------+-------
 www.domain.com/1  | 2016-04-12    |   1231   | 23423 |
 www.domain.com/1  | 2016-04-13    |   1374   | 26482 |
 www.domain.com/1  | 2016-04-14    |   1374   | 26482 |     <-added
 www.domain.com/1  | 2016-04-15    |   1374   | 26482 |     <-added
 www.domain.com/1  | 2016-04-16    |   1374   | 26482 |     <-added
 www.domain.com/1  | 2016-04-17    |   1262   | 21493 |
 www.domain.com/2  | 2016-05-09    |   2345   | 35471 |          

I can move a part of the logic into php, but it is undesirable, because my table has billions of missing dates.

SUMMARY:

During a few last days I foud out that:

1. Amazon-redshift works with 8-th version of PostgreSql, that's why it does not support such a beautiful command like JOIN LATERAL
2. Redshift also does not support generate_series and CTEs
3. But it supports simple WITH (thank you @systemjack) but WITH RECURSIVE does not

Scrutable

An obvious question: why? Wouldn't it make more sense to leave the gaps as they are, and let the web pages/whatever choose how to display this? – Schermerhorn

This is a requirement, because our customers use tables directly, not via some interface. – Scrutable

Does redshift support (recursive) CTEs? – Vaporize

I don not know about CTE, i will read some documentation and answer later – Scrutable

as I found out CTE's also is not supported... – Scrutable

This answer uses a window function to generate rows of dates: https://mcmap.net/q/394340/-generate_series-method-fails-in-redshift – Belter

Answer 1

Finally, I finished my task and I want to share some useful things.

Instead of generate_series I used this hook:

WITH date_range AS (
  SELECT trunc(current_date - (row_number() OVER ())) AS date
  FROM any_table  -- any of your table which has enough data
  LIMIT 365
) SELECT * FROM date_range;

To get list of URLs which I have to fill with the data I used this:

WITH url_list AS (
  SELECT
    url AS gapsed_url,
    MIN(timestamp_gmt) AS min_date,
    MAX(timestamp_gmt) AS max_date
  FROM daily_table
  WHERE url IN (
    SELECT url FROM daily_table GROUP BY url
    HAVING count(url) < (MAX(timestamp_gmt) - MIN(timestamp_gmt) + 1)
  )
  GROUP BY url
) SELECT * FROM url_list;

Then I combinet given data, let's call it url_mapping:

SELECT t1.*, t2.gapsed_url FROM date_range AS t1 CROSS JOIN url_list AS t2
WHERE t1.date <= t2.max_date AND t1.date >= t2.min_date;

And to get data by closest date I did the following:

SELECT sd.*
FROM url_mapping AS um JOIN daily_table AS sd
ON um.gapsed_url = sd.url AND (
  sd.timestamp_gmt = (SELECT max(timestamp_gmt) FROM daily_table WHERE url = sd.url AND timestamp_gmt <= um.date)
)

I hope it will help someone.

Scrutable

Answer 2

Look at the idea behind the query:

select distinct on (domain, new_date) *
from (
    select new_date::date 
    from generate_series('2016-04-12', '2016-04-17', '1d'::interval) new_date
    ) s 
left join a_table t on date <= new_date
order by domain, new_date, date desc;

  new_date  |     domain      |    date    | visitors | hits  
------------+-----------------+------------+----------+-------
 2016-04-12 | www.domain1.com | 2016-04-12 |     1231 | 23423
 2016-04-13 | www.domain1.com | 2016-04-13 |     1374 | 26482
 2016-04-14 | www.domain1.com | 2016-04-13 |     1374 | 26482
 2016-04-15 | www.domain1.com | 2016-04-13 |     1374 | 26482
 2016-04-16 | www.domain1.com | 2016-04-13 |     1374 | 26482
 2016-04-17 | www.domain1.com | 2016-04-17 |     1262 | 21493
(6 rows)

You'll have to choose start and end dates according to your requirements. The query may be quite expensive (you mentioned about billions gaps) so apply it with caution (test on a smaller data subset or execute by stages).

In the absence of generate_series() you can create your own generator. Here is an interesting example. Views from the cited article can be used instead of generate_series(). For example, if you need the period '2016-04-12' + 5 days:

select distinct on (domain, new_date) *
from (
    select '2016-04-12'::date+ n new_date
    from generator_16
    where n < 6
    ) s 
left join a_table t on date <= new_date
order by domain, new_date, date desc;

you'll get the same result like in the first example.

Cooperage

It's really interesting, thanks for sharing, will try and give an aswer – Scrutable

Answer 3

An alternative solution, avoiding all "modern" features ;-]

-- \i tmp.sql

        -- NOTE: date and domain are keywords in SQL
CREATE TABLE ztable
        ( zdomain      TEXT NOT NULL
        , zdate       DATE NOT NULL
        , visitors      INTEGER NOT NULL DEFAULT 0
        , hits          INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zdomain,zdate)
        );
INSERT INTO ztable (zdomain,zdate,visitors,hits) VALUES
  ('www.domain1.com', '2016-04-12' ,1231 ,23423 )
 ,('www.domain1.com', '2016-04-13' ,1374 ,26482 )
 ,('www.domain1.com', '2016-04-17' ,1262 ,21493 )
 ,('www.domain3.com', '2016-04-14' ,3245 ,53471 )       -- << cheating!
 ,('www.domain3.com', '2016-04-15' ,2435 ,34571 )
 ,('www.domain3.com', '2016-04-16' ,2354 ,35741 )
 ,('www.domain2.com', '2016-05-09' ,2345 ,35471 ) ;

        -- Create "Calendar" table with all possible dates
        -- from the existing data in ztable.
        -- [if there are sufficient different domains
        -- in ztable there will be no gaps]
        -- [Normally the table would be filled by generate_series()
        -- or even a recursive CTE]
        -- An exta advantage is that a table can be indexed.
CREATE TABLE date_domain AS
SELECT DISTINCT zdate AS zdate
FROM ztable;
ALTER TABLE date_domain ADD PRIMARY KEY (zdate);
-- SELECT * FROM date_domain;

        -- Finding the closest previous record
        -- without using window functions or aggregate queries.
SELECT d.zdate, t.zdate, t.zdomain
        ,t.visitors, t.hits
        , (d.zdate <> t.zdate) AS is_fake -- for fun
FROM date_domain d
LEFT JOIN ztable t
        ON t.zdate <= d.zdate
        AND NOT EXISTS ( SELECT * FROM ztable nx
                WHERE nx.zdomain = t.zdomain
                AND nx.zdate > d.zdate
                AND nx.zdate < t.zdate
                )
ORDER BY t.zdomain, d.zdate
        ;

Vaporize

Answer 4

Here's an ugly hack to get redshift to generate new rows into a table using a date in this case. This example limits the output to the previous 30 days. The ranges can be tweaked or removed. This same approach can be used for minutes, seconds, etc. as well.

with days as (
    select (dateadd(day, -row_number() over (order by true), sysdate::date+'1 day'::interval)) as day
            from stv_blocklist limit 30
)
select day from days order by day

To target a specific time range change the sysdate to a literal which would be the last day after the end of the range you want and the limit to how many days to cover.

The insert would be something like so:

with days as (
    select (dateadd(day, -row_number() over (order by true), sysdate::date+'1 day'::interval)) as day
            from stv_blocklist limit 30
)
insert into your_table (domain, date) (
    select dns.domain, d.day
    from days d
    cross join (select distinct(domain) from your_table) dns
    left join your_table y on y.domain=dns.domain and y.date=d.day
    where y.date is null
)

I wasn't able to test the insert so that might need some tweaking.

The reference to the stv_blocklist table could be any table with enough rows in it to cover the range limit in the with clause and is used to provide a seed for the row_number() window function.

Once you have the date only rows in place you can update them with the most recent full record like so:

update your_table set visitors=t.visitors, hits=t.hits
from (
    select a.domain, a.date, b.visitors, b.hits
    from your_table a
    inner join your_table b
        on b.domain=a.domain and b.date=(SELECT max(date) FROM your_table where domain=a.domain and hits is not null and date < a.date)
    where a.hits is null
) t
where your_table.domain=t.domain and your_table.date=t.date

This is pretty slow but for a smaller data set or a one-off it should be fine. I was able to test a similar query.

UPDATE: I think this version of the query to fill in the nulls should work better and account for domain and date. I tested a similar version.

update your_table set visitors=t.prev_visitors, hits=t.prev_hits
from (
    select domain, date, hits
        lag(visitors,1) ignore nulls over (partition by domain order by date) as prev_visitors,
        lag(hits,1) ignore nulls over (partition by domain order by date) as prev_hits
    from your_table
) t
where t.hits is null and your_table.domain=t.domain and your_table.date=t.date

It should be possible to combine this with the initial population query and do it all at once.

Belter

I will try and let you know ASAP – Scrutable

CTEs are not supported in redsift. (see OP's comment just below the question) I doubt if window functions are supported, since they were introduced in postgres-8.4. – Vaporize

@Vaporize Redshift supports window functions just fine. I use them all the time. docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.html – Belter

I was surprised, that WITH xx AS is supported by Redshift, while WITH RECURSIVE xx AS is not. Now I'm trying to use it and it seems that it wuld be useful. – Scrutable

Unfortunately i have multiple domains per each date, and min/max dates per domen are different – Scrutable

That's a tough one. Are there lots of domains or a few? Should each domain have a record for every day or are there start and end dates for a given domain? – Belter

I made untested updates which should fill in all dates within the specified range for each domain. I also have another idea I'll post when it's ready. – Belter