PostgreSQL窗口函数计算：统计90天内消费≥2次的客户数

你之前的窗口函数写法错误在于用了PARTITION BY date_purchase——这是按日期分区，只能计算单日内的订单数，无法跟踪单个客户在90天滑动窗口内的购买行为。正确的做法是按客户ID分区，结合时间滑动窗口统计每个客户的购买次数，再筛选出符合条件的客户并按日期汇总。

以下是两种场景的实现方案（以PostgreSQL为例，其他数据库语法略有差异）：

场景1：同一客户同一天多笔订单算1次购买 #

WITH daily_purchases AS (
    -- 去重，每个客户每天只保留一条购买记录
    SELECT DISTINCT client_id, date_purchase::DATE
    FROM sales_data
),
customer_90d_purchases AS (
    -- 计算每个客户在当前日期及往前90天内的购买次数
    SELECT
        client_id,
        date_purchase,
        COUNT(*) OVER (
            PARTITION BY client_id
            ORDER BY date_purchase
            RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
        ) AS purchase_count
    FROM daily_purchases
),
qualified_customers AS (
    -- 筛选出90天内购买≥2次的客户，按日期去重避免重复统计
    SELECT DISTINCT date_purchase, client_id
    FROM customer_90d_purchases
    WHERE purchase_count >= 2
)
-- 按日期统计符合条件的客户数量
SELECT
    date_purchase,
    COUNT(client_id) AS number_of_customers
FROM qualified_customers
GROUP BY date_purchase
ORDER BY date_purchase;

场景2：同一客户同一天多笔订单算多次购买 #

如果需要把同一天的多笔订单都计入购买次数，去掉第一步的去重即可：

WITH customer_90d_purchases AS (
    SELECT
        client_id,
        date_purchase::DATE,
        COUNT(*) OVER (
            PARTITION BY client_id
            ORDER BY date_purchase
            RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
        ) AS purchase_count
    FROM sales_data
),
qualified_customers AS (
    SELECT DISTINCT date_purchase, client_id
    FROM customer_90d_purchases
    WHERE purchase_count >= 2
)
SELECT
    date_purchase,
    COUNT(client_id) AS number_of_customers
FROM qualified_customers
GROUP BY date_purchase
ORDER BY date_purchase;

补充：包含无符合条件客户的日期 #

如果需要输出所有存在订单的日期（即使当天没有符合条件的客户，显示0），可以生成完整日期表左连接：

WITH all_dates AS (
    -- 生成数据集中所有日期的序列
    SELECT generate_series(
        (SELECT MIN(date_purchase::DATE) FROM sales_data),
        (SELECT MAX(date_purchase::DATE) FROM sales_data),
        INTERVAL '1 day'
    )::DATE AS date_purchase
),
daily_purchases AS (
    SELECT DISTINCT client_id, date_purchase::DATE
    FROM sales_data
),
customer_90d_purchases AS (
    SELECT
        client_id,
        date_purchase,
        COUNT(*) OVER (
            PARTITION BY client_id
            ORDER BY date_purchase
            RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
        ) AS purchase_count
    FROM daily_purchases
),
qualified_customers AS (
    SELECT DISTINCT date_purchase, client_id
    FROM customer_90d_purchases
    WHERE purchase_count >= 2
),
daily_customer_counts AS (
    SELECT date_purchase, COUNT(client_id) AS number_of_customers
    FROM qualified_customers
    GROUP BY date_purchase
)
SELECT
    ad.date_purchase,
    COALESCE(dcc.number_of_customers, 0) AS number_of_customers
FROM all_dates ad
LEFT JOIN daily_customer_counts dcc ON ad.date_purchase = dcc.date_purchase
ORDER BY ad.date_purchase;

内容的提问来源于stack exchange，提问作者Santiago Valdés