Я пытаюсь заполнить ежедневные данные для пропущенных дат и не могу найти ответ, пожалуйста, помогите.
мой daily_table
пример:
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 |
Ожидаемый результат: я хочу заполнить эту таблицу данными для каждого домена и каждый день, которые просто копируют данные из предыдущего 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 |
Я могу переместить часть логики в php, но это нежелательно, потому что в моей таблице миллиарды пропущенных дат.
РЕЗЮМЕ:
В последние несколько дней я обнаружил, что:
JOIN LATERAL
generate_series
а также CTEs
WITH
(спасибо @systemjack) но WITH RECURSIVE
неПосмотрите на идею запроса:
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)
)
Надеюсь, это кому-нибудь поможет.