Блог

Я пытаюсь перейти из следующей таблицы

 | 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$
 

По сути, я пытаюсь создать новую строку для каждого уникального пользователя в таблице каждый раз, когда в строке, разделенной запятыми, встречается «Покупка».

Кроме того, учитывайте разницу между первым касанием и первой покупкой для каждого уникального пользователя. Когда создается новая строка, мы делаем то же самое для того же пользователя, как показано в примере выше.

Из того немногого, что я собрал, мне нужно использовать смесь перекрестного соединения и строкового параметра, но я попытался использовать оператор case в string agg и не смог получить требуемый результат.

Есть ли лучший способ сделать это в SQL (Bigquery).

Спасибо

Ниже приведен стандартный SQL BigQuery

 #standardSQL
select user_id, 
  string_agg(touch order by date) path,
  date_diff(max(date), min(date), day) days,
  sum(amount) amount
from (
  select user_id, touch, date, amount,
    countif(touch = 'Purchase') over win grp
  from `project.dataset.table`
  window win as (partition by user_id order by date rows between unbounded preceding and 1 preceding)
)
group by user_id, grp    
 

если применить к образцу данных из вашего вопроса — вывод

еще одно изменение, в случае отсутствия покупки на ощупь, мы вычисляем количество дней из установленного нами фиксированного окна. Как я могу добавить это к приведенному выше запросу?

 select user_id, 
  string_agg(touch order by date) path,
  date_diff(if(countif(touch = 'Purchase') = 0, '2020-12-31', max(date)), min(date), day) days,
  sum(amount) amount
from (
  select user_id, touch, date, amount,
    countif(touch = 'Purchase') over win grp
  from `project.dataset.table`
  window win as (partition by user_id order by date rows between unbounded preceding and 1 preceding)
)
group by user_id, grp    
 

с выводом

Означает, что вам нужно решение, которое разделяет строку, если есть покупка в контакте.

Используйте следующий запрос:

 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
 

Мы могли бы подойти к этому как к проблеме пробелов и островов, когда каждый остров заканчивается покупкой. Как мы определяем группы? Подсчитав, сколько покупок у нас впереди (включая текущую строку) — так и с сортировкой по убыванию в запросе.

 select user_id, string_agg(touch order by date), 
    min(date) as first_date, max(date) as max_date,
    date_diff(max(date), min(date)) as cnt_days
from (
    select t.*,
        countif(touch = 'Purchase') over(partition by user_id order by date desc) as grp
    from mytable t
) t
group by user_id, grp
 

Вы можете создать значение для каждой строки, соответствующее количеству экземпляров where table.touch = 'Purchase' , которое затем можно использовать для группировки по:

 with r as (select row_number() over(order by t1.user_id) rid, t1.* from table t1)
select t3.user_id, group_concat(t3.touch), sum(t3.amount), date_diff(max(t3.date), min(t3.date))
from (select 
       (select sum(r1.touch = 'Purchase' AND r1.rid < r2.rid) from r r1) c1, r2.* from r r2
    ) t3 
group by t3.c1;