BigQuery中将单行日志拆分为多列并汇总对应值的方法

要实现把日志中的TestXXX(N)格式项转为列、数值作为对应值并汇总，核心是拆分提取键值对 + 动态行转列，以下是通用步骤和不同数据库的实现示例：

1. 拆分并提取测试项的名称与数值 #

首先把单行日志按空格拆分为单个测试项，再用正则表达式提取名称和对应的数值：

PostgreSQL 示例 #

-- 先验证提取结果
SELECT 
  log_id, -- 假设表中有唯一标识每行日志的字段
  (regexp_match(item, '(\w+)\((\d+)\)'))[1] AS test_name,
  (regexp_match(item, '(\w+)\((\d+)\)'))[2]::INT AS test_value
FROM (
  SELECT 
    log_id,
    unnest(string_to_array(log_line, ' ')) AS item -- 按空格拆分日志
  FROM your_table
) t
WHERE item ~ '^\w+\(\d+\)$'; -- 过滤无效格式的项

BigQuery 示例 #

-- 先验证提取结果
SELECT 
  log_id,
  REGEXP_EXTRACT(item, r'(\w+)\(\d+\)') AS test_name,
  SAFE_CAST(REGEXP_EXTRACT(item, r'\w+\((\d+)\)') AS INT64) AS test_value
FROM your_table,
UNNEST(SPLIT(log_line, ' ')) AS item
WHERE REGEXP_CONTAINS(item, r'^\w+\(\d+\)$');

2. 动态行转列（PIVOT） #

由于测试项数量不固定，无法硬编码列名，需要先获取所有唯一的测试项名称，再动态生成PIVOT语句：

PostgreSQL 动态SQL实现 #

DO $$
DECLARE
  test_cols TEXT;
BEGIN
  -- 获取所有唯一的测试项名称，转成SQL合法列名
  SELECT string_agg(DISTINCT quote_ident(test_name), ', ') INTO test_cols
  FROM (
    SELECT 
      (regexp_match(item, '(\w+)\((\d+)\)'))[1] AS test_name
    FROM (
      SELECT unnest(string_to_array(log_line, ' ')) AS item
      FROM your_table
    ) t
    WHERE item ~ '^\w+\(\d+\)$'
  ) t;

  -- 执行动态PIVOT，SUM用于汇总同一行可能重复的测试项数值
  EXECUTE format('
    SELECT 
      log_id,
      %s
    FROM (
      SELECT 
        log_id,
        (regexp_match(item, ''(\w+)\((\d+)\)''))[1] AS test_name,
        (regexp_match(item, ''(\w+)\((\d+)\)''))[2]::INT AS test_value
      FROM (
        SELECT log_id, unnest(string_to_array(log_line, '' '')) AS item
        FROM your_table
      ) t
      WHERE item ~ ''^\w+\(\d+\)$''
    ) src
    PIVOT (
      SUM(test_value)
      FOR test_name IN (%s)
    ) pvt;
  ', test_cols, test_cols);
END $$;

BigQuery 动态SQL实现 #

DECLARE test_cols ARRAY<STRING>;
SET test_cols = ARRAY(
  SELECT DISTINCT REGEXP_EXTRACT(item, r'(\w+)\(\d+\)')
  FROM your_table, UNNEST(SPLIT(log_line, ' ')) AS item
  WHERE REGEXP_CONTAINS(item, r'^\w+\(\d+\)$')
);

EXECUTE IMMEDIATE FORMAT("""
  SELECT *
  FROM (
    SELECT 
      log_id,
      REGEXP_EXTRACT(item, r'(\w+)\(\d+\)') AS test_name,
      SAFE_CAST(REGEXP_EXTRACT(item, r'\w+\((\d+)\)') AS INT64) AS test_value
    FROM your_table, UNNEST(SPLIT(log_line, ' ')) AS item
    WHERE REGEXP_CONTAINS(item, r'^\w+\(\d+\)$')
  )
  PIVOT (
    SUM(test_value) FOR test_name IN (%s)
  )
""", ARRAY_TO_STRING(ARRAY(SELECT CONCAT('"', col, '"') FROM UNNEST(test_cols) col), ', '));

注意事项 #

• 正则适配：如果测试项名称包含下划线、连字符等，把正则中的\w+改为[\w-]+即可
• 数据清洗：确保过滤掉日志中不符合XXX(N)格式的无效项，避免提取错误
• 数据库差异：不同数据库的字符串拆分、正则函数语法不同，比如MySQL用REGEXP_SUBSTR和JSON_TABLE实现拆分，需根据所用数据库调整

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