将第一行与所有其他行合并|大查询

9nvpjoqh 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(311)

我正在为顾客寻找所有可能的购买途径。我目前呈现数据的方式如下所示：

email           purchase_order  sku      first_order  second_order  third_order  
abc@email.com   1               PS2525   PS2525       null          null         
abc@email.com   2               PS2525   PS2525       PS2525        null         
abc@email.com   2               LG1515   PS2525       LG1515        null         
abc@email.com   3               PS2525   PS2525       null          PS2525       
abc@email.com   3               RG7578   PS2525       null          RG7578

但我要找的是每次购买的路线。我希望我的数据如下所示：

email           first_order  second_order  third_order  
abc@email.com   PS2525       PS2525        PS2525       
abc@email.com   PS2525       PS2525        RG7578       
abc@email.com   PS2525       LG1515        PS2525       
abc@email.com   PS2525       LG1515        RG7578

这显示了客户在第一次订购后所走的每一条路线。有没有办法创建这种类型的表？我很难想办法让这一切发生。

sql google-bigquery

来源：https://stackoverflow.com/questions/62818802/combine-first-row-with-all-other-rows-big-query

2条答案

按热度按时间

63lcw9qa1#

下面是bigquery标准sql


# standardSQL

SELECT email, o1.sku AS first_order, o2.sku AS second_order, o3.sku AS third_order
FROM `project.dataset.table` o1
LEFT JOIN `project.dataset.table` o2
USING(email) 
LEFT JOIN `project.dataset.table` o3
USING(email) 
WHERE o1.purchase_order = 1
AND o2.purchase_order = 2
AND o3.purchase_order = 3

如果应用于问题的样本数据-输出为

Row email           first_order second_order    third_order  
1   abc@email.com   PS2525      PS2525          PS2525   
2   abc@email.com   PS2525      PS2525          RG7578   
3   abc@email.com   PS2525      LG1515          PS2525   
4   abc@email.com   PS2525      LG1515          RG7578

赞(0）回复(0）举报 2021-07-24

brccelvz2#

看起来您需要聚合：

select email, sku, max(first_order), max(second_order), max(third_order)
from t
group by email, sku;

赞(0）回复(0）举报 2021-07-24

我来回答

将第一行与所有其他行合并|大查询

2条答案

相关问题

热门标签

最新问答