SQL查询实现按行提取最大值及对应贡献列名（适配大宽表）

你这个35000行、30000列的宽表场景，别手写逐列比较的逻辑——维护成本拉满还容易触发SQL长度、单表列数相关的数据库限制，最优思路是先把宽表转成长表再做聚合，不同场景的具体实现如下： #

通用最优方案：逆透视后分组聚合（适配绝大多数数据库） #

先把原本每列对应一个指标的宽表，转换成每行存分组ID、列名、列值的长表结构，再按分组ID排序取最大值对应的行即可，全程不需要手动列所有字段，直接查系统表生成列名拼动态SQL就行。
以PostgreSQL为例，核心代码：

WITH long_format AS (
    SELECT
        "Group",
        unnest(:col_name_arr) AS metric_name,
        unnest(:col_val_arr) AS metric_value
    FROM your_raw_table
)
SELECT DISTINCT ON ("Group")
    "Group",
    metric_value AS max_val,
    metric_name AS max_column
FROM long_format
ORDER BY "Group", metric_value DESC;

其中:col_name_arr和:col_val_arr不需要手动填3万个列名，直接查系统表information_schema.columns拿到目标表除了Group之外的所有列名，拼接成数组字符串传入即可，整个SQL生成只需要几行代码。
如果是用Oracle、SQL Server，直接用内置的UNPIVOT算子实现逆透视，逻辑完全一致。 #

快捷方案：用数据库原生行转键值对函数 #

如果不想写动态SQL，也可以用数据库自带的JSON/行处理函数直接把行转成键值对再取最大值，不需要提前枚举列：

PostgreSQL 实现 #

SELECT
    "Group",
    (kv).value::int AS max_val,
    (kv).key AS max_column
FROM (
    SELECT
        "Group",
        jsonb_each(to_jsonb(your_raw_table) - 'Group') kv
    FROM your_raw_table
) t
ORDER BY "Group", max_val DESC
LIMIT 1 PER PARTITION BY "Group";

BigQuery 实现 #

SELECT
    `Group`,
    val AS max_val,
    key AS max_column
FROM your_raw_table,
UNNEST(bqutil.fn.json_extract_keys(TO_JSON(your_raw_table))) key WITH OFFSET pos1,
UNNEST(bqutil.fn.json_extract_values(TO_JSON(your_raw_table))) val WITH OFFSET pos2
WHERE pos1 = pos2 AND key != 'Group'
QUALIFY ROW_NUMBER() OVER (PARTITION BY `Group` ORDER BY val DESC) = 1

注意点 #

• 如果单行存在多个列值同为最大值的情况，把上述逻辑里的ROW_NUMBER()/DISTINCT ON换成RANK()，再用STRING_AGG拼接所有符合条件的列名即可。
• 35000行的数据量不管用上面哪种方案，执行耗时都在秒级，不会有性能问题。
• 禁止硬编码3万个列的GREATEST+CASE判断逻辑，后续加列减列都要改SQL，维护成本极高。

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