Заполните таблицу данными о пропущенной дате (postgresql, красное смещение)

Взгляните на идею запроса:

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)

Вам нужно будет выбрать даты начала и окончания в соответствии с вашими требованиями. Запрос может быть довольно дорогим (вы упомянули о миллиардах пробелов), поэтому применяйте его с осторожностью (тестируйте на меньшем подмножестве данных или выполняйте поэтапно).

При отсутствии generate_series() вы можете создать свой собственный генератор. Вот интересный пример. Взгляды из цитируемой статьи можно использовать вместо generate_series(). Например, если вам нужен период '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;

вы получите тот же результат, что и в первом примере.

Альтернативное решение, избегающее всех «современных» функций; -]

-- \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
        ;

Вот уродливый способ получить красное смещение для создания новых строк в таблице, используя в этом случае дату. В этом примере вывод ограничен предыдущими 30 днями. Диапазоны можно изменять или удалять. Тот же подход можно использовать и для минут, секунд и т. Д.

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

Чтобы настроить таргетинг на определенный временной диапазон, измените sysdate на литерал, который будет последним днем ​​после конца диапазона, который вы хотите, и ограничение на количество дней, которое нужно охватить.

Вставка будет примерно такой:

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
)

Мне не удалось протестировать вставку, поэтому, возможно, потребуется дополнительная настройка.

Ссылкой на таблицу stv_blocklist может быть любая таблица с достаточным количеством строк в ней, чтобы покрыть предел диапазона в предложении with, и используется для предоставления начального значения для оконной функции row_number().

Если у вас есть строки только с датой, вы можете обновить их самой последней полной записью следующим образом:

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

Это довольно медленно, но для небольшого набора данных или разового вполне подойдет. Мне удалось протестировать аналогичный запрос.

ОБНОВЛЕНИЕ: я думаю, что эта версия запроса для заполнения нулей должна работать лучше и учитывать домен и дату. Тестировал аналогичную версию.

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

Должна быть возможность объединить это с первоначальным запросом населения и сделать все сразу.

Наконец-то я выполнил свою задачу и хочу поделиться некоторыми полезными вещами.

Вместо generate_series я использовал этот хук:

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;

Чтобы получить список URL-адресов, которые я должен заполнить данными, я использовал это:

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;

Затем я комбинирую данные, назовем их 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;

А чтобы получить данные к ближайшей дате, я сделал следующее:

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)
)

Надеюсь, это кому-то поможет.