时间戳

首页 > 解决方案 > 如何将 Bigquery 中的 Cross Join 和 String Agg 与日期时间差结合起来

sql - 如何将 Bigquery 中的 Cross Join 和 String Agg 与日期时间差结合起来

问题描述

我正在尝试从下表中走

| user_id | touch     | Date       | Purchase Amount
| 1       | Impression| 2020-09-12 |0
| 1       | Impression| 2020-10-12 |0
| 1       | Purchase  | 2020-10-13 |125$
| 1       | Email     | 2020-10-14 |0
| 1       | Impression| 2020-10-15 |0
| 1       | Purchase  | 2020-10-30 |122
| 2       | Impression| 2020-10-15 |0
| 2       | Impression| 2020-10-16 |0
| 2       | Email     | 2020-10-17 |0

到

| user_id | path                           | Number of days between First  Touch and Purchase | Purchase Amount
| 1       | Impression,Impression,Purchase | 2020-10-13(Purchase) - 2020-09-12 (Impression) |125$
| 1       |  Email,Impression, Purchase    | 2020-10-30(Purchase) - 2020-10-14(Email) | 122$
| 2       | Impression, Impression, Email  | 2020-12-31 (Fixed date) - 2020-10-15(Impression) | 0$

本质上，每次在逗号分隔的字符串中遇到“购买”时，我都会尝试为表中的每个唯一用户创建一个新行。

此外，计算每个唯一用户的第一次接触和首次购买之间的差异。创建新行时，我们对同一用户执行相同操作，如上例所示。

从我收集的少量数据来看，我需要混合使用交叉连接和字符串 agg，但我尝试在字符串 agg 中使用 case 语句，但无法获得所需的结果。

有没有更好的方法在 SQL (Bigquery) 中做到这一点。

谢谢

标签： sqlgoogle-bigquerygaps-and-islandsdate-arithmetic

解决方案

意味着如果有购买联系，您需要划分行的解决方案。

使用以下查询：

Select user_id,
       Aggregation function according to your requirement,
       Sum(purchase_amount)
  From
(Select t.*,
       Sum(case when touch = 'Purchase' then 1 else 0 end) over (partition by user_id order by date) as sm
  From t) t
Group by user_id, sm

推荐阅读