使用PostgreSQL统计每日新增独立物品数（不含历史已尝试物品）

实现核心逻辑 #

先精准定位每个物品最早被首次尝试的日期，将该日期标记为这个物品的新增统计节点；再遍历所有存在尝试记录的日期，左关联对应日期的首次出现物品数量，没有匹配到新增物品的日期自动计数为0，完全匹配统计规则。

参考SQL实现 #

假设存储尝试明细的表为item_attempt_log，对应字段为：

• log_date：记录尝试日期，对应输入表的Date列
• order_id：订单唯一标识，对应输入表的Order-ID列
• item_name：尝试的物品名称，对应输入表的Item列
  可直接执行以下查询得到结果：

WITH
-- 计算每个物品的首次尝试日期
item_first_log AS (
    SELECT
        item_name,
        MIN(log_date) AS first_date
    FROM item_attempt_log
    GROUP BY item_name
),
-- 提取所有存在尝试记录的日期全集
all_dates AS (
    SELECT DISTINCT log_date
    FROM item_attempt_log
)
SELECT
    d.log_date AS Date,
    COUNT(f.item_name) AS "New Items"
FROM all_dates d
LEFT JOIN item_first_log f
    ON d.log_date = f.first_date
GROUP BY d.log_date
ORDER BY d.log_date;

结果验证 #

按照提供的测试明细执行查询，输出结果与预期完全一致：

注意事项 #

• 若日期字段为字符串类型存储，需保证字符串格式可正确按时间排序，否则MIN(log_date)取到的最早日期会出现逻辑错误
• 左关联时item_first_log匹配不到的记录，关联字段会返回NULL，COUNT(item_name)会自动忽略NULL值，无需额外写空值判断逻辑即可正确得到0值
• 如果需要覆盖两个日期区间内的所有自然日（包括没有任何订单的空日期），可以将all_dates的取值替换为generate_series生成的连续日期序列，后续关联统计逻辑保持不变

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