SQL计数不同的分区累计

Question

我使用AWS Athena(基于Presto)，我有这个表命名为base:

<表类> id 类别 年 月 tbody><<tr>1202161b2022812022112202212202242b20226

• 创建一个新列，它将包含每个类别"第一次看到(分区上的">id"， ">类别"和订购">年",">"月)
• 在同一分区
上计算该列的运行和

WITH cte AS (
SELECT *, 
CASE WHEN ROW_NUMBER() OVER(
PARTITION BY id, category
ORDER     BY year, month) = 1
THEN 1 
ELSE 0 
END AS rn1
FROM base
ORDER BY id, 
year_, 
month_
)
SELECT id,
category,
year_,
month_,
SUM(rn1) OVER(
PARTITION BY id
ORDER     BY year, month 
) AS sumC
FROM cte