时间戳

首页 > 解决方案 > 从 SQL 中许多列名中具有最高值的前 2 个列名中生成二进制值

sql - 从 SQL 中许多列名中具有最高值的前 2 个列名中生成二进制值

问题描述

我有一个如下表：

ID col1 col2 col3 col4
一种 100 400 30 800
乙 600 50 500 75

我想要一个查询，我可以在其中返回类似

ID col1 col2 col3 col4
一种 0 1 0 1
乙 1 0 1 0

除了，我希望逻辑查看每一行，并为每一行找到哪两列具有前 2 个值。我想可能涉及一些 CTE 或子查询。即使达到会产生以下结果的 CTE 也足够了，但不知道如何达到这个 CTE：

ID top_2_col_name
一种 col2
一种 col4
乙 col1
乙 col3

有没有办法按行而不是按列执行聚合和窗口函数？我正在使用 Google 的 BigQuery SQL。

标签： sqlgoogle-bigquery

解决方案

如果您想要前两个值，那么一种方法是取消嵌套值并计算排名并选择它们：

with t as (
      select 'A' as id, 100 as col1, 400 as col2,  30 as col3, 800 as col4 union all
      select 'B' as id, 600 as col1, 50  as col2, 500 as col3, 75 as col4 
     )
select * except (seqnum)
from (select t.id, col.*, row_number() over (partition by t.id order by col.val desc) as seqnum
      from t cross join
           unnest(array[struct('col1' as col, t.col1 as val),
                              struct('col2', t.col2),
                              struct('col3', t.col3),
                              struct('col4', t.col4)
                             ]
                        ) col
     ) tc
where seqnum <= 2;

这是结果集的第二种形式。

您可以使用 JSON 技巧将其推广到任意数量的列。这会产生一个字符串，然后为您关心的列解析字符串，将它们取消嵌套并执行类似的操作：

with t as (
      select 'A' as id, 100 as col1, 400 as col2,  30 as col3, 800 as col4 union all
      select 'B' as id, 600 as col1, 50  as col2, 500 as col3, 75 as col4 
     )
select t.id, concat('col', n), val
from (select t.id, val, n, row_number() over (partition by t.id order by val desc) as seqnum
      from t cross join
           unnest(regexp_extract_all(to_json_string(t), '"col[0-9]+":([0-9]+)')) val with offset n
     ) t
where seqnum <= 2;

这可以在任意数量的列上工作。当然，如果你有这样的数据结构，那么这些值真的应该存储在一个数组中。

推荐阅读