RECURSIVE и Оконная функция / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / RECURSIVE и Оконная функция

17 сообщений из 17, страница 1 из 1

RECURSIVE и Оконная функция

#39973476

О-О-О

Гость

Добрый день.

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

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

Код: sql

значение Х(текущая строка)*0,2+Y(предыдущая строка)*0,8

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

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

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

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

...

Рейтинг:

0 / 0

26.06.2020, 10:50

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39973513

Guzya

Гость

Смотрите функции LAG и LEAD.

...

Рейтинг:

0 / 0

26.06.2020, 12:21

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39973536

О-О-О

Гость

Guzya,

Я в курсе данных функций. Одна берет предыдущую запись, вторая - последующую. Записи в таблицу не идут.
Проверял.
Попробую еще раз.
.

...

Рейтинг:

0 / 0

26.06.2020, 13:04

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39973711

О-О-О

Гость

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

Код: sql

1.
2.
3.
4.
5.
6.
7.

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 раз быстрее. Получил что и хотел.
.

...

Рейтинг:

0 / 0

27.06.2020, 10:08

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39974429

О-О-О

Гость

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

...

Рейтинг:

0 / 0

30.06.2020, 09:52

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39976242

Flashpoke

Гость

Можно сделать рекурсией, поскольку у вас формула рекуррентная.

Предположим, есть таблица:

Код: sql

1.
2.
3.
4.
5.

CREATE TABLE newtable (
	id bigserial NOT NULL,
	x int8 NOT NULL,
	y numeric NULL
);

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

"id""x"15243647596117128896

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

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"00215243647596117128896
сделав сдвиг:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

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;

...

Рейтинг:

0 / 0

04.07.2020, 18:34

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39977784

О-О-О

Гость

Flashpoke,

Спасибо. Понял. Проверяю.

...

Рейтинг:

0 / 0

09.07.2020, 14:28

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39977793

О-О-О

Гость

Flashpoke,

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

...

Рейтинг:

0 / 0

09.07.2020, 15:24

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39977873

О-О-О

Гость

Flashpoke,

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

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.

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=#

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

...

Рейтинг:

0 / 0

09.07.2020, 19:22

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39977879

О-О-О

Гость

Flashpoke,

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

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

Спасибо, Flashpoke.
.

...

Рейтинг:

0 / 0

09.07.2020, 20:01

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39977935

Flashpoke

Гость

О-О-О

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

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

В любом случае, PostgreSQL сначала строит граф зависимостей между CTE и только потом идёт по нему от начала до конца.
Иногда бывает удобно сделать мешанину из разных CTE, не только с SELECT, а и с INSERT...RETURNING или DELETE...RETURNING.

...

Рейтинг:

0 / 0

09.07.2020, 23:21

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39977976

О-О-О

Гость

Flashpoke,

Этот как раз тот случай, когда на изучение не жалко своего времени.
Такие ситуации в будущем серьёзно помогают.
.
Очень необычный подход, в котором много всего, что ты вроде бы знаешь, но вот воедино связать не думал.
В общем, мучаю код, понимаю примерно как работает, но при попытке написать что то похожее - выходят ошибки. Но в любом случае, разберусь и сравню, стоили ли это тех усилий, которые затрачены на его понимание(думаю дня два уйдёт).
.
Для сведений, через стандартный For .. удалось выполнять код за 0,0067 сек (против 0.030 сек) , просто избавившись от OFFSET (иногда без него ну никак).
Очень надеюсь, что по вашему примеру код будет работать хотя бы за 0,0061 сек ;-)
.

...

Рейтинг:

0 / 0

10.07.2020, 07:31

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#39979314

О-О-О

Гость

Разобрался и добрался до тестов.

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

Вот вариант, когда запрос делался при первом запуске :

Код: sql

1.
2.
3.

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

Вот второй прогон, когда код выполнялся через 20 секунд после первого запуска этого же кода:

Код: sql

1.
2.
3.

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

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

...

Рейтинг:

0 / 0

14.07.2020, 10:02

| Ответить | Цитировать | Написать

Период между сообщениями больше года.

RECURSIVE и Оконная функция

#40113513

Fedor123

Гость

Flashpoke,

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

...

Рейтинг:

0 / 0

21.11.2021, 12:36

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#40113518

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Fedor123

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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

21.11.2021, 13:18

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#40113660

Fedor123

Гость

Maxim Boguk,
А есть идеи как обойти ?
Может можно как то связаться с вами ?

...

Рейтинг:

0 / 0

22.11.2021, 10:17

| Ответить | Цитировать | Написать

RECURSIVE и Оконная функция

#40113674

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Fedor123

Maxim Boguk,
А есть идеи как обойти ?
Может можно как то связаться с вами ?

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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

22.11.2021, 10:41

| Ответить | Цитировать | Написать

17 сообщений из 17, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / RECURSIVE и Оконная функция

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=39973536&tid=1993776]:	0ms
get settings:	9ms
get forum list:	15ms
check forum access:	3ms
check topic access:	3ms
track hit:	28ms
get topic data:	10ms
get forum data:	2ms
get page messages:	56ms
get tp. blocked users:	1ms
others:	13ms

total:	140ms