如何不使用嵌套循环查找各产品当前问题起始日期

原始表结构与数据 #

需求说明 #

需要为每个产品找出当前问题集的起始日期（即issues_found从0变为非0的最近日期），并添加新列issues_started。

预期结果 #

当前低效实现 #

当前使用嵌套循环的方式，处理大量数据时耗时极长：

DECLARE found_date DATE;
FOR row in (SELECT * FROM my_table)
DO
  SET found_date = NULL;
  FOR historical_entry in (SELECT * FROM my_table WHERE product = row.product ORDER BY run_date DESC)
  DO
    IF historical_entry.issues_found <> 0 THEN
      SET found_date = historical_entry.run_date;
    ELSE
      BREAK;
    END IF;
  END FOR;
  UPDATE my_table SET issues_started = found_date where product = row.product;
END FOR;

高效替代方案 #

可以通过窗口函数+聚合查询的方式一次性计算出结果，彻底避免嵌套循环的低效操作：

完整SQL语句 #

WITH product_last_zero AS (
    -- 找到每个产品最后一次出现issues_found=0的日期，无0值则用极小值替代
    SELECT 
        product,
        COALESCE(MAX(run_date), '1970-01-01') AS last_zero_date
    FROM my_table
    WHERE issues_found = 0
    GROUP BY product
),
product_issue_start AS (
    -- 计算有0值产品的问题起始日期：最后一次0值之后最早的非0日期
    SELECT 
        t.product,
        MIN(t.run_date) AS issues_started
    FROM my_table t
    JOIN product_last_zero plz ON t.product = plz.product
    WHERE t.issues_found <> 0 AND t.run_date > plz.last_zero_date
    GROUP BY t.product
    UNION ALL
    -- 计算从未出现0值产品的问题起始日期：最早的非0日期
    SELECT 
        product,
        MIN(run_date) AS issues_started
    FROM my_table
    WHERE issues_found <> 0
    AND product NOT IN (SELECT product FROM product_last_zero)
    GROUP BY product
)
-- 批量更新原表
UPDATE my_table t
JOIN product_issue_start pis ON t.product = pis.product
SET t.issues_started = pis.issues_started;

优化补充 #

• 该方案仅需几次表扫描，时间复杂度为O(n)，相比嵌套循环的O(n²)效率提升显著
• 建议创建复合索引加速查询：

  CREATE INDEX idx_product_run_issues ON my_table(product, run_date, issues_found);
  

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