如何用SQL实现滚动去重物品统计及列表？是否需用递归SQL？

问题说明 #

现有一张每日库存记录表inventory，表结构查询语句：

SELECT timestamp, item FROM inventory;

数据示例：

需求是获取截至各日期的滚动去重物品数量和滚动去重物品列表，预期结果如下：

滚动去重物品数量 #

滚动去重物品列表 #

实现方法 #

方法一：非递归窗口函数+聚合（推荐，性能更优） #

核心思路是先找出每个物品首次出现的日期，再针对每个统计日期，统计所有首次出现日期≤当前日期的物品：

-- 获取所有需要统计的日期列表
WITH date_list AS (
    SELECT DISTINCT timestamp AS dt FROM inventory
),
-- 找出每个物品的最早出现日期
first_occurrence AS (
    SELECT item, MIN(timestamp) AS first_dt
    FROM inventory
    GROUP BY item
)
-- 统计每个日期的滚动去重数量和列表
SELECT
    dl.dt AS Time,
    COUNT(fo.item) AS distinct_item_count(rolling),
    STRING_AGG(fo.item, ',' ORDER BY fo.item) AS distinct_item
FROM date_list dl
LEFT JOIN first_occurrence fo ON fo.first_dt <= dl.dt
GROUP BY dl.dt
ORDER BY dl.dt;

方法二：递归SQL实现 #

递归CTE可以按日期顺序逐步累加物品，每次保留之前所有已出现的物品：

-- 获取排序后的日期列表
WITH date_sequence AS (
    SELECT 
        timestamp AS dt,
        ROW_NUMBER() OVER (ORDER BY timestamp) AS rn
    FROM inventory
    GROUP BY timestamp
),
-- 递归CTE：逐步构建滚动物品集合
rolling_inventory AS (
    -- 递归起点：第一个日期的物品
    SELECT
        ds.dt,
        ARRAY_AGG(DISTINCT i.item ORDER BY i.item) AS item_array,
        ds.rn
    FROM date_sequence ds
    JOIN inventory i ON ds.dt = i.timestamp
    WHERE ds.rn = 1
    GROUP BY ds.dt, ds.rn
    
    UNION ALL
    
    -- 递归步骤：合并当前日期的新物品与之前的物品集合
    SELECT
        ds.dt,
        ARRAY(
            SELECT DISTINCT unnest(ri.item_array || ARRAY_AGG(DISTINCT i.item ORDER BY i.item))
            ORDER BY 1
        ),
        ds.rn
    FROM rolling_inventory ri
    JOIN date_sequence ds ON ds.rn = ri.rn + 1
    LEFT JOIN inventory i ON ds.dt = i.timestamp
    GROUP BY ds.dt, ds.rn, ri.item_array
)
-- 转换为预期格式输出
SELECT
    dt AS Time,
    ARRAY_LENGTH(item_array, 1) AS distinct_item_count(rolling),
    STRING_AGG(item, ',' ORDER BY item) AS distinct_item
FROM rolling_inventory, unnest(item_array) AS item
GROUP BY dt, item_array
ORDER BY dt;

说明：递归SQL的写法更直观体现“滚动累加”的逻辑，但对于数据量较大的场景，性能不如非递归方法。

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