时间戳

首页 > 解决方案 > 对 BigQuery 中的所有列应用函数

sql - 对 BigQuery 中的所有列应用函数

问题描述

我有一个超过 100 列 c1,c2,c3....c200 的表，我想在所有列上应用函数（假设 MAX）。我可以为每一列编写查询，但我不能使用 BigQuery 引发此错误的 *Argument * can only be used in COUNT(*)

这种查询格式有效，但我的查询大小会更大，并且与列数成正比。

SELECT max(c1) as c1, max(c2) as c2 .... max(c200) as c200 FROM `MYTABLE` group by user

我可以用更短的形式编写查询吗？

标签： sqlfunctionoptimizationgoogle-bigquerymultiple-columns

解决方案

下面的示例适用于 BigQuery 标准 SQL，并避免按名称调用所有列，但作为副作用结果只是一个逗号分隔的最大值列表，按相应列的顺序排列 

#standardSQL
SELECT STRING_AGG(CAST(max_val AS STRING) ORDER BY pos) max_values
FROM (
  SELECT pos, MAX(CAST(val AS INT64)) max_val
  FROM `project.dataset.table` t,
  UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'".+?":([^,}]+)')) val WITH OFFSET pos
  GROUP BY pos
)

在上面的示例中，我假设列都是 INT64 数据类型

您可以使用虚拟数据进行测试，使用上面的示例，如下例所示 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 c1, 2 c2, 3 c3 UNION ALL
  SELECT 11, 1, 22
)
SELECT STRING_AGG(CAST(max_val AS STRING) ORDER BY pos) max_values
FROM (
  SELECT pos, MAX(CAST(val AS INT64)) max_val
  FROM `project.dataset.table` t,
  UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'".+?":([^,}]+)')) val WITH OFFSET pos
  GROUP BY pos
)

结果 

Row max_values   
1   11,2,22

推荐阅读