sql - BigQuery SQL 查询中的动态列名

Question

我有一个 BigQuery 表，其中每一行都是某个国家/地区用户的访问。模式是这样的:

UserID   |   Place   |   StartDate   |   EndDate   | etc ...
---------------------------------------------------------------
134      |  Paris    |   234687432   |   23648949  | etc ...
153      |  Bangkok  |   289374897   |   2348709   | etc ...
134      |  Paris    |   9287324892  |   3435438   | etc ...

“地点”列的值最多不过几十个选项，但我事先并不知道。

我想查询这张表，结果表中的列被命名为Place列的所有可能性，值是每个用户在这个地方的总访问次数。最终结果应该是这样的:

UserID | Paris | Bangkok | Rome | London | Rivendell | Alderaan 
----------------------------------------------------------------
134    |  2    |  0      |  0   |  0     |  0        |  0 
153    |  0    |  1      |  0   |  0     |  0        |  0

我想我可以使用 SELECT DISTINCT 选择“Place”的所有可能值，但我怎样才能实现结果表的这种结构？

谢谢

Answer 1

以下是 BigQuery 标准 SQL

第 1 步 - 使用“place”字段的所有可能值动态组装正确的 SQL 语句

#standardSQL
SELECT '''
SELECT UserID,''' || STRING_AGG(DISTINCT
  ' COUNTIF(Place = "' || Place || '") AS ' || REPLACE(Place, ' ', '_')
) || ''' FROM `project.dataset.table`
GROUP BY UserID
'''
FROM `project.dataset.table`

注意:您将得到一行输出，文本如下(为了更好阅读，已经分成多行

SELECT UserID, 
COUNTIF(Place = "Paris") AS Paris, 
COUNTIF(Place = "Los Angeles") AS Los_Angeles 
FROM `project.dataset.table` 
GROUP BY UserID

注意；我用 Los Angeles 替换了 Bangkok 所以你明白为什么用下划线替换可能的空格很重要

第 2 步 - 只需复制第 1 步的输出文本并简单地运行它

显然，您可以使用您选择的任何客户端自动执行上述两个步骤