Выборка последних сообщений диалогов пользователей. SQL.

[optimden]

Здравствуйте. Есть таблица базы данных, в которой содержатся атрибуты: 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, упрощающие решение данной задачи?

[rockclimber]

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, которые есть во всех СУБД, ничего не нужно.

[optimden]

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 запрос длиннее, чем мог бы быть, поскольку требуется группировка по всем полям, участвующим в выборке и не входящим в агрегатную функцию.

[rockclimber]

optimden в сообщении #1453491 писал(а):

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

Это не ограничение PostgreSQL, это требование стандарта, которое PostgreSQL (и все остальные СУБД) неукоснительно выполняют, за что им честь и хвала. Единственная СУБД, которая не выполняет это требование, это MySQL, но там, как я понимаю, конструкция без группировки - это такой извращенный способ реализовать аналитические функции.

[rockclimber]

optimden
Сейчас перечитал ваше стартовое сообщение и ваше решение еще раз, и у меня для вас плохие новости. Вашу задачу

optimden в сообщении #1452930 писал(а):

для каждого диалога между двумя пользователями отобрать последнее по времени отправки сообщение.

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

[optimden]

rockclimber в сообщении #1454091 писал(а):

ваше решение не решает.

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

[rockclimber]

Чем гадать, написали бы запрос и проверили, тем более что 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/ и дать ссылку на пример, созданный там.
Заодно на упрощенном модельном примере можно быстро проверить свои идеи. Часто после такого необходимость в вопросе пропадает еще до того, как успеешь до конца пост на форум дописать.

[optimden]

Хорошо, в будущем учту.