如何在SQL中按分区实现去重累计计数？

大多数SQL数据库不支持在带ORDER BY的窗口函数中使用COUNT(DISTINCT)，因为排序后去重的逻辑会带来性能和实现复杂度问题。以下是两种可行的解决方案：

方法一：标记首次出现的purchase-id后累计求和 #

先为每个用户的每个purchase-id标记首次出现的记录，再通过窗口函数累计求和得到去重后的计数：

SELECT 
  date,
  `user-id`,
  `purchase-id`,
  SUM(is_first_purchase) OVER (PARTITION BY `user-id` ORDER BY date) AS cum_purchase
FROM (
  SELECT 
    date,
    `user-id`,
    `purchase-id`,
    -- 标记当前purchase-id是否为该用户的首次出现
    CASE 
      WHEN ROW_NUMBER() OVER (PARTITION BY `user-id`, `purchase-id` ORDER BY date) = 1 
      THEN 1 
      ELSE 0 
    END AS is_first_purchase
  FROM your_table_name
) AS marked_data
ORDER BY date;

逻辑说明 #

• 子查询通过ROW_NUMBER()按用户和purchase-id分组，给每个purchase-id的第一条记录标记为1，后续重复记录标记为0；
• 外层用SUM()窗口函数按用户分组、日期排序，累计求和标记值，最终得到去重后的累计购买数。

方法二：先提取最小日期再关联统计 #

先获取每个用户每个purchase-id的最早出现日期，再关联原表统计当前日期及之前的去重数量：

SELECT 
  t.date,
  t.`user-id`,
  t.`purchase-id`,
  COUNT(DISTINCT p.min_date) AS cum_purchase
FROM your_table_name t
LEFT JOIN (
  SELECT 
    `user-id`,
    `purchase-id`,
    MIN(date) AS min_date
  FROM your_table_name
  GROUP BY `user-id`, `purchase-id`
) p ON t.`user-id` = p.`user-id` AND p.min_date <= t.date
GROUP BY t.date, t.`user-id`, t.`purchase-id`
ORDER BY t.date;

逻辑说明 #

• 子查询提取每个用户每个purchase-id的最早出现日期；
• 关联原表后，统计该用户所有purchase-id的最早日期<=当前记录日期的数量，即为去重后的累计计数。

两种方法均可得到目标结果：

date       | user-id | purchase-id | cum_purchase
2020-01-01 | 1       | 244         | 1
2020-01-03 | 1       | 244         | 1
2020-02-01 | 1       | 524         | 2
2020-03-01 | 2       | 443         | 1

内容的提问来源于stack exchange，提问作者Tara-S1983