SQL中如何判断按客户聚合的数值序列是否始终递增或递减

以下实现对齐你给出的Python reduce逻辑，校验序列是否严格递减，可根据需求调整比对规则适配非严格递减/递增校验。

方案1：窗口函数实现（优先推荐，性能更优） #

无需提前聚合数组，直接通过相邻行比对得到结果，适合大数据量场景：

WITH
    tbl (customer, purchasedate, quantity) AS (
SELECT * FROM VALUES 
    ('Lucus',       '9/1/2021',        5),
    ('Lucus',       '9/10/2021',       6),
    ('Lucus',       '9/18/2021',       10),
    ('Tom',         '9/1/2021',        10),
    ('Tom',         '9/10/2021',       6),
    ('Tom',         '9/18/2021',       5)
),
-- 为每个用户的每条购买记录匹配上一次购买的数量
add_prev_qty AS (
    SELECT 
        customer,
        quantity,
        LAG(quantity) OVER (PARTITION BY customer ORDER BY TO_DATE(purchasedate, 'MM/DD/YYYY')) AS prev_quantity
    FROM tbl
)
SELECT 
    customer,
    -- 所有相邻行都满足当前数量 < 上一次数量则返回true，否则返回false
    BOOL_AND(quantity < prev_quantity) AS is_strictly_decreasing
FROM add_prev_qty
WHERE prev_quantity IS NOT NULL -- 过滤每个用户的第一条记录（无前置购买记录）
GROUP BY customer;

返回结果：Lucus对应false，Tom对应true，和Python示例逻辑完全一致。

方案2：已有聚合数组时的校验实现（以PostgreSQL语法为例） #

如果已经得到按时间排序的数量数组，可直接遍历数组比对相邻元素：

WITH
    tbl (customer, purchasedate, quantity) AS (
SELECT * FROM VALUES 
    ('Lucus',       '9/1/2021',        5),
    ('Lucus',       '9/10/2021',       6),
    ('Lucus',       '9/18/2021',       10),
    ('Tom',         '9/1/2021',        10),
    ('Tom',         '9/10/2021',       6),
    ('Tom',         '9/18/2021',       5)
),
agg_array AS (
    SELECT
        customer, 
        -- 必须加ORDER BY保证数组按购买时间排序，否则校验结果无效
        array_agg(quantity ORDER BY TO_DATE(purchasedate, 'MM/DD/YYYY')) AS qty_list
    FROM tbl 
    GROUP BY customer
)
SELECT 
    customer,
    qty_list,
    BOOL_AND(qty_list[i] > qty_list[i+1]) AS is_strictly_decreasing
FROM agg_array,
     GENERATE_SERIES(1, ARRAY_LENGTH(qty_list, 1)-1) AS i
GROUP BY customer, qty_list;

规则调整说明 #

• 校验非严格递减（允许相邻值相等）：将比对条件改为>=
• 校验严格递增：将比对条件改为qty_list[i] < qty_list[i+1]
• 校验非严格递增：将比对条件改为qty_list[i] <= qty_list[i+1]

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