首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >需要帮助来连接两个表,并使用BigQuery SQL以独特的方式将它们卷起来。

需要帮助来连接两个表,并使用BigQuery SQL以独特的方式将它们卷起来。
EN

Stack Overflow用户
提问于 2019-05-30 06:46:51
回答 1查看 105关注 0票数 0

我在一个BigQuery数据集中有两个不同粒度的表。我需要使用BigQuery SQL将这两个表连接起来,使第二个表其中一列中的值成为最后一个表中的列。

表1- tb1如下所示

user_id     event_date
A          2019-02-01
B          2019-02-10
C          2019-01-15

表2- tb2如下所示

user_id    activity_id   activity_date
A          1             2019-01-01
A          1             2019-02-05
A          2             2019-01-15
B          2             2019-02-02
B          3             2019-02-01
C          1             2019-01-02

我正在尝试编写一个SQL查询来创建最终的表,该表告诉我们user_id的每个活动的记录数,其中activity_date在该user_id的(event_date - 90天)内,即activity_date在event_date之前的90天内。因此,在本例中,输出将如下所示

user_id   event_date  act_1   act_2    act_3 
A         2019-02-01   1        1        0
B         2019-02-10   0        2        1
C         2019-01-15   1        0        0

act_1列对应于activity_id=1,依此类推。

还有一些额外的复杂情况,如:

表2中不同activity_ids的

    1. The数量可以随时间变化。因此,我事先不知道在输出table.

中将创建多少列

    1. I不能在python中做到这一点,但必须在BQ中完成。这是因为实际的表2非常大(42TB,有310亿行),将它从BQ拉到另一个GCS产品中以运行python可能是cumbersome.

任何帮助都是非常感谢的。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-05-30 09:03:18

下面是BigQuery标准SQL,只是为了向您演示旋转数据的方法

如果您预先知道您有多少个不同的activity_id,并且它们的数量很少-例如三个-就像您的示例中那样-那么您就会像下面这样简单地执行操作

#standardSQL
SELECT 
  user_id,
  event_date,
  COUNTIF(activity_id = 1) act_1,
  COUNTIF(activity_id = 2) act_2,
  COUNTIF(activity_id = 3) act_3
FROM `project.dataset.table1` t1
JOIN `project.dataset.table2` t2
USING(user_id)
GROUP BY user_id, event_date
ORDER BY user_id, event_date   

如果要应用于您的问题中的样本数据-结果将为

Row user_id event_date  act_1   act_2   act_3    
1   A       2019-02-01  2       1       0    
2   B       2019-02-10  0       1       1    
3   C       2019-01-15  1       0       0      

但正如你所说的

表2中的distinct activity_ids的数量可以随时间变化。因此,我事先不知道输出表

中将创建多少列

因此,您需要动态生成上述查询-以下是此类查询示例

#standardSQL
WITH activities AS (
  SELECT DISTINCT activity_id 
  FROM `project.dataset.table2`
), generate_query AS (
  SELECT CONCAT(
    'SELECT user_id, event_date',
    STRING_AGG(CONCAT(',COUNTIF(activity_id = ', CAST(activity_id AS STRING), ') act_', CAST(activity_id AS STRING)), ''),
    ' FROM `project.dataset.table1` t1 JOIN `project.dataset.table2` t2 USING(user_id) GROUP BY user_id, event_date ORDER BY user_id, event_date'
  ) AS query
  FROM activities
)
SELECT query FROM generate_query 

同样,如果要应用于您的样本数据-结果将是

SELECT user_id, event_date,COUNTIF(activity_id = 1) act_1,COUNTIF(activity_id = 2) act_2,COUNTIF(activity_id = 3) act_3 FROM `project.dataset.table1` t1 JOIN `project.dataset.table2` t2 USING(user_id) GROUP BY user_id, event_date ORDER BY user_id, event_date   

如果仔细观察上面的结果-您可以看到-它正是我们最初手动编写的查询-但现在它是为我们生成的-并且无论您有多少不同的activity_id (显然对列数的限制仍然适用),它都会产生所需的查询

因此,现在您只需要从上面的结果中复制查询的文本,然后简单地运行它-这将产生所需的结果

Row user_id event_date  act_1   act_2   act_3    
1   A       2019-02-01  2       1       0    
2   B       2019-02-10  0       1       1    
3   C       2019-01-15  1       0       0      

正如您所看到的,这是两个步骤的过程-但您可以使用您选择的客户端编写脚本

注意:我专注于问题的实质,根本没有涉及90天的相关内容-我觉得这是你问题中的次要细节。

票数 4
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56369478

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档