如何在BigQuery中按条件累积统计设备适用的规格修改次数？

背景数据 #

ST表（应用计划规格修改记录） #

AQ表（设备生产记录） #

已拆分的ST_UNN表 #

已将ST表的Specs字段拆分为单个规格条目，得到ST_UNN表：

需求 #

统计每个设备对应的符合以下条件的规格修改次数：

• 修改所属App_ID与设备App_ID一致
• 设备生产日期（AQ.Prod_Date）≥ 修改生效日期（ST.Start_Date）
• 设备的Spec_string包含该修改对应的单个规格（ST_UNN.Specs_ind）

尝试的SQL语句 #

SELECT
  a.* EXCEPT(Spec_String),
  SUM(
    CASE
      WHEN s.App_ID = a.App_ID AND s.Start_Date <= a.Prod_Date THEN ARRAY_LENGTH(REGEXP_EXTRACT_ALL(Spec_String, Specs_ind))
    ELSE
    0
  END
    ) OVER(PARTITION BY s.Specs_ind ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS num_changes,
FROM
  AQ AS a
LEFT JOIN
  ST_UNN AS s
ON
  a.App_ID  = s.App_ID 

预期结果 #

问题分析与解决方案 #

原SQL的核心问题：

1. 窗口函数的分区逻辑错误，按s.Specs_ind分组求和不符合"按设备统计"的需求
2. REGEXP_EXTRACT_ALL未处理独立规格匹配，可能出现部分匹配（比如S1匹配S10）
3. LEFT JOIN后产生重复行，窗口函数的累积求和逻辑完全偏离需求

修正后的BigQuery SQL（方案一：JOIN+分组统计） #

SELECT
  a.Device_ID,
  a.App_ID,
  a.Spec_string,
  a.Prod_Date,
  -- 统计满足所有条件的修改次数
  COUNTIF(
    s.Start_Date <= a.Prod_Date 
    AND REGEXP_CONTAINS(a.Spec_string, r'(\s|^)' || s.Specs_ind || r'(\s|$)')
  ) AS num_changes
FROM AQ a
LEFT JOIN ST_UNN s
ON a.App_ID = s.App_ID
GROUP BY a.Device_ID, a.App_ID, a.Spec_string, a.Prod_Date
ORDER BY a.Device_ID;

修正后的BigQuery SQL（方案二：子查询统计） #

SELECT
  *,
  (
    SELECT COUNT(*)
    FROM ST_UNN s
    WHERE s.App_ID = a.App_ID
      AND s.Start_Date <= a.Prod_Date
      AND REGEXP_CONTAINS(a.Spec_string, r'(\s|^)' || s.Specs_ind || r'(\s|$)')
  ) AS num_changes
FROM AQ a
ORDER BY Device_ID;

关键说明 #

• REGEXP_CONTAINS(..., r'(\s|^)' || s.Specs_ind || r'(\s|$)')：确保匹配独立的规格条目，避免部分匹配问题
• 两种方案均直接按设备维度统计符合条件的修改次数，无需多余的窗口函数累积逻辑
• LEFT JOIN/子查询保证所有设备都会被保留，无符合条件修改时num_changes自动为0

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