#sql #google-bigquery #gaps-and-islands #date-arithmetic
# #sql #google-bigquery #пробелы и острова #дата-арифметика
Вопрос:
Я пытаюсь перейти из следующей таблицы
| 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).
Спасибо
Ответ №1:
Ниже приведен стандартный 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
с выводом
Комментарии:
1. @ Mikhail, извините, мне пришлось внести еще одно изменение, в случае отсутствия покупки в touch мы вычисляем количество дней из установленного нами фиксированного окна. Как я могу добавить это к приведенному выше запросу?
2. вы должны перестать менять свой вопрос — и лучше попробуйте что-нибудь самостоятельно, основываясь на трех ответах, которые вы уже получили! если у вас все еще будут проблемы — отправьте новый вопрос, и мы вам поможем. Тем временем рассмотрите возможность голосования и примите ответ, в противном случае я не вижу причин продолжать отвечать: o)
3. подумайте о том, чтобы проголосовать за ответ, тем временем я могу добавить это дополнительное условие (похоже, у меня будет несколько минут на это): o) но, как я уже сказал, вы не должны менять свой вопрос, особенно после уже предоставленных ответов!
Ответ №2:
Означает, что вам нужно решение, которое разделяет строку, если есть покупка в контакте.
Используйте следующий запрос:
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
Комментарии:
1. Это поместило бы каждую «покупку» в начало каждой группы, а не в конец.
2. @Popeye Я только что добавил еще один столбец, который мне понадобится для агрегирования. Извините за изменение
3. @GMB, я четко упомянул об использовании агрегатной функции в соответствии с требованиями.
Ответ №3:
Мы могли бы подойти к этому как к проблеме пробелов и островов, когда каждый остров заканчивается покупкой. Как мы определяем группы? Подсчитав, сколько покупок у нас впереди (включая текущую строку) — так и с сортировкой по убыванию в запросе.
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
Комментарии:
1. Я только что добавил еще один столбец, который мне понадобится для агрегирования. Извините за изменение
2. @VardaanKhanted: просто добавьте
sum(amount)
кselect
предложению во внешнем запросе.
Ответ №4:
Вы можете создать значение для каждой строки, соответствующее количеству экземпляров 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;