如何用SQL识别购买量逐次持续下降的客户

我们可以通过窗口函数获取每个客户上一次购买的商品数量，再统计每个客户是否存在购买量未低于上一次的记录，同时过滤掉只有1条购买记录的客户，即可得到符合要求的结果。

具体SQL语句（支持MySQL 8.0+/PostgreSQL/SQL Server等主流支持窗口函数的数据库） #

WITH purchase_with_prev AS (
    SELECT 
        Customer,
        Quantity,
        -- 按购买日期排序，获取同一客户上一次购买的数量
        LAG(Quantity) OVER (PARTITION BY Customer ORDER BY PurchaseDate) AS prev_quantity,
        -- 统计每个客户的总购买次数
        COUNT(*) OVER (PARTITION BY Customer) AS total_purchases
    FROM purchases
),
customer_violation AS (
    SELECT 
        Customer,
        total_purchases,
        -- 统计该客户有多少次购买量大于等于上一次（即违规次数）
        SUM(CASE WHEN Quantity >= prev_quantity THEN 1 ELSE 0 END) AS violation_count
    FROM purchase_with_prev
    GROUP BY Customer, total_purchases
)
SELECT Customer 
FROM customer_violation
WHERE 
    -- 无违规记录
    violation_count = 0 
    -- 购买次数至少2次
    AND total_purchases >= 2;

逻辑说明 #

1. 第一个CTE purchase_with_prev 用LAG窗口函数，按客户分组、购买日期升序排序，给每一条购买记录匹配同客户上一次的购买数量，同时统计每个客户的总购买次数。
2. 第二个CTE customer_violation 按客户分组，统计每个客户的违规次数：只要有一次购买数量大于等于上一次，就会计为1次违规。
3. 最终筛选出违规次数为0、且总购买次数大于等于2的客户，就是所有购买量逐次严格下降的客户，和预期结果一致。

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