2014 dxdy logo

Научный форум dxdy

Математика, Физика, Computer Science, Machine Learning, LaTeX, Механика и Техника, Химия,
Биология и Медицина, Экономика и Финансовая Математика, Гуманитарные науки




Начать новую тему Ответить на тему
 
 Выборка последних сообщений диалогов пользователей. SQL.
Сообщение08.04.2020, 22:52 


21/02/19
108
Здравствуйте. Есть таблица базы данных, в которой содержатся атрибуты: receiver as r, sender as s, sending_time as t.
Как мне из этой таблицы выбрать все неповторяющиеся по комбинации (r,s) строки. Причём если (r,s) = (s,r), то беру такую строку, для которой t больше.
В полях r и s - числа.
Сама предметная область:
Есть сообщения пользователей, для каждого из них указан id отправителя, получателя, время отправки. Мне надо для каждого диалога между двумя пользователями отобрать последнее по времени отправки сообщение.
Мой план решение:
1. Сгруппировать таблицу по (r,s), в t писать максимальное для группы время.
2. Затем разбить эту таблицу на две:
- в первой r >=s
- во второй s >=r
3. Попарно сравнивать их. Но вот дальше не знаю, как отбирать.
Уже после 2-го этапа sql-запрос выглядит довольно громоздко. Может быть есть какие-то операторы sql, упрощающие решение данной задачи?

 Профиль  
                  
 
 Re: Выборка последних сообщений диалогов пользователей. SQL.
Сообщение09.04.2020, 00:06 
Заслуженный участник


06/07/11
5627
кран.набрать.грамота
optimden в сообщении #1452930 писал(а):
1. Сгруппировать таблицу по (r,s), в t писать максимальное для группы время.
Это имеет смысл в целях оптимизации, если выполняются два условия:
1) сообщений так много, что время выполнения запроса становится некомфортным
2) количество сообщений сильно больше, чем количество пользователей (в разы как минимум). Если в среднем каждая пара пользователей пишет друг другу одно-два сообщения, то выигрыша в производительности не будет, кода будет в два раза больше, а толку - меньше.
optimden в сообщении #1452930 писал(а):
2. Затем разбить эту таблицу на две:
- в первой r >=s
- во второй s >=r
Непонятно, что бы это значило. Хорошо бы пример кода привести. Возможно, вы имели в виду не "разбить", а "объединить саму с собой", и не "в первой r >=s, - во второй s >=r", а "первую часть оставить как есть, а во второй поменять местами r и s"? Тогда похоже на правду.
optimden в сообщении #1452930 писал(а):
Но вот дальше не знаю, как отбирать.
Дальше остается самая простая часть - взять максимум.
Итого, если с оптимизацией (здесь messages - это ваша исходная таблица с данными):

Используется синтаксис SQL
WITH last_msg AS (
        SELECT sender, receiver, MAX(sending_time) AS sending_time
          FROM messages
         GROUP BY sender, receiver)
SELECT sender, receiver, MAX(sending_time) AS sending_time
  FROM (SELECT sender, receiver, sending_time
          FROM last_msg
         UNION ALL
        SELECT receiver, sender, sending_time
          FROM last_msg) t
 GROUP BY sender, receiver

Но сначала лучше выполнить без "оптимизации". Возможно, данных достаточно мало, и ощутимой разницы нет:

Используется синтаксис SQL
SELECT sender, receiver, MAX(sending_time) AS sending_time
  FROM (SELECT sender, receiver, sending_time
          FROM messages
         UNION ALL
        SELECT receiver, sender, sending_time
          FROM messages) t
 GROUP BY sender, receiver
 


optimden в сообщении #1452930 писал(а):
Может быть есть какие-то операторы sql, упрощающие решение данной задачи?
Перед тем, как задавать такой вопрос, лучше уточнять, какая у вас СУБД (включая версию). Разработчики СУБД очень творчески подходят к реализации стандартов. У разных производителей есть разные операторы, функции, синтаксические конструкции и так далее. Но конкретно в этой задаче кроме MAX и UNION ALL, которые есть во всех СУБД, ничего не нужно.

 Профиль  
                  
 
 Re: Выборка последних сообщений диалогов пользователей. SQL.
Сообщение11.04.2020, 03:45 


21/02/19
108
rockclimber в сообщении #1452953 писал(а):
Дальше остается самая простая часть - взять максимум.

Спасибо за помощь. Долго на форум не заглядывал, за это время нашел компактное решение на stackoverflow:
Используется синтаксис PostgreSQL
SELECT * FROM
        (SELECT MAX(sending_time) as time, CASE
WHEN sender_id_id = 7 THEN receiver_id_id
WHEN receiver_id_id = 7 THEN sender_id_id END
FROM forum_message WHERE (receiver_id_id = 7 OR sender_id_id = 7)
GROUP BY CASE
WHEN sender_id_id = 7 THEN receiver_id_id
WHEN receiver_id_id = 7 THEN sender_id_id END) AS T JOIN  forum_message
ON T.time = forum_message.sending_time ORDER BY T.time, forum_message.sender_id_id DESC;
 

Т.е. в зависимости от того, кем является текущий пользователь - отправителем или получателем, отбираю id получателя/отправителя, соответственно, в новое поле. Правда из-за ограничений Postgresql запрос длиннее, чем мог бы быть, поскольку требуется группировка по всем полям, участвующим в выборке и не входящим в агрегатную функцию.

 Профиль  
                  
 
 Re: Выборка последних сообщений диалогов пользователей. SQL.
Сообщение12.04.2020, 14:55 
Заслуженный участник


06/07/11
5627
кран.набрать.грамота
optimden в сообщении #1453491 писал(а):
Правда из-за ограничений Postgresql запрос длиннее, чем мог бы быть, поскольку требуется группировка по всем полям, участвующим в выборке и не входящим в агрегатную функцию.
Это не ограничение PostgreSQL, это требование стандарта, которое PostgreSQL (и все остальные СУБД) неукоснительно выполняют, за что им честь и хвала. Единственная СУБД, которая не выполняет это требование, это MySQL, но там, как я понимаю, конструкция без группировки - это такой извращенный способ реализовать аналитические функции.

 Профиль  
                  
 
 Re: Выборка последних сообщений диалогов пользователей. SQL.
Сообщение13.04.2020, 13:11 
Заслуженный участник


06/07/11
5627
кран.набрать.грамота
optimden
Сейчас перечитал ваше стартовое сообщение и ваше решение еще раз, и у меня для вас плохие новости. Вашу задачу
optimden в сообщении #1452930 писал(а):
для каждого диалога между двумя пользователями отобрать последнее по времени отправки сообщение.
ваше решение не решает. Оно находит для одного конкретного пользователя дату последнего сообщения, которое было отправлено ему или от него, причем кто именно собеседник - вообще не проверяется. То есть если Вася (ID=7) отправил сообщение Пете и получил сообщение от Коли, вы найдете просто последнее сообщение, где Вася был получатель или отправитель, хотя постановка задачи указывает на то, что диалогов было два (с Петей и с Колей), и строк в ответе тоже должно быть две.

 Профиль  
                  
 
 Re: Выборка последних сообщений диалогов пользователей. SQL.
Сообщение16.04.2020, 15:22 


21/02/19
108
rockclimber в сообщении #1454091 писал(а):
ваше решение не решает.

Получается при соединении таблиц через JOIN надо проверять, что не только время из выборки совпадает со временем из исходной таблицы, но и id пользователей для этого времени одни и те же в обеих таблицах?

 Профиль  
                  
 
 Re: Выборка последних сообщений диалогов пользователей. SQL.
Сообщение17.04.2020, 21:12 
Заслуженный участник


06/07/11
5627
кран.набрать.грамота
Чем гадать, написали бы запрос и проверили, тем более что SQL - язык довольно компактный. Предположение выглядит разумно, но ведь его разумность еще надо не потерять, пока код пишется.
Вообще, стандартная рекомендация для вопросов по SQL - готовить исходные данные в таком виде, чтобы с ними можно было работать. И приводить пример того, что хочется получить в итоге. Например, для оракла пример исходных данных будет выглядеть так:

Используется синтаксис Oracle 11 SQL
WITH source_table (sender, receiver, message_time) AS (
        SELECT 1, 2, TO_DATE('01.01.2020 20:00:00', 'dd.mm.yyyy hh24:mi:ss') FROM dual
        UNION ALL
        SELECT 2, 1, TO_DATE('01.01.2020 21:00:00', 'dd.mm.yyyy hh24:mi:ss') FROM dual)
SELECT *
  FROM source_table

Результат выполнения:

Код:
    SENDER   RECEIVER MESSAGE_TIME     
---------- ---------- -------------------
         1          2 01/01/2020 20:00:00
         2          1 01/01/2020 21:00:00

А дальше любой человек может скопировать текст и делать запросы к этой source_table напрямую у себя на компьютере.
Можно также воспользоваться услугами сервиса http://sqlfiddle.com/ и дать ссылку на пример, созданный там.
Заодно на упрощенном модельном примере можно быстро проверить свои идеи. Часто после такого необходимость в вопросе пропадает еще до того, как успеешь до конца пост на форум дописать.

 Профиль  
                  
 
 Re: Выборка последних сообщений диалогов пользователей. SQL.
Сообщение17.04.2020, 22:44 


21/02/19
108
Хорошо, в будущем учту.

 Профиль  
                  
Показать сообщения за:  Поле сортировки  
Начать новую тему Ответить на тему  [ Сообщений: 8 ] 

Модераторы: Karan, Toucan, PAV, maxal, Супермодераторы



Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей


Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете добавлять вложения

Найти:
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group