如何优化BigQuery中对名称含相同子串的多列的操作查询？

针对动态列（FLCOLY/FLCOLX系列列数不固定）的SQL需求，以下是两种核心操作的优化写法，适配大多数支持SQL标准的数据库（如BigQuery、PostgreSQL、Spark SQL等）：

一、优化ROW_NUMBER的分区逻辑 #

原写法需手动枚举所有FLCOLY/FLCOLX列，列数变化时需频繁修改代码。可通过**将同系列列转为结构化数据（数组/JSON）**简化分区条件：

方法1：数组聚合简化（支持数组函数的数据库） #

把每组FLCOLYn和FLCOLXn转为数组元素，用整个数组作为分区键：

ROW_NUMBER() OVER (
    PARTITION BY 
        name, surname, description,
        -- 聚合所有FLCOLY列为字符串数组
        ARRAY[CAST(FLCOLY01 AS STRING), CAST(FLCOLY02 AS STRING), ..., CAST(FLCOLYn AS STRING)],
        -- 聚合所有FLCOLX列为字符串数组
        ARRAY[CAST(FLCOLX01 AS STRING), CAST(FLCOLX02 AS STRING), ..., CAST(FLCOLXn AS STRING)]
    ORDER BY date ASC
)

若数据库支持ARRAY_CONCAT，可合并两个数组进一步简化：

ROW_NUMBER() OVER (
    PARTITION BY 
        name, surname, description,
        ARRAY_CONCAT(
            ARRAY[CAST(FLCOLY01 AS STRING), ..., CAST(FLCOLYn AS STRING)],
            ARRAY[CAST(FLCOLX01 AS STRING), ..., CAST(FLCOLXn AS STRING)]
        )
    ORDER BY date ASC
)

方法2：动态生成SQL（适配列数频繁变化的场景） #

通过查询数据库元数据（如INFORMATION_SCHEMA.COLUMNS）自动生成分区列列表，以PostgreSQL为例：

-- 查询目标表的FLCOLY/FLCOLX列，生成CAST语句
SELECT string_agg('CAST(' || column_name || ' AS STRING)', ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'your_table_name'
  AND (column_name LIKE 'FLCOLY%' OR column_name LIKE 'FLCOLX%')
ORDER BY column_name;

将查询结果直接替换到ROW_NUMBER的PARTITION BY中即可，无需手动维护列列表。

二、优化FLCOLX=125的CASE WHEN逻辑 #

原写法需逐个列判断，列数变化时需增减WHEN分支。可通过行转列+条件匹配简化：

方法1：UNNEST横向展开（支持数组的数据库） #

把FLCOLX和对应VALUE列转为数组，展开后匹配条件：

SELECT
    -- 其他业务列
    (SELECT val
     FROM UNNEST(ARRAY[FLCOLX01, FLCOLX02, ..., FLCOLXn]) AS col
     WITH OFFSET idx
     JOIN UNNEST(ARRAY[VALUE_1, VALUE_2, ..., VALUE_n]) AS val
     WITH OFFSET idx2
     ON idx = idx2
     WHERE col = 125
     LIMIT 1) AS FLCOLX125
FROM your_table;

方法2：COALESCE简化（列数变化不频繁时） #

用COALESCE替代冗长的CASE WHEN，代码更紧凑：

COALESCE(
    CASE WHEN FLCOLX01 = 125 THEN VALUE_1 END,
    CASE WHEN FLCOLX02 = 125 THEN VALUE_2 END,
    -- ... 依次添加后续列的判断
    NULL
) AS FLCOLX125

方法3：动态生成CASE WHEN语句（适配列数变化） #

利用元数据查询自动生成完整的CASE WHEN分支：

SELECT string_agg(
    'WHEN ' || column_name || ' = 125 THEN VALUE_' || substring(column_name from 'FLCOLX(\d+)'),
    ' '
) || ' ELSE NULL END AS FLCOLX125'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'your_table_name'
  AND column_name LIKE 'FLCOLX%'
ORDER BY column_name;

执行后直接复制生成的语句到主SQL即可。

额外建议：长期优化方案 #

如果频繁处理这类动态列，建议重构数据模型：将FLCOLY/FLCOLX系列列转为行存储结构，比如新增关联表：

主表ID | 类型（Y/X） | 序号 | 值

重构后，分区、匹配等操作会更灵活，无需依赖动态SQL或复杂数组处理。

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