在Snowflake中提取数组内M2开头元素的BITC值并求和

给定如下字符串数组，需要提取所有以M2开头的元素中BITC对应的数值，并对这些值求和：

[
  "M1 USD 3399.43 BITC 3990.50 MAD 0.00 LOSS -591.07",
  "M2 USD 3144.96 BITC **3399.43** MAD 0.00 LOSS -254.47",
  "M3 USD 1131.78 BITC 3144.96 MAD 0.00 LOSS -2013.18",
  "M1 USD 7.91 BITC 3.92 MAD 0.00 LOSS 3.99",
  "M2 USD 1.47 BITC **7.91** MAD 0.00 LOSS -6.44",
  "M3 USD 2.85 BITC 1.47 MAD 0.00 LOSS 1.38",
  "M1 USD 0.30 BITC 0.30 MAD 0.00 LOSS 0.00",
  "M2 USD 158.21 BITC** 0.30** MAD 0.00 LOSS 157.91",
  "M3 USD 204.17 BITC 158.21 MAD 0.00 LOSS 45.96",
  "M1 USD 0.63 BITC 0.63 MAD 0.00 LOSS 0.00",
  "M2 USD 0.63 BITC **0.63 ** MAD 0.00 LOSS 0.00",
  "M3 USD 0.63 BITC 0.63 MAD 0.00 LOSS 0.00"
]

目标求和值为：3399.43 + 7.91 + 0.30 + 0.63 = 3408.27，同时需要兼容不规范格式（如M2USD这种无空格的开头）。

原尝试的SQL语句提取失败：

regexp_substr_all(ARRAY_TO_STRING(X, ','), 'M2.*?BITC\s?(-?\d+\.?\d+)',1,1,'e') as BITC_M2,

仅返回["0.63"]，未获取所有符合条件的值。

原SQL的核心问题：

• 将数组转为字符串后整体匹配，非贪婪模式.*?会在长字符串中仅匹配到最后一个符合条件的结果
• 未处理数据中存在的**干扰字符，导致正则无法正确捕获数值
• 未遍历数组元素，而是对整个数组字符串做单次匹配，无法提取所有M2开头元素的对应值

步骤说明 #

1. 展平数组：将数组拆分为单行元素，方便逐个处理
2. 过滤M2开头元素：使用STARTS_WITH筛选出符合条件的行
3. 提取BITC数值：用正则匹配BITC后被任意非数字字符包裹/分隔的数值，兼容**、空格等不规范格式
4. 转换并求和：将提取的字符串转为数值类型，最终求和

完整SQL代码 #

假设数组列名为X，表名为your_table：

WITH flattened_data AS (
  SELECT 
    value AS m_item
  FROM your_table,
  LATERAL FLATTEN(input => X)
  WHERE STARTS_WITH(m_item, 'M2')
),
extracted_bitc AS (
  SELECT
    TRY_CAST(REGEXP_SUBSTR(m_item, 'BITC\W*(-?\d+\.?\d+)', 1, 1, 'e') AS FLOAT) AS bitc_value
  FROM flattened_data
)
SELECT SUM(bitc_value) AS total_bitc_sum
FROM extracted_bitc;

正则说明 #

BITC\W*(-?\d+\.?\d+)：

• BITC：匹配固定标识
• \W*：匹配任意非数字字符（包括**、空格、无空格连接等），零次或多次
• (-?\d+\.?\d+)：捕获可选负号、整数部分、可选小数点和小数部分的数值，使用'e'参数仅返回捕获组内容

结果验证 #

执行后将得到正确求和结果：3408.27

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