AWS Athena(Presto)按年月累计统计分区内去重category数量

我正在使用基于Presto引擎的AWS Athena服务，现有名为base的表，表结构及样例数据如下：

需求说明 #

需要编写SQL查询，实现按id分区、按年月时间正序，累计统计每个id下截至当前行出现过的category去重值数量，同时保留原表所有列，新增sumC字段的期望输出如下：

已尝试的错误方案 #

• 普通累计计数窗口函数方案：得到的是不去重的累计值，返回sumC为1,2,3,1,2,3，不符合要求，SQL如下：

SELECT id, 
       category, 
       year, 
       month, 
       COUNT(category) OVER (PARTITION BY id ORDER BY year, month) AS sumC 
FROM base;

注：Presto/Athena引擎不支持窗口函数内直接使用COUNT(DISTINCT xxx)语法，无法直接修改上述语句实现需求。

• DENSE_RANK方案：因为排序维度没有加入年月时间字段，所有行返回sumC均为2,2,2,2,2,2，不符合预期，SQL如下：

SELECT 
  DENSE_RANK() OVER (PARTITION BY id ORDER BY category) 
+ DENSE_RANK() OVER (PARTITION BY id ORDER BY category) 
- 1 as sumC
FROM base;

通过标记每个category首次出现的行+累计求和的方式实现：对于每个id下的每个category，只在它第一次按时间排序出现的那一行记为1，其余重复出现的行记为0，之后按时间顺序做累计求和，就能得到滚动去重的计数结果。

可直接在Athena中运行的SQL如下：

WITH base_with_first_flag AS (
  SELECT 
    id,
    category,
    year,
    month,
    -- 按id分区、年月排序，判断当前行的category是否是第一次出现
    CASE 
      WHEN ROW_NUMBER() OVER (PARTITION BY id, category ORDER BY year, month) = 1 THEN 1 
      ELSE 0 
    END AS is_first_occur
  FROM base
)
SELECT 
  id,
  category,
  year,
  month,
  -- 对首次出现标记做累计求和，得到截至当前行的去重category数量
  SUM(is_first_occur) OVER (
    PARTITION BY id 
    ORDER BY year, month 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS sumC
FROM base_with_first_flag
ORDER BY id, year, month;

逻辑说明 #

• 第一层CTE中，用ROW_NUMBER()给每个id+category组合按时间排序打序号，序号为1的就是该分类在对应id下第一次出现的记录，标记为1，重复出现的标记为0
• 第二层查询中，对标记位按时间顺序做累计求和，每遇到一个新的首次出现的分类，计数就加1，重复出现的分类不会增加计数，完全匹配需求的累计去重统计逻辑
• 该写法完全兼容Presto/Athena引擎，不需要使用引擎不支持的COUNT(DISTINCT)窗口语法

内容的提问来源于stack exchange，提问作者dragonlobster