用缺失日期的数据填充表格(postgresql，redshift)

Question

我正在尝试填写缺少日期的每日数据，但找不到答案，请帮忙。

我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 中，但这不可取，因为我的表有数十亿个缺失的日期。

总结：

在最后几天，我说：

1. Amazon-redshift 适用于第 8 版 PostgreSql，这就是为什么它不支持像JOIN LATERAL这样漂亮的命令
2. Redshift 也不支持generate_series和CTEs
3. 但它支持简单的WITH(谢谢@systemjack)，但WITH RECURSIVE不支持

Answer 1

最后，我完成了我的任务，我想分享一些有用的东西。

我没有使用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)
)

我希望它能帮助某人。

Answer 2

看看查询背后的想法：

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;

您将获得与第一个示例中相同的结果。

Answer 3

另一种解决方案，避免所有"现代"功能 ;-]

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

Answer 4

这是一个丑陋的技巧，在这种情况下，让 redshift 使用日期将新行生成到表中。此示例将输出限制为前 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

应该可以将其与初始人口查询相结合，并一次完成所有操作。