Я пытаюсь получить самую старую запись для каждого обновления/изменения статуса в следующей таблице.
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
Вы можете обратиться к этому сообщению и попробовать переписать свой запрос. stackoverflow.com/questions/11127461/…
Конечно, позвольте мне попытаться уточнить вопрос.
Обновлено описание проблемы, пожалуйста, проверьте сейчас @nbk
проблема возникает из-за того, что строки по определению не отсортированы, и вам нужно придать им порядок, которого у вас нет, для алгоритма, все утвержденные принадлежат друг другу, поскольку у них нет столбца, который сообщает им, какие из них принадлежат друг другу
Получил вашу точку зрения. Постоянно застреваю на одном и том же. Должен быть способ группировать строки до тех пор, пока они не изменят шаблон, в моем случае статус, когда данные упорядочены по дате.
Я открыт для изменения схемы таблицы. Любые предложения
Добавлен «id» в качестве первичного ключа в таблицу. Пожалуйста, проверьте сейчас @nnichols
вот запросы:
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". Интервал между датами в моем случае не фиксирован, он недетерминирован. Обновлен пример таблицы выше, чтобы показать это. Пожалуйста, проверьте.
второе решение почти такое же, но присоединиться по 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
.
sql возвращает именно то, что ожидается
Только до тех пор, пока вы не введете другой entity_id или у вас не будет дыры в вашей последовательности ПК - db<>fiddle
Просто используя отставание:
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
Если прогоны короткие, а таблица узкая, вы можете извлечь выгоду из отказа от объединения и возврата всех необходимых столбцов во внутреннем выборе. Стоит проверить с вашим полным набором данных.
а какой у тебя вопрос?