如何编写SQL为多日期统计用户最近购买的产品数量？

我有一张名为sales的表，包含购买日期（date）、用户ID（user_id）以及用户购买的产品（product），表数据如下：

sales表结构及数据 #

若要查看基于每个用户最近一次购买的产品数量统计，我会使用如下SQL：

原统计SQL（仅统计最终结果） #

WITH latest_sales AS (
SELECT
    date
    , user_id
    , product
    , row_number() OVER(PARTITION BY user_id ORDER BY date DESC) AS rn
FROM
    sales
)
SELECT
    product
    , count(1) AS count
FROM
    latest_sales
WHERE
    rn = 1
GROUP BY
    product

原SQL输出结果 #

但该SQL仅能生成最新日期的统计结果。例如在2021-01-02日期下，统计结果如下：

截至2021-01-02的预期统计结果 #

需要编写SQL实现对多个日期分别统计每个用户最近购买产品的数量，输出格式如下：

目标输出格式 #

解决方案SQL #

-- 生成所有日期与产品的组合，确保每个日期下所有产品都能显示
WITH all_dates_products AS (
    SELECT DISTINCT s.date, p.product
    FROM sales s
    CROSS JOIN (SELECT DISTINCT product FROM sales) p
),
-- 对每个用户，标记截至每个日期的最近购买记录
user_latest_by_date AS (
    SELECT
        adp.date AS stat_date,
        s.user_id,
        s.product,
        -- 对每个用户，在小于等于统计日期的记录中，按日期倒序排，取第一行
        ROW_NUMBER() OVER(PARTITION BY adp.date, s.user_id ORDER BY s.date DESC) AS rn
    FROM all_dates_products adp
    LEFT JOIN sales s 
        ON s.date <= adp.date
),
-- 筛选每个用户在对应统计日期的最近购买产品
latest_sales_by_date AS (
    SELECT stat_date, product
    FROM user_latest_by_date
    WHERE rn = 1
)
-- 按统计日期和产品分组统计数量，没有的补0
SELECT
    adp.date,
    adp.product,
    COUNT(lsbd.product) AS count
FROM all_dates_products adp
LEFT JOIN latest_sales_by_date lsbd
    ON adp.date = lsbd.stat_date
    AND adp.product = lsbd.product
GROUP BY adp.date, adp.product
ORDER BY adp.date, adp.product;

代码说明 #

• all_dates_products：通过笛卡尔积生成所有日期和产品的组合，保证每个日期下每个产品都有对应的行，解决count为0的显示问题。
• user_latest_by_date：将每个统计日期与该日期之前的所有用户购买记录关联，通过窗口函数ROW_NUMBER()为每个用户在每个统计日期下的购买记录按日期倒序编号，最近的记录编号为1。
• latest_sales_by_date：筛选出每个用户在对应统计日期的最近购买产品。
• 最后通过左连接将所有日期产品组合与筛选结果关联，统计数量，左连接确保没有匹配的记录显示为0。

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