SQL — вывод таблицы ежедневного статуса пользователей из таблицы активности пользователей
У меня есть таблица, в которой указаны действия пользователей по их членству (активировать или деактивировать) и когда эти действия произошли. Таблица активности выглядит следующим образом:
Существует еще одна таблица под названием dim_date, которая в основном содержит одну строку для каждой даты до сегодняшнего дня, например:
Каков оптимальный способ написания SQL для возврата таблицы ежедневного статуса, в которой есть одна строка для каждого пользователя в день, показывающая, является ли его статус членства активным или неактивным? Выходная таблица должна выглядеть так:
На данный момент у меня есть:
with cte as (
select
a.user_id
,a.activity
,a.date as activity_date
,dd.date
,row_number() over (partition by a.user_id, dd.date order by a.date desc) as rn
from activity a
left join dim_date dd on a.date <= dd.date
)
select
user_id
,date
,case when a.activity = "activate" then "active" else "inactive" end as membership_status
from cte
where rn = 1
Интересно, может ли кто-нибудь предложить что-нибудь попроще? Возможно без cte/подзапроса?
Что не так с вашим запросом? Это кажется достаточно простым.
@Эрик, извини, я хотел попросить более оптимизированный запрос, а не просто более простой.
Ответы 2
Возможно, не совсем проще, но я надеюсь, что это будет понятно и легко читать с помощью скалярного подзапроса для membership_status.
select dd.date, user_id,
(
select case a.activity when 'activate' then 'active' when 'deactivate' then 'inactive' end
from activity a
where user_id = u.user_id and a.date <= dd.date
order by a.date desc limit 1
) membership_status
from dim_date dd
cross join (select distinct user_id from activity) u
order by user_id, dd.date;
«Проще» — это субъективно. Основная проблема вашего исходного запроса не в сложности, он не очень сложен. Но у него будет большой промежуточный набор результатов с большой избыточностью, чтобы row_number() мог рассчитывать только на наличие условия a.date <= dd.date.
Явное определение эффективного диапазона (начало/конец) с помощью lead() и присоединение к дате, попадающей в этот диапазон, вероятно, будет более эффективным.
Рабочий пример: https://dbfiddle.uk/8WcyWwOa
Примечание. От coalesce() до «9999-12-31» — это немного синтаксического сахара, просто чтобы заставить дату измерения оказаться ниже производного конца последнего статуса пользователя.
CREATE TABLE activities (
user_id INTEGER,
activity VARCHAR(20),
date DATE
);
INSERT INTO activities (user_id, activity, date) VALUES
(123, 'activate', '2024-06-01'),
(123, 'deactivate', '2024-06-15'),
(123, 'activate', '2024-06-20'),
(123, 'deactivate', '2024-06-30'),
(456, 'activate', '2024-06-25'),
(123, 'deactivate', '2024-07-08'),
(123, 'activate', '2024-07-10');
CREATE TABLE dim_date (
date_value DATE
);
INSERT INTO dim_date (date_value)
SELECT generate_series('2024-06-01'::DATE, '2024-07-22'::DATE, '1 day'::INTERVAL)::DATE;
with assign_end_date_to_status as
(
select a.*,
coalesce(
lead(a.date) over ( partition by a.user_id order by a.date)
- interval '1 DAY', '9999-12-31'::DATE) as status_end
from activities a
)
select ads.user_id,
dd.date_value,
case when ads.activity = 'activate' then 'active'
else 'inactive'
end as membership_status
from assign_end_date_to_status ads
inner
join dim_date dd
on dd.date_value between ads.date and ads.status_end;
Будет идеально, если вы сможете разместить таблицы DDL и образцы данных в онлайн-скрипте, например sqize.online или другом.