主表80M记录下日期范围左连接优化及空值补0需求

原SQL可正常运行，但存在缺陷：当detail表中某日期无数据时，该日期对应的所有ProductionStatusNo记录会缺失，需要显示这些日期各状态计数为0的行。

原SQL问题分析 #

原查询内层使用INNER JOIN关联detail表和date_range_production_status，这会导致无对应数据的日期直接被过滤，无法保留空状态的行。要解决这个问题，需从完整的日期-状态组合表出发，左关联业务数据的聚合结果，确保所有日期-状态组合都被保留，再用COALESCE将空计数转为0。

修正后的SQL #

-- 生成日期范围临时表
DROP TEMPORARY TABLE IF EXISTS date_range;
CREATE TEMPORARY TABLE date_range AS
SELECT DATE('2023-12-01') + INTERVAL (n-1) DAY AS StatusDate
FROM (
  SELECT ROW_NUMBER() OVER () AS n 
  FROM detail 
  LIMIT 31  
) AS nums;

-- 生成日期与生产状态的全量组合表
DROP TEMPORARY TABLE IF EXISTS date_range_production_status; 
CREATE TEMPORARY TABLE date_range_production_status AS
SELECT 
  d.StatusDate,
  p.Status AS ProductionStatus,
  p.Id AS ProductionStatusNo
FROM date_range d
CROSS JOIN productionstatus p 
WHERE p.Id NOT IN (0,1);

-- 计算各日期-状态的计数，补全无数据的0值
SELECT
  COALESCE(ed.c, 0) AS c,
  drps.ProductionStatusNo,
  drps.StatusDate,
  drps.ProductionStatus,
  COALESCE(ed.ProductionFacility, '') AS ProductionFacility
FROM date_range_production_status drps
LEFT JOIN (
  -- 先聚合每个UniqueFormId在各日期的最新状态
  SELECT
    dr.StatusDate,
    detail.ProductionFacility,
    MAX(detail.productionStatusNo) AS MaxPrductionStatusNo,
    COUNT(*) AS c
  FROM detail
  INNER JOIN date_range_production_status dr 
    ON detail.StatusDate <= dr.StatusDate 
    AND detail.ProductionStatusNo != 1
  GROUP BY dr.StatusDate, detail.ProductionFacility, detail.UniqueFormId
) AS ed 
  ON drps.StatusDate = ed.StatusDate 
  AND drps.ProductionStatusNo = ed.MaxPrductionStatusNo
GROUP BY drps.StatusDate, drps.ProductionStatusNo, drps.ProductionStatus, ed.ProductionFacility
ORDER BY drps.StatusDate, drps.ProductionStatusNo, drps.ProductionFacility;

关键改动说明 #

• 主查询从全量组合表出发：用LEFT JOIN关联业务聚合数据，确保所有日期-状态组合都被保留，即使无对应数据也不会被过滤。
• 用COALESCE处理空计数：将关联后的空计数转为0，满足无数据时显示0的需求。
• 明确分组逻辑：确保分组包含所有需要展示的字段，避免分组歧义。

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