Получение данных из postgres с условиями 2.0

Question

Получение данных из postgres с условиями 2.0

Рейтинг: 0Ответов: 1Опубликовано: 08.03.2023

Есть две таблицы: users и user_status.

Код создания таблицы users.

CREATE TABLE users(      
    Id SERIAL,
    user_id BIGINT NOT NULL,
    user_name TEXT NOT NULL
);

Код создания таблицы user_status.

CREATE TABLE user_status(
    Id SERIAL,
    user_id BIGINT NOT NULL,
    status TEXT NOT NULL,
    date_in date DEFAULT CURRENT_DATE,
    time_in time without time zone DEFAULT CURRENT_TIME(0)
);

Нужно получить последнее вхождение каждого user'a ЗА ТЕКУЩУЮ дату.

Ожидаемый результат:

user_name  ---  status

Виктор        Статус 1
Павел         Статус 2
Екатерина     Статус 3
Светлана      Статус 2
Федор         Статус 1
Роман         Статус 2

Так же, если возможно, подскажите общие недочеты таблицы. Например, не назначены PK и FK. Поле таблицы users - user_id - уникально, и соответствует аналогичному полю в таблице user_status, но если их назначать на PK и FK, идет ошибка ввода, то есть, если один из пользователей выбирает статус, его данные не помещаются второй раз в таблицу users, так как он по сути уже там есть.

Но важнее запрос с последними вхождениями.

Всем большое спасибо! Все женщин с праздником!

sql postgresql mysql база-данных оптимизация-запросов

Источник: Stack Overflow на русском

Answer 1

▲ 0Принят

Не совсем понятен смысл таблиц у вас. Например - users. Обычно смысл такой таблицы - это список всех уникальных пользователей, поэтому непонятно, когда вы пишите про "последнее вхождение в таблицу users".

В таблице users привычнее видеть что каждый пользователь уникален, т.е. входит один раз, для их различия введен первичный ключ (PK) - id, и, как и в жизни, у них могут быть одинаковые имена. У вас же есть записи с одним и тем же user_id и name в таблице users повторяются. В чем смысл этой таблицы у вас?

Если отбросить этот момент, то, предположительно, таблица user_status содержит какие-то статусы для разных пользователей (связь по полю user_id).Исходя из того, что поле id у вас SERIAL, т.е. целое, автоинкрементируемое (т.е. не вставляется "руками" и каждый раз само увеличивается и в общем-то должно быть первичным ключом), то... Как бы напрашивается, что последнее вхождение" для любого пользователя (user_id) за текущую дату будет

select max(id), user_id 
from user_status where date_in = CURRENT_DATE 
group by user_id, date_in;

Пока запомним, что нам нужен именно максимальный ID каждого пользователя (user_id) из таблицы user_status на нужную дату (текущую, по условию задачи).

Чтобы вывести статусы для конкретных пользователей нам нужно объединить эти таблицы через JOIN, в общем виде это было бы так:

select user_status.user_id, user_name, status 
from user_status 
join users on users.user_id = user_status.user_id 
order by user_status.id;

Но нам нужны только последние статусы по каждому пользователю за текущую дату, так что добавим еще условие - максимальный id из user_status для каждого пользователя за текущую дату:

select user_status.user_id, user_name, status 
from user_status 
join users on users.user_id = user_status.user_id 
    where user_status.id IN (
        select max(id) 
        from user_status 
            where date_in=CURRENT_DATE 
        group by user_id
    )
 group by user_status.user_id, users.user_name, status order by user_status.user_id;

P.S. Я все же не понимаю вашу таблицу users, зачем несколько раз в нее записывать пользователей? Я бы сделал первичный ключ по user_id и если такой пользователь уже есть не заносил бы его повторно.

Ну и в таблице user_status явно напрашивается: первичный ключ по полю id, возможно составной индекс по user_id и date_in, но.... Мне кажется вам стоит все же почитать теорию и разобраться, какие задачи вы решаете и какие таблицы и почему создаете?

Получение данных из postgres с условиями 2.0

Ответы