使用SQL基于复合键遍历表查询数据并计算新值的问题咨询

你的需求可以通过SQL标准窗口函数实现，无需自定义循环遍历逻辑，执行效率远高于逐行遍历，适配大数据量场景。

1. 提取单订单首个非空BOL记录 #

核心逻辑是按订单号分组，对组内所有BOL非空的记录按时间戳升序排序，取排序后的第一条记录即可：

WITH ranked_order_events AS (
  SELECT 
    `Order`,
    `Timestamp`,
    BOL,
    ROW_NUMBER() OVER (
      PARTITION BY `Order` 
      ORDER BY STR_TO_DATE(`Timestamp`, '%m.%d.%y.%H:%i') ASC
    ) AS rn
  FROM order_events
  WHERE BOL IS NOT NULL
)
SELECT 
  `Order`,
  `Timestamp` AS bol_date,
  BOL
FROM ranked_order_events
WHERE rn = 1;

注意：以上为MySQL语法，不同数据库的日期转换函数存在差异：SQL Server用CONVERT(DATETIME, Timestamp, 1)，PostgreSQL用TO_TIMESTAMP(Timestamp, 'MM.DD.YY.HH24:MI')；Order为SQL保留字，实际使用建议修改字段名，或用对应数据库的标识符包裹符处理。

2. 插入结果到新表 #

-- 首次使用先创建存储结果的新表
CREATE TABLE order_bol_date (
  `Order` VARCHAR(50) PRIMARY KEY,
  bol_date DATETIME NOT NULL,
  bol_value VARCHAR(50) NOT NULL
);

-- 批量插入所有订单的首个BOL记录
INSERT INTO order_bol_date (`Order`, bol_date, bol_value)
WITH ranked_order_events AS (
  SELECT 
    `Order`,
    STR_TO_DATE(`Timestamp`, '%m.%d.%y.%H:%i') AS bol_date,
    BOL AS bol_value,
    ROW_NUMBER() OVER (PARTITION BY `Order` ORDER BY STR_TO_DATE(`Timestamp`, '%m.%d.%y.%H:%i') ASC) AS rn
  FROM order_events
  WHERE BOL IS NOT NULL
)
SELECT `Order`, bol_date, bol_value
FROM ranked_order_events
WHERE rn = 1;

3. 计算生命周期事件间隔 #

将新表和原始事件表关联，用日期差函数即可计算各节点间隔，示例计算订单生成到BOL生成的间隔天数：

SELECT 
  e.`Order`,
  DATEDIFF(b.bol_date, MIN(STR_TO_DATE(e.`Timestamp`, '%m.%d.%y.%H:%i'))) AS days_from_create_to_bol
FROM order_events e
LEFT JOIN order_bol_date b ON e.`Order` = b.`Order`
GROUP BY e.`Order`, b.bol_date;

低版本SQL兼容写法 #

如果你的数据库版本不支持CTE（如MySQL 8.0以下），可以把子查询嵌套在FROM子句中：

SELECT `Order`, `Timestamp` AS bol_date, BOL
FROM (
  SELECT 
    `Order`,
    `Timestamp`,
    BOL,
    ROW_NUMBER() OVER (PARTITION BY `Order` ORDER BY STR_TO_DATE(`Timestamp`, '%m.%d.%y.%H:%i') ASC) AS rn
  FROM order_events
  WHERE BOL IS NOT NULL
) t
WHERE rn = 1;

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