关于2020年3月-2021年3月月度客户数与全生命周期购买频次的SQL查询需求及问题

嘿，我完全明白你的需求了！你要统计2020年3月到2021年3月每个月里有购买行为的客户数量，以及这些客户从首次下单到当月为止的累计购买频次平均值——而不是他们当月的购买频次对吧？原查询的问题在于只计算了当月的订单，没有覆盖客户的全生命周期订单，我来帮你优化一下！

1. 单月查询（手动修改参数） #

如果你还是倾向于按月手动调整参数查询，我们可以先用CTE锁定当月有购买的客户，再关联整个订单表统计这些客户截至当月的所有订单：

-- 示例：查询2021年1月的数据
WITH monthly_active_customers AS (
    -- 第一步：筛选出2021年1月有购买行为的客户
    SELECT DISTINCT customer_id
    FROM "order_user"
    WHERE order_date >= TIMESTAMP'2021-01-01 00:00:00' 
      AND order_date < TIMESTAMP'2021-02-01 00:00:00'
)
SELECT 
    COUNT(mac.customer_id) AS customer_count,
    -- 统计这些客户截至2021年1月底的所有订单数，除以客户数得到平均全生命周期频次
    COUNT(ou.orderid) / COUNT(DISTINCT mac.customer_id) AS alltime_frequency
FROM monthly_active_customers mac
LEFT JOIN "order_user" ou 
    ON mac.customer_id = ou.customer_id
    AND ou.order_date < TIMESTAMP'2021-02-01 00:00:00'; -- 关键：包含截至当月的所有订单

核心调整点： #

• 用CTE先锁定当月有购买的客户，避免统计无关客户
• 关联订单表时，条件限定为客户的所有订单截至当月月底，这样就能拿到他们的全生命周期累计订单数
• 修复了原查询中日期范围写反的错误（原查询里order_date >= '2021-01-01' AND order_date < '2020-02-01'逻辑矛盾，会返回空结果）

2. 批量生成所有月份的结果（更高效） #

如果不想每月手动改参数，可以一次性生成2020年3月到2021年3月所有月份的统计结果，下面以PostgreSQL为例（其他数据库可以替换生成日期序列的方式，比如MySQL用递归CTE）：

WITH date_ranges AS (
    -- 生成2020-03到2021-03每个月的起始和结束日期
    SELECT 
        date_trunc('month', series) AS month_start,
        date_trunc('month', series) + INTERVAL '1 month' AS month_end
    FROM generate_series(
        TIMESTAMP'2020-03-01 00:00:00',
        TIMESTAMP'2021-03-01 00:00:00',
        INTERVAL '1 month'
    ) AS series
),
monthly_customers AS (
    -- 找到每个月有购买行为的客户
    SELECT 
        dr.month_start,
        DISTINCT ou.customer_id
    FROM date_ranges dr
    JOIN "order_user" ou 
        ON ou.order_date >= dr.month_start 
        AND ou.order_date < dr.month_end
),
customer_lifetime_totals AS (
    -- 统计每个客户在截至对应月份的累计订单数
    SELECT 
        mc.month_start,
        mc.customer_id,
        COUNT(ou.orderid) AS total_lifetime_orders
    FROM monthly_customers mc
    JOIN "order_user" ou 
        ON mc.customer_id = ou.customer_id
        AND ou.order_date < mc.month_end
    GROUP BY mc.month_start, mc.customer_id
)
-- 最终按月统计客户数和平均全生命周期频次
SELECT 
    TO_CHAR(clt.month_start, 'YYYY-MM') AS month,
    COUNT(clt.customer_id) AS customer_count,
    AVG(clt.total_lifetime_orders) AS alltime_frequency
FROM customer_lifetime_totals clt
GROUP BY clt.month_start
ORDER BY clt.month_start;

输出示例： #

这样你就能一次性得到所有月份的数据，不用重复修改查询参数啦！

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