我有下面的示例表,它使用变更数据捕获来捕获历史信息:
id cdc_date cdc_flag active name
1 2020-07-12 Insert true a
2 2020-07-12 Insert true b
3 2020-07-12 Insert true c
4 2020-07-12 Insert true d
1 2020-07-13 Update false a
3 2020-07-13 Update true c_changed
4 2020-07-14 Deleted true d
对于任何列的更新,都会向表中添加一个新条目。因此,存在同一id的多个版本。
我需要找到在每个cdc\ U日期当天或之前处于活动状态的ID的总数。
期望输出:
cdc_date count_active
2020-07-12 4
2020-07-13 4
2020-07-14 3
我无法在athena中为所需的输出形成查询,因为没有过程或递归查询可用。
以下是我计算某个特定日期的活动ID的方法:
id cdc_date cdc_flag active rank
1 2020-07-12 Insert true 2
2 2020-07-12 Insert true 1
3 2020-07-12 Insert true 2
4 2020-07-12 Insert true 2
1 2020-07-13 Update false 1
3 2020-07-13 Update true 1
4 2020-07-14 Deleted true 1
Select date('2020-07-14') as cdc_date, sum(if(active = 'true',1,0)) as count_active from
(Select *, rank over (partition by id over cdc_date desc) as rank)
where rank = 1 and cdc_flag != 'Deleted' and cdc_date <= date('2020-07-14')
我需要为每个cdc\u日期这样做,但排名需要为每个cdc\u日期重新计算,我无法想到一个没有过程或递归的解决方案。
请建议使用athena/presto sql解决此问题。
1条答案
按热度按时间exdqitrt1#
可以将累积和与聚合一起使用: