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

#sql #postgresql

Вопрос:

У меня есть таблица со столбцами:

 id (PK),
low_value,
high_value

Идентификатор и значение high_value заполняются в этой таблице, а значение low_value должно быть заполнено с использованием заполненных данных.
Low_value должно быть предыдущим упорядоченным значением high_value в таблице.

Пример — данные таблицы перед обновлением low_value:

 id     | low_value      | high_value
---------------------------------------
1      |                | 1000
2      |                | 1000  
3      |                | 2000  
4      |                | 5000  
5      |                | 7000  
6      |                | 10000 
7      |                | 10000 
8      |                | 20000

данные таблицы после обновления low_value должны быть:

 id     | low_value      | high_value
---------------------------------------
1      |    0           | 1000
2      |    0           | 1000  
3      |    1000        | 2000  
4      |    2000        | 5000  
5      |    5000        | 7000  
6      |    7000        | 10000 
7      |    7000        | 10000 
8      |    10000       | 20000

Я подумываю о присвоении ранга (с помощью оконной функции rank()) каждой строке на основе значения high_value,
поэтому строки с одинаковым значением high_value будут иметь одинаковый ранг, а затем выберите значение с рангом меньше текущего ранга для обновляемой строки.
Но я не в состоянии построить sql. Может ли кто-нибудь помочь с sql с ранжированием строк или без него ?

Ответ №1:

Хммм … немного странно, что есть дубликаты. Предполагая, что значения упорядочены, вы можете использовать:

 update t
    set low_value = coalesce(prev_high_value, 0)
    from (select high_value,
                 lag(high_value) over (order by high_value) as prev_high_value
          from t
          group by high_value
         ) tt
    where tt.high_value = t.high_value

1. Спасибо за публикацию, но это не работает, для первых 10000 значение prev_high_value = 7000, но для следующих 7000 это само по себе 7000.

2. @user10916892 . . . Как написано, произошла опечатка, и она не должна была запускаться. Однако возможно ли, что вы сохраняете значение в виде строки, а не в виде числа? Если это так, вам следует исправить данные . Не храните числа в виде строк!

Ответ №2:

Поскольку у вас есть дубликаты high_value s в последовательных id s LAG() , не всегда будет возвращено правильное значение.
Для каждой строки вы должны получить high_value строку с максимальным идентификатором with , отличным high_value от текущей строки:

 WITH cte AS (
  SELECT *, SUM(flag) OVER (ORDER BY id) grp
  FROM (
    SELECT *, 
           LAG(high_value, 1, 0) OVER (ORDER BY id) prev_high_value,
           (high_value <> LAG(high_value, 1, 0) OVER (ORDER BY id))::int flag
    FROM tablename
  ) t  
)
UPDATE tablename t
SET low_value = new_value
FROM (
  SELECT *, FIRST_VALUE(prev_high_value) OVER (PARTITION BY grp ORDER BY flag DESC) new_value
  FROM cte
) c
WHERE t.id = c.id

Смотрите демонстрацию.