PostgreSQL中计算特定时间段客户各周期平均生命周期价值

问题背景 #

我在PostgreSQL中有一张记录商店客户订单的orders表，示例数据如下：

执行查询：

SELECT * FROM orders;

返回结果：

注：实际表不会按customer_id和created_at排序。

需求 #

1. 筛选出在指定时间段（示例为2021-01-01至2021-12-31）内有下单记录的客户，示例中为客户1和2；
2. 计算这些客户在首次下单后以下各时间段内的累计订单价值的平均值：
   • 60-day
   • 90-day
   • 6-months
   • 12-months

示例计算逻辑 #

• 客户1首次下单时间为2021-01-05，60天内累计订单价值为200.00+150.00=350.00；
• 客户2首次下单时间为2021-03-03，60天内累计订单价值仅为首次订单的100.00；
• 60-day的客户平均价值为(350.00+100.00)/2=225.00。

期望最终结果：

解决方案 #

以下是实现需求的PostgreSQL查询语句：

WITH eligible_customers AS (
    -- 筛选指定时间段内有下单的客户，并获取他们的首次下单时间
    SELECT 
        customer_id,
        MIN(created_at) AS first_order_date
    FROM orders
    WHERE created_at BETWEEN '2021-01-01' AND '2021-12-31'
    GROUP BY customer_id
),
customer_timeframe_totals AS (
    -- 计算每个符合条件的客户在各个时间段内的累计订单价值
    SELECT 
        ec.customer_id,
        -- 60天内累计价值
        SUM(CASE WHEN o.created_at <= ec.first_order_date + INTERVAL '60 days' THEN o.value ELSE 0 END) AS total_60d,
        -- 90天内累计价值
        SUM(CASE WHEN o.created_at <= ec.first_order_date + INTERVAL '90 days' THEN o.value ELSE 0 END) AS total_90d,
        -- 6个月内累计价值
        SUM(CASE WHEN o.created_at <= ec.first_order_date + INTERVAL '6 months' THEN o.value ELSE 0 END) AS total_6m,
        -- 12个月内累计价值
        SUM(CASE WHEN o.created_at <= ec.first_order_date + INTERVAL '12 months' THEN o.value ELSE 0 END) AS total_12m
    FROM eligible_customers ec
    JOIN orders o ON ec.customer_id = o.customer_id
    GROUP BY ec.customer_id, ec.first_order_date
),
timeframe_averages AS (
    -- 计算各时间段的客户平均价值
    SELECT
        AVG(total_60d) AS avg_60d,
        AVG(total_90d) AS avg_90d,
        AVG(total_6m) AS avg_6m,
        AVG(total_12m) AS avg_12m
    FROM customer_timeframe_totals
)
-- 将横向的平均值转为纵向的结果格式
SELECT
    unnest(ARRAY['60-day', '90-day', '6-month', '1-year']) AS timeframe,
    unnest(ARRAY[avg_60d, avg_90d, avg_6m, avg_12m]) AS av_value_per_customer
FROM timeframe_averages;

代码逻辑说明 #

1. eligible_customers CTE：筛选出指定时间段内有下单的客户，同时计算每个客户的首次下单时间，作为后续时间段计算的基准。
2. customer_timeframe_totals CTE：通过关联原订单表，使用CASE语句分别统计每个客户在各个时间段内的订单价值总和。
3. timeframe_averages CTE：计算所有符合条件客户在各时间段的累计价值平均值。
4. 最后使用unnest将横向的平均值结果转为需求中的纵向表格格式，便于查看。

验证结果 #

执行上述SQL后，将得到与期望一致的结果：

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