基于客户首次购买日的7天周期销售额及客均购买量计算需求

需要处理包含采购日期、客户ID、销售数量的数据集，以每位客户的首次购买日为起点划分7天周期，统计每个周期的销售额，最终计算客户的7天周期平均购买量。

示例数据与目标输出 #

初始数据表 #

7天累计销售额中间表 #

最终周期销售额表 #

客均购买量表 #

技术难点 #

• 每位客户的首次购买日期不同，周期起点无法统一
• 采购日期不连续，固定行数的窗口函数（如rows between 6 preceding and current row）无效
• 数据集跨度5年，无法手动调整日期
• 尝试过date_trunc('week', date, min(date) over (partition by customerid))，但无法匹配自定义的7天周期逻辑

解决方案（以PostgreSQL为例） #

步骤1：标记客户首次购买日 #

先提取每位客户的首次购买日期，作为周期计算的基准：

WITH customer_first_purchase AS (
    SELECT 
        customerid,
        MIN(purchase_date) AS first_purchase_date
    FROM sales_data
    GROUP BY customerid
),

步骤2：计算每条记录的所属周期起始日 #

基于首次购买日，推导每条采购记录对应的7天周期起始日：

sales_with_cycle AS (
    SELECT 
        s.purchase_date,
        s.customerid,
        s.quantity,
        -- 核心逻辑：从首次购买日开始，计算当前采购日期所在的7天周期起点
        cf.first_purchase_date + INTERVAL '7 days' * 
            FLOOR((s.purchase_date - cf.first_purchase_date) / INTERVAL '7 days') 
            AS cycle_start_date,
        -- 计算当前周期内的累计销量（对应中间表的7天累计销售额）
        SUM(s.quantity) OVER (
            PARTITION BY s.customerid, cycle_start_date 
            ORDER BY s.purchase_date
        ) AS 7天累计销售额
    FROM sales_data s
    JOIN customer_first_purchase cf ON s.customerid = cf.customerid
),

步骤3：统计每个周期的总销量 #

按客户和周期起始日分组，得到每个周期的总销售数量：

cycle_sales AS (
    SELECT 
        cycle_start_date AS 采购周期起始日,
        customerid AS 客户ID,
        SUM(quantity) AS 7天销售数量
    FROM sales_with_cycle
    GROUP BY customerid, cycle_start_date
),

步骤4：计算客户的周期平均购买量 #

基于每个客户的周期销量，计算平均值并生成可视化计算过程：

customer_avg AS (
    SELECT 
        customerid AS 客户ID,
        AVG(7天销售数量) AS 7天周期平均销售数量,
        '(' || STRING_AGG(CAST(7天销售数量 AS VARCHAR), '+') || ')/' || COUNT(*) AS 计算过程
    FROM cycle_sales
    GROUP BY customerid
)

完整SQL查询 #

整合以上逻辑，可按需查询中间表或最终结果：

WITH customer_first_purchase AS (
    SELECT 
        customerid,
        MIN(purchase_date) AS first_purchase_date
    FROM sales_data
    GROUP BY customerid
),
sales_with_cycle AS (
    SELECT 
        s.purchase_date,
        s.customerid,
        s.quantity,
        cf.first_purchase_date + INTERVAL '7 days' * 
            FLOOR((s.purchase_date - cf.first_purchase_date) / INTERVAL '7 days') 
            AS cycle_start_date,
        SUM(s.quantity) OVER (
            PARTITION BY s.customerid, cycle_start_date 
            ORDER BY s.purchase_date
        ) AS 7天累计销售额
    FROM sales_data s
    JOIN customer_first_purchase cf ON s.customerid = cf.customerid
),
cycle_sales AS (
    SELECT 
        cycle_start_date AS 采购周期起始日,
        customerid AS 客户ID,
        SUM(quantity) AS 7天销售数量
    FROM sales_with_cycle
    GROUP BY customerid, cycle_start_date
),
customer_avg AS (
    SELECT 
        customerid AS 客户ID,
        AVG(7天销售数量) AS 7天周期平均销售数量,
        '(' || STRING_AGG(CAST(7天销售数量 AS VARCHAR), '+') || ')/' || COUNT(*) AS 计算过程
    FROM cycle_sales
    GROUP BY customerid
)
-- 可选：查询7天累计销售额中间表
SELECT purchase_date AS 采购日期, customerid AS 客户ID, quantity AS 销售数量, 7天累计销售额 FROM sales_with_cycle ORDER BY customerid, purchase_date;
-- 可选：查询最终周期销售额表
-- SELECT * FROM cycle_sales ORDER BY customerid, 采购周期起始日;
-- 可选：查询客均购买量表
-- SELECT * FROM customer_avg ORDER BY customerid;

关键逻辑说明 #

• 周期起始日计算：通过FLOOR((采购日期 - 首次购买日)/7天)获取完整周期数，再反向推导周期起点，完美适配每个客户的自定义周期。
• 累计销量计算：窗口函数仅对当前客户的当前周期记录生效，避免跨周期累加。
• 计算过程生成：通过STRING_AGG拼接周期销量，还原示例中的可视化计算逻辑。

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