如何使用SQL窗口函数处理订单合并发货逻辑_基于ROW

不能直接用GROUP BY处理合并发货，因其需按“发货窗口”动态分组（同客户、同日、订单时间间隔≤30分钟），而GROUP BY无法表达带时间偏移的邻接关系，必须借助ROW_NUMBER()配合LAG和累计断点求和构造批次号。

如何使用sql窗口函数处理订单合并发货逻辑_基于row_number的分组标记

为什么不能直接用 GROUP BY 处理合并发货？

因为合并发货不是简单按客户或时间聚合，而是要按「发货窗口」分组：同一客户、同一天内、且订单创建时间间隔 ≤ 30 分钟的订单，才视为可合并。GROUP BY 无法表达这种带时间偏移的动态邻接关系，必须依赖行序和前向/后向比较——这正是 ROW_NUMBER() 搭配自连接或递归逻辑的切入点。

用 ROW_NUMBER() 标记发货批次的核心思路

关键不在于给每行编号，而是在编号基础上构造「批次锚点」：对每个订单，往前找最近一个无法合并的订单（即与它间隔 > 30 分钟），该订单的编号就是当前批次的起始标识。实际中更稳定的做法是用「累计断点计数」：ROW_NUMBER() 配合条件累积求和。

先按 customer_id, order_time 排序，确保时序正确
用 LAG(order_time) 取上一行时间，判断是否 > 30 分钟，生成布尔断点
用 SUM(CASE WHEN ... THEN 1 ELSE 0 END) OVER (ORDER BY ...) 累积断点，得到每个订单所属的发货批次号
这个批次号才是后续 GROUP BY 的依据，不是原始 ROW_NUMBER()

容易踩的坑：时间差计算和时区陷阱

LAG() 和时间差必须在同一时区下运算，否则跨时区订单会错误断开批次。PostgreSQL/MySQL 8.0+ 支持 EXTRACT(EPOCH FROM ...) 或 TIMESTAMPDIFF(MINUTE, ...)，但注意：

MySQL 中 TIMESTAMPDIFF(MINUTE, prev_time, curr_time) 返回的是有符号整数，需用 > 30 而非 >= 30 避免边界重复断点
PostgreSQL 若字段是 TIMESTAMPTZ，LAG() 返回值自动转为当前 session 时区，但计算差值前最好显式 AT TIME ZONE 'UTC' 对齐
SQL Server 不支持直接在窗口函数里嵌套 DATEADD 判断，得先用 CTE 提前算好 is_break 列

SELECT
  order_id,
  customer_id,
  order_time,
  SUM(is_break) OVER (
    PARTITION BY customer_id 
    ORDER BY order_time
    ROWS UNBOUNDED PRECEDING
  ) AS shipment_batch
FROM (
  SELECT *,
    CASE 
      WHEN order_time - LAG(order_time) OVER (
        PARTITION BY customer_id ORDER BY order_time
      ) > INTERVAL '30 minutes'
      THEN 1 ELSE 0 
    END AS is_break
  FROM orders
) t;