Как получить уникальные значения для сравнения двух столбцов одной и той же таблицы?

#sql #django #sqlite #django-queryset

#sql #django #sqlite #django-набор запросов

Вопрос:

В настоящее время я работаю над проектом Django с использованием SQLite, в котором я создаю окно чата сообщений и хочу получить самое последнее сообщение вошедшего в систему пользователя, отправлено оно или получено.

 id   receiver_id      sender_id    message_content   created_at
1    1                 2            some text         2020-08-11 13:29:47.342944
3    3                 2            some text         2020-08-11 13:44:55.499638
4    2                 1            some text         2020-08-11 14:20:55.499638
5    1                 2            some text         2020-08-12 05:06:05.497500
6    2                 5            some text         2020-08-12 10:39:31.234082
7    4                 1            some text         2020-08-14 13:25:19.357876

После использования приведенного ниже SQL-запроса.

 SELECT max(created_at), * 
FROM hireo_messages 
WHERE receiver_id=2 or sender_id=2 
GROUP BY receiver_id, sender_id 
ORDER BY created_at DESC

Я получил следующий результат.

 id      receiver_id   sender_id     message_content   created_at
6       2             5             some text         2020-08-12 10:39:31.234082
5       1             2             some text         2020-08-12 05:06:05.497500
4       2             1             some text         2020-08-11 14:20:55.499638
3       3             2             some text         2020-08-11 13:44:55.499638

И, как вы можете видеть, id 5, 4 оба общаются друг с другом. Итак, я хочу получить все записи, кроме id 4, потому что самый последний чат между двумя пользователями находится в id 5. Это та же концепция, что используется в Facebook Messenger dashboard.
пожалуйста, подскажите мне, решить ли это с помощью запроса или использовать любой другой способ. Заранее спасибо за вашу помощь!

1. Если сообщение id автоматически увеличивается или какой-либо эквивалент, вам не нужно беспокоиться о order вводе данных, id по определению задает порядок. SELECT max(id), * FROM hireo_messages WHERE receiver_id=2 or sender_id=2 GROUP BY receiver_id, sender_id; должно быть достаточно.

Ответ №1:

Вы можете использовать функцию window MAX() :

 select id, receiver_id, sender_id, message_content, created_at 
from (
  select *,
    max(created_at) over (partition by min(receiver_id, sender_id), max(receiver_id, sender_id)) last_date
  from hireo_messages
  where 2 in (receiver_id, sender_id) 
)
where created_at = last_date
order by id desc

Если ваша версия SQLite не поддерживает оконные функции, вы можете использовать NOT EXISTS :

 select h.* from hireo_messages h
where 2 in (receiver_id, sender_id)
and not exists (
  select 1 from hireo_messages
  where min(receiver_id, sender_id) = min(h.receiver_id, h.sender_id)
    and max(receiver_id, sender_id) = max(h.receiver_id, h.sender_id)
  and created_at > h.created_at
)  
order by h.id desc

Смотрите демонстрацию.
Результаты:

 | id  | receiver_id | sender_id | message_content | created_at                 |
| --- | ----------- | --------- | --------------- | -------------------------- |
| 6   | 2           | 5         | some text       | 2020-08-12 10:39:31.234082 |
| 5   | 1           | 2         | some text       | 2020-08-12 05:06:05.497500 |
| 3   | 3           | 2         | some text       | 2020-08-11 13:44:55.499638 |

Ответ №2:

Я думаю, вы хотите:

 SELECT LEAST(receiver_id, sender_id), GREATEST(receiver_id, sender_id), MAX(created_at)
FROM hireo_messages
WHERE 2 IN (receiver_id, sender_id)
GROUP BY LEAST(receiver_id, sender_id), GREATEST(receiver_id, sender_id)
ORDER BY MAX(created_at) DESC;

Если вам нужна полная строка, используйте оконные функции:

 SELECT m.*
FROM (SELECT m.*,
             ROW_NUMBER() OVER (PARTITION BY LEAST(receiver_id, sender_id), GREATEST(receiver_id, sender_id) ORDER BY created_at DESC) as seqnum
      FROM hireo_messages m
      WHERE 2 IN (receiver_id, sender_id)
     ) m
WHERE seqnum = 1
ORDER BY MAX(created_at) DESC;