ááŸá¯ááºááœá±ážáá±á¬ ERP á áá áºáá»á¬ážááœáẠáá»á¬ážá áœá¬áá±á¬ á¡áá¬áá»á¬ážááẠá¡áááºá¡á±á¬áẠááá±á¬ááá¬áááŸááááºááá áºáá¬ážáááºážááŒá áºáá±áá±á¬ á¡áá¬ááá¹áá¯áá»á¬áž áááºážá á®áá±áá±á¬á¡áá« ááá¯ážáá±áž-áá»áá¯ážááẠáááºáá¶áá±ážáá áºááẠ- á€áááºááŸá¬ áá¯ááºáááºážá á¡ááœá²á·á¡á ááºážááœá²á·á ááºážáá¯á¶ (á€áá¬áááœá²áá»á¬ážá áá¬ááá»á¬ážááŸáá·áº á¡áá¯ááºá¡ááœá²á·áá»á¬áž)á áá¯ááºáá á¹á ááºážá á¬áááºážá áá¯ááºáááºážáááºáááºáá»á¬ážá á¡áá±á¬ááºážá¡ááŸááºáá»á¬ážá áááá®áááºá¡áá±á¡áá¬ážá...
ááááºáá±á¬á· áá
áºáá¯á០áááŸááá«áá°ážá
ááá¯áá²á·ááá¯á·áá±á¬áá áºáááºááᯠDBMS ááœááºááááºážáááºážáááºáááºážáááºážáá»á¬ážá áœá¬ááŸááááºá ááá¯á·áá±á¬áºááá±á·áá»áœááºá¯ááºááá¯á·áááºááœá±ážáá»ááºá áá¬áá áºáá¯áááºážááá¯áá¬á¡á¬áá¯á¶á áá¯ááºáá«áááº-
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- Ме забÑваеЌ, ÑÑП FK Ме пПЎÑазÑÐŒÐµÐ²Ð°ÐµÑ Ð°Ð²ÑПÑПзЎаМОе ОМЎекÑа, в ПÑлОÑОе ÐŸÑ PK
á¡áááºáááºážá¡ááá·áºá áááºáá²áá¬ááá¯á· áááºááŒáá·áºááŸá¯áá±áá»áááºááœááºá ááá¯ááá¯á·áá±á¬ááœá²á·á ááºážáá¯á¶ááŸáá·áºá¡áá° áááºá âáá¯á¶á¡áá±á¬â áááºážáááºážáá»á¬áž áááºááá¯á·áááá±á¬ááºáááºááᯠááŒáá·áºááŸá¯ááẠá áááºááŸááºá áœá¬á á±á¬áá·áºááá¯ááºážáá±áá«áááºá
áá±á«áºáá±á«ááºáá¬áá±á¬ áá¬áá¬ááºááŒá¿áá¬áá»á¬ážá SQL ááœáẠáááºážááá¯á·á á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯ááŸáá·áº áááºážááá¯á·áá
áœááºážáá±á¬ááºáááºááᯠááŒáŸáá·áºáááºááẠááŒáá¯ážá
á¬ážááŒáá«á
áá¯á·á
#áá áá¯ááºááœááºážá áááºáá±á¬ááºáááºáá²á
á€ááœá²á·á
ááºážáá¯á¶ááẠá¡ááœá²á·á¡á
ááºážáááœá²á·á
ááºážáá¯á¶ááœáẠáá¬áááœá²áá»á¬ážá áá¬áááœá²áá»á¬ážá ááá¹ááá»á¬ážá á¡ááá¯ááºážá¡áááºáá»á¬ážá á¡áá¯ááºá¡ááœá²á·áá»á¬áž... - áááºážááá¯á·ááᯠáááºáááºááá¯á·áááºáá±á«áºáááºááŒá
áºá
á± áááºá¡á±á¬ááºáá¶áá¬ááá»á¬ážá áááºá¡á±á¬ááºáááºáá¬ážáá»á¬ážááᯠáááºáááºá
á±áááºááᯠá¡ááá¡áá»áááºáá¶ááŒáá«á
áá¯á·á
áŠážá áœá¬á áá»áœááºá¯ááºááá¯á·á 10K ááŒááºá ááºáá»á¬ážá 'áá áºáááº' ááᯠáá¯ááºáá¯ááºááŒáá«á áá¯á·
INSERT INTO hier
WITH RECURSIVE T AS (
SELECT
1::integer id
, '{1}'::integer[] pids
UNION ALL
SELECT
id + 1
, pids[1:(random() * array_length(pids, 1))::integer] || (id + 1)
FROM
T
WHERE
id < 10000
)
SELECT
pids[array_length(pids, 1)] id
, pids[array_length(pids, 1) - 1] pid
FROM
T;
á¡ááá¯ážááŸááºážáá¯á¶ážá¡áá¯ááºááŒáá·áº á
áááºááŒáá«á
áá¯á· - áááá»áá±á¬ááá¹ááá
áºáá¯á¡ááœááºáž á¡áá¯ááºáá¯ááºáá±á¬ áááºáááºážáá»á¬ážá¡á¬ážáá¯á¶ážááᯠááŸá¬ááœá±ááŒááºáž ááá¯á·ááá¯áẠá¡áááºáááºážá¡ááá·áº áááºááŸááºáá»ááºáá»á¬ážá¡á - node áá
áºáá¯áááá±ážáá»á¬ážá¡á¬ážáá¯á¶ážááá¯ááŸá¬áá«á. áá¬ážá
ááºááŒá±ážáááºá "á¡áááº" ááá¯ááááºáááºáž áá±á¬ááºážáá±áááº... á€á¡áá¬á¡á¬ážáá¯á¶ážááẠá¥ááᬠáá
áºáá»áá¯ážáá»áá¯ážááᯠáááºáá±á¬ááºááẠááá¯á¡ááºáá±áááá·áºáááºá
á€áá¬ážá ááºááŒá±ážáááºáá»á¬ážá á¡ááá·áºááŸá áºááá·áºáá¬ááŸáááŒá®áž á¡áá±á¡ááœááºááẠáá áºáá«áááºá¡ááœááºážáá¬ááŸááá±á¬áºáááºáž á¡ááá·áº 5 áááºááá¯á áá»áá¯ážáááºáá«áááºáá»á¬ážá áœá¬ááŸááá±ááŒá®ááá¯áá»áŸáẠá¡á¬ážáá¯á¶ážá¡áááºááŒá±ááœá¬ážáá«áááºá ááá¯ážáᬠdown-tree ááŸá¬ááœá±ááŸá¯ ááœá±ážáá»ááºááŸá¯áá»á¬ážááᯠáááºááá¯á·áá±ážáá¬ážááẠ(ááŸáá·áº á¡áá¯ááº) ááᯠááŒáá·áºááŒáá«á áá¯á·á ááá¯á·áá±á¬áº áŠážá áœá¬á áá»áœááºá¯ááºááá¯á·ááá¯áá±ááá¡ááœáẠá áááºáááºá á¬ážá áá¬á¡áá±á¬ááºážáá¯á¶ážááŒá áºááá·áº node áá»á¬ážááᯠáá¯á¶ážááŒááºááŒáá«á áá¯á·á
á¡áá±á¬ááºážáá¯á¶áž "áááºáá²" áá áºáááºááœá²áá»á¬áž
WITH RECURSIVE T AS (
SELECT
id
, pid
, ARRAY[id] path
FROM
hier
WHERE
pid IS NULL
UNION ALL
SELECT
hier.id
, hier.pid
, T.path || hier.id
FROM
T
JOIN
hier
ON hier.pid = T.id
)
TABLE T ORDER BY array_length(path, 1) DESC;
id | pid | path
---------------------------------------------
7624 | 7623 | {7615,7620,7621,7622,7623,7624}
4995 | 4994 | {4983,4985,4988,4993,4994,4995}
4991 | 4990 | {4983,4985,4988,4989,4990,4991}
...
á¡áá±á¬ááºážáá¯á¶áž "áá»ááº" áá áºáááºááœá²áá»á¬áž
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;
id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
á€áá±ážááœááºážáá»á¬ážá¡ááœáẠáá»áœááºá¯ááºááá¯á·ááẠáá¯á¶ááŸááºááᯠá¡áá¯á¶ážááŒá¯áá²á·áááºá áááºáá«ááá²áá² JOIN:
áá®áá±á¬ááºážááá¯ááŸá¯áá¯á¶á
á¶áá²á· áááá¬áá«áááºá áááºááá²áá²ááŒá¯áá¯ááºááá·áºá¡áá±á¡ááœááºááẠá
á¯á
á¯áá±á«ááºážáá¬ážá
ááºááŒá±ážáááºá¡áá±á¡ááœááºááŸáá·áº ááá¯ááºáá®áááºááŒá
áºáááºá (áááºážááá¯á·á¡áá²á០áá«áááºáá»á¬ážá
áœá¬ááŸááááº)á áááºážááẠá¡ááœááºá¡áá±ážááŒá®ážáá±á¬ á¡áááºážá¡ááŒá
áºáá»á¬ážááᯠáá°ááá¯ááºááŒá®áž ááááºá¡áá±ááŒáá·áº á¡áá»áááºááŒá
áºáááºá
"á¡áá»ááºáá¯á¶áž" á¡áááºááœá²ááᯠá á áºáá±ážááŒáá·áºáá¡á±á¬ááºá
WITH RECURSIVE T AS (
SELECT
id
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
hier.id
FROM
T
JOIN
hier
ON hier.pid = T.id
)
TABLE T;
áá»áŸá±á¬áºááá·áºáá¬ážááá·áºá¡ááá¯ááºáž áá»áœááºá¯ááºááá¯á·ááẠááŸááºáááºáž 30 áá¯á¶ážááᯠááœá±á·ááŸááá²á·áááºá ááá¯á·áá±á¬áº áááºážááá¯á·ááẠá€á¡áá¬á¡ááœáẠá á¯á á¯áá±á«ááºážá¡áá»áááºá 60% ááᯠá¡áá¯á¶ážááŒá¯áá²á·ááŒááẠ- á¡ááŒá±á¬ááºážááŸá¬ áááºážááá¯á·ááẠá¡ááœáŸááºážááœáẠááŸá¬ááœá±ááŸá¯ 30 ááá¯áááºáž ááŒá¯áá¯ááºáá²á·áááºá áá»áŸá±á¬á·ááá¯á· ááŒá áºááá¯ááºááá¬ážá
á¡ááœáŸááºážá¡á¬ážááŒáá·áº á¡á á¯ááá¯áẠááŒááºáááºá á áºáá±ážááŒááºážá
node áá
áºáá¯á
á®á¡ááœáẠáá®ážááŒá¬ážá¡ááœáŸááºážáá±ážááœááºážáá
áºáᯠááŒá¯áá¯ááºááẠááá¯á¡ááºáá«ááá¬ážá ááá¯ááºáá°áž - á¡ááœáŸááºážááá±áááºááá¯á·ááááºá áá±á«áºááá¯ááŸá¯áá
áºáá¯ááœáẠáá±á¬á·áá»á¬ážá
áœá¬ááᯠáá
áºááŒáá¯ááºááẠá¡áá¯á¶ážááŒá¯ááŒááºážá ááŒááº. = ANY(array)
.
ááá¯áá²á·ááá¯á·áá±á¬ ááœá²ááŒá¬ážáááºááŸááºááŸá¯á¡á¯ááºá á¯áá áºáá¯á á®ááœáẠáá»áœááºá¯ááºááá¯á·ááẠááááºá¡ááá·áºááœááºááœá±á·ááŸáááá±á¬ ID á¡á¬ážáá¯á¶ážááᯠ"nodes" ááŒáá·áº ááá°ááá¯ááºáááºá á¡á²áá«ááá±á¬á· áá±á¬ááºáááá·áºáá»ááºážá á®ááŸá¬ áá»áá±á¬áºááá¯á· áá¯ááºááŸá¬áá«á á¡ááá·áºáá áºáá¯á á¡áá»áá¯ážá¡ááœááºá¡á¬ážáá¯á¶ážááᯠáá áºááŒáá¯ááºááẠááŸá¬ááœá±áá«á.
áá¶ááá¯ážáá¬áá²á áááºáá«ááá²áá²ááœá±ážáá»ááºááŸá¯ááœááºá á¡á
á¯á¡áá±ážáá±ážááŒááºážááŸá¯áá
áºáá¯ááœáẠáááºááá¯ááºááá¯ááºáááºáá±á¬ááºááááá«áááá¯á·áá±á¬áº ááááºá¡ááá·áºááœáẠááœá±á·ááŸááá²á·ááá·áºá¡áá¬ááᯠáá
áºáááºážáááºážááŒáá·áº ááœá±ážáá»ááºááẠááá¯á¡ááºáááº... ááœá±ážáá»ááºááŸá¯áá
áºáá¯áá¯á¶ážá¡ááœáẠnested query ááŒá¯áá¯ááºááẠáááŒá
áºááá¯ááºáá±á¬áºáááºáž áááºážá áá®ážááŒá¬ážáááºáááºá¡ááœáẠááŒá
áºááá¯ááºáááºá á€á¡ááœááºááẠáá»áœááºá¯ááºááá¯á·á¡áá¯á¶ážááŒá¯áááºááá¯á¡ááºáá±á¬á¡áá¬ááŒá
áºááá·áº array áá
áºáá¯áááºážááŒá
áºáááºá ANY
.
áááºážáááºážáá±á¬á· áá°ážááœááºáááºááá¯á· áááºááá±ááá·áº áá¯á¶áá²ááŸá¬áá±á¬á· á¡áá¬á¡á¬ážáá¯á¶ážá ááá¯ážááŸááºážáá«áááºá
WITH RECURSIVE T AS (
SELECT
ARRAY[id] id$
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
ARRAY(
SELECT
id
FROM
hier
WHERE
pid = ANY(T.id$)
) id$
FROM
T
WHERE
coalesce(id$, '{}') <> '{}' -- ÑÑлПвОе вÑÑ
ПЎа Оз ÑОкла - пÑÑÑПй ЌаÑÑОв
)
SELECT
unnest(id$) id
FROM
T;
áá®áá±áá¬ááŸá¬ á¡áá±ážááŒá®ážáá¯á¶ážá¡áá¬ááá±á¬áẠááá¯ááºáá°ážá á¡áá»áááºáá® 1.5 ááŒáááºá¡ááá¯ááºáááŸáá·áº 5 á¡á á¬áž á¡ááœáŸááºážááá¯á· áá±á«áºááá¯ááŸá¯ á ááŒáááºáᬠááŸááááºááŒá áºáá±á¬ááŒá±á¬áá·áº áá»áœááºá¯ááºááá¯á·ááœáẠbuffers áááºážáá«ážááœá¬ážáááºááᯠáá¯ááºááá¯ááºáá«áááºá
á¡ááá¯áá¯ááŒá±ážáá áºáá¯ááẠáá±á¬ááºáá¯á¶ážááááºáááŒáááºááŒá áºááŒá®ážáá±á¬ááºá ááœá²ááŒá¬ážáááºááŸááºáá°áá»á¬ážááᯠ"á¡ááá·áºáá»á¬áž" ááŒáá·áº á¡á ááºááá¯ááºáá±áááºáá°áá±á¬ á¡áá»ááºááŒá áºáááºá
Node ááá¹ááá¬
á áœááºážáá±á¬ááºáááºááᯠááŒáŸáá·áºáááºáá¬ááœáẠá¡áá±á¬ááºá¡áá°ááŒá áºá á±ááá·áº áá±á¬ááºá ááºážá á¬ážáá»ááºááŸá¬ - "á¡ááœááº" áá¬ážááá®ážááááá¯ááºáá«áááá¯ááá¯áááºááŸá¬ áááºážááá¯á·á¡ááœáẠáá¯á¶ážá "á¡á±á¬ááºáááºážáá»" ááẠáááá¯á¡ááºáá«á áá»áœááºá¯ááºááá¯á·ááá¯ááºáááºážáá¬áááºááᯠáá¯á¶áá±á¬áºáá¬ááœááºá ááá¯ááá¯áááºááŸá¬ áá»áœááºá¯ááºááá¯á·ááẠáá¬áááá¯ááºáá¬ááœááºážáááºáá»á¬ážááᯠááá¯ááºáá¬ááŒá®áž áááºáááºážáá áºáŠážáá¶áá±á¬ááºááŸááá«á á€áá¬áááœá²ááœáẠáá±á¬ááºáááºááŒáá·áºááŸá¯áááºáááá¯á¡ááºáá«á
áá«ááá¯á· á
á¬ážááœá²áá²ááᯠáááºáá¡á±á¬áẠá¡ááá¯áá±á¬ááºáž boolean
-áááºáá»áœááºá¯ááºááá¯á·ááá
áºáááºááŸá á€á¡áá°ážáááºáá±á«ááºááẠânodeâ ááŸáá áááŸá áá»áœááºá¯ááºááá¯á·á¡á¬áž áá»ááºáá»ááºážááŒá±á¬ááŒáááá·áºááẠ- ááá¯ááá¯áááºááŸá¬á áááºážááœáẠáá¬ážá
ááºááŒá±ážáááºáá»á¬áž áá¯á¶ážáááŸáááá¯ááºáááºááŒá
áºá
á±á
ALTER TABLE hier
ADD COLUMN branch boolean;
UPDATE
hier T
SET
branch = TRUE
WHERE
EXISTS(
SELECT
NULL
FROM
hier
WHERE
pid = T.id
LIMIT 1
);
-- ÐапÑÐŸÑ ÑÑпеÑМП вÑпПлМеМ: 3033 ÑÑÑПк ОзЌеМеМП за 42 ÐŒÑ.
ááá¯ááºáááº! áá áºáááºááŒááºá ááºá¡á¬ážáá¯á¶ážá 30% ááẠá¡áááºážáááºáᬠáááºážáááºáá¬ááŒá±á¬ááºáž ááœá±á·ááŸáááá«áááºá
ááᯠá¡áááºážáááºááŒá¬ážáá¬ážáá±á¬ á
ááºááŒááºáá
áºáá¯ááᯠá¡áá¯á¶ážááŒá¯ááŒáá«á
áá¯á· - ááŸáááá·áº recursive á¡ááá¯ááºážááá¯á· áá»áááºáááºááŸá¯áá»á¬áž LATERAL
áááºážááẠáá»áœááºá¯ááºááá¯á·á¡á¬áž recursive âtableâ á á¡ááœááºáá»á¬ážááᯠáá»ááºááŒááºážáááºáá±á¬ááºááŒáá·áºááŸá¯ááá¯ááºáááºááŒá
áºááŒá®áž áá±á¬á·á¡á
á¯á¶ááá¯áá»áŸá±á¬á·áá»ááẠnode áá
áºáá¯áá±á«áºá¡ááŒá±áá¶á á
á
áºáá¯ááºááŸá¯á¡ááŒá±á¡áá±áá
áºáá¯ááŸáá·áºá¡áá° áá±á«ááºážá
ááºáá¯ááºáá±á¬ááºáá»ááºááᯠá¡áá¯á¶ážááŒá¯áá«-
WITH RECURSIVE T AS (
SELECT
array_agg(id) id$
, array_agg(id) FILTER(WHERE branch) ns$
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
X.*
FROM
T
JOIN LATERAL (
SELECT
array_agg(id) id$
, array_agg(id) FILTER(WHERE branch) ns$
FROM
hier
WHERE
pid = ANY(T.ns$)
) X
ON coalesce(T.ns$, '{}') <> '{}'
)
SELECT
unnest(id$) id
FROM
T;
áá»áœááºá¯ááºááá¯á·ááẠáá±á¬ááºááẠá¡ááœáŸááºážáá±á«áºááá¯ááŸá¯áá áºáá¯ááᯠáá»áŸá±á¬á·áá»ááá¯ááºáá²á·áááºá Volume ááŸá¬ 2 ááŒáááºáááºááá¯á¡ááá¯ááºáááŸááá²á·áá«áááºá á á áºááá¯á·á
#áá á¡ááŒá áºááá¯ááŒááºááœá¬ážáá¡á±á¬ááº
áááºááá·áºá¡áááºážá¡ááŒá áºá á¬ááœáẠ(ááŸáá·áºáááºááá·áºá¡ááœáŸááºážááááºážáá»á¬ážááŒáá·áº) ááá¯ááá°áá¬ááœááºááá·áºááœááºážá á±ááááºáž áá°áá±á¬á¡áá»ááºá¡áááºááá¯áááºáááºááááºážááááºážáá¬ážá ááºááœááºááŒááºá ááºá¡á¬ážáá¯á¶ážá¡ááœááºááŸááºáááºážáá»á¬ážááá¯á á¯áá±á¬ááºážáááºááá¯á¡ááºáá«áဠalgorithm áááºá¡áá¯á¶ážáááºáááá·áºááẠ- á¥ááᬠ- á¡áá»ááºážáá»á¯ááºá¡á á®áááºáá¶á á¬ááá¯áááºáá®ážááẠnode áá»á¬ážáá²ááá¯á· áá±á«ááºážá ááºážááŒááºážááŒáá·áº
áá±á¬ááºážááá¯áá»ááºááẠá¡ááœááºáááºáá²áááºáᯠáááºááá±á¬ááŒá±á¬áá·áº á¡á±á¬ááºáá«á¡áá»ááºááᯠá¡áá°á¡á áááºáá±á¡ááŒá
áºáᬠáá°ááá·áºáááºá áá«áá±ááá·áº ááá·áºáá±áá¬áá±á·á
áºááᯠááœáŸááºážááá¯ážáá¬ážáááºááá¯ááẠá¡áá¬ážáá°áááºážáááºážááœá±ááᯠá¡áá¯á¶ážááŒá¯ááá¯á· á
ááºážá
á¬ážááá·áºáá«áááºá
ááá¯ážááŸááºážáá±á¬áá±á¬áºááŒáá»ááºá¡áá»áá¯á·ááŒáá·áº á áááºááŒáá«á áá¯á·á
- áá±áá¬áá±á·á áºááŸáá°áá®áá±á¬ááŸááºáááºáž áá áºááŒáááºáá²áááºáᬠá¡áá±á¬ááºážáá¯á¶ážáá«áá²á.
- áá±áá¬áá±á·á áºááŸááŸááºáááºážáá»á¬áž á¡ááœá²ááá¯ááºáááºááᬠááá¯áááá±á¬ááºáááºááá áºáá±á¬ááºáááºážáááºá
ááá¯áá»áœááºá¯ááºááá¯á·ááá¯á¡ááºáá±á¬áá±á¬ááºážááá¯áá»ááºááá¯áááºáá±á¬ááºáááºááŒáá¯ážá á¬ážááŒáá«á áá¯á·á
ááŒá±ááŸááºáž 1
áááºááŸá¬ážáááºááŸá¬á recursion ááᯠá¡á ááŒá¯áá±á¬á¡áá« (ááá¯á¡áá¬áááŸááá»áŸáẠáááºááŸá¬ááŸááááºáááºáž)á áááŠážáááºááŸááºáááºááŸááºááŸá¯á¡á á¯á¡áá±á«áºá¡ááŒá±áá¶á á¡ááœááºáá»á¬ážáááŸááºáááºážáá»á¬ážááᯠáááºážááá¯á·ááá¯ááºááá¯ááºáá¯ááºáá°ááááºááŒá áºáá«áááºá
WITH RECURSIVE tree AS (
SELECT
rec -- ÑÑП ÑелÑÐœÐ°Ñ Ð·Ð°Ð¿ÐžÑÑ ÑаблОÑÑ
, id::text chld -- ÑÑП "МабПÑ" пÑОвеЎÑОÑ
ÑÑЎа ОÑÑ
ПЎМÑÑ
лОÑÑÑев
FROM
hier rec
WHERE
id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
...
"set" ááᯠarray áá
áºáá¯ááá¯ááºáá² string áá
áºáá¯á¡ááŒá
Ạááááºážáááºážáá¬ážááá·áº áá
áºá
á¯á¶áá
áºáŠážá¡ááœáẠáá°ážáááºážáááºáᯠáááºáá«áá á€á¡ááœáẠááá¯ážááŸááºážáá±á¬ ááŸááºážáááºážáá»ááºáá
áºáᯠááŸááá«áááºá strings áá»á¬ážá¡ááœáẠbuilt-in "gluing" áá¯ááºáá±á¬ááºáá»ááºááŸááááºá string_agg
áá«áá±ááá·áº arrays á¡ááœáẠááá¯ááºáá«áá°ážá áá«âáá±áá²á· áá°á
ááŒá±ááŸááºáž 2
ááᯠáááºáááºáááºááŸá¯ááẠááá¯á¡ááºááá·áº ááá¹á ID á¡á á¯á¡áá±ážáá áºáá¯ááᯠáá»áœááºá¯ááºááá¯á· áááŸááááºááŒá áºáááºá áááºážááá¯á·ááᯠáá°áááºážáááºááŸááºááŸá¯á ááá°áá®áá±á¬ ááŸááºáááºážáá»á¬ážááœáẠá¡ááŒá²ááá¯ááᯠááœá¬ážáá±áááá·áºááẠ- ááá¯á·ááŒá±á¬áá·áº áá»áœááºá¯ááºááá¯á·ááẠáá°ááá¯á·ááᯠá¡á¯ááºá á¯ááá¯ááºá¡áááºážá¡ááŒá áºá¡ááœááºá¡ááŒá±á¬ááºážá¡áá»ááºá¡áááºááá¯ááááºážááááºážá á±á¬áá·áºááŸá±á¬ááºáá±á ááºá
ááá¯á·áá±á¬áº á€ááœáẠááŒá¿áá¬áá¯á¶ážáá¯á áá»áœááºá¯ááºááá¯á·ááᯠá á±á¬áá·áºááŒáá¯áá±áá«áááºá
- áá±ážááŒááºážááŸá¯á "subrecursive" á¡ááá¯ááºážááœáẠáá±á«ááºážá
ááºáá¯ááºáá±á¬ááºáá»ááºáá»á¬áž ááá«áááºááá¯ááºáá«á
GROUP BY
. - áááºáá«ááá²áá² "ááá¬áž" ááᯠááá¯ážáá¬ážááŒááºážááẠnested subquery áá áºáá¯ááœáẠáááŒá áºááá¯ááºáá«á
- ááŒááºáá±á¬ááºááá·áºá¡ááá¯ááºážááŸá áá±á¬ááºážááá¯áá»ááºáá áºáá¯ááœáẠCTE ááá«áááºááá¯ááºáá«á
áá¶áá±á¬ááºážáá±á¬ááºáá áœá¬á á€ááŒá¿áá¬áá»á¬ážá¡á¬ážáá¯á¶ážáááºááẠá ááºážáá»ááºááœááºáá¯ááºáá±á¬ááºáááºá¡ááœááºááœááºáá°áááºá á¡áá¯á¶ážááá± á ááá¯ááºáá¡á±á¬ááºá
CTE ááẠrecursive á¡ááá¯ááºážááŒá áºáááºá
á€áá²á·ááá¯á·áá±á¬ ááá¯áẠá¡áá¯ááºáá¯ááºáááº-
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)
ááá¯á·ááŒá±á¬áá·áº áááºážááẠá¡áá¯ááºááŒá áºáááºá ááœááºážá á¥áºáá»á¬ážááẠááŒá¬ážáá¬ážááŸá¯ááᯠááŒá áºá á±áááºá
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)
áááºáá°áá»áá±á¬ "ááá¬áž" ááᯠááá·áºáá»ááºááá·áº á¡á á¯á¡áá±ážáá±ážááŒááºážáá»ááº
áááºáž... áááºáá«ááá²áá² CTE ááᯠá á¯á¶á ááºážáá±ážááŒááºážááŸá¯áá áºáá¯ááœáẠáááºáá±á¬ááºááááá«á áá«áá±ááá·áº CTE áá²ááŸá¬ ááŒá áºááá¯ááºáááºá ááŸáá·áº á¡á á¯á¡áá±ážáá±á¬ááºážááá¯ááŸá¯áá áºáá¯ááẠဠCTE ááᯠá¡áá¯á¶ážááŒá¯ááŒá®ážááŒá áºáááºá
ááŒááºááŸáá·áºááŒááºážá¡ááœááºážá á¡á¯ááºá á¯ááœá²á·áá«á
á¡áááºáááŒá±áá«áá°ážá áá«áá±ááá·áº... á¡áá¯á¶ážááŒá¯ááŒááºážááŒáá·áº GROUP ááᯠââá¡áá¯áá°ááẠááá¯ážááŸááºážáá±á¬áááºážáááºážáá
áºáá¯ááŸááááºá DISTINCT ON
ááŸáá·áº window functions áá»á¬ážá
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- Ме ÑабПÑаеÑ!
áá«á áááºááá¯á¡áá¯ááºáá¯ááºáá²á
SELECT DISTINCT ON((rec).pid)
(rec).pid id
, string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
ááᯠáá»áœááºá¯ááºááá¯á·ááẠááááºážááááºáž ID ááᯠá¡áááºááŒá±á¬áá·áº á á¬áá¬ážá¡ááŒá áºááá¯á· ááŒá±á¬ááºážáá²áá¬ážáááºááᯠááœá±á·áááẠ- áááºážááá¯á·ááᯠáá±á¬áºáá¬áá»á¬ážááŒáá·áº ááœá²á á¡áá°ááᜠáá±á«ááºážááá¯ááºá á±áááºá
ááŒá±ááŸááºáž 3
ááá¯ááºáááºá¡ááœáẠáá»áœááºáá±á¬áºááá¯á· áá¬á០ááá»ááºáá±á¬á·áá«á
- á¡á¯ááºá á¯ááœá²á· ID á¡á á¯á¶á¡áá±á«áº á¡ááŒá±áá¶á "ááá¹á" ááŸááºáááºážáá»á¬ážááᯠáá»áœááºá¯ááºááá¯á· áááºáá«áááºá
- áá°áááºážá á¬ááœááºáá»á¬ážá "á¡á á¯á¶" ááŸáá·áº áá¯ááºáá¬ážáá±á¬á¡ááá¯ááºážáá»á¬ážááᯠááŸáá¯ááºážááŸááºáá«áááºá
- set-string ááá¯áá¯á¶ážááŒá®áž "áá»á²á·"
unnest(string_to_array(chld, ',')::integer[])
WITH RECURSIVE tree AS (
SELECT
rec
, id::text chld
FROM
hier rec
WHERE
id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
(
WITH prnt AS (
SELECT DISTINCT ON((rec).pid)
(rec).pid id
, string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
)
, nodes AS (
SELECT
rec
FROM
hier rec
WHERE
id = ANY(ARRAY(
SELECT
id
FROM
prnt
))
)
SELECT
nodes.rec
, prnt.chld
FROM
prnt
JOIN
nodes
ON (nodes.rec).id = prnt.id
)
)
SELECT
unnest(string_to_array(chld, ',')::integer[]) leaf
, (rec).*
FROM
tree;
source: www.habr.com