求计算客户每日多周期营收总和的Customer LTV SQL查询方案

基于tblCustomerRevenue表（字段cuid、revenue、purchase-date），以下是计算每个客户每日维度下当日总营收、过去7天总营收、过去30天总营收、过去365天总营收的SQL实现：

基础实现（支持多记录同日场景） #

MySQL 版本 #

SELECT
    cuid,
    `purchase-date` AS stat_date,
    SUM(revenue) OVER (PARTITION BY cuid ORDER BY `purchase-date` RANGE BETWEEN CURRENT ROW AND CURRENT ROW) AS daily_revenue,
    SUM(revenue) OVER (PARTITION BY cuid ORDER BY `purchase-date` RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS weekly_revenue,
    SUM(revenue) OVER (PARTITION BY cuid ORDER BY `purchase-date` RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW) AS monthly_revenue,
    SUM(revenue) OVER (PARTITION BY cuid ORDER BY `purchase-date` RANGE BETWEEN INTERVAL 364 DAY PRECEDING AND CURRENT ROW) AS yearly_revenue
FROM tblCustomerRevenue
ORDER BY cuid, stat_date;

PostgreSQL 版本 #

SELECT
    cuid,
    "purchase-date" AS stat_date,
    SUM(revenue) OVER (PARTITION BY cuid ORDER BY "purchase-date" RANGE BETWEEN CURRENT ROW AND CURRENT ROW) AS daily_revenue,
    SUM(revenue) OVER (PARTITION BY cuid ORDER BY "purchase-date" RANGE BETWEEN '6 days' PRECEDING AND CURRENT ROW) AS weekly_revenue,
    SUM(revenue) OVER (PARTITION BY cuid ORDER BY "purchase-date" RANGE BETWEEN '29 days' PRECEDING AND CURRENT ROW) AS monthly_revenue,
    SUM(revenue) OVER (PARTITION BY cuid ORDER BY "purchase-date" RANGE BETWEEN '364 days' PRECEDING AND CURRENT ROW) AS yearly_revenue
FROM tblCustomerRevenue
ORDER BY cuid, stat_date;

去重优化版本（确保每日一行） #

如果同一客户同一天存在多条消费记录，可先按客户+日期聚合，再计算时段营收：

MySQL 版本 #

WITH daily_agg AS (
    SELECT
        cuid,
        `purchase-date` AS stat_date,
        SUM(revenue) AS daily_total
    FROM tblCustomerRevenue
    GROUP BY cuid, `purchase-date`
)
SELECT
    cuid,
    stat_date,
    daily_total AS daily_revenue,
    SUM(daily_total) OVER (PARTITION BY cuid ORDER BY stat_date RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS weekly_revenue,
    SUM(daily_total) OVER (PARTITION BY cuid ORDER BY stat_date RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW) AS monthly_revenue,
    SUM(daily_total) OVER (PARTITION BY cuid ORDER BY stat_date RANGE BETWEEN INTERVAL 364 DAY PRECEDING AND CURRENT ROW) AS yearly_revenue
FROM daily_agg
ORDER BY cuid, stat_date;

关键逻辑说明 #

• 使用窗口函数按客户分组（PARTITION BY cuid），按消费日期排序，通过RANGE范围限定统计时段：
  • CURRENT ROW：定位到当前行对应的日期
  • INTERVAL X DAY PRECEDING：表示当前日期往前推X天的时间范围
• 若需按自然周/月/年统计（如每周从周一算起），可替换日期排序逻辑为DATE_FORMAT(purchase-date, '%Y-%u')（周）、DATE_FORMAT(purchase-date, '%Y-%m')（月）等，再调整窗口范围。

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