Получить данные за 30 дней до каждой строки запроса
У меня есть запрос, в котором у меня есть список из ~ 20 тыс. пользователей за определенную неделю месяца, что означает, что они вошли на наш сайт.
Что мне нужно получить - для каждого из этих пользователей за последние 30 дней, если они 1. вошел в систему: определяется любыми строками, записанными в той же таблице 2. максимальное событие в 30-дневном окне, предшествующее дате в текущем предложении where
Это текущий фрагмент кода, который помогает мне сузить до ~ 20 000 пользователей за данную неделю:
select
user_id,
max(timestamp)
from table
where timestamp between '2019-02-01' and '2019-02-05'
group by 1,2;
Ожидаемый набор результатов/столбцы:
- ID пользователя,
- макс (отметка времени),
- logged_on, [если у них есть какое-либо количество строк в одной и той же таблице в течение 30 дней до их максимальной даты (отметка времени)]
- предыдущая_timestamp, [2-я самая последняя дата входа в систему в течение 30 дней до их максимальной (временной метки) даты]
Существует ли одна «максимальная» дата или максимальная дата для каждого пользователя?
@BobMcCormick - Спасибо. Все остальные столбцы в этой таблице связаны с URL/трафиком. Когда пользователь входит в систему, столбец user_id заполняется значением. Если пользователь не вошел в систему, этот столбец будет NULL. Я намерен отфильтровать строки NULL, чтобы просматривать только зарегистрированных пользователей. Для каждого пользователя не существует единой «максимальной» даты. Я применяю функцию max к метке времени, привязанной к неделе пользователей, которых я не буду профилировать и начинать с.
Я думаю, вам нужно предоставить конкретный пример нескольких строк таблицы только с интересующими столбцами. Кроме того, какой запрос вы пробовали до сих пор?
Вот столбцы таблицы с двумя образцами значений в скобках: 1. user_id (A1B2C3; NULL) 2. отметка времени (2019-02-01 10:56:35.044; 2019-02-02 09:50:34.045) 3 .url (www.page1.com; www.page2.com) 4. источник (facebook; twitter) Используя этот набор данных, я в настоящее время фильтрую данные только за одну неделю, чтобы получить список UserID. Для этого подмножества пользователей за 30 дней до одной недели данных (упомянутых выше) требуется получить: 1. количество строк 2. содержит ли исходный столбец для любой из этих строк «facebook»
Ответы 1
Я думаю, это то, что вы ищете. Не уверен, что это самый эффективный метод - возможно, оконные функции могут работать лучше, но, как упоминал Боб-Маккормик: хитрый бит будет заполнять даты, когда пользователь (ключ раздела) не был активен, чтобы запрос диапазона работал правильно.
Пример настройки данных (синтаксис Snowflake)
-- Create sample table
create temporary table user_logins (userid number, date_logged_on timestamp);
;
-- Insert some random sample data
insert overwrite into user_logins
select
uniform(1,10,random()) userid,
dateadd('minutes', uniform(1,86400,random()) * -1,current_timestamp::timestamp_ntz) date_logged_on
from table(generator(rowcount => 100))
;
Выберите заявление
-- Run select
with user_last_logins as (
select
userid,
max(date_logged_on) last_login
from user_logins
where
date_logged_on between '2019-01-01' and '2019-05-08'
group by userid
)
select
user_last_logins.userid,
max(user_last_logins.last_login) last_logged_on,
count(prior_30_each_user.userid) num_logins_prior_30,
max(prior_30_each_user.date_logged_on)
from user_last_logins
left join user_logins prior_30_each_user
on user_last_logins.userid = prior_30_each_user.userid
and prior_30_each_user.date_logged_on > dateadd('day', -30, user_last_logins.last_login) and prior_30_each_user.date_logged_on < user_last_logins.last_login
group by user_last_logins.userid
;
Спасибо @SimonD - это сработало! Оцените помощь здесь.
Было бы полезно иметь схему таблицы и несколько строк-образцов, в том числе несколько строк, которые могут проиллюстрировать, каким должен быть ответ. Одной из трудностей будет отображение пользователей, которые не вошли в систему, поскольку я думаю, что у них не будет записей для окна даты.