sql - 如何缩放BigQuery中的Pivot？

假设，我有一天的音乐视频播放统计信息表mydataset.stats(3B行，1M用户，6K艺术家)。
简化的模式是:
UserGUID字符串，ArtistGUID字符串

我需要将数据透视/转置艺术家从行到列，所以架构将是:
UserGUID字符串，Artist1整数，Artist2整数，…Artist8000整数
与艺术家播放按各自用户计数

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?中建议使用一种方法，但是它似乎无法适应我的示例中的数字

可以针对我的示例扩展此方法吗？

最佳答案

我尝试了以下方法，最多可使用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年之前”的项目，所有费用均列为第1层，但在2016年10月之后可能会成为问题。
有关更多信息，请参见High-Compute queries中的Timing。
d 。上面的示例展示了使用BigQuery进行大规模数据转换的强大功能!我仍然认为(但是我可能错了)存储物化特征矩阵不是最好的主意

MyDataSet

sql - 如何缩放BigQuery中的Pivot？