Порядок сортировки при повторяющихся значениях в оконных функциях

Question

Есть набор данных следующего формата:

id, val
(0, val1) ---- equivalent val
(1, val1) ---- equivalent val
(2, val1) -- equivalent id ---- equivalent val
(2, val2) -- equivalent id
(3, val3)

Я использую запрос вида: select lag(val) over (order by id) from Table и сталкиваюсь с непредсказуемым значением оконной функции в силу повторяющегося значения в столбце сортировки.

Дублирующихся значений id может быть только 2 подряд.

Мой конечный запрос сводится к сравнению значения val текущей с val предыдущей строки, то есть что-то вроде select case when lag(val) over (order by id) = val then 'result' else 'another result' from Table

Была идея проверять значение сразу 2 строк, чтобы "перекрыть" рандомную сортировку:

select case when lag(val, 1 , null) over (order by id) = val or lag(val, 2, null) over (order by id) = val then 'result' else 'another result' from Table

Но это решение не подошло в силу того, что значения val могут повторяться и дать псевдопожительный результат (см. id = 0, id = 2), тогда как меня интересует строго предыдущая строка.

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

Answer 1

Ответ на заданный вопрос

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

Можно сортировать по не по значению, а по служебной колонке ctid. Это даст стабильный порядок строк.

Но в общем случае значения во втором столбце могут идти не по порядку. Например:

id, val
(0, val1)
(1, val1)
(2, val1)
(2, val2)
(3, val3)
(4, val3)
(4, val2) -- уменьшение val
(5, val2)

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

Алгоритм сортировки получается рекурсивным. Чтобы отсортировать две строки с id = i и определить, какая из них первая, а какая последняя, — надо сравнить их значения со значением, указанным в последней строке с id = i - 1.
Для данной задачи явная сортировка вообще не нужна, достаточно помечать, какая строка последняя, чтобы продолжать рекурсию от неё.

На больших объёмах данных рекурсия сильно неоптимальна. Как указали в комментариях, бизнес-логика кривая. Либо структура данных.

Тем не менее, технических проблем написать такой SQL нет:

WITH RECURSIVE r AS (
  SELECT
    id,
    val,
    null as result,
    true as use_later_flg
  FROM t
  WHERE id = 0
  UNION ALL
  SELECT
    t.id,
    t.val,
    case when t.val = r.val then 'result' else 'another_result' end as result,
    t.val <> r.val or count(*) over () = 1 as use_later_flg
  from
    r
    join
    t
    on r.id + 1 = t.id
    and r.use_later_flg
)
SELECT
  id,
  val,
  result
FROM r;

id	val	result
0	val1	null
1	val1	result
2	val1	result
2	val2	another_result
3	val3	another_result
4	val3	result
4	val2	another_result
5	val2	result

Предполагается, что id последовательные, без пропусков.

Вот это требование не реализовано:

Алгоритму важны только случаи равенства. Когда val предыдущей != val текущей, делать ничего не нужно

Без примера непонятно, что имеется в виду. Но полагаю, что добавить фильтры в код не будет проблемой.

Демо: https://dbfiddle.uk/StAEiHb_

Ответ с учётом комментария про даты

Следует изменить структуру данных, отслеживать и сохранять также время. Тогда получится медленно меняющееся измерение. Также, если значение не меняется, то повторять его каждый день незачем:

begin_ts, val
(01.01 00:00, val1)
(02.01 12:30, val2)
(03.01 14:10, val3)
(05.01 02:59, val2)

Тут сортировка зашита в колонку begin_ts, и никаких проблем с обработкой данных в такой структуре не будет.

БЛОГ НА HUSL

Порядок сортировки при повторяющихся значениях в оконных функциях

Ответы (1 шт):

Ответ на заданный вопрос

Ответ с учётом комментария про даты