Выбор только first_value по дате и ID в BigQuery

avatar
mrDaft
8 августа 2021 в 17:15
488
2
0

Я пытаюсь получить только первое пользовательское событие (строку) за каждый день.

дата ID пользователя событие
2018-09-30 1 логин
2018-09-30 2 логин
2018-09-30 1 следующий
2018-09-30 1 следующий
2018-09-30 2 следующий
2018-09-29 1 логин

и моя цель - получить это.

дата ID пользователя событие
2018-09-30 1 логин
2018-09-30 2 логин
2018-09-29 1 логин

Пока остановился на этом. Но он возвращает первую дату активности пользователей. Но мне нужно только первое событие пользователя по дате.

select *, FIRST_VALUE(date) over(partition by date order by userId) AS firstValue
FROM table
дата идентификатор пользователя событие firstValue
2018-09-30 1 логин 2018-09-29
2018-09-30 2 логин 2018-09-30
2018-09-30 1 следующий 2018-09-29
2018-09-30 1 следующий 2018-09-29
2018-09-30 2 следующий 2018-09-30
2018-09-29 1 логин 2018-09-29

Итак, что я должен сделать, чтобы получить только первое появление пользователя по дням?

Источник

Ответы (2)

avatar
Mikhail Berlyant
8 августа 2021 в 17:39
2

В вашем дизайне отсутствует дополнительный столбец, определяющий порядок в течение дня

Если вы будете использовать ниже - это даст вам одно событие на пользователя в день - но порядок в течение дня, определенного/гарантированного здесь:

select *
from `project.dataset.table`
where true 
qualify row_number() over(partition by userid, date) =  1

с выходом

enter image description here

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

select *
from `project.dataset.table`
where true 
qualify row_number() over(partition by userid, date order by order_column) =  1
avatar
user16586978
8 августа 2021 в 17:43
0

Сначала сгенерируйте row_number. Если есть какой-либо другой столбец, указывающий порядок в вашем наборе данных, например. метка времени или номер заказа, пожалуйста, используйте его вместо этого. На следующем шаге сгруппируйте по нужным полям date и userid.

Чтобы определить первый элемент array_agg полный столбец и взять первую запись по offset(0). Этот подход достаточно гибкий; здесь также показано количество событий на пользователя и дату.

select date,userID , array_agg(event order by row_id limit 1)[offset(0)]
,count(1) as event_per_user_date
from(
select *, row_number() over() as row_id
from(
Select "2018-09-30" as date,    1 as userID,    "login" as event
union all select "2018-09-30",  2,  "login"
union all select "2018-09-30",  1   ,"next"
union all select "2018-09-30",  1   ,"next"
union all select "2018-09-30",  2   ,"next"
union all select "2018-09-29",  1   ,"login"
)
)
group by 1,2