sql - 如何在 BigQuery 中扩展数据透视？

Question

假设我有给定日期的音乐视频播放统计表 mydataset.stats(3B 行、100 万用户、6K 艺术家)。简化的架构是:UserGUID 字符串，ArtistGUID 字符串

我需要将艺术家从行转置到列，因此架构将是:
UserGUID 字符串、Artist1 Int、Artist2 Int、... Artist8000 Int
艺术家按各自用户播放次数

How to transpose rows to columns with large amount of the data in BigQuery/SQL? 中建议了一种方法和 How to create dummy variable columns for thousands of categories in Google BigQuery?但看起来它无法根据我的示例中的数字进行缩放

这种方法可以针对我的示例进行扩展吗？

Answer 1

我尝试了以下方法来处理多达 6000 个功能，它按预期工作。我相信它最多可以处理 10K 个功能，这是表中列数的硬性限制

第 1 步 - 按用户/艺术家汇总播放

SELECT userGUID as uid, artistGUID as aid, COUNT(1) as plays 
FROM [mydataset.stats] GROUP BY 1, 2

第 2 步 – 标准化 uid 和 aid – 因此它们是连续的数字 1、2、3、...。
我们需要这个至少有两个原因:a)使以后动态创建的 SQL 尽可能紧凑，b)拥有更多可用/友好的列名称

与第一步相结合 - 它将是:

SELECT u.uid AS uid, a.aid AS aid, plays 
FROM (
  SELECT userGUID, artistGUID, COUNT(1) AS plays 
  FROM [mydataset.stats] 
  GROUP BY 1, 2
) AS s
JOIN (
  SELECT userGUID, ROW_NUMBER() OVER() AS uid FROM [mydataset.stats] GROUP BY 1
) AS u ON u. userGUID = s.userGUID
JOIN (
  SELECT artistGUID, ROW_NUMBER() OVER() AS aid FROM [mydataset.stats] GROUP BY 1
) AS a ON a.artistGUID = s.artistGUID 

让我们将输出写入表 - mydataset.aggs

第 3 步 – 一次对 N 个功能(艺术家)使用已经建议的(在上述问题中)方法。在我的特定示例中，通过实验，我发现基本方法对于 2000 到 3000 之间的特征数量效果很好。为了安全起见，我决定一次使用 2000 个功能

下面的脚本用于动态生成查询，然后运行该查询来创建分区表

SELECT 'SELECT uid,' + 
   GROUP_CONCAT_UNQUOTED(
      'SUM(IF(aid=' + STRING(aid) + ',plays,NULL)) as a' + STRING(aid) 
   ) 
   + ' FROM [mydataset.aggs] GROUP EACH BY uid'
FROM (SELECT aid FROM [mydataset.aggs] GROUP BY aid HAVING aid > 0 and aid < 2001)

上面的查询会生成另一个查询，如下所示:

SELECT uid,SUM(IF(aid=1,plays,NULL)) a1,SUM(IF(aid=3,plays,NULL)) a3,
  SUM(IF(aid=2,plays,NULL)) a2,SUM(IF(aid=4,plays,NULL)) a4 . . .
FROM [mydataset.aggs] GROUP EACH BY uid 

这应该运行并写入 mydataset.pivot_1_2000

再执行STEP 3两次(调整HAVING aid > NNNN and aid < NNNN)，我们又得到三个表mydataset.pivot_2001_4000 , mydataset.pivot_4001_6000
正如您所看到的 - mydataset.pivot_1_2000 具有预期的架构，但针对从 1 到 2001 的辅助功能； mydataset.pivot_2001_4000 仅包含 2001 至 4000 年间的特征；等等

第 4 步 – 将所有分区数据透视表合并到最终数据透视表，其中所有功能都表示为一个表中的列

与上述步骤相同。首先我们需要生成查询然后运行它因此，最初我们将“缝合”mydataset.pivot_1_2000 和 mydataset.pivot_2001_4000。然后结果为 mydataset.pivot_4001_6000

SELECT 'SELECT x.uid uid,' + 
   GROUP_CONCAT_UNQUOTED(
      'a' + STRING(aid) 
   ) 
   + ' FROM [mydataset.pivot_1_2000] AS x
JOIN EACH [mydataset.pivot_2001_4000] AS y ON y.uid = x.uid
'
FROM (SELECT aid FROM [mydataset.aggs] GROUP BY aid HAVING aid < 4001 ORDER BY aid)

应该运行上面的输出字符串并将结果写入 mydataset.pivot_1_4000

然后我们像下面一样重复第 4 步

SELECT 'SELECT x.uid uid,' + 
   GROUP_CONCAT_UNQUOTED(
      'a' + STRING(aid) 
   ) 
   + ' FROM [mydataset.pivot_1_4000] AS x
JOIN EACH [mydataset.pivot_4001_6000] AS y ON y.uid = x.uid
'
FROM (SELECT aid FROM [mydataset.aggs] GROUP BY aid HAVING aid < 6001 ORDER BY aid)

结果写入mydataset.pivot_1_6000

结果表具有以下架构:

uid int, a1 int, a2 int, a3 int, . . . , a5999 int, a6000 int 

注意:
一个。我只尝试了这种方法最多 6000 个功能，并且它按预期工作
b。步骤 3 和 4 中第二个/主要查询的运行时间从 20 分钟到 60 分钟不等
c。重要提示:步骤 3 和 4 中的计费层从 1 到 90 不等。好消息是相应表的大小相对较小 (30-40MB)，计费字节也较小。对于“2016 年之前”的项目，一切都按一级计费，但 2016 年 10 月之后这可能会成为一个问题。
欲了解更多信息，请参阅Timing在 High-Compute queries
d。上面的示例展示了使用 BigQuery 进行大规模数据转换的威力!我仍然认为(但我可能是错的)存储物化特征矩阵不是最好的主意