Выберите самую старую запись определенной группы, пока она не изменит шаблон, в SQL

Я пытаюсь получить самую старую запись для каждого обновления/изменения статуса в следующей таблице.

Таблица (status_updates):

идентификатор entity_id положение дел дата 7 2 Одобренный 2022-02-10 6 2 Одобренный 2022-02-05 5 2 Одобренный 2022-02-04 4 2 На удерживании 2022-02-04 3 2 На удерживании 2022-02-03 2 2 Одобренный 2022-02-02 1 2 Одобренный 2022-02-01

Нужен результат:

идентификатор entity_id положение дел дата 5 2 Одобренный 2022-02-04 3 2 На удерживании 2022-02-03 1 2 Одобренный 2022-02-01

Пытался :

select
    `status`,
    `created_at`
from
    `status_updates`
left join
    (select
        `id`,
        row_number() over (partition by status_updates.entity_id, status_updates.status order by status_updates.created_at asc) as sequence
    from
        `status_updates`)
    as `oldest_history`
    on
        `oldest_history`.`id` = `shipper_credit_histories`.`id`
where `sequence` = 1

Достигнутый результат:

идентификатор entity_id положение дел дата 3 2 На удерживании 2022-02-03 1 2 Одобренный 2022-02-01

а какой у тебя вопрос?

nbk 13.02.2023 23:21

Вы можете обратиться к этому сообщению и попробовать переписать свой запрос. stackoverflow.com/questions/11127461/…

Nitin88 13.02.2023 23:26

Конечно, позвольте мне попытаться уточнить вопрос.

Tanmay 14.02.2023 00:55

Обновлено описание проблемы, пожалуйста, проверьте сейчас @nbk

Tanmay 14.02.2023 00:58

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

nbk 14.02.2023 01:06

Получил вашу точку зрения. Постоянно застреваю на одном и том же. Должен быть способ группировать строки до тех пор, пока они не изменят шаблон, в моем случае статус, когда данные упорядочены по дате.

Tanmay 14.02.2023 01:13

Я открыт для изменения схемы таблицы. Любые предложения

Tanmay 14.02.2023 01:25

Добавлен «id» в качестве первичного ключа в таблицу. Пожалуйста, проверьте сейчас @nnichols

Tanmay 14.02.2023 01:43
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
8
100
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

вот запросы:

create table status_updates
(entity_id integer,
status varchar(32),
date date
);

insert into status_updates values (2, 'Approved', '2022-02-05');
insert into status_updates values (2, 'Approved', '2022-02-04');
insert into status_updates values (2, 'On Hold', '2022-02-04');
insert into status_updates values (2, 'On Hold', '2022-02-03');
insert into status_updates values (2, 'Approved', '2022-02-02');
insert into status_updates values (2, 'Approved', '2022-02-01');

select b.*
from status_updates a
right join status_updates b 
on a.status=b.status and a.date=(b.date - interval 1 day) 
where a.entity_id is null;

или этот запрос (если вы предпочитаете левое соединение)

select a.*
from status_updates a
left join status_updates b
on a.status=b.status and a.date=(b.date + interval 1 day)
where b.entity_id is null;

в обоих вы увидите ожидаемый результат

Хорошее предложение @"Alex G". Интервал между датами в моем случае не фиксирован, он недетерминирован. Обновлен пример таблицы выше, чтобы показать это. Пожалуйста, проверьте.

Tanmay 14.02.2023 01:02

второе решение почти такое же, но присоединиться по id вместо даты

create table status_updates
(id integer,
entity_id integer,
 status varchar(32),
 date date
);

insert into status_updates values (7, 2, 'Approved', '2022-02-10');
insert into status_updates values (6, 2, 'Approved', '2022-02-05');
insert into status_updates values (5, 2, 'Approved', '2022-02-04');
insert into status_updates values (4, 2, 'On Hold', '2022-02-04');
insert into status_updates values (3, 2, 'On Hold', '2022-02-03');
insert into status_updates values (2, 2, 'Approved', '2022-02-02');
insert into status_updates values (1, 2, 'Approved', '2022-02-01');

select a.*
from status_updates a
         left join status_updates b
                   on a.status=b.status and a.id=b.id + 1
where b.entity_id is null;

результат такой же, как вы ожидали

К сожалению, вы не можете полагаться на то, что автоинкрементный PK будет непрерывным, поскольку существует ряд ситуаций, которые могут привести к пробелам. Вы можете использовать вариант этого с другим левым соединением, чтобы проверить значения между a и b.

nnichols 14.02.2023 09:35

sql возвращает именно то, что ожидается

Alex G 15.02.2023 13:22

Только до тех пор, пока вы не введете другой entity_id или у вас не будет дыры в вашей последовательности ПК - db<>fiddle

nnichols 15.02.2023 13:30
Ответ принят как подходящий

Просто используя отставание:

select s.*
from (
    select id, status<>coalesce(lag(status) over (partition by entity_id order by id),'') status_change
    from status_updates
) ids
join status_updates s using (id)
where status_change

Если прогоны короткие, а таблица узкая, вы можете извлечь выгоду из отказа от объединения и возврата всех необходимых столбцов во внутреннем выборе. Стоит проверить с вашим полным набором данных.

nnichols 14.02.2023 11:24

Другие вопросы по теме