我在一个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的
中将创建多少列
。
任何帮助都是非常感谢的。
发布于 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天的相关内容-我觉得这是你问题中的次要细节。
https://stackoverflow.com/questions/56369478
复制相似问题