在BigQuery中按列堆栈/追加表

Question

我在BigQuery中有一个表，它每天获取新数据并将其附加到当前表中。此表称为：score_FULL。此外，我每天都会保存各个分数，如果我们选择今天的日期，score_20180125可以访问这些分数。每日分数每天都会附加到score_FULL。

score_FULL包含：

visitorID         score
#Older scores first
1                 0.15
2                 0.78
3                 0.12
6                 0.90
------------------------
2                 0.22
6                 0.65
7                 0.61
10                0.24
------------------------
1                 0.31
2                 0.41
10                0.12
-------------------------
#Newest scores appended

我希望看到用户分数的变化。每次用户获得新分数时，我们都会水平追加。每次我们获得新用户时，我们都会垂直追加。假设用户每天只能获得1个分数，理想的解决方案是：

visitorID     score1      score2       score3
1             0.15        0.31
2             0.78        0.22         0.41
3             0.12 

例如，水平增长(新得分)和垂直增长(新用户)的表格

我可以在单个表上使用一系列左连接来执行类似的操作，但这只会为我提供我们运行左连接的第一个表中的访问者数据。

注意：我可以添加另一列Date，如果它使事情变得更容易，它将简单地为所有值重复相同的日期。

Answer 1

与其动态地添加列(这在这里是一个相当大的挑战)，我建议在一个列中聚集各个访问者的分数，作为单独分数的数组或字符串

以下是 BigQuery Standard SQL

sql prettyprint-override">#standardSQL
WITH `project.dataset.score_FULL` AS ( 
  SELECT 1 visitorID, 0.15 score UNION ALL
  SELECT 2, 0.78 UNION ALL
  SELECT 3, 0.12 UNION ALL
  SELECT 6, 0.90 UNION ALL
  SELECT 2, 0.22 UNION ALL
  SELECT 6, 0.65 UNION ALL
  SELECT 7, 0.61 UNION ALL
  SELECT 10, 0.24 UNION ALL
  SELECT 1, 0.31 UNION ALL
  SELECT 2, 0.41 UNION ALL
  SELECT 10, 0.12 
)
SELECT 
  visitorID,
  ARRAY_AGG(score) scores_as_array,
  STRING_AGG(CAST(score AS STRING)) scores_as_list
FROM `project.dataset.score_FULL`
GROUP BY visitorID   

输出为

Row visitorID   scores_as_array scores_as_list   
1   1           0.15            0.15,0.31    
                0.31         
2   2           0.78            0.78,0.22,0.41   
                0.22         
                0.41         
3   3           0.12            0.12     
4   6           0.9             0.9,0.65     
                0.65         
5   7           0.61            0.61     
6   10          0.24            0.24,0.12    
                0.12