Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Добрый день.

Неделю решаю(оптимизирую) задачку.
Есть текущая строка в ней значение Х.
Есть предыдущая строка в ней значение Y.
Строки должны вычисляться последовательно (сортировка по времени). Это принципиально!

Нужно последовательно вычислить / добавить в каждую строку в поле Y значение Z
Z=X*0.2+Y*0.8
то есть в текущую строку в поле Y добавить:
значение Х(текущая строка)*0,2+Y(предыдущая строка)*0,8

То есть текущее знаяение зависит от предыдущего и если порядок строк изменяется/сбивается, то вычисления неверные!
Пример в виде фото прилагаю.

Вроде бы просто. Через циклы (plpgsql) решается вообще без проблем.
Но время получается около 0,16 сек.
Если вычислять при УЖЕ ЗАПОЛНЕННЫХ полях Y, то оконная функция это делает за 0,0008 сек, то есть в 200 !!! раз быстрее.

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

Может кто уже знает как это реализовать.
.

К сообщению приложен файл. Размер - 13Kb


Сообщение было отредактировано: 26 июн 20, 10:55
26 июн 20, 10:50    [22157663]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
Guzya
Member

Откуда:
Сообщений: 818
Смотрите функции LAG и LEAD.
26 июн 20, 12:21    [22157716]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Guzya,

Я в курсе данных функций. Одна берет предыдущую запись, вторая - последующую. Записи в таблицу не идут.
Проверял.
Попробую еще раз.
.
26 июн 20, 13:04    [22157758]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Сработало.
Немного модифицировал и получилось:

WITH with_z AS
	(SELECT дата_время AS дата, 
	Х*0.2+0.8*(lag(Y, 1) OVER (PARTITION BY дата_время ORDER BY дата_время)) AS Y_сейчас
		FROM таблица ORDER BY дата_время OFFSET 1) 
	UPDATE таблица (та же) AS tab_now 
	SET Y = Y_сейчас FROM with_z 
	WHERE дата =tab_now.дата_время;


.
Текущий код работает 0.00175 сек,
против 0,16 сек (при работе через циклы For .. Loop ... End Loop;)
Это в 91 раз быстрее. Получил что и хотел.
.
27 июн 20, 10:08    [22158124]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
.
Код выше на следующий день оказался не рабочим !?! .
.
В общем, только через цикл, с использованием переменной типа record + индекс на "время" выдают 0.014 сек.
Без использования индекса код работает около 0,030 сек (в 2 раза дольше).
Обычная выборка того же количества строк (без обновления данных) с применением индекса тратит 0,008 сек. Соответственно, если данные еще нужно записать/обновить, то и получаем 0,008*2=0,016 сек.
.
Минус оконных функций в моем случае - они ссылаются на пустое место, пока данные не перезаписаны (пока предыдущая строка не обновилась). То есть из БД делается выборка ВСЕХ строк, затем сортировка, затем идет сам расчет, и итоговый результат выдается/записывается в БД. Соответственно, если строка ссылается на предыдущую строку в памяти, то там будет ноль, пока данные не обновятся.
А рекурсивные функции вообще подвешивают систему и говорят, что место закончилось, хотя может что то делаю и не так.
.
В общем, оконные функции хороши когда данные уже имеются в полях, а вот если нужно высчитать пустые поля и вставить эти значения в БД, то они бесполезны.
.

Сообщение было отредактировано: 30 июн 20, 09:57
30 июн 20, 09:52    [22159464]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
Flashpoke
Member

Откуда:
Сообщений: 31
Можно сделать рекурсией, поскольку у вас формула рекуррентная.

Предположим, есть таблица:
CREATE TABLE newtable (
	id bigserial NOT NULL,
	x int8 NOT NULL,
	y numeric NULL
);

В ней id задаёт порядок, x содержит значения X, содержимое поля y не имеет значения.
У вас вместо id порядок будет задаваться полем типа timestamp.

"id""x"
15
24
36
47
59
611
712
88
96

WITH RECURSIVE r AS (
	SELECT
		1 AS rownum,
		2::numeric AS calc_y
	UNION ALL
	SELECT
		rownum+1 AS rownum,
		x*0.2+calc_y*0.8 AS calc_y  
	FROM r
	JOIN t ON r.rownum = t.n
	WHERE rownum <= cnt
),
t AS (
	SELECT 
		id, x, ROW_NUMBER() OVER (ORDER BY id) AS n, count(*) over() AS cnt
	FROM newtable
)
SELECT id, x, calc_y
FROM t
JOIN r ON r.rownum-1 = t.n;

Это запрос подсчитает все Y. Вместо финального SELECT можно сделать UPDATE и проапдейтить сразу все нужные строки в newtable нужными значениями Y.

Я не совсем понял, откуда должно браться стартовое значение Y, поэтому просто написал 2::numeric.
Вместо этого его можно сеттить параметром.

Или же брать из нулевой строки в t:
"id""x""y"
002
15
24
36
47
59
611
712
88
96

сделав сдвиг:
WITH RECURSIVE r AS (
	SELECT
		1 AS rownum,
		(SELECT y FROM t ORDER BY n LIMIT 1) AS calc_y
	UNION ALL
	SELECT
		rownum+1 AS rownum,
		x*0.2+calc_y*0.8 AS calc_y  
	FROM r
	JOIN t ON r.rownum = t.n
	WHERE rownum <= cnt
),
t AS (
	SELECT 
		id, x, y, ROW_NUMBER() OVER (ORDER BY id) - 1 AS n, count(*) over() AS cnt
	FROM newtable
)
SELECT id, x, calc_y
FROM t
JOIN r ON r.rownum-1 = t.n;
4 июл 20, 18:34    [22162113]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Flashpoke,

Спасибо. Понял. Проверяю.
9 июл 20, 14:28    [22164664]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Flashpoke,

Мозг закипает от данных закольцованных вложений. Но обязательно протестирую ваш код в работе.

Сообщение было отредактировано: 9 июл 20, 15:22
9 июл 20, 15:24    [22164685]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Flashpoke,

На удивление, код рабочий, хотя до сих пор никак не могу понять его.
.
postgres=# 
postgres=# WITH RECURSIVE r AS (
postgres(#  SELECT 1 AS rownum,
postgres(#  (SELECT y FROM t ORDER BY n LIMIT 1) AS calc_y
postgres(#  UNION ALL
postgres(#  SELECT
postgres(#   rownum+1 AS rownum,
postgres(#   x*0.2+calc_y*0.8 AS calc_y  
postgres(#  FROM r
postgres(#  JOIN t ON r.rownum = t.n
postgres(#  WHERE rownum <= cnt
postgres(# ),
postgres-# t AS (
postgres(#  SELECT 
postgres(#   id, x, y, ROW_NUMBER() OVER (ORDER BY id) - 1 AS n, count(*) over() AS cnt
postgres(#  FROM newtable
postgres(# )
postgres-# SELECT id, x, calc_y
postgres-# FROM t
postgres-# JOIN r ON r.rownum-1 = t.n;
 id | x  |   calc_y    
----+----+-------------
  0 |  0 |           2
  1 |  5 |         2.6
  2 |  4 |        2.88
  3 |  6 |       3.504
  4 |  7 |      4.2032
  5 |  9 |     5.16256
  6 | 11 |    6.330048
  7 | 12 |   7.4640384
  8 |  8 |  7.57123072
  9 |  6 | 7.256984576
(10 строк)

postgres=# 


Соответственно проверить не могу.

К сообщению приложен файл. Размер - 137Kb
9 июл 20, 19:22    [22164817]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Flashpoke,

В книжках пишут, что нельзя ссылаться на переменные, которые будут созданы позже.
В вашем коде получается, что рекурсивный запрос ссылается на выборку "t", которая появляется позже.
Теоретически - нарушение последовательности и книжных инструкций. практически - все работает.

В общем, я в шоке, пытаюсь переварить информацию.

Спасибо, Flashpoke.
.
9 июл 20, 20:01    [22164829]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
Flashpoke
Member

Откуда:
Сообщений: 31
О-О-О
В вашем коде получается, что рекурсивный запрос ссылается на выборку "t", которая появляется позже.

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

В любом случае, PostgreSQL сначала строит граф зависимостей между CTE и только потом идёт по нему от начала до конца.
Иногда бывает удобно сделать мешанину из разных CTE, не только с SELECT, а и с INSERT...RETURNING или DELETE...RETURNING.
9 июл 20, 23:21    [22164934]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Flashpoke,

Этот как раз тот случай, когда на изучение не жалко своего времени.
Такие ситуации в будущем серьёзно помогают.
.
Очень необычный подход, в котором много всего, что ты вроде бы знаешь, но вот воедино связать не думал.
В общем, мучаю код, понимаю примерно как работает, но при попытке написать что то похожее - выходят ошибки. Но в любом случае, разберусь и сравню, стоили ли это тех усилий, которые затрачены на его понимание(думаю дня два уйдёт).
.
Для сведений, через стандартный For .. удалось выполнять код за 0,0067 сек (против 0.030 сек) , просто избавившись от OFFSET (иногда без него ну никак).
Очень надеюсь, что по вашему примеру код будет работать хотя бы за 0,0061 сек ;-)
.
10 июл 20, 07:31    [22164988]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
Разобрался и добрался до тестов.

Немного разочарован. Итоговые результаты после выполнения кода совпадают 1 в 1, то есть результаты полученные двумя разными способами - одинаковые.
А вот по времени выполнения - не все так гладко.

Вот вариант, когда запрос делался при первом запуске :
	 Выборка записей м1 из архивной таблицы заняла 	00:00:00.238352 сек
         *** по всей табл (мой вариант) = 	00:00:00.00641 
       	 *** рекурсивный (Flashpoke) = 	        00:00:00.010818 


Вот второй прогон, когда код выполнялся через 20 секунд после первого запуска этого же кода:
	 Выборка записей м1 из архивной таблицы заняла 	00:00:00.251115 сек
         *** по всей табл (мой вариант) = 		00:00:00.007054
       	  *** рекурсивный (Flashpoke) = 		00:00:00.008497 


Время почти одинаковое, но все равно у рекурсивного запроса оказалось больше.
При этом, времени на создание данного кода для рекурсивного запроса истратилось больше (через циклы FOR мне гораздо привычнее, но это около 5-10% всех выборок, не больше).
Но есть в этой ситуации БОЛЬШОЙ ПЛЮС. При работе для внешних клиентов, этот код по сложности выше среднего и демпинговщики его не смогут повторить (слишком дорого по времени он получается). Это значит, что клиент в следующий раз идет ко мне.
;-)
.

Сообщение было отредактировано: 14 июл 20, 10:00
14 июл 20, 10:02    [22166975]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: RECURSIVE и Оконная функция  [new]
Fedor123
Member

Откуда:
Сообщений: 7
Flashpoke,

Увидел ваш код по рекурсивным запросам, может с этим что то подскажете
https://stackoverflow.com/questions/70027171/error-on-query-postgres-13-with-recursive-error-42601-error-syntax-error-at
21 ноя 21, 12:36    [22398607]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
Fedor123
Flashpoke,

Увидел ваш код по рекурсивным запросам, может с этим что то подскажете
https://stackoverflow.com/questions/70027171/error-on-query-postgres-13-with-recursive-error-42601-error-syntax-error-at


Так нельзя из CTE результатов delete делать... delete можно из физической таблицы только.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
21 ноя 21, 13:18    [22398623]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
Fedor123
Member

Откуда:
Сообщений: 7
Maxim Boguk,
А есть идеи как обойти ?
Может можно как то связаться с вами ?
22 ноя 21, 10:17    [22398925]     Ответить | Цитировать Сообщить модератору
 Re: RECURSIVE и Оконная функция  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
Fedor123
Maxim Boguk,
А есть идеи как обойти ?
Может можно как то связаться с вами ?


Сделайте новый пост с описанием какую проблему/задачу решаете.
И желательно с тестовым примером данных минимальным и требуемым ответом.
Тогда можно будет подумать как её решать.
Дописывать несвязанные вопросы к древним тредам - практика плохая.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
22 ноя 21, 10:41    [22398944]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить