Фильтр по предыдущему значению из этой же таблицы в sql выборке

Question

Есть 2 таблицы, допустим:

group (id, name)

value (id, group_id, val, date)

Нужно получить все строчки из таблицы value, у которых в предшествующей строке значение val меньше чем у текущей.

Пример:

[id: 1, group_id: 1, val: 150, date: 2020-17-02]
[id: 2, group_id: 1, val: 200, date: 2020-17-03]
[id: 3, group_id: 2, val: 111, date: 2020-17-02]
[id: 4, group_id: 1, val: 500, date: 2020-17-01]

после sql запроса, должна выбраться строчка с id = 2, т.к. значение val = 150 (id=1) меньше чем val = 200 (id=2) и в id = 3 (т.к. больше нет записей с group_id = 2)

то есть, грубо говоря, нужно сделать сортировку ORDER BY date DESC, пройтись по всем строчкам, и если есть такая, у которой найдена строчка с той же group_id и датой, меньше чем в текущей строке, то вернуть в результате эту строчку

пробовал сделать примерно так:

SELECT * FROM value v1 ... AND WHERE EXISTS (SELECT * FROM value v2 WHERE v1.group_id = v2.group_id AND v1.id != v2.id AND v1.value > v2.value ORDER BY date DESC limit 1)

и это работает, если у каждой группы по 2 значения в values, а если больше, то нет вероятности, что найденная внутри EXISTS строка будет именно предшествующей перед текущей

надеюсь всё понятно объяснил, всем заранее спасибо

Answer 1

можно так попробывать:

with 
  g as (select
          lag(x.id) over(order by x.id) as id,
          lag(x.group_id) over(order by x.id) as group_id,
          lag(x.val) over(order by x.id) as val,
          lag(x.dat) over(order by x.id) as dat
        from Value x),
  v as (select * from Value v)

select v.* from v, g
  where v.group_id = g.group_id 
    and v.id != g.id 
    and v.val > g.val 
    and v.dat > g.dat
  order by v.id;

Результат:

БЛОГ НА HUSL

Фильтр по предыдущему значению из этой же таблицы в sql выборке

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