优化SQL Lag计算堆叠订单配送时间差的性能问题

我就职于一家B2C生鲜配送企业，为提升运营效率启用了订单堆叠模式（单次配送承接多个订单），需要计算堆叠订单间的配送时间差。数据结构如下：

*注：stack_id为null的订单为非堆叠订单。

当前实现代码 #

with vals as (
  select
    1 as order_id,
    1 as stack_id,
    datetime('2022-01-01T13:00:00') as delivered
  union all
  select
    2 as order_id,
    1 as stack_id,
    datetime('2022-01-01T13:05:00') as delivered
  union all
  select
    3 as order_id,
    1 as stack_id,
    datetime('2022-01-01T13:12:00') as delivered
  union all
  select
    4 as order_id,
    null as stack_id,
    datetime('2022-01-01T14:00:00') as delivered
  union all
  select
    5 as order_id,
    2 as stack_id,
    datetime('2022-01-01T14:10:00') as delivered
  union all
  select
    6 as order_id,
    2 as stack_id,
    datetime('2022-01-01T14:17:00') as delivered
  union all
  select
    7 as order_id,
    null as stack_id,
    datetime('2022-01-01T16:00:00') as delivered
),

last_order as (
  select
    *,
    if(stack_id is not null, lag(delivered) over (partition by stack_id order by delivered), null) as previous_order_delivered
  from vals
),

time_between_dropoffs as (
  select
    *,
    datetime_diff(delivered, previous_order_delivered, second) as seconds_between_dropoff
  from last_order
)

select * from time_between_dropoffs
order by delivered

问题 #

当前实现中，带order by的窗口函数在生产环境运行时内存占用极高，且订单量越大，问题越严重。有没有更高效的实现方式？

当前输出结果 #

优化方案 #

1. 创建复合索引减少排序开销 #

窗口函数的partition by stack_id order by delivered需要对每个stack_id组内的数据按delivered排序，若没有合适索引，数据库会全表扫描并排序，导致内存飙升。创建以下复合索引：

CREATE INDEX idx_stack_delivered ON your_table(stack_id, delivered);

该索引让数据库直接按stack_id分组，且组内数据已按delivered有序，避免运行时排序，大幅降低内存消耗。

2. 提前过滤非堆叠订单 #

非堆叠订单无需计算时间差，可在CTE第一步就过滤，只处理堆叠订单，最后合并结果：

WITH stacked_orders AS (
  SELECT 
    order_id, stack_id, delivered,
    LAG(delivered) OVER (PARTITION BY stack_id ORDER BY delivered) AS previous_order_delivered
  FROM your_table
  WHERE stack_id IS NOT NULL
),
non_stacked_orders AS (
  SELECT 
    order_id, stack_id, delivered,
    NULL AS previous_order_delivered
  FROM your_table
  WHERE stack_id IS NULL
)
SELECT 
  *,
  DATETIME_DIFF(delivered, previous_order_delivered, SECOND) AS seconds_between_dropoff
FROM stacked_orders
UNION ALL
SELECT *, NULL AS seconds_between_dropoff
FROM non_stacked_orders
ORDER BY delivered;

窗口函数仅处理堆叠订单，数据量大幅减少，内存占用自然降低。

3. 分批次处理超大规模数据 #

若数据量极大，可按日期或stack_id范围分批次计算，再合并结果，避免一次性加载全量数据到内存。

4. 按需调整数据库配置 #

若使用云数仓（如BigQuery），可调整查询槽位配置；若使用关系型数据库（如MySQL），可适当增大排序缓冲区（sort_buffer_size），但需结合服务器资源评估，不建议盲目调整。

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