Таинственный рекурсивный запрос / PostgreSQL

ReSQL.ru

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

Гость

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

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

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

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

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Таинственный рекурсивный запрос

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

Таинственный рекурсивный запрос

#39636960

polin11

Гость

Я плохо понимаю логику рекурсивных запросов, есть запрос

Код: 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.

UPDATE "Table" main_table 
SET 
   "Field1" = 
   (
      WITH RECURSIVE
      Hier("Field2", "Fileld3", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2", "Fileld3", "Fileld4"
         FROM "Table" t1 
            WHERE t1."Fileld3" = main_table."Fileld3"		
         UNION ALL
         SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM  "Table" t2
            INNER JOIN Hier h ON ( t2."Fileld3" = h."Fileld4" )
      )
      SELECT h."Field2" FROM Hier h WHERE h."Fileld4" <> 1 LIMIT 1 
   )
   ,
   "Fileld4" = 
   (
      WITH RECURSIVE
      Hier("Fileld3", "Fileld4")
      AS
      (
         SELECT "Fileld3", "Fileld4"
         FROM "Table" t1 
            WHERE t1."Fileld3" = main_table."Fileld3"				
         UNION ALL
         SELECT t2."Field2", t2."Fileld4" FROM "Table" t2 
            INNER JOIN Hier h ON ( t2."@Fileld3" = h."Fileld4" )
      )
      SELECT COUNT(*) FROM Hier
   )

Можно ли вынести стартовую часть рекурсии, то есть сделать что-то типа этого

Код: 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.
36.
37.
38.

WITH my("Fileld3", "Fileld4") as
(
  SELECT 1 AS "Field2", t1."Fileld3", t1."Fileld4"
         FROM "Table" t1
         INNER JOIN "Table" t2 
         ON (t1."Fileld3" = t2."Fileld3")
)
UPDATE "Table" main_table 
SET 
   "Field1" = 
   (
      WITH RECURSIVE
      Hier("Field2", "Fileld3", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2", "Fileld3", "Fileld4"
         FROM my
         UNION ALL
         SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM  "Table" t2
            INNER JOIN Hier h ON ( t2."Fileld3" = h."Fileld4" )
      )
      SELECT h."Field2" FROM Hier ht WHERE ht."Fileld4" <> 1 LIMIT 1 
   )
   ,
   "Fileld4" = 
   (
      WITH RECURSIVE
      Hier("Fileld3", "Fileld4")
      AS
      (
         SELECT "Fileld3", "Fileld4"
         FROM my
         UNION ALL
         SELECT t2."Field2", t2."Fileld4" FROM "Table" t2 
            INNER JOIN Hier h ON ( t2."@Fileld3" = h."Fileld4" )
      )
      SELECT COUNT(*) FROM Hier
   )

Можно ли верхний запрос заменить нижним

...

Рейтинг:

0 / 0

26.04.2018, 18:41

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

Таинственный рекурсивный запрос

#39637010

polin11

Гость

Насколько я поминаю так делать нельзя, тогда другой вопрос
можно каким-либо образом вынести стартовую часть рекурсии, чтобы вычислялась 1 раз, для обновления 2 полей?

...

Рейтинг:

0 / 0

26.04.2018, 20:12

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

Таинственный рекурсивный запрос

#39637014

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

polin11Можно ли верхний запрос заменить нижним

а сами как думаете.

можно попытатся посмотреть в эту сторону :

Код: sql

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

[src]
UPDATE "Table" main_table 
SET 
   ("Field1"   ,   "Fileld4" )= 
 = 
--( select a,b from --если синаксически упрется с сте 
   (
      WITH RECURSIVE
      Hier("Field2", "Fileld3", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2", "Fileld3", "Fileld4"
         FROM "Table" t1 
            WHERE t1."Fileld3" = main_table."Fileld3"		
         UNION ALL
         SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM  "Table" t2
            INNER JOIN Hier h ON ( t2."Fileld3" = h."Fileld4" )
      )
SELECT 
      (SELECT h."Field2" FROM Hier h WHERE h."Fileld4" <> 1 LIMIT 1) as a
      ,(SELECT COUNT(*) FROM Hier)  as b
) -- foo )

...

Рейтинг:

0 / 0

26.04.2018, 20:19

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

Таинственный рекурсивный запрос

#39637024

polin11

Гость

Мне кажется это подойдет, а как вам кажется?

Код: 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.

UPDATE "Table" main_table 
SET 
   "Field1" = 
   (
      WITH RECURSIVE
      Hier("Field2", "Fileld3", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2",  main_table."Fileld3",  main_table."Fileld4"
         UNION ALL
         SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM  "Table" t2
            INNER JOIN Hier h ON ( t2."Fileld3" = h."Fileld4" )
      )
      SELECT h."Field2" FROM Hier h WHERE h."Fileld4" <> 1 LIMIT 1 
   )
   ,
   "Fileld4" = 
   (
      WITH RECURSIVE
      Hier("Fileld3", "Fileld4")
      AS
      (
         SELECT  main_table."Fileld3",  main_table."Fileld4"			
         UNION ALL
         SELECT t2."Field2", t2."Fileld4" FROM "Table" t2 
            INNER JOIN Hier h ON ( t2."@Fileld3" = h."Fileld4" )
      )
      SELECT COUNT(*) FROM Hier
   )

...

Рейтинг:

0 / 0

26.04.2018, 21:32

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

Таинственный рекурсивный запрос

#39637031

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

polin11Мне кажется это подойдет, а как вам кажется?

мне кажется что вы лысого вхолостую гоняете. например.

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

то, что я вам предложил как отругалось ? или вы и не пытались ?

...

Рейтинг:

0 / 0

26.04.2018, 21:58

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

Таинственный рекурсивный запрос

#39637037

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

-- там у меня одна опечатка , как минимум:
qwwqpolin11Можно ли верхний запрос заменить нижним

а сами как думаете.

можно попытатся посмотреть в эту сторону :

Код: sql

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

[src]
UPDATE "Table" main_table 
SET 
   ("Field1"   ,   "Fileld4" )= 
=  --очепятка тут лишнее = -- оставьте одно "="
--( select a,b from --если синаксически упрется с сте 
   (
      WITH RECURSIVE
      Hier("Field2", "Fileld3", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2", "Fileld3", "Fileld4"
         FROM "Table" t1 
            WHERE t1."Fileld3" = main_table."Fileld3"		
         UNION ALL
         SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM  "Table" t2
            INNER JOIN Hier h ON ( t2."Fileld3" = h."Fileld4" )
      )
SELECT 
      (SELECT h."Field2" FROM Hier h WHERE h."Fileld4" <> 1 LIMIT 1) as a
      ,(SELECT COUNT(*) FROM Hier)  as b
) -- foo )

ага, вижу.
должно работать . +- синтаксические камушки.

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

...

Рейтинг:

0 / 0

26.04.2018, 22:09

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

Таинственный рекурсивный запрос

#39637041

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

polin11,
поле "Fileld3" у вас в Тейбл уникально ?

если да (и только в этом случае) -- возможно ваш последний запрос формально эквивалентен исходному (если навскидку)

врд ли пж дотумкает, что набор 1. т.ч. можете скрестить с конструкцией

Код: plaintext

 Set  ( column_name [, ...] ) = ( sub-SELECT )

сэкономите на материализациях копий cte.

...

Рейтинг:

0 / 0

26.04.2018, 22:23

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

Таинственный рекурсивный запрос

#39637049

polin11

Гость

qwwq,
Да Вы, батенька, просто гений!!!
Для полного счастья мне нужно обновить третье поле

Код: sql

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

"Field5" =
   (
      WITH RECURSIVE
      HierTable("Field2", "Fileld3", "Fileld6", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2", "Fileld3", "Field6", "Fileld4" 
            FROM "Table1" t1 
            WHERE t1."Fileld3" = main_table."Fileld3"
         UNION ALL
         SELECT h."Field2" + 1 AS "Fileld2", t2."Fileld3", t2."Fileld6", t2."Fileld4" FROM "Table" t2 
            INNER JOIN Hier h ON ( t2."Fileld2" = h."Fileld3" )
      )
      SELECT  string_agg( hier_col.code, '#' ) AS result
      FROM
         ( SELECT "Код" AS code 
            FROM Hier 
            WHERE "Fileld2" <> 1 
            ORDER BY "Fileld2" DESC 
            OFFSET 1 
         ) AS hier_col
   )

Пробовал аналогичным образом,

Код: 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.

UPDATE "Table" main_table 
SET 
   ("Field1", "Fileld4", "Field5" )= 
   (
      WITH RECURSIVE
      Hier("Field2", "Fileld3", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2", "Fileld3", "Fileld4"
         FROM "Table" t1 
            WHERE t1."Fileld3" = main_table."Fileld3"		
         UNION ALL
         SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM  "Table" t2
            INNER JOIN Hier h ON ( t2."Fileld3" = h."Fileld4" )
      )
SELECT 
      (SELECT h."Field2" FROM Hier h WHERE h."Fileld4" <> 1 LIMIT 1) as a,
      (SELECT COUNT(*) FROM Hier)  as b,
      (SELECT string_agg( hier_col.code, '#' )
      FROM
         ( SELECT "Код" AS code 
            FROM Hier 
            WHERE "Fileld2" <> 1 
            ORDER BY "Fileld2" DESC 
            OFFSET 1 
         ) AS hier_col) as c
)

Но оно, Field5 неверно считается...

...

Рейтинг:

0 / 0

26.04.2018, 22:57

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

Таинственный рекурсивный запрос

#39637052

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

polin11qwwq,
Да Вы, батенька, просто гений!!!
Для полного счастья мне нужно обновить третье поле

Код: sql

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

"Field5" =
   (
      WITH RECURSIVE
      HierTable("Field2", "Fileld3", "Fileld6", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2", "Fileld3", "Field6", "Fileld4" 
            FROM "Table1" t1 
            WHERE t1."Fileld3" = main_table."Fileld3"
         UNION ALL
         SELECT h."Field2" + 1 AS "Fileld2", t2."Fileld3", t2."Fileld6", t2."Fileld4" FROM "Table" t2 
            INNER JOIN Hier h ON ( t2."Fileld2" = h."Fileld3" )
      )
      SELECT  string_agg( hier_col.code, '#' ) AS result
      FROM
         ( SELECT "Код" AS code 
            FROM Hier 
            WHERE "Fileld2" <> 1 
            ORDER BY "Fileld2" DESC 
            OFFSET 1 
         ) AS hier_col
   )

Пробовал аналогичным образом,

Код: 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.

UPDATE "Table" main_table 
SET 
   ("Field1", "Fileld4", "Field5" )= 
   (
      WITH RECURSIVE
      Hier("Field2", "Fileld3", "Fileld4")
      AS
      (
         SELECT 1 AS "Field2", "Fileld3", "Fileld4"
         FROM "Table" t1 
            WHERE t1."Fileld3" = main_table."Fileld3"		
         UNION ALL
         SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM  "Table" t2
            INNER JOIN Hier h ON (  t2."Fileld3" = h."Fileld4" )
      )
SELECT 
      (SELECT h."Field2" FROM Hier h WHERE h."Fileld4" <> 1 LIMIT 1) as a,
      (SELECT COUNT(*) FROM Hier)  as b,
      (SELECT string_agg( hier_col.code, '#' )
      FROM
         ( SELECT "Код" AS code 
            FROM Hier 
            WHERE "Fileld2" <> 1 
            ORDER BY "Fileld2" DESC 
            OFFSET 1 
         ) AS hier_col) as c
)

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

а зачем вы сами себя через себя пересчитываете ?

...

Рейтинг:

0 / 0

26.04.2018, 23:11

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

Таинственный рекурсивный запрос

#39637056

polin11

Гость

qwwq,
Прощу прощения, я сам уже запутался, запрос первоначальный выглядит так

Код: 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.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.

UPDATE "Table" main_table 
SET 
   "Field5" =
   (
      WITH RECURSIVE
      Heir("Field2", "Field1", "Field3", "Field4")
      AS
      (
         SELECT 1 AS "Field2", "Field1", "Field3", "Field4" 
            FROM "Table" k1 
            WHERE k1."Field1" = main_table."Field1"
         UNION ALL
         SELECT ht."Field2" + 1 AS "Field2", k2."Field1", k2."Field3", k2."Field4" FROM "Table" k2 
            INNER JOIN Heir ht ON ( k2."Field1" = ht."Field4" )
      )
      SELECT 
         string_agg( hier_sel.code, '#' )
      FROM
         ( SELECT "Field3" AS code 
            FROM Heir 
            WHERE "Field2" <> 1 
            ORDER BY "Field2" DESC 
            OFFSET 1 
         ) AS hier_sel
   ),
   "Field6" = 
   (
      WITH RECURSIVE
      Heir("Field2", "Field1", "Field4")
      AS
      (
         SELECT 1 AS "Field2", "Field1", "Field4" 
            FROM "Table" k1 
            WHERE k1."Field1" = main_table."Field1"
         UNION ALL
         SELECT ht."Field2" + 1 AS "Field2", k2."Field1", k2."Field4" FROM "Table" k2 
            INNER JOIN Heir ht ON ( k2."Field1" = ht."Field4" )
      )
      SELECT ht."Field1" FROM Heir ht WHERE ht."Field2" <> 1 ORDER BY ht."Field2" DESC LIMIT 1 
   ),
   "Field2" = 
   (
      WITH RECURSIVE
      Heir("Field1", "Field4")
      AS
      (
         SELECT "Field1", "Field4" 
            FROM "Table" k1 
            WHERE k1."Field1" = main_table."Field1"
         UNION ALL
         SELECT k2."Field1", k2."Field4" FROM "Table" k2 
            INNER JOIN Heir ht ON ( k2."Field1" = ht."Field4" )
      )
      SELECT COUNT(*) FROM Heir ht
   )

Осталась проблема, обновить также изящно поле Field5

...

Рейтинг:

0 / 0

26.04.2018, 23:38

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

Таинственный рекурсивный запрос

#39637452

polin11

Гость

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

Код: sql

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

SELECT 1 AS "Field2", "Fileld3", "Field6", "Fileld4" 
FROM "Table1" t1 
WHERE t1."Fileld3" = main_table."Fileld3"
UNION ALL
SELECT h."Field2" + 1 AS "Fileld2", t2."Fileld3", t2."Fileld6", t2."Fileld4" FROM "Table" t2 
INNER JOIN Hier h ON ( t2."Fileld2" = h."Fileld3" )

сделать

Код: sql

1.
2.
3.
4.

SELECT 1 AS "Field2", main_table."Fileld3", main_table."Field6", main_table."Fileld4" 
UNION ALL
SELECT h."Field2" + 1 AS "Fileld2", t2."Fileld3", t2."Fileld6", t2."Fileld4" FROM "Table" t2 
INNER JOIN Hier h ON ( t2."Fileld2" = h."Fileld3" )

...

Рейтинг:

0 / 0

27.04.2018, 13:53

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

Таинственный рекурсивный запрос

#39637489

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

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

Код: sql

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

SELECT 1 AS "Field2", "Fileld3", "Field6", "Fileld4" 
FROM "Table1" t1 
WHERE t1."Fileld3" = main_table."Fileld3"
UNION ALL
SELECT h."Field2" + 1 AS "Fileld2", t2."Fileld3", t2."Fileld6", t2."Fileld4" FROM "Table" t2 
INNER JOIN Hier h ON ( t2."Fileld2" = h."Fileld3" )

сделать

Код: sql

1.
2.
3.
4.

SELECT 1 AS "Field2", main_table."Fileld3", main_table."Field6", main_table."Fileld4" 
UNION ALL
SELECT h."Field2" + 1 AS "Fileld2", t2."Fileld3", t2."Fileld6", t2."Fileld4" FROM "Table" t2 
INNER JOIN Hier h ON ( t2."Fileld2" = h."Fileld3" )

является ли поле 3 -- уникальным.
если да -- то скорее да,чем нет

если нет -- то совершенно нет.

совсем не обязательно первой итерации содержать 1 запись

-------------
к примеру бывает и так :

Код: sql

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

with recursive 
y as (
	(select n_year FROM ngramsr.ngrams1 ORDER BY n_year limit 1)
	union all 
	select t.n_year FROM y
	,lateral (select n_year from ngramsr.ngrams1 t
	where t.n_year>=( y.n_year+1 )
	ORDER BY n_year limit 1) t
	)
,yf AS (
		select n_year, fok_code from y 
		,lateral (SELECT fok_code FROM ngramsr.ngrams1 f where f.n_year= y.n_year ORDER BY n_year,fok_code limit 1) foo
		union all
		SELECT n_year, foo.fok_code FROM YF
		,lateral (SELECT f.fok_code FROM ngramsr.ngrams1 f where f.n_year= yf.n_year AND yf.fok_code<f.fok_code ORDER BY n_year,fok_code limit 1) foo
		)
		
SELECT DISTINCT fok_code from yf

-- 2-й итеративный прогон бежит по граблям заданным первым

...

Рейтинг:

0 / 0

27.04.2018, 14:24

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

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

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Таинственный рекурсивный запрос

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=53&msg=39637049&tid=1995798]:	0ms
get settings:	11ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	29ms
get topic data:	11ms
get forum data:	3ms
get page messages:	47ms
get tp. blocked users:	1ms
others:	295ms

total:	419ms