如何用SQL计算账户截至各日期的累计唯一SKU购买数量

现有一张存储账户购买信息的销售订单行项目表purchases，包含以下字段：

• accountid：账户ID
• purchase_date：购买日期
• productid：商品SKU
  每条记录代表1件商品的采购（即所有记录的quantity=1）。

需要生成结果表，展示每个账户截至特定日期，从首次购买日起累计购买的唯一SKU总数。

示例数据 #

create schema adhoc_data.temp;
create table adhoc_data.temp.purchases ( accountid varchar, purchase_date date, productid varchar);
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-01', '534ad451f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-02', '534ad451f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-03', '534ad451f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-04', '534ad451f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-05', '0f9d321ad');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-06', '0f9d321ad');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-07', '534ad451f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-08', '4a5d93a1f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-09', '4a5d93a1f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-10', '4a5d93a1f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-10', '534ad451f');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-10', '0f9d321ad');
insert into purchases (accountid, purchase_date, productid) values ('a1', '2022-01-11', '9cd018fc0');

期望结果 #

'a1', '2022-01-01', 1
'a1', '2022-01-02', 1
'a1', '2022-01-03', 1
'a1', '2022-01-04', 1
'a1', '2022-01-05', 2
'a1', '2022-01-06', 2
'a1', '2022-01-07', 2
'a1', '2022-01-08', 3
'a1', '2022-01-09', 3
'a1', '2022-01-10', 3
'a1', '2022-01-11', 4

尝试的错误SQL #

select 
    t1.accountid, 
    t1.purchasedate,
    count(distinct t1.productid)
from
    purchases as t1
left join purchases as t2 on t1.accountid = t2.accountid and t1.purchase_date >= t2.purchase_date
group by 
    t1.accountid, 
    t1.purchasedate,
order by 
    t1.accountid,
    t2.purchasedate

错误原因 #

你的SQL存在两个关键问题：

1. 字段拼写错误：t1.purchasedate应为t1.purchase_date，与表中字段名不一致。
2. 逻辑错误：自连接后未正确利用t2表数据，统计的是当前日期的唯一SKU数，而非截至当前日期的累计值。

正确实现方案 #

方法一：基于CTE的通用实现（兼容多数SQL引擎）

先获取每个账户每个SKU的首次购买日期，再关联原表计算每个日期的累计唯一SKU数：

WITH first_purchase AS (
    -- 第一步：计算每个账户每个SKU的首次购买日期
    SELECT 
        accountid,
        productid,
        MIN(purchase_date) AS first_buy_date
    FROM adhoc_data.temp.purchases
    GROUP BY accountid, productid
),
date_cumulative AS (
    -- 第二步：对每个购买日期，统计截至该日期的累计唯一SKU数
    SELECT 
        p.accountid,
        p.purchase_date,
        COUNT(fp.productid) AS cumulative_unique_sku
    FROM adhoc_data.temp.purchases p
    LEFT JOIN first_purchase fp 
        ON p.accountid = fp.accountid 
        AND p.purchase_date >= fp.first_buy_date
    GROUP BY p.accountid, p.purchase_date
)
-- 第三步：输出结果并排序
SELECT 
    accountid,
    purchase_date,
    cumulative_unique_sku
FROM date_cumulative
ORDER BY accountid, purchase_date;

方法二：窗口函数实现（高效简洁，需引擎支持）

如果使用PostgreSQL、BigQuery、Spark SQL等支持窗口函数的引擎，可采用更简洁的写法：

SELECT DISTINCT
    accountid,
    purchase_date,
    COUNT(DISTINCT productid) OVER (
        PARTITION BY accountid 
        ORDER BY purchase_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_unique_sku
FROM adhoc_data.temp.purchases
ORDER BY accountid, purchase_date;

注意：部分SQL引擎（如MySQL 8.0之前版本）不支持窗口函数中的COUNT(DISTINCT)，此时优先使用方法一。

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