如何筛选连续订单中下单量逐次递增的BuyId？

现有Buyer表，结构及数据如下：

需要筛选出拥有多笔订单，且每笔后续订单的下单量（QuantityOrdered）都严格大于上一笔的BuyId：

• BuyId=1：第一笔10，第二笔20，符合要求
• BuyId=2：第一笔50，第二笔60，符合要求
• BuyId=3、4：仅1笔订单，不符合要求

原查询仅能筛选出多笔订单的BuyId，缺少递增校验逻辑：

select buyid, count(*) as ordered
from buyer
group by buyid
having count(*) >1

解决方案 #

可以通过窗口函数LAG()获取同一BuyId按下单时间排序后的上一笔订单量，校验是否存在不满足递增的记录，最终筛选出符合要求的BuyId。

方法一：标记异常BuyId后排除 #

WITH ordered_orders AS (
    SELECT 
        BuyId,
        QuantityOrdered,
        -- 按下单时间排序，获取同一BuyId的上一笔订单量
        LAG(QuantityOrdered) OVER (PARTITION BY BuyId ORDER BY dateordered) AS prev_quantity
    FROM Buyer
),
invalid_buyers AS (
    SELECT DISTINCT BuyId
    FROM ordered_orders
    -- 筛选出存在"当前订单量≤上一笔"的BuyId
    WHERE prev_quantity IS NOT NULL AND QuantityOrdered <= prev_quantity
)
SELECT DISTINCT BuyId
FROM Buyer
WHERE BuyId NOT IN (SELECT BuyId FROM invalid_buyers)
GROUP BY BuyId
HAVING COUNT(*) >= 2;

方法二：分组统计异常次数直接筛选 #

SELECT BuyId
FROM (
    SELECT 
        BuyId,
        -- 统计同一BuyId中，订单量不大于上一笔的次数
        SUM(CASE 
            WHEN LAG(QuantityOrdered) OVER (PARTITION BY BuyId ORDER BY dateordered) >= QuantityOrdered 
            THEN 1 
            ELSE 0 
        END) AS non_increment_count
    FROM Buyer
) AS order_checks
GROUP BY BuyId
HAVING COUNT(*) >= 2 AND non_increment_count = 0;

逻辑解释 #

1. LAG()窗口函数：按BuyId分组、dateordered排序，获取当前订单的上一笔订单量，用于对比是否递增。
2. 异常判断：如果某BuyId存在任意一笔非首订单的量≤上一笔，就标记为不符合要求。
3. 最终筛选：只保留订单数≥2，且无任何异常记录的BuyId。

内容的提问来源于stack exchange，提问作者Sven Marenković