SQL中如何按时间排序聚合口味序列并实现冰淇淋订单统计

1. 修正用户口味序列的时间排序 #

原XMLAGG函数未指定排序逻辑，导致聚合的口味序列可能乱序。在XMLAGG中加入ORDER BY order_date子句，确保每个用户的口味严格按订单时间排列：

WITH user_flavor_sequences AS (
    SELECT
        name,
        -- 按订单时间排序后聚合口味，用分隔符区分不同口味
        XMLAGG(ice_cream_flavor ORDER BY order_date SEPARATOR '->') AS flavor_pattern,
        -- 统计该用户的总订单数
        COUNT(*) AS user_order_count
    FROM myt
    GROUP BY name
)

2. 统计口味模式的订单数与客户数 #

基于上述CTE，按口味模式分组，计算对应总订单数（所有用户订单数之和）和客户数（拥有该模式的用户数量）：

SELECT
    flavor_pattern,
    SUM(user_order_count) AS total_orders,
    COUNT(name) AS customer_count
FROM user_flavor_sequences
GROUP BY flavor_pattern
ORDER BY total_orders DESC;

3. 测试示例与结果 #

假设测试数据如下：

执行SQL后输出结果：

关键说明 #

• XMLAGG的ORDER BY子句是核心，确保口味序列严格遵循订单时间顺序，避免模式统计错误。
• 若需处理同日期的订单，可在ORDER BY中追加订单ID等字段，保证排序唯一性。
• 分隔符可按需调整（如逗号、竖线），只要能清晰区分不同口味即可。

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