临时表意外过滤带子女家庭数据，求排查解决

架构老林

2026-6-13

问题分析与解决方案

1. 核心问题：WHERE条件误过滤有效数据

你添加的WHERE条件看似覆盖所有情况，实则会过滤掉正则提取失败的行：

WHERE CAST(REGEXP_EXTRACT(family_member_count, r'p(\d+)c') AS INT64) > 0 
OR CAST(REGEXP_EXTRACT(family_member_count, r'p(\d+)c') AS INT64) < 1 ;

如果某些带子女的行格式不符合XpYc（比如含空格、大小写异常），REGEXP_EXTRACT会返回NULL，CAST后仍是NULL。而NULL在比较运算中返回UNKNOWN，导致这些行被直接过滤。

2. 正则表达式健壮性不足

默认正则r'p(\d+)c'仅匹配严格的p数字c格式，若原数据存在格式变体（如2p 3c、1P4C），会导致提取失败。

3. 修正步骤

步骤1：移除冗余过滤条件

创建临时表时直接拉取全量数据，避免误过滤：

CREATE OR REPLACE TEMP TABLE temp_us_col AS
SELECT *
FROM `capstone-project-456214.us_col_county.us_cost_of_living_county`;

步骤2：优化正则匹配

调整正则兼容空格、大小写等异常格式：

ALTER TABLE temp_us_col
    ADD COLUMN family_count INT64,
    ADD COLUMN children INT64;

UPDATE temp_us_col
SET 
  family_count = CAST(REGEXP_EXTRACT(family_member_count, r'(\d+)p', 'i') AS INT64) +
                 CAST(REGEXP_EXTRACT(family_member_count, r'p\s*(\d+)\s*c', 'i') AS INT64),
  children = CAST(REGEXP_EXTRACT(family_member_count, r'p\s*(\d+)\s*c', 'i') AS INT64)
WHERE TRUE;

'i'参数：忽略大小写匹配
\s*：匹配任意数量的空格

步骤3：验证提取结果

先单独检查正则提取是否正常，定位格式异常的行：

SELECT family_member_count,
       REGEXP_EXTRACT(family_member_count, r'p\s*(\d+)\s*c', 'i') AS extracted_children
FROM `capstone-project-456214.us_col_county.us_cost_of_living_county`
WHERE family_member_count LIKE '%p%c%'
ORDER BY extracted_children;