Категоризация посетителей сайта, начиная с первого раза, в зависимости от условий

Не могли бы вы помочь мне с оператором sql, предпочтительно, он должен работать в большом запросе. У меня есть 3 столбца ID пользователя, дата, имя хоста. Мне нужно создать дополнительный столбец - client_type при следующем условии: когда идентификатор пользователя впервые приходит на hostname = "online-store.com", тогда с этой даты на client_type для этого конкретного идентификатора пользователя всегда будет "current_client", иначе "посетитель".

Например, на изображении (ссылка прикреплена) мы имеем userid = 1 и 4, которые стали "текущим клиентом". Пользователь 4 был просто посетителем, но после посещения hostname = "online-store.com" он всегда будет классифицирован как "текущий клиент". введите описание изображения здесь

Источник

2 ответа

Ниже для BigQuery Standard SQL

#standardSQL
SELECT 
  userid, date, hostname,
  IF(0 = COUNTIF(hostname = 'online-store.com') OVER(
    PARTITION BY userid ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ), 'visitor', 'current_client') client_type
FROM `project.dataset.table`

Вы можете проверить, поиграть с выше, используя фиктивные данные, которые вы предоставили в своем вопросе

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 userid, DATE '2018-02-01' date, 'online-store.com' hostname UNION ALL
  SELECT 2, '2018-02-01', 'other' UNION ALL
  SELECT 3, '2018-02-01', 'other' UNION ALL
  SELECT 4, '2018-02-01', 'other' UNION ALL
  SELECT 1, '2018-02-01', 'other' UNION ALL
  SELECT 1, '2018-04-07', 'other' UNION ALL
  SELECT 4, '2018-04-08', 'online-store.com' UNION ALL
  SELECT 5, '2018-04-08', 'other' UNION ALL
  SELECT 6, '2018-04-08', 'other' UNION ALL
  SELECT 4, '2018-04-08', 'other' UNION ALL
  SELECT 8, '2018-04-08', 'other' UNION ALL
  SELECT 1, '2018-07-07', 'other' UNION ALL
  SELECT 1, '2018-11-22', 'online-store.com' 
)
SELECT 
  userid, date, hostname,
  IF(0 = COUNTIF(hostname = 'online-store.com') OVER(
    PARTITION BY userid ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ), 'visitor', 'current_client') client_type
FROM `project.dataset.table`
ORDER BY date

с результатом

Row userid  date        hostname            client_type  
1   1       2018-02-01  online-store.com    current_client   
2   1       2018-02-01  other               current_client   
3   2       2018-02-01  other               visitor  
4   3       2018-02-01  other               visitor  
5   4       2018-02-01  other               visitor  
6   1       2018-04-07  other               current_client   
7   4       2018-04-08  online-store.com    current_client   
8   4       2018-04-08  other               current_client   
9   5       2018-04-08  other               visitor  
10  6       2018-04-08  other               visitor  
11  8       2018-04-08  other               visitor  
12  1       2018-07-07  other               current_client   
13  1       2018-11-22  online-store.com    current_client
Источник

Это должно быть хорошо:

#standardSQL
with userdates as (
  select userid, hostname, min(date) as mindate from `dataset.table` where hostname = 'online-store.com' group by userid, hostname
)

select u.userid, u.date, u.hostname, case when  u.date >= ud.mindate then 'current_user' else 'visitor' end as client_type
from `dataset.table` u
left outer join userdates ud on u.userid = ud.userid
order by 1, 2
Источник
Другие вопросы по тегам