SQL聚合查询中如何统计排除紧跟香蕉的苹果数量

实现逻辑 #

要统计「后面不紧跟香蕉的苹果」数量，不需要拆分已聚合的数组，直接基于源表用窗口函数取相邻记录判断即可，执行效率更高，且能保证和原有数组排序规则完全一致：

• 按用户分组、食用时间正序排序，用LEAD()窗口函数拿到每条记录的下一条水果类型
• 计数规则：当前记录为苹果，且下一条记录不是香蕉（序列末尾的苹果没有下一条记录，默认符合计数规则）时计入统计

可直接运行的SQL代码 #

WITH fruit_with_next AS (
    SELECT
        name,
        fruits,
        time,
        LEAD(fruits) OVER (PARTITION BY name ORDER BY time ASC) AS next_fruit
    FROM table_fruits
)
SELECT
    name,
    ARRAY_AGG(fruits ORDER BY time ASC) AS all_fruits,
    SUM(
        CASE
            WHEN fruits = 'Apple' AND (next_fruit IS NULL OR next_fruit != 'Banana')
            THEN 1 ELSE 0
        END
    ) AS count_of_apple
FROM fruit_with_next
GROUP BY name;

结果校验 #

对照给出的样例数据，上述代码的计算逻辑完全匹配预期结果：

• Person A：序列为Apple, Banana, Apple, Apple, Apple, Apple，仅第1位苹果后面紧跟香蕉不计数，剩余4个苹果符合要求
• Person B：序列为Apple, Apple, Apple, Banana, Apple, Banana，第3、5位苹果后面紧跟香蕉不计数，剩余2个苹果符合要求
• Person C：序列为Banana, Banana, Apple, Banana, Apple, Apple，第3位苹果后面紧跟香蕉不计数，剩余2个苹果符合要求

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