时间戳

首页 > 解决方案 > 有没有办法从 BigQuery 的表中获取所有列的不同值？

google-cloud-platform - 有没有办法从 BigQuery 的表中获取所有列的不同值？

问题描述

我试图使用 Select column_name from projectname.tablename.INFORMATION_SCHEMA.COLUMNS where table_name = 'something'.

我不确定正确的方法是否是遍历所有列名，同时为每个列名获取不同的值，或者在 BigQuery 中是否有不同的方法可以遵循。

期望结果是：

Col1 Col2 Col3
1     4    7
2     5    8
3          9
           11

查询必须返回所有列的不同值，而不考虑值的长度差异。

标签： google-cloud-platformgoogle-bigquery

解决方案

不确定您的要求有多实用，但它可能适用于相对较小的表 - 所以下面是 BigQuery 标准 SQL 

#standardSQL
CREATE TEMP FUNCTION DISTINCT_VALUES (arr ANY TYPE) AS (
  ARRAY(SELECT DISTINCT el FROM UNNEST(arr) AS el ORDER BY el)
);
SELECT 
  DISTINCT_VALUES(col1) col1,
  DISTINCT_VALUES(col2) col2,
  DISTINCT_VALUES(col3) col3
FROM (
  SELECT 
    ARRAY_AGG(col1) OVER() col1,
    ARRAY_AGG(col2) OVER() col2,
    ARRAY_AGG(col3) OVER() col3
  FROM `project.dataset.table`
  LIMIT 1
) t

是否适用于您的问题中的示例数据，如下例所示 

#standardSQL
CREATE TEMP FUNCTION DISTINCT_VALUES (arr ANY TYPE) AS (
  ARRAY(SELECT DISTINCT el FROM UNNEST(arr) AS el ORDER BY el)
);
WITH `project.dataset.table` AS (
  SELECT 1 col1, 4 col2, 7 col3 UNION ALL
  SELECT 2, 5, 8 UNION ALL
  SELECT 3, 4, 9 UNION ALL
  SELECT 1, 5, 11 
)
SELECT 
  DISTINCT_VALUES(col1) col1,
  DISTINCT_VALUES(col2) col2,
  DISTINCT_VALUES(col3) col3
FROM (
  SELECT 
    ARRAY_AGG(col1) OVER() col1,
    ARRAY_AGG(col2) OVER() col2,
    ARRAY_AGG(col3) OVER() col3
  FROM `project.dataset.table`
  LIMIT 1
) t

结果是

在此处输入图像描述

所以，我认为更合理的方法是得到如下结果

在此处输入图像描述

这可以通过以下查询来实现 

#standardSQL
SELECT DISTINCT 'col1' col, col1 value FROM `project.dataset.table` UNION ALL
SELECT DISTINCT 'col2', col2 FROM `project.dataset.table` UNION ALL
SELECT DISTINCT 'col3', col3 FROM `project.dataset.table`

如果不同的列具有不同的数据类型 - 您可以将它们转换为 STRING，如下例所示 

#standardSQL
SELECT DISTINCT 'col1' col, CAST(col1 AS STRING) value FROM `project.dataset.table` UNION ALL
SELECT DISTINCT 'col2', CAST(col2 AS STRING) FROM `project.dataset.table` UNION ALL
SELECT DISTINCT 'col3', CAST(col3 AS STRING) FROM `project.dataset.table`

最后说明：如果列数足够大，可以手动输入上述所有查询 - 您可以轻松编写脚本 - 请参阅https://stackoverflow.com/a/61716652/5221944中的示例

推荐阅读