BigQuery 从复杂字符串提取252=后数值并求和的实现方法
问题描述
需对结构化存储的混合格式字符串做数值提取求和,测试用临时表及示例数据如下:
with temp as ( select 1 as event_id, ';t-Tew00;1;1.00;252=100.00,;SM-R190;1;1.00;252=200.00,;SM-G998B/DS;1;6347.00;252=300.00,;EF-PG99P;1;249.00;252=400.00' as event_list union all select 2 as event_id, ';asdI-Tww5300;1;1.00;252=99.00,,;EP-TA845;.252=49.00' as event_list union all select 3 as event_id, ';asdI-Tww5300;1;1.00;252=10.00,,;EP-TA845;,.252=20.00,:etw:1002:2020,' as event_list ) select * from temp
需求规则
- 从
event_list列中,提取所有紧跟在252=后的整型、浮点型数值 - 对单条记录内提取到的所有数值做累加,生成独立的求和列
- 若字符串中不存在
252=的匹配项,求和结果返回0 - 预期计算结果:
- event_id=1:提取值为100.00、200.00、300.00、400.00,求和值1000.00
- event_id=2:提取值为99.00、49.00,求和值148.00
- event_id=3:提取值为10.00、20.00,求和值30.00
BigQuery 实现方案
核心逻辑为通过正则全量匹配目标数值片段,转类型后求和,对无匹配场景做兜底处理,完整可运行SQL如下:
WITH temp AS ( SELECT 1 AS event_id, ';t-Tew00;1;1.00;252=100.00,;SM-R190;1;1.00;252=200.00,;SM-G998B/DS;1;6347.00;252=300.00,;EF-PG99P;1;249.00;252=400.00' AS event_list UNION ALL SELECT 2 AS event_id, ';asdI-Tww5300;1;1.00;252=99.00,,;EP-TA845;.252=49.00' AS event_list UNION ALL SELECT 3 AS event_id, ';asdI-Tww5300;1;1.00;252=10.00,,;EP-TA845;,.252=20.00,:etw:1002:2020,' AS event_list ) SELECT event_id, COALESCE( ( SELECT SUM(CAST(num_str AS FLOAT64)) FROM UNNEST(REGEXP_EXTRACT_ALL(event_list, r'252=([0-9]+(?:\.[0-9]+)?)')) AS num_str ), 0 ) AS sum_252_target_value FROM temp
逻辑说明
- 用
REGEXP_EXTRACT_ALL函数全量扫描字符串,匹配所有252=后紧跟的整数/小数片段,返回匹配结果组成的数组 - 通过
UNNEST将数组拆分为逐行的数值字符串,转换为浮点类型后执行求和 - 通过
COALESCE函数做异常兜底:当记录无任何匹配值时,直接返回0 - 适配所有测试场景的分隔符格式(分号、逗号、点号等),不会误提取非目标位置的数字
内容的提问来源于stack exchange,提问作者Regressor
相关产品推荐
相关产品推荐

