sql - BigQuery SQL - 根据多列的最大值创建新列

问题描述

我有一张表格，其中包含有关客户的信息及其每种食物的购买量。我想创建新列，这是他们购买的最常见的食物类型。有没有一种有效的方法来做到这一点？

我尝试使用 case when 并进行一对一的比较，但它变得非常乏味。

样本数据：

客户 ID	apple_type1	apple_type2	apple_type3	apple_type4	apple_type5	apple_type6
1	2	0	0	3	6	1
2	0	0	0	1	0	1
3	4	2	1	1	0	1
4	5	5	5	0	0	0
5	0	0	0	0	0	0

- 想

客户 ID	freq_apple_type_buy
1	类型5
2	类型 4 和类型 6
3	类型1
4	type1 和 type2 和 type3
5	未知

标签： sqlgoogle-bigquery

考虑以下方法

select Cust_ID, if(count(1) = any_value(all_count), 'unknown', string_agg(type, ' and ')) freq_apple_type_buy
from (
  select *, count(1) over(partition by Cust_ID) all_count
  from (
    select Cust_ID, replace(arr[offset(0)], 'apple_', '') type,cast(arr[offset(1)] as int64) value
    from data t,
    unnest(split(translate(to_json_string((select as struct * except(Cust_ID) from unnest([t]))), '{}"', ''))) kv,
    unnest([struct(split(kv, ':') as arr)])
  )
  where true qualify 1 = rank() over(partition by Cust_ID order by value desc)
)
group by Cust_ID

如果应用于您问题中的样本数据 - 输出是

sql - BigQuery SQL - 根据多列的最大值创建新列

问题描述

解决方案

推荐阅读