笔者今天遇到一个数据需求，计算从2024年1月1日到今天为止每一天的累计UV，即每一天都是一个从2024年1月1日累计到那一天的UV。
暴力法
一种从结果结果的办法就是制作一个数据集，这个数据集里面的每个date分区都包含了从2024年1月1日到当前日期的所有id，那么根据日期聚合即可得到最终结果。
那么难点在于怎么计算这个数据集了。比较有经验的人可能会想到采用笛卡尔积的形式，先冗余所有数据，然后根据日期过滤出数据，就得到了一个这样数据集了。
with base_tb as (
select *
from values
('id1', 20240101),
('id1', 20240102),
('id1', 20240103),
('id2', 20240102),
('id3', 20240102),
('id3', 20240103)
as (id, date)
)
select a.date, count(distinct b.id)
from base_tb as a
join base_tb as b
on a.date >= b.date and a.date >= 20240101
group by a.date
这里比较大的问题是因为笛卡尔积造成的大量重复导致数据膨胀，任务极有可能跑不出来。
优化一下笛卡尔积的结果
将原始数据列出如下，可以发现，id2这一行，其实在20240102之后统计uv的时候都是会统计到里面的，所以，不管后面id2再在什么时候出现都不影响那天的uv统计，因为它已经在20240102出现过了，所以我们可以对原始数据进行精简。
| id | |||
|---|---|---|---|
| id1 | 20240101 | 20240102 | 20240103 |
| id2 | 20240102 | ||
| id3 | 20240102 | 20240103 |
得到了下面的逻辑：
- 精简数据：计算每个id出现最早的时间。
- 扩充日期：扩充出每个id出现的所有日期。
- 数据展开得到上面1中提到的数据集。
- 再聚合就是最终的结果了。
with base_tb as (
select *
from values
('id1', 20240101),
('id1', 20240102),
('id1', 20240103),
('id2', 20240102),
('id3', 20240102),
('id3', 20240103)
as (id, date)
)
select date, count(1)
from (
select id, explode(dates) date
from (
select id, sequence(
to_date(cast(min_date as string), 'yyyyMMdd'),
to_date(cast(20240105 as string), 'yyyyMMdd'),
interval 1 day
) dates
from (
select id, min(date) min_date
from base_tb
group by id
) as a
) as a
) as a
group by date
