时间戳

首页 > 解决方案 > Athena/Presto SQL 每天汇总历史数据信息

sql - Athena/Presto SQL 每天汇总历史数据信息

问题描述

我有以下示例表，它使用变更数据捕获来捕获历史信息：

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_date 上或之前处于活动状态的 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_date 执行此操作，但需要为每个 cdc_date 重新计算排名，我无法想到没有过程或递归的解决方案。

请使用 Athena/Presto SQL 提出解决方案。

标签： sqlaggregateprestoamazon-athenachange-data-capture

解决方案

您可以将累积和与聚合一起使用：

select cdc_date,
       sum(sum(case when cdc_flag = 'active' then 1
                    when cdc_flag = 'Deleted' then -1
                    else 0
               end)
          ) over (order by cdc_date) as num_actives
from t
group by cdc_date;

推荐阅读