Как произвести группировку по последовательным повторам mysql?

Question

[MegaAnimeshnik]

Всем привет, подскажите пожалуйста, кто знает.
Есть таблица вот такого содержание

spoiler

Я хочу получить данные типа
2023-02-26 21:24:01 0
2023-02-26 21:12:02 1
2023-02-26 21:06:02 0
2023-02-26 20:48:01 1
т.е. мне интересны только периоды изменения значения, их повторы не нужны.
Как составить запрос чтобы получить желаемо и возможно ли это вообще?

Comments

[Slava Rozhnev]

Версия СУБД?

[MegaAnimeshnik]

Slava Rozhnev, 8.0.32 - MySQL Community Server - GPL

[Slava Rozhnev]

MegaAnimeshnik, Выложите Вашу таблицу на https://sqlize.online , вместо картинки опубликуйте линк

Answer 1

[MegaAnimeshnik]

решил примерно так

WITH T1 as (SELECT  `status`, `date`, (LAG(`status`, 1)OVER ()) as last_status FROM `data` ORDER BY `date` DESC)
SELECT * FROM T1 where `status` <> last_status or last_status is null;

Запрос включает функцию LAG(вроде как с 8.0.22 доступна), благодаря которой получаем значение прошлой строки.
Весь результат в табличное выражение.
В выборке сравниваем текущий status с прошлым.
Спасибо rPman и Rsa97 за идеи.

По производительности хз, мне для себя, если будут проблемы, могу позволить переделать вообще всё.

Answer 2

[rPman]

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

У тебя должно быть поле, которое позволит определить однозначный порядок записей (дата у тебя не уникальна значит не подходит), например id с полем autoincrement (осторожно, некоторые базы не гарантируют что значения будут гарантированно расти).

Я бы решал эту задачу, добавлением дополнительного поля - предыдущее значение (например id на предыдущую запись), особенно это легко и логично, если данные попадают в таблицу последовательно и нет изменений типа удаления и обновления дат, т.е. всего что ведет к изменению порядка.

Если будет такое поле (id таблицы или сразу значение твоей второй колонки), то запрос, выдающий что тебе надо будет очень простым и отрабатывать очень быстро

select date,val from table where val<>prev_val order by id

p.s. иначе тебе придется на каждое значение искать его предыдущее вложенным запросом через order by limit или max/min limit что не очень быстро

select date,val from table x where val<>(select val from table y where y.date<x.date order by id desc limit 1)

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

Comments

[MegaAnimeshnik]

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

А если был бы уникальный и последовательный id, как получить предыдущие значения для сравнивания?

Answer 3

[Rsa97]

WITH `cte` AS (
  SELECT `date`, `state`,
         IFNULL(NTH_VALUE(`state`, 2) OVER `win`, -1)  AS `prev_state`
    FROM `test`
    WINDOW `win` AS (
      ORDER BY `date` DESC
      RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    )
)
SELECT *
  FROM `cte`
  WHERE `state` != `prev_state`