Как оконнной функцией вытащить последнее значение для предыдущего года?

Question

[Иван Мельников]

Имеется такая табличка (без поля prev_value):

*таблица представлена после сортировки OREDER BY year, updated_at
Необходимо оконной функцией заполнить поле prev_value как это показано выше. А именно, для значений с текущим годом (текщей строкой) нужно заполнить значением value из предыдущего года с максимальным updated_at.

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

last_value(value) over(order by year, updated_at rows range ...)

, но не понимаю как в range можно задать условие для year...

Comments

[Akina]

Не лечим мы по фотографиям.
Пример данных, в виде форматированного текстового кода, скриптами CREATE TABLE + INSERT INTO (или ссылку на online fiddle), и требуемый результат для именно этих данных - в студию.

PS. Нафиг тут не нужна оконная функция. Коррелированный подзапрос в списке вывода легко решает проблему.

Answer 1

[Akina]

Будет что-то вроде

SELECT *,
       ( SELECT value
         FROM tablename t2
         WHERE t1.year - 1 = t2.year
         ORDER BY created_at DESC LIMIT 1 
         ) AS prev_value
FROM tablename t1;

Comments

[Иван Мельников]

Спаибо за ответ!
Действительно, работает (https://sqlfiddle.com/postgresql/online-compiler?i...):

CREATE TABLE t (
  id SERIAL PRIMARY KEY,
  year int not null,
  updated_at timestamp not null,
  value int not null
);

INSERT INTO t(year, updated_at, value) VALUES (2022, '2022-01-01 23:11:11', 17);
INSERT INTO t(year, updated_at, value) VALUES (2022, '2024-01-01 20:11:11', 13);
INSERT INTO t(year, updated_at, value) VALUES (2023, '2024-06-02 20:11:11', 0);
INSERT INTO t(year, updated_at, value) VALUES (2023, '2024-06-02 20:11:13', 101);
INSERT INTO t(year, updated_at, value) VALUES (2023, '2024-06-02 20:11:17', 55);
INSERT INTO t(year, updated_at, value) VALUES (2024, '2024-03-02 20:11:11', 117);
INSERT INTO t(year, updated_at, value) VALUES (2024, '2024-03-02 20:11:22', 5);

SELECT t1.*,
       ( SELECT value
         FROM t t2
         WHERE t1.year - 1 = t2.year
         ORDER BY updated_at DESC LIMIT 1 
         ) AS prev_value
FROM t t1;

Однако, хочется всё-таки получить решение с помощью оконки и без подзапросов. Скорей всего оконка будет работать оптимальнее, чем коррелированный подзапрос.

[Akina]

Иван Мельников,

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

Вот этот момент мне крайне сомнителен. Если в таблице есть индекс (year, updated_at) - то показанный запрос просто полетит как на крыльях. Тогда как оконная функция мало того что будет применяться к динамическому набору, так ещё и для каждого набора отдельно сортироваться.

Но если уж очень хочется оконку, то там будет не одно окно, а два. Смотри. Тебе нужно сначала определить окно для отбора по году. Это ORDER BY year RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING. Но финально тебе нужно сортировать по updated_at - а это получается окно поверх окна. И итогово всё получится очень грустно и медленно.