ืืื ืงืึธืืคึผืืขืงืก ืขืจืคึผ ืกืืกืืขืืขื ืคืืืข ืขื ืืืืื ืืึธืื ืึท ืืืืขืจืึทืจืงืึทืงืึทื ื ืึทืืืจืืืขื ืืึธืืืึทืืืฉืื ืืึทืก ืึทืืืืฉืขืงืฅ ืฉืืจื ืึทืจืืืฃ ืืื ืืืื ืคืื ืึธืืืขืก-ืึธืคึผืฉืืึทืืืื ื ืืึทืฆืืื ืืขื - ืืึธืก ืืื ืื ืึธืจืืึทื ืึทืืืืฉืึทื ืึทื ืกืืจืืงืืืจ ืคืื ืื ืคืึทืจื ืขืืื ื (ืึทืืข ืื ืฆืืืืืื, ืืืคึผืึทืจืืืึทื ืฅ ืืื ืึทืจืืขื ืืจืืคึผืขืก), ืืื ืืขืจ ืงืึทืืึทืืึธื ืคืื ืกืืืืจืข ืืื ืึทืจืืขื ืืขืืืื, ืืื ืื ืืขืึธืืจืึทืคื ืคืื ืคืืจืงืืืคืื ื ืคืื ืงืื, ...
ืืื ืคืึทืงื, ืขืก ืืื ืืึธืจื ืื , ืืื ืขืก ืืืึธืื ื ืืฉื ืืืื ืงืืื ืืืืขืจืึทืจืงื ืืื ืึท ืจืขืืืืืึทื. ืึธืืขืจ ืืคืืื ืืืื ืืืจ ืืึธื ื ืื ืึทืจืืขื "ืคึฟืึทืจ ืื ืืขืฉืขืคื," ืืืจ ืงืขื ืขื ื ืึธื ืืืืื ืืจืขืคื ืืืืขืจืึทืจืงืึทืงืึทื ืืืฆืืื ืืขื. ืขืก ืืื ืคึผืฉืื, ืืคืืื ืืืื ืืฉืคึผืื ืืืื ืึธืืขืจ ืฉืืึธืง ืคึผืืึทื ืคืื ืืึธืงืึทื ืืื ืึท ืฉืึทืคึผืื ื ืฆืขื ืืขืจ ืืื ืื ืืขืืืข ืกืืจืืงืืืจ.
ืขืก ืืขื ืขื ืคืืืข ืืืขืื ืฆื ืงืจืึธื ืึทืืึท ืึท ืืืื ืืื ืึท DBMS, ืึธืืขืจ ืืืึทื ื ืืืจ ืืืขืื ืคืึธืงืืก ืืืืฃ ืืืืื ืืืื ืึธืคึผืฆืืข:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- ะฝะต ะทะฐะฑัะฒะฐะตะผ, ััะพ FK ะฝะต ะฟะพะดัะฐะทัะผะตะฒะฐะตั ะฐะฒัะพัะพะทะดะฐะฝะธะต ะธะฝะดะตะบัะฐ, ะฒ ะพัะปะธัะธะต ะพั PK
ืืื ืืฉืขืช ืืืจ ืงืืง ืืื ืื ืืืคืขื ืืฉื ืคืื ืืขืจ ืืืืขืจืึทืจืงื, ืขืก ืืื ืืขืืืืืืง ืืืืจืื ืฆื ืืขื ืืื [ืืื] ืขืคืขืงืืืื ืืืื "ื ืึทืืื" ืืืขืื ืคืื ืืจืืขืื ืืื ืึทืืึท ืึท ืกืืจืืงืืืจ ืืืขื ืืืื.
ืืื ืก ืงืืง ืืื ืืืคึผืืฉ ืคึผืจืึธืืืขืืก ืืืึธืก ืฉืืืืขื, ืืืืขืจ ืืืคึผืืึทืืขื ืืืืฉืึทื ืืื SQL, ืืื ืคึผืจืืืืจื ืฆื ืคึฟืึทืจืืขืกืขืจื ืืืืขืจ ืคืึธืจืฉืืขืืื ื.
#1. ืืื ืืืฃ ืืื ืื ืงืื ืืื ืืึธื?
ืืึธืืืจ, ืคึฟืึทืจ ืืืืขืจืงืืื, ืึธื ื ืขืืขื, ืึทื ืื ืืึธืืืงืข ืกืืจืืงืืืจ ืืืขื ืึธืคึผืฉืคึผืืืืขื ืื ืืื ืืขืจื ืขืืื ื ืคึฟืื ืื ืึธืคึผืืืืื ืืื ืืขืจ ืกืืจืืงืืืจ ืคึฟืื ืืขืจ ืึธืจืืึทื ืืืึทืฆืืข: ืึธืคึผืืืืื, ืึธืคึผืืืืื, ืกืขืงืืึธืจื, ืฆืืืืืื, ืึทืจืืขืึพืืจืืคึผืขืก... โ ืืืขืืืขืจ ืืืจ ืจืืคึฟื ืืื.
ืขืจืฉืืขืจ, ืืึธืื ืืื ืื ืืืฉืขื ืขืจืืื ืืื ืืืขืจ 'ืืืื' ืคืื 10 ืง ืขืืขืืขื ืื
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;ืืึธืืืจ ืึธื ืืืืื ืืื ืื ืกืืืคึผืืึทืกื ืึทืจืืขื - ืืขืคึฟืื ืขื ืึทืืข ืขืืคึผืืืืื ืืืึธืก ืึทืจืืขื ืืื ืึท ืกืคึผืขืฆืืคืืฉ ืกืขืงืืึธืจ, ืึธืืขืจ ืืื ืืขืจืืื ืขื ืคืื ืืืืขืจืึทืจืงื - ืืขืคึฟืื ืขื ืึทืืข ืงืื ืืขืจ ืคืื ืึท ื ืึธืืข. ืขืก ืืืึธืื ืืืื ืืขืืืขื ืคืืึทื ืฆื ืืึทืงืืืขื ืื "ืืืคืงืืึทื" ืคืื ืื ืึธืคึผืฉืืึทืืืื ื ... ืืึธืก ืึทืืฅ ืงืขื ืืืื ื ืืืืืง, ืืืฉื, ืฆื ืืืืขื ืึท ืืื ืคืื .
ืึทืืฅ ืืืึธืื ืืืื ืืื ืืืื ืขืก ืืขื ืขื ืืืืื ืึท ืคึผืึธืจ ืคืื ืืขืืืขืืก ืคืื ืื ืงืื ืืกืงืื ืืขืจ ืืื ืื ื ืืืขืจ ืืื ืื ืึท ืืืฅ, ืึธืืขืจ ืืืื ืขืก ืืขื ืขื ืืขืจ ืืื 5 ืืขืืืขืืก, ืืื ืขืก ืืขื ืขื ืฉืืื ืืึทืืึทื ื ืคืื ืงืื ืืกืงืื ืืขืจ, ืขืก ืงืขื ืืืื ืคึผืจืึธืืืขืืก. ืืื ืก ืงืืง ืืื ืืื ืืจืืืืฆืืื ืขืื ืึทืจืึธืคึผ-ืืืื ืืืื ืึธืคึผืฆืืขืก ืืขื ืขื ืืขืฉืจืืื (ืืื ืึทืจืืขื). ืึธืืขืจ ืขืจืฉืืขืจ, ืืึธืื ืก ืืึทืฉืืืกื ืืืึธืก ื ืึธืืื ืืืขื ืืืื ืื ืืขืจืกื ืืฉืืงืึทืืืข ืคึฟืึทืจ ืืื ืืืขืจ ืคืึธืจืฉืื ื.
Most "ืืืฃ" ืกืืืืจืืื:
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}
...Most "ืืจืืื" ืกืืืืจืืื:
...
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 ืึธื ืคึฟืจืขืื ืืื ืื ืืื ืืขืงืก. ืืื ืขืก ืืขืืืขื ืฆื ืืึธื ืืืืื ืืงืขืจ?
ืคืึทืจื ืขื ืคึผืจืืจืขืึทืืื ื ืืืจื ืืื ืืขืงืก
ืฆื ืืืจ ืืึทืจืคึฟื ืฆื ืืึทืื ืึท ืืึทืืื ืืขืจ ืืื ืืขืงืก ืึธื ืคึฟืจืขื ืคึฟืึทืจ ืืขืืขืจ ื ืึธืืข? ืขืก ืืืจื ืก ืืืืก ืึทื ื ืื - ืืืจ ืงืขื ืขื ืืืืขื ืขื ืคืื ืื ืืื ืืขืงืก ื ืืฆื ืขืืืขืืข ืฉืืืกืืขื ืืื ืึทืืึธื ืืื ืืืื ืจืืคื ืืืจื = ANY(array).
ืืื ืืื ืืขืืขืจ ืึทืืึท ืืจืืคึผืข ืคืื โโืืืขื ืืืคืืขืจืก ืืืจ ืงืขื ืขื ื ืขืืขื ืึทืืข ืื IDs ืืขืคึฟืื ืขื ืืื ืื ืคืจืืขืจืืืงืข ืฉืจืื ืืืจื "ื ืึธืืขืก". ืึทื ืืื, ืืื ืืขืืขืจ ืืืืึทืืขืจ ืฉืจืื ืืืจ ืืืขืื ืืืื ืคึฟืึทืจ ืึทืืข ืงืื ืืกืงืื ืืขืจ ืคืื ืึท ืืืืขืจ ืืืจืื ืืื ืึทืืึธื.
ื ืึธืจ ืืึธ ืืื ืื ืคึผืจืึธืืืขื, ืืื ืจืขืงืืจืกืืืืข ืกืขืืขืงืฆืืข, ืืืจ ืงืขื ืขื ื ืืฉื ืึทืงืกืขืก ืืื ืืื ืึท ื ืขืกืืขื ืึธื ืคึฟืจืขื, ืืืขืจ ืืืจ ืืืจืคื ืขืคืขืก ืืืืกืงืืืืื ื ืืจ ืืืืก ืืขื ืืื ืืขืคืื ืขื ืืืืคื ืคืจืืขืจืืืื ืฉืืืคื... ืขืก ืงืืื ืืืืก ืื ืขืก ืืื ืืืืืขืืืื ืฆื ืืืื ื ื ืขืกืืขื ืื ืคืจืืืข ืคืืจ ืื ืืื ืฆืข ืกืขืืขืงืฆืืข, ืืืขืจ ืคืืจ ืืืจ ืกืคืขืฆืืคืืฉื ืคืขืื ืืื ืขืก ืืขืืืื. ืืื ืืึธืก ืคืขืื ืงืขื ืืืื ืืืื ืึท ืืขื ืืข - ืืืึธืก ืืื ืืืึธืก ืืืจ ืืึทืจืคึฟื ืฆื ื ืืฆื 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 ืจืืคื ืฆื ืื ืืื ืืขืงืก ืึทื ืฉืืึธื ืคืื 30!
ืึทื ื ืึธื ืืึธื ืืก ืืื ืืขืจ ืคืึทืงื ืึทื ื ืึธื ืื ืืขืฆื ืืื ืึทืกื, ืื ืืืขื ืืืคืืขืจืก ืืืขืื ืืืืืื ืึธืจืืขืจื ืืืจื "ืืขืืืขืืก".
ื ืึธืืข ืฆืืืื
ืืขืจ ืืืืึทืืขืจ ืืึทืืจืึทืืืื ื ืืืึธืก ืืืขื ืืขืืคึฟื ืคึฟืึทืจืืขืกืขืจื ืคืึธืจืฉืืขืืื ื ืืื - "ืืืขืืขืจ" ืงืขื ืขื ื ืืฉื ืืึธืื ืงืื ืืขืจ, ืืึธืก ืืืืกื, ืคืึทืจ ืืื ืืึทืจืฃ ืืขื ืืึธืจ ื ืื ืงืืงื โืึทืจืึธืคึผโ. ืืื ืืขืจ ืคืึธืจืืืจืื ื ืคืื ืืื ืืืขืจ ืึทืจืืขื, ืืึธืก ืืืื ืึทื ืืืื ืืืจ ื ืึธืืคืึธืืื ืื ืงืืื ืคืื ืืืคึผืึทืจืืืึทื ืฅ ืืื ืืขืจืืจืืืื ืึทื ืึธื ืืขืฉืืขืืืขืจ, ืขืก ืืื ื ืื ืืึทืจืคึฟื ืฆื ืงืืงื ืืืืึทืืขืจ ืฆืืืืืขื ืืขื ืฆืืืืึทื.
ืืึธืืืจ ืึทืจืฒึทื ืืื ืืื ืืืขืจ ืืืฉ ื ืึธื boolean-ืคืขืื, ืืืึธืก ืืืขื ืืื ืื ืืืืื ืืึธืื ืฆื ืืขืจ ืืึทืืื ืืขืจ ืคึผืึธืืืฆืืข ืืื ืืื ืืืขืจ ืืืื ืืื ืึท "ื ืึธืืข" - ืืึธืก ืืื, ืฆื ืขืก ืงืขื ืืึธืื ืงืื ืืกืงืื ืืขืจ ืืื ืึทืืข.
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% ืคืื ืึทืืข ืืืื ืขืืขืืขื ืื ืืึธืื ืงืื ืืกืงืื ืืขืจ.
ืืืฆื ืืึธืื ืก ื ืืฆื ืึท ืืืกื ืึทื ืืขืจืฉ ืืขืืึทื ืืงืขืจ - ืงืึทื ืขืงืฉืึทื ื ืฆื ืื ืจืขืงืืจืกืืืืข ืืืื ืืืจื LATERAL, ืืืึธืก ืืืขื ืืึธืื ืืื ืื ืืืืื ืึทืงืกืขืก ืื ืคืขืืืขืจ ืคืื ืื ืจืขืงืืจืกืืืืข "ืืืฉ" ืืื ื ืืฆื ืึท ืืขืืืื ืืึทื ืคืื ืงืฆืืข ืืื ืึท ืคึฟืืืืจืืจืื ื ืฆืืฉืืึทื ื ืืืืืจื ืืืืฃ ืึท ื ืึธืืข ืฆื ืจืขืืืฆืืจื ืื ืืึทื ื ืคืื ืฉืืืกืืขื:
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;
ืืืจ ืืขื ืขื ืืืืืืช ืฆื ืจืขืืืฆืืจื ืืืื ืขืจ ืืขืจ ืืื ืืขืงืก ืจืืคื ืืื ืืืึทื ืืขืจ ืืื 2 ืืื ืืื ืืึทื ื ืงืึธืจืขืงืืึธืจ.
#2. ืืืืืจ ืฆืืจืืง ืืืื ืฆื ืื ืืืืจืฆืืขื
ืืขืจ ืึทืืืขืจืืืึทื ืืืขื ืืืื ื ืืฆืืง ืืืื ืืืจ ืืึทืจืคึฟื ืฆื ืืึทืืืขื ืจืขืงืึธืจืืก ืคึฟืึทืจ ืึทืืข ืขืืขืืขื ืื "ืึทืจืืืฃ ืืขื ืืืื", ืืฉืขืช ืืืจ ืืึทืืื ืืื ืคึฟืึธืจืืึทืฆืืข ืืืขืื ืืืึธืก ืืงืืจ ืืืึทื (ืืื ืืื ืืืึธืก ืื ืืืงืึทืืึธืจืก) ืืขืคึฟืืจื ืขืก ืฆื ืืืื ืึทืจืืึทื ืืขืจืขืื ื ืืื ืืขืจ ืืืกืืขืจ - ืืืฉื, ืฆื ืืืฉืขื ืขืจืืื ืึท ืงืืฆืขืจ ืืึทืจืืื. ืืื ืึทืืืจืขืืึทืืืึธื ืืื ื ืึธืืื.
ืืืก ืืืืก ืืื ืืืืืืขืจ ืืืจืฃ ืืขื ืื ื ืขืืขื ื ืืจ ืืืก ื ืืืืืืื, ืืืืื ืื ืืงืฉื ืืื ืืืืขืจ ืฉืืืขืจ. ืึธืืขืจ ืืืื ืขืก ืืึทืืึทื ืืืฅ ืืืื ืืึทืืึทืืืืก, ืืืจ ืืึธื ืืจืึทืืื ืืืขืื ื ืืฆื ืขื ืืขื ืืขืงื ืืงืก.
ืืื ืก ืึธื ืืืืื ืืื ืึท ืคึผืึธืจ ืคืื ืคึผืฉืื ืกืืืืืืึทื ืฅ:
- ืืขืจ ืืขืืืืงืขืจ ืจืขืงืึธืจื ืคืื ืื ืืึทืืึทืืืืก ืขืก ืืื ืืขืกืืขืจ ืฆื ืืืืขื ืขื ืขืก ื ืึธืจ ืึทืืึธื.
- ืจืขืงืึธืจืืก ืคืื ืื ืืึทืืึทืืืืก ืขืก ืืื ืืขืจ ืขืคืขืงืืืื ืฆื ืืืืขื ืขื ืืื ืืึทืืฉืึทืืืื ืึทืืืื.
ืืืฆื ืืึธืืืจ ืคึผืจืืืืจื ืฆื ืืืืขื ืื ืืงืฉื ืืืึธืก ืืืจ ืืึทืจืคึฟื.
ืฉืจืื ืงืกื ืืืงืก
ืืึธื, ืืืขื ืื ืืืืึทืืืืื ื ืจืขืงืืจืกืืึธื (ืืื ืืืึธืื ืืืจ ืืืื ืึธื ืขืก!) ืืืจ ืืืขืื ืืึธืื ืฆื ืึทืจืึธืคึผืจืขืืขื ืขื ืื ืจืขืงืึธืจืืก ืคืื ืื ืืืขืืขืจ ืืื ืืืืืจื ืืืืฃ ืื ืืึทื ื ืคืื ืขืจืฉื ืืืขื ืืืคืืขืจืก:
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
...
ืืืื ืขืืขืฆืขืจ ืืขืืึทื ืง ืขืก ืืื ืืึธืื ืข ืึทื ืื "ืฉืืขืื" ืืื ืกืืึธืจื ืืื ืึท ืฉืืจืืงื ืืื ื ืืฉื ืึท ืืขื ืืข, ืขืก ืืื ืึท ืคึผืฉืื ืืขืจืงืืขืจืื ื ืคึฟืึทืจ ืืขื. ืขืก ืืื ืึท ืื ืึทืืจืืืืื "ืืืืื ื" ืคืื ืงืฆืืข ืคึฟืึทืจ ืกืืจืื ืืก string_agg, ืึธืืขืจ ื ืืฉื ืคึฟืึทืจ ืขืจืืื. ืืึธืืฉ ืื .
ืฉืจืื ืงืกื ืืืงืก
ืืืฆื ืืืจ ืืืึธืื ืืึทืงืืืขื ืึท ืกืืื ืคืื ืึธืคึผืืืืืื ื IDs ืืืึธืก ืืืขื ืืึทืจืคึฟื ืฆื ืืืืขื ืขื ืืืืึทืืขืจ. ืึผืืขื ืฉืืขื ืืืง ืืื ืืืขืื ืืืื ืืืคึผืืืงืืืืื ืืื ืคืึทืจืฉืืืขื ืข ืจืขืงืึธืจืืก ืคืื ืืขืจ ืึธืจืืืื ืขื ืฉืืขืื - ืึทืืื ืืืจ ืืืึธืื ืืจืืคึผืข ืืื, ืืฉืขืช ืคึผืจืึทืืขืจืืืื ื ืืื ืคึฟืึธืจืืึทืฆืืข ืืืขืื ืื ืืงืืจ ืืืขืืขืจ.
ืืืขืจ ืื ืืืืจืื ืืืืฃ ืืื ื ืืจืื ืฆืจืืช:
- ืืขืจ "ืกืืืจืขืงืืจืกืืืืข" ืืืื ืคืื ืื ืึธื ืคึฟืจืขื ืงืขื ื ืืฉื ืึทื ืืืึทืืื ืืขืืืื ืืึทื ืคืึทื ืืงืฉืึทื ื ืืื
GROUP BY. - ื ืจืขืคึฟืขืจืขื ืฅ ืฆื ืึท ืจืขืงืืจืกืืืืข "ืืืฉ" ืงืขื ืขื ื ืื ืืืื ืงืึทื ืืืื ื ืืื ืึท ื ืขืกืืขื ืกืึทืืงืืืขืจื.
- ื ืืงืฉื ืืื ืื ืจืขืงืืจืกืืืืข ืืืื ืงืขื ื ืืฉื ืึทื ืืืึทืืื ืึท CTE.
ืฆืื ืืืืง, ืึทืืข ืื ืคืจืืืืขืืขื ืืขื ืขื ืืึทื ืฅ ืืจืื ื ืฆื ืึทืจืืขืื ืึทืจืื. ืืื ืก ืึธื ืืืืื ืคืื ืื ืกืืฃ.
CTE ืืื ืจืขืงืืจืกืืืืข ืืืื
ืึทืืื ืงืืื ืืจืืขืื:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)ืืื ืึทืืื ืขืก ืึทืจืืขื, ืื ืงืืึทืืขืจื ืืึทืื ืื ืืืืืง!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)ื ืขืกืืขื ืึธื ืคึฟืจืขื ืงืขืื ืึท ืจืขืงืืจืกืืืืข "ืืืฉ"
ืืื... ื ืจืขืงืืจืกืืืืข CTE ืงืขื ืขื ื ืื ืืืื ืึทืงืกืขืกื ืืื ืึท ืกืึทืืงืืืขืจื. ืึธืืขืจ ืขืก ืงืขื ืืืื ืืื CTE! ืืื ืึท ื ืขืกืืขื ืืขืื ืงืขื ืขื ืฉืืื ืึทืงืกืขืก ืืขื CTE!
GROUP BY ืื ืจืขืงืืจืกืืึธื
ืขืก ืืื ืคึผืจืืงืจืข, ืึธืืขืจ ... ืืืจ ืืึธืื ืึท ืคึผืฉืื ืืืขื ืฆื ืขืืืึทืืืื GROUP BY ื ืืฆื DISTINCT ON ืืื ืคึฟืขื ืฆืืขืจ ืคืึทื ืืงืฉืึทื ื!
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ืืืฆื ืืืจ ืืขื ืืืึธืก ืื ื ืืืขืจืืง ืฉืืึทื ืืื ืคืืจืืืื ืืืขื ืืื ืืขืงืกื - ืึทืืื ืึทื ืืื ืงืขื ืขื ืืืื ืืืฉืืื ื ืฆืืืึทืืขื ืืคืืขืฉืืืื ืืืจื ืงืึธืืืึทืก!
ืฉืจืื ืงืกื ืืืงืก
ืคึฟืึทืจ ืื ืืขืฆื ืืืจ ืืึธืื ืืึธืจื ืืฉื ืืื ืงืก:
- ืืืจ ืืืืขื ืขื "ืึธืคึผืืืืืื ื" ืจืขืงืึธืจืืก ืืืืืจื ืืืืฃ ืึท ืืึทื ื ืคืื ืืจืืคึผื IDs
- ืืืจ ืคืึทืจืืืืึทืื ืื ืกืึทืืืจืึทืงืืื ืกืขืงืฉืึทื ื ืืื ืื "ืกืขืฅ" ืคืื ืืขืจ ืึธืจืืืื ืขื ืฉืืฅ
- "ืืจืืืืขืจื" ืื ืฉืืขืื-ืฉืืจืืงื ื ืืฆื
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;
ืืงืืจ: www.habr.com