Блог

Главная - Вопросы по программированию - Как определить членство в датах транзакций с помощью SQL?

Как определить членство в датах транзакций с помощью SQL?

#sql #date #join

#sql #Дата #Присоединиться

Вопрос:

У меня есть две очень большие таблицы в БД.

Один из них касается членства и когда пользователи были участниками:

 User|Date|Membership
1111|2020-12-01 06:00:00|False
1111|2020-12-20 18:00:00|True
1111|2020-12-30 12:00:00|False
2222|2020-12-01 06:00:00|True
2222|2020-12-20 18:00:00|False
2222|2020-12-30 12:00:00|True
...

А другой касается транзакций этих пользователей:

 User|Date|Transaction
1111|2020-12-02 06:00:00|3.00
1111|2020-12-19 18:00:00|2.00
1111|2020-12-29 12:00:00|4.00
2222|2020-12-02 06:00:00|1.00
2222|2020-12-19 18:00:00|2.00
2222|2021-01-06 12:00:00|4.00
...

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

 User|Date|Transaction|Was_Member
1111|2020-12-02 06:00:00|3.00|False
1111|2020-12-19 18:00:00|2.00|False
1111|2020-12-29 12:00:00|4.00|True
2222|2020-12-02 06:00:00|1.00|True
2222|2020-12-19 18:00:00|2.00|True
2222|2021-01-06 12:00:00|4.00|True
...

Как я могу эффективно сделать что-то подобное в SQL, где я объединяю временные диапазоны, чтобы определить членство? Подойдет любой язык SQL, просто хочу понять метод.

Ответ №1:

Вы можете объединить две таблицы на основе дат (меньше) и найти последнюю запись с помощью аналитической функции следующим образом:

 select user, date, transaction, membership as was_member from
(select t.user, t.date, t.transaction, m.membership,
       row_number() over (partition by t.user, t.date order by m.date desc) as rn
  from membership m join transactions t
    on t.user = m.user and t.date >= m.date) t
where rn = 1

Комментарии:

1. Просто писал то же самое. Должно быть t.date >= m.date нет?

2. Также может быть записан как коррелированный подзапрос или как apply/lateral. Зависит от размера набора и индексов относительно того, является ли он более производительным

3. Да, в соответствии с требованием. Я написал код, основанный на моем понимании из примеров данных. OP может изменить некоторые условия в соответствии с требованиями.

Ответ №2:

 SELECT *,
    Was_member =
    (SELECT TOP 1 membership
    FROM membership m
    WHERE m.user = t.user
        AND t.date <= m.date)
FROM transaction t;

Для этого требуется хорошая индексация и небольшой набор транзакций, чтобы хорошо работать.

Это также можно сделать с помощью решения с номером строки, которое может быть более эффективным для больших наборов или плохих индексов.

Ответ №3:

Возможно, вам покажется это проще всего с помощью left join :

 select t.*, m.was_member
from transactions t join
     (select m.*, lead(date) over (partition by user order by date) as next_date
      from members m
     ) m
     on t.user = m.user and
        t.date >= m.date and
        (t.date < m.next_date or m.next_date is null);

Для повышения производительности вам нужен индекс members(user, date) .

Могут быть более эффективные методы, в зависимости от того, что вы подразумеваете под «очень большими таблицами», как структурированы данные и какую базу данных вы используете.