用语言来表达这个问题,我有一个包含订阅者和每天数据的大表。如果订阅者不再存在,那么他们将没有更多的记录,即SUB123从2021年10月28日起不再存在,那么该订阅者将每天都有记录,直到2021年10月27日。手头的问题是,一些订阅者没有日期,这可能是由于周末或其他问题。我想用空值填充这些记录,这样它们就可以记录了。
当前问题:
<表类>
用户
日期
牧师
tbody><<tr>sub123 25/10/2021 256 sub456 25/10/2021 282 sub123 26/10/2021 652 sub123 27/10/2021 396 sub456 28/10/2021 132 sub456 29/10/2021 484 sub456 01/11/2021 96 sub456 02/11/2021 45 表类>
您可以使用lag
函数生成缺失范围以与unnest
平整化,并额外处理Rev
:
-- sample data
WITH dataset (Subscriber, Date, Rev) AS (
VALUES ('sub123', date_parse('25-10-2021', '%d-%m-%Y'), 256),
('sub456', date_parse('25-10-2021', '%d-%m-%Y'), 282),
('sub123', date_parse('26-10-2021', '%d-%m-%Y'), 652),
('sub123', date_parse('27-10-2021', '%d-%m-%Y'), 396),
('sub456', date_parse('28-10-2021', '%d-%m-%Y'), 132),
('sub456', date_parse('29-10-2021', '%d-%m-%Y'), 484),
('sub456', date_parse('01-11-2021', '%d-%m-%Y'), 96),
('sub456', date_parse('02-11-2021', '%d-%m-%Y'), 45)
)
-- query
select subscriber, lifted_date as date, if(date = lifted_date, rev, NULL) rev
from
(
select Subscriber,
Rev,
cast(date as date) date,
lag(cast(date as date)) over(partition by Subscriber order by date) prev_date
from dataset
)
cross join unnest(
array_except(sequence(coalesce(prev_date, date), date, interval '1' day), array[prev_date])
) as t(lifted_date)
order by subscriber, date
输出:
<表类>用户 日期 牧师 tbody><<tr>sub123 2021-10-25 00:00:00.000 256 sub123 2021-10-26 00:00:00.000 652 sub123 2021-10-27 00:00:00.000 396 sub456 2021-10-25 00:00:00.000 282 sub456 2021-10-26 00:00:00.000 sub456 2021-10-27 00:00:00.000 sub456 2021-10-28 00:00:00.000 132 sub456 2021-10-29 00:00:00.000 484 sub456 2021-10-30 00:00:00.000 sub456 2021-10-31 00:00:00.000 sub456 2021-11-01 00:00:00.000 96 sub456 2021-11-02 00:00:00.000 45 表类>