āĻāĻāĻŋāĻ˛ āĻāĻāĻ°āĻĒāĻŋ āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽā§ āĻ āĻ¨ā§āĻ āĻ¸āĻ¤ā§āĻ¤ā§āĻŦāĻž āĻāĻāĻāĻŋ āĻļā§āĻ°ā§āĻŖāĻŋāĻŦāĻĻā§āĻ§ āĻĒā§āĻ°āĻā§āĻ¤āĻŋ āĻāĻā§āĻ¯āĻāĻ¨ āĻ¸āĻŽāĻāĻžāĻ¤ā§āĻ¯āĻŧ āĻŦāĻ¸ā§āĻ¤ā§āĻā§āĻ˛āĻŋ āĻ¸āĻžāĻ°āĻŋāĻŦāĻĻā§āĻ§ āĻšāĻ¯āĻŧ āĻĒā§āĻ°ā§āĻŦāĻĒā§āĻ°ā§āĻˇ-āĻŦāĻāĻļā§āĻ¯āĻŧ āĻ¸āĻŽā§āĻĒāĻ°ā§āĻā§āĻ° āĻāĻžāĻ - āĻāĻāĻŋ āĻāĻ¨ā§āĻāĻžāĻ°āĻĒā§āĻ°āĻžāĻāĻā§āĻ° āĻ¸āĻžāĻāĻāĻ āĻ¨āĻŋāĻ āĻāĻžāĻ āĻžāĻŽā§ (āĻāĻ āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻļāĻžāĻāĻž, āĻŦāĻŋāĻāĻžāĻ āĻāĻŦāĻ āĻāĻžāĻā§āĻ° āĻā§āĻ°ā§āĻĒ), āĻāĻŦāĻ āĻĒāĻŖā§āĻ¯ā§āĻ° āĻā§āĻ¯āĻžāĻāĻžāĻ˛āĻ, āĻāĻŦāĻ āĻāĻžāĻā§āĻ° āĻā§āĻˇā§āĻ¤ā§āĻ° āĻāĻŦāĻ āĻŦāĻŋāĻā§āĻ°āĻ¯āĻŧ āĻĒāĻ¯āĻŧā§āĻ¨ā§āĻāĻā§āĻ˛āĻŋāĻ° āĻā§āĻā§āĻ˛, ...
āĻāĻ¸āĻ˛ā§, āĻ¸ā§āĻāĻžāĻ¨ā§ āĻā§āĻ āĻ¨ā§āĻ
āĻĄāĻŋāĻŦāĻŋāĻāĻŽāĻāĻ¸ā§ āĻāĻ āĻāĻžāĻ¤ā§āĻ¯āĻŧ āĻāĻžāĻ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻžāĻ° āĻ āĻ¨ā§āĻ āĻāĻĒāĻžāĻ¯āĻŧ āĻ°āĻ¯āĻŧā§āĻā§, āĻ¤āĻŦā§ āĻāĻ āĻāĻŽāĻ°āĻž āĻā§āĻŦāĻ˛ āĻāĻāĻāĻŋ āĻŦāĻŋāĻāĻ˛ā§āĻĒā§āĻ° āĻāĻĒāĻ° āĻĢā§āĻāĻžāĻ¸ āĻāĻ°āĻŦ:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- ĐŊĐĩ СайŅваĐĩĐŧ, ŅŅĐž FK ĐŊĐĩ ĐŋОдŅаСŅĐŧĐĩваĐĩŅ авŅĐžŅОСдаĐŊиĐĩ иĐŊĐ´ĐĩĐēŅĐ°, в ĐžŅĐģиŅиĐĩ ĐžŅ PK
āĻāĻŦāĻ āĻāĻĒāĻ¨āĻŋ āĻ¯āĻāĻ¨ āĻļā§āĻ°ā§āĻŖāĻŋāĻŦāĻŋāĻ¨ā§āĻ¯āĻžāĻ¸ā§āĻ° āĻāĻā§āĻ°āĻ¤āĻžāĻ° āĻŽāĻ§ā§āĻ¯ā§ āĻāĻāĻāĻŋ āĻĻāĻŋāĻā§āĻā§āĻ¨, āĻ¤āĻāĻ¨ āĻ§ā§āĻ°ā§āĻ¯ āĻ¸āĻšāĻāĻžāĻ°ā§ āĻāĻ āĻ§āĻ°āĻ¨ā§āĻ° āĻāĻžāĻ āĻžāĻŽā§āĻ° āĻ¸āĻžāĻĨā§ āĻāĻžāĻ āĻāĻ°āĻžāĻ° āĻāĻĒāĻ¨āĻžāĻ° "āĻ¨āĻŋāĻˇā§āĻĒāĻžāĻĒ" āĻāĻĒāĻžāĻ¯āĻŧāĻā§āĻ˛āĻŋ āĻāĻ¤āĻāĻž āĻāĻžāĻ°ā§āĻ¯āĻāĻ° āĻšāĻŦā§ āĻ¤āĻž āĻĻā§āĻāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ āĻĒā§āĻā§āĻˇāĻž āĻāĻ°āĻā§āĨ¤
āĻāĻ¸ā§āĻ¨ āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻž āĻĻā§āĻāĻž āĻ¯āĻžāĻ, āĻāĻ¸āĻāĻŋāĻāĻāĻ˛-āĻ āĻ¤āĻžāĻĻā§āĻ° āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨, āĻāĻŦāĻ āĻ¤āĻžāĻĻā§āĻ° āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽāĻ¤āĻž āĻāĻ¨ā§āĻ¨āĻ¤ āĻāĻ°āĻžāĻ° āĻā§āĻˇā§āĻāĻž āĻāĻ°ā§āĻ¨āĨ¤
#1 āĻāĻ°āĻā§āĻļā§āĻ° āĻāĻ°ā§āĻ¤ āĻāĻ¤ āĻāĻā§āĻ°?
āĻāĻ¸ā§āĻ¨ āĻāĻŽāĻ°āĻž āĻ¨āĻŋāĻļā§āĻāĻŋāĻ¤āĻāĻžāĻŦā§ āĻ¸ā§āĻŦā§āĻāĻžāĻ° āĻāĻ°āĻŋ āĻ¯ā§ āĻāĻ āĻāĻžāĻ āĻžāĻŽā§āĻāĻŋ āĻ¸āĻāĻ¸ā§āĻĨāĻžāĻ° āĻāĻžāĻ āĻžāĻŽā§āĻ¤ā§ āĻŦāĻŋāĻāĻžāĻāĻā§āĻ˛āĻŋāĻ° āĻ
āĻ§ā§āĻ¨āĻ¤āĻžāĻā§ āĻĒā§āĻ°āĻ¤āĻŋāĻĢāĻ˛āĻŋāĻ¤ āĻāĻ°āĻŦā§: āĻŦāĻŋāĻāĻžāĻ, āĻŦāĻŋāĻāĻžāĻ, āĻ¸ā§āĻā§āĻāĻ°, āĻļāĻžāĻāĻž, āĻāĻ¯āĻŧāĻžāĻ°ā§āĻāĻŋāĻ āĻā§āĻ°ā§āĻĒ... - āĻāĻĒāĻ¨āĻŋ āĻ¯ā§ āĻ¯āĻžāĻ āĻŦāĻ˛ā§āĻ¨ āĻ¨āĻž āĻā§āĻ¨āĨ¤
āĻĒā§āĻ°āĻĨāĻŽā§, āĻāĻ¸ā§āĻ¨ 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;
āĻāĻ˛ā§āĻ¨ āĻļā§āĻ°ā§ āĻāĻ°āĻž āĻ¯āĻžāĻ āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻ¸āĻšāĻ āĻāĻžāĻāĻāĻŋ - āĻāĻāĻāĻŋ āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻ¸ā§āĻā§āĻāĻ°ā§āĻ° āĻŽāĻ§ā§āĻ¯ā§ āĻāĻžāĻ āĻāĻ°ā§ āĻāĻŽāĻ¨ āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻāĻ°ā§āĻŽāĻāĻžāĻ°ā§āĻĻā§āĻ° āĻā§āĻāĻā§ āĻŦā§āĻ° āĻāĻ°āĻž, āĻŦāĻž āĻ
āĻ¨ā§āĻā§āĻ°āĻŽā§āĻ° āĻĒāĻ°āĻŋāĻĒā§āĻ°ā§āĻā§āĻˇāĻŋāĻ¤ā§ - āĻāĻāĻāĻŋ āĻ¨ā§āĻĄā§āĻ° āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻļāĻŋāĻļā§ āĻā§āĻāĻā§āĻ¨. āĻāĻāĻŋ āĻŦāĻāĻļāĻ§āĻ°ā§āĻ° "āĻāĻā§āĻ°āĻ¤āĻž" āĻĒā§āĻ¤ā§āĻ āĻāĻžāĻ˛ āĻšāĻŦā§... āĻāĻ āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻāĻŋāĻā§ āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨ āĻšāĻ¤ā§ āĻĒāĻžāĻ°ā§, āĻāĻĻāĻžāĻšāĻ°āĻŖāĻ¸ā§āĻŦāĻ°ā§āĻĒ, āĻāĻŋāĻā§ āĻ§āĻ°āĻŖā§āĻ° āĻ¨āĻŋāĻ°ā§āĻŽāĻžāĻŖā§āĻ° āĻāĻ¨ā§āĻ¯
āĻ¯āĻĻāĻŋ āĻāĻ āĻŦāĻāĻļāĻ§āĻ°āĻĻā§āĻ° āĻŽāĻžāĻ¤ā§āĻ° āĻāĻ¯āĻŧā§āĻāĻāĻŋ āĻ¸ā§āĻ¤āĻ° āĻĨāĻžāĻā§ āĻāĻŦāĻ āĻ¸āĻāĻā§āĻ¯āĻžāĻāĻŋ āĻāĻ āĻĄāĻāĻ¨ā§āĻ° āĻŽāĻ§ā§āĻ¯ā§ āĻĨāĻžāĻā§ āĻ¤āĻŦā§ āĻ¸āĻŦāĻāĻŋāĻā§ āĻ āĻŋāĻ āĻšāĻŦā§, āĻ¤āĻŦā§ āĻ¯āĻĻāĻŋ 5āĻāĻŋāĻ° āĻŦā§āĻļāĻŋ āĻ¸ā§āĻ¤āĻ° āĻĨāĻžāĻā§ āĻāĻŦāĻ āĻāĻ¤āĻŋāĻŽāĻ§ā§āĻ¯ā§ āĻāĻ¯āĻŧā§āĻ āĻĄāĻāĻ¨ āĻŦāĻāĻļāĻ§āĻ° āĻĨāĻžāĻā§ āĻ¤āĻŦā§ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻž āĻšāĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤ āĻāĻ˛ā§āĻ¨ āĻĻā§āĻāĻŋ āĻāĻŋāĻāĻžāĻŦā§ āĻāĻ¤āĻŋāĻšā§āĻ¯āĻāĻ¤ āĻĄāĻžāĻāĻ¨-āĻĻā§āĻ¯-āĻā§āĻ°āĻŋ āĻ āĻ¨ā§āĻ¸āĻ¨ā§āĻ§āĻžāĻ¨ āĻŦāĻŋāĻāĻ˛ā§āĻĒāĻā§āĻ˛āĻŋ āĻ˛ā§āĻāĻž āĻšāĻ¯āĻŧ (āĻāĻŦāĻ āĻāĻžāĻ)āĨ¤ āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻĒā§āĻ°āĻĨāĻŽā§, āĻāĻ¸ā§āĻ¨ āĻ¨āĻŋāĻ°ā§āĻ§āĻžāĻ°āĻŖ āĻāĻ°āĻŋ āĻā§āĻ¨ āĻ¨ā§āĻĄāĻā§āĻ˛āĻŋ āĻāĻŽāĻžāĻĻā§āĻ° āĻāĻŦā§āĻˇāĻŖāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻāĻāĻ°ā§āĻˇāĻŖā§āĻ¯āĻŧ āĻšāĻŦā§āĨ¤
āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻŦā§āĻļā§ "āĻāĻā§āĻ°" āĻāĻĒāĻŦā§āĻā§āĻˇ:
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
āĻāĻ āĻĒā§āĻ°āĻļā§āĻ¨āĻā§āĻ˛āĻŋāĻ° āĻāĻ¨ā§āĻ¯ āĻāĻŽāĻ°āĻž āĻ¸āĻžāĻ§āĻžāĻ°āĻŖāĻ¤ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§āĻāĻŋ āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤ āĻ¯ā§āĻāĻĻāĻžāĻ¨:
āĻ¸ā§āĻĒāĻˇā§āĻāĻ¤āĻ, āĻāĻ āĻ
āĻ¨ā§āĻ°ā§āĻ§ āĻŽāĻĄā§āĻ˛ āĻ¸āĻā§āĻā§ āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤āĻŋāĻ° āĻ¸āĻāĻā§āĻ¯āĻž āĻŽā§āĻ āĻŦāĻāĻļāĻ§āĻ°ā§āĻ° āĻ¸āĻāĻā§āĻ¯āĻžāĻ° āĻ¸āĻžāĻĨā§ āĻŽāĻŋāĻ˛āĻŦā§ (āĻāĻŦāĻ āĻ¤āĻžāĻĻā§āĻ° āĻŽāĻ§ā§āĻ¯ā§ āĻāĻ¯āĻŧā§āĻ āĻĄāĻāĻ¨ āĻāĻā§), āĻāĻŦāĻ āĻāĻāĻŋ āĻŦā§āĻļ āĻāĻ˛ā§āĻ˛ā§āĻāĻ¯ā§āĻā§āĻ¯ āĻ¸āĻāĻ¸ā§āĻĨāĻžāĻ¨ āĻ¨āĻŋāĻ¤ā§ āĻĒāĻžāĻ°ā§, āĻāĻŦāĻ āĻĢāĻ˛āĻ¸ā§āĻŦāĻ°ā§āĻĒ, āĻ¸āĻŽāĻ¯āĻŧ āĻ¨āĻŋāĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤
āĻāĻ¸ā§āĻ¨ "āĻĒā§āĻ°āĻļāĻ¸ā§āĻ¤" āĻ¸āĻžāĻŦāĻā§āĻ°āĻŋ āĻĒāĻ°ā§āĻā§āĻˇāĻž āĻāĻ°āĻŋ:
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)
.
āĻāĻŦāĻ āĻāĻ āĻ§āĻ°āĻ¨ā§āĻ° āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻāĻāĻĄā§āĻ¨ā§āĻāĻŋāĻĢāĻžāĻ¯āĻŧāĻžāĻ° āĻā§āĻ°ā§āĻĒā§ āĻāĻŽāĻ°āĻž "āĻ¨ā§āĻĄ" āĻĻā§āĻŦāĻžāĻ°āĻž āĻĒā§āĻ°ā§āĻŦāĻŦāĻ°ā§āĻ¤ā§ āĻ§āĻžāĻĒā§ āĻĒāĻžāĻāĻ¯āĻŧāĻž āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻāĻāĻĄāĻŋ āĻ¨āĻŋāĻ¤ā§ āĻĒāĻžāĻ°āĻŋāĨ¤ āĻ āĻ°ā§āĻĨāĻžā§, āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻĒāĻ°āĻŦāĻ°ā§āĻ¤ā§ āĻ§āĻžāĻĒā§ āĻāĻŽāĻ°āĻž āĻāĻ°āĻŦ āĻāĻāĻŦāĻžāĻ°ā§ āĻāĻāĻāĻŋ āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻ¸ā§āĻ¤āĻ°ā§āĻ° āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻŦāĻāĻļāĻ§āĻ°āĻĻā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ āĻ¨ā§āĻ¸āĻ¨ā§āĻ§āĻžāĻ¨ āĻāĻ°ā§āĻ¨.
āĻļā§āĻ§ā§, āĻāĻāĻžāĻ¨ā§āĻ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻž, āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤ āĻ¨āĻŋāĻ°ā§āĻŦāĻžāĻāĻ¨ā§, āĻāĻĒāĻ¨āĻŋ āĻāĻāĻāĻŋ āĻ¨ā§āĻ¸ā§āĻā§āĻĄ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§āĻ¤ā§ āĻ¨āĻŋāĻā§āĻā§ āĻ
ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°āĻŦā§āĻ¨ āĻ¨āĻž, āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻĒā§āĻ°ā§āĻŦāĻŦāĻ°ā§āĻ¤ā§ āĻ¸ā§āĻ¤āĻ°ā§ āĻ¯āĻž āĻĒāĻžāĻāĻ¯āĻŧāĻž āĻā§āĻā§ āĻ¤āĻž āĻāĻŽāĻžāĻĻā§āĻ°āĻā§ āĻā§āĻ¨ā§āĻāĻžāĻŦā§ āĻ¨āĻŋāĻ°ā§āĻŦāĻžāĻāĻ¨ āĻāĻ°āĻ¤ā§ āĻšāĻŦā§... āĻĻā§āĻāĻž āĻ¯āĻžāĻā§āĻā§ āĻ¯ā§ āĻ¸āĻŽāĻā§āĻ° āĻ¨āĻŋāĻ°ā§āĻŦāĻžāĻāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻāĻŋ āĻ¨ā§āĻ¸ā§āĻā§āĻĄ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻāĻ°āĻž āĻ
āĻ¸āĻŽā§āĻāĻŦ, āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻāĻ° āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻā§āĻˇā§āĻ¤ā§āĻ°ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻŋ āĻ¸āĻŽā§āĻāĻŦāĨ¤ āĻāĻŦāĻ āĻāĻ āĻā§āĻˇā§āĻ¤ā§āĻ°āĻāĻŋ āĻāĻāĻāĻŋ āĻ
ā§āĻ¯āĻžāĻ°ā§āĻ āĻšāĻ¤ā§ āĻĒāĻžāĻ°ā§ - āĻ¯āĻž āĻāĻŽāĻžāĻĻā§āĻ° āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻ¤ā§ āĻšāĻŦā§ 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āĨ¤ āĻāĻ˛ā§āĻ¨ āĻļāĻŋāĻāĻĄāĻŧā§ āĻĢāĻŋāĻ°ā§ āĻ¯āĻžāĻ
āĻāĻ āĻ ā§āĻ¯āĻžāĻ˛āĻāĻ°āĻŋāĻĻāĻŽāĻāĻŋ āĻāĻĒāĻ¯ā§āĻā§ āĻšāĻŦā§ āĻ¯āĻĻāĻŋ āĻāĻĒāĻ¨āĻžāĻā§ "āĻāĻžāĻā§āĻ° āĻāĻĒāĻ°ā§" āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻāĻĒāĻžāĻĻāĻžāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ°ā§āĻāĻ°ā§āĻĄ āĻ¸āĻāĻā§āĻ°āĻš āĻāĻ°āĻ¤ā§ āĻšāĻ¯āĻŧ, āĻā§āĻ¨ āĻā§āĻ¸ āĻĒāĻ¤ā§āĻ°āĻ (āĻāĻŦāĻ āĻā§āĻ¨ āĻ¨āĻŋāĻ°ā§āĻĻā§āĻļāĻāĻā§āĻ˛āĻŋāĻ° āĻ¸āĻžāĻĨā§) āĻāĻāĻŋ āĻ¨āĻŽā§āĻ¨āĻžāĻ¯āĻŧ āĻ āĻ¨ā§āĻ¤āĻ°ā§āĻā§āĻā§āĻ¤ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛ āĻ¸ā§ āĻ¸āĻŽā§āĻĒāĻ°ā§āĻā§ āĻ¤āĻĨā§āĻ¯ āĻŦāĻāĻžāĻ¯āĻŧ āĻ°āĻžāĻāĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ - āĻāĻĻāĻžāĻšāĻ°āĻŖāĻ¸ā§āĻŦāĻ°ā§āĻĒ, āĻāĻāĻāĻŋ āĻ¸āĻžāĻ°āĻžāĻāĻļ āĻĒā§āĻ°āĻ¤āĻŋāĻŦā§āĻĻāĻ¨ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻ¤ā§ āĻ¨ā§āĻĄ āĻŽāĻ§ā§āĻ¯ā§ āĻāĻāĻ¤ā§āĻ°āĻŋāĻ¤ āĻ¸āĻā§āĻā§.
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻā§āĻ˛āĻŋāĻā§ āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° āĻ§āĻžāĻ°āĻŖāĻžāĻ° āĻĒā§āĻ°āĻŽāĻžāĻŖ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻ¨ā§āĻāĻ¯āĻŧāĻž āĻāĻāĻŋāĻ¤, āĻ¯ā§āĻšā§āĻ¤ā§ āĻ
āĻ¨ā§āĻ°ā§āĻ§āĻāĻŋ āĻā§āĻŦ āĻāĻˇā§āĻāĻāĻ° āĻšāĻ¤ā§ āĻĻā§āĻāĻž āĻ¯āĻžāĻā§āĻā§ā§ˇ āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻ¯āĻĻāĻŋ āĻāĻāĻŋ āĻāĻĒāĻ¨āĻžāĻ° āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§ āĻāĻ§āĻŋāĻĒāĻ¤ā§āĻ¯ āĻŦāĻŋāĻ¸ā§āĻ¤āĻžāĻ° āĻāĻ°ā§, āĻ¤āĻžāĻšāĻ˛ā§ āĻāĻĒāĻ¨āĻžāĻ° āĻ
āĻ¨ā§āĻ°ā§āĻĒ āĻā§āĻļāĻ˛āĻā§āĻ˛āĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻžāĻ° āĻŦāĻŋāĻˇāĻ¯āĻŧā§ āĻāĻŋāĻ¨ā§āĻ¤āĻž āĻāĻ°āĻž āĻāĻāĻŋāĻ¤āĨ¤
āĻāĻ¸ā§āĻ¨ āĻāĻ¯āĻŧā§āĻāĻāĻŋ āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻŦāĻŋāĻŦā§āĻ¤āĻŋ āĻĻāĻŋāĻ¯āĻŧā§ āĻļā§āĻ°ā§ āĻāĻ°āĻŋ:
- āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ āĻĨā§āĻā§ āĻāĻāĻ āĻ°ā§āĻāĻ°ā§āĻĄ āĻāĻāĻž āĻļā§āĻ§ā§ āĻāĻāĻŦāĻžāĻ° āĻĒāĻĄāĻŧāĻž āĻāĻžāĻ˛.
- āĻĄā§āĻāĻžāĻŦā§āĻ¸ āĻĨā§āĻā§ āĻ°ā§āĻāĻ°ā§āĻĄ āĻāĻāĻŋ āĻŦā§āĻ¯āĻžāĻā§ āĻĒāĻĄāĻŧāĻž āĻāĻ°āĻ āĻĻāĻā§āĻˇāĻāĻāĻž āĻĨā§āĻā§
āĻāĻāĻ¨ āĻāĻŽāĻžāĻĻā§āĻ° āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨ā§āĻ¯āĻŧ āĻ āĻ¨ā§āĻ°ā§āĻ§āĻāĻŋ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻžāĻ° āĻā§āĻˇā§āĻāĻž āĻāĻ°āĻž āĻ¯āĻžāĻāĨ¤
āĻĒāĻāĻ āĻž 1
āĻ¸ā§āĻĒāĻˇā§āĻāĻ¤āĻ, āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤āĻŋ āĻļā§āĻ°ā§ āĻāĻ°āĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ (āĻāĻāĻŋ āĻāĻžāĻĄāĻŧāĻž āĻāĻŽāĻ°āĻž āĻā§āĻĨāĻžāĻ¯āĻŧ āĻĨāĻžāĻāĻŦ!) āĻāĻŽāĻžāĻĻā§āĻ° āĻĒā§āĻ°āĻžāĻĨāĻŽāĻŋāĻ āĻļāĻ¨āĻžāĻā§āĻ¤āĻāĻžāĻ°ā§āĻ° āĻ¸ā§āĻā§āĻ° āĻāĻĒāĻ° āĻāĻŋāĻ¤ā§āĻ¤āĻŋ āĻāĻ°ā§ āĻĒāĻžāĻ¤āĻžāĻ° āĻ°ā§āĻāĻ°ā§āĻĄāĻā§āĻ˛āĻŋ āĻŦāĻŋāĻ¯āĻŧā§āĻ āĻāĻ°āĻ¤ā§ āĻšāĻŦā§:
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
, āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻ
ā§āĻ¯āĻžāĻ°ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ¨āĻ¯āĻŧāĨ¤ āĻ¯āĻĻāĻŋāĻ āĻ¸ā§
āĻĒāĻāĻ āĻž 2
āĻāĻāĻ¨ āĻāĻŽāĻ°āĻž āĻŦāĻŋāĻāĻžāĻ āĻāĻāĻĄāĻŋāĻā§āĻ˛āĻŋāĻ° āĻāĻāĻāĻŋ āĻ¸ā§āĻ āĻĒāĻžāĻŦ āĻ¯āĻž āĻāĻ°āĻ āĻĒāĻĄāĻŧāĻ¤ā§ āĻšāĻŦā§āĨ¤ āĻĒā§āĻ°āĻžāĻ¯āĻŧ āĻ¸āĻŦāĻ¸āĻŽāĻ¯āĻŧ āĻ¤āĻžāĻ°āĻž āĻŽā§āĻ˛ āĻ¸ā§āĻā§āĻ° āĻŦāĻŋāĻāĻŋāĻ¨ā§āĻ¨ āĻ°ā§āĻāĻ°ā§āĻĄā§ āĻ¨āĻāĻ˛ āĻāĻ°āĻž āĻšāĻŦā§ - āĻ¤āĻžāĻ āĻāĻŽāĻ°āĻž āĻāĻ°āĻŦ āĻ¤āĻžāĻĻā§āĻ° āĻĻāĻ˛āĻŦāĻĻā§āĻ§ āĻāĻ°ā§āĻ¨, āĻā§āĻ¸ āĻĒāĻžāĻ¤āĻž āĻ¸āĻŽā§āĻĒāĻ°ā§āĻā§ āĻ¤āĻĨā§āĻ¯ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ.
āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻāĻāĻžāĻ¨ā§ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻž āĻāĻŽāĻžāĻĻā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ āĻĒā§āĻā§āĻˇāĻž āĻāĻ°āĻā§:
- āĻĒā§āĻ°āĻļā§āĻ¨ā§āĻ° "āĻ¸āĻžāĻŦāĻŦā§āĻ°ā§āĻāĻžāĻ°ā§āĻ¸āĻŋāĻ" āĻ
āĻāĻļā§ āĻ¸āĻŽāĻˇā§āĻāĻŋāĻāĻ¤ āĻĢāĻžāĻāĻļāĻ¨ āĻĨāĻžāĻāĻ¤ā§ āĻĒāĻžāĻ°ā§ āĻ¨āĻž
GROUP BY
. - āĻāĻāĻāĻŋ āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤ "āĻā§āĻŦāĻŋāĻ˛" āĻāĻ° āĻāĻāĻāĻŋ āĻ°ā§āĻĢāĻžāĻ°ā§āĻ¨ā§āĻ¸ āĻ¨ā§āĻ¸ā§āĻā§āĻĄ āĻ¸āĻžāĻŦāĻā§āĻ¯āĻŧā§āĻ°āĻŋāĻ¤ā§ āĻĨāĻžāĻāĻ¤ā§ āĻĒāĻžāĻ°ā§ āĻ¨āĻžāĨ¤
- āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤āĻŋāĻŽā§āĻ˛āĻ āĻ āĻāĻļā§ āĻāĻāĻāĻŋ āĻ āĻ¨ā§āĻ°ā§āĻ§ā§ āĻāĻāĻāĻŋ CTE āĻĨāĻžāĻāĻ¤ā§ āĻĒāĻžāĻ°ā§ āĻ¨āĻžāĨ¤
āĻ¸ā§āĻāĻžāĻā§āĻ¯āĻŦāĻļāĻ¤, āĻāĻ āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻž āĻĒā§āĻ°āĻžāĻ¯āĻŧ āĻāĻžāĻ āĻāĻ°āĻž āĻŦā§āĻļ āĻ¸āĻšāĻāĨ¤ āĻļā§āĻˇ āĻĨā§āĻā§ āĻļā§āĻ°ā§ āĻāĻ°āĻž āĻ¯āĻžāĻāĨ¤
āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤ āĻ āĻāĻļā§ CTE
āĻāĻāĻžāĻ¨ā§ āĻ¤āĻžāĻ āĻ¨āĻž āĻāĻžāĻ:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)
āĻāĻŦāĻ āĻ¤āĻžāĻ āĻāĻāĻŋ āĻāĻžāĻ āĻāĻ°ā§, āĻŦāĻ¨ā§āĻ§āĻ¨ā§ āĻĒāĻžāĻ°ā§āĻĨāĻā§āĻ¯ āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)
āĻāĻāĻāĻŋ āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤ "āĻā§āĻŦāĻŋāĻ˛" āĻāĻ° āĻŦāĻŋāĻ°ā§āĻĻā§āĻ§ā§ āĻ¨ā§āĻ¸ā§āĻā§āĻĄ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§
āĻšā§āĻŽ... āĻāĻāĻāĻŋ āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤ CTE āĻāĻāĻāĻŋ āĻ¸āĻžāĻŦāĻā§āĻ¯āĻŧā§āĻ°āĻŋāĻ¤ā§ āĻ ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻāĻ°āĻž āĻ¯āĻžāĻŦā§ āĻ¨āĻžā§ˇ āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻ¸ā§āĻāĻž āĻ¸āĻŋāĻāĻŋāĻāĻ° āĻāĻŋāĻ¤āĻ°ā§āĻ āĻšāĻ¤ā§ āĻĒāĻžāĻ°ā§! āĻāĻŦāĻ āĻāĻāĻāĻŋ āĻ¨ā§āĻ¸ā§āĻā§āĻĄ āĻ āĻ¨ā§āĻ°ā§āĻ§ āĻāĻ¤āĻŋāĻŽāĻ§ā§āĻ¯ā§āĻ āĻāĻ CTE āĻ ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°ā§!
āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤āĻŋāĻ° āĻāĻŋāĻ¤āĻ°ā§ āĻā§āĻ°ā§āĻĒ āĻāĻ°ā§
āĻāĻāĻž āĻ
āĻĒā§āĻ°ā§āĻ¤āĻŋāĻāĻ°, āĻāĻŋāĻ¨ā§āĻ¤ā§... āĻāĻŽāĻžāĻĻā§āĻ° āĻāĻžāĻā§ 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
āĻāĻāĻ¨ āĻāĻŽāĻ°āĻž āĻĻā§āĻāĻ¤ā§ āĻĒāĻžāĻā§āĻāĻŋ āĻā§āĻ¨ āĻ¸āĻžāĻāĻā§āĻ¯āĻŋāĻ āĻāĻāĻĄāĻŋāĻā§ āĻā§āĻā§āĻ¸āĻā§ āĻĒāĻ°āĻŋāĻŖāĻ¤ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛ - āĻ¯āĻžāĻ¤ā§ āĻ¤āĻžāĻ°āĻž āĻāĻŽāĻž āĻĻāĻŋāĻ¯āĻŧā§ āĻāĻ˛āĻžāĻĻāĻž āĻāĻ°āĻž āĻ¯āĻžāĻ¯āĻŧ!
āĻĒāĻāĻ āĻž 3
āĻĢāĻžāĻāĻ¨āĻžāĻ˛ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻŽāĻžāĻĻā§āĻ° āĻāĻ° āĻāĻŋāĻā§āĻ āĻŦāĻžāĻāĻŋ āĻ¨ā§āĻ:
- āĻāĻŽāĻ°āĻž āĻā§āĻˇā§āĻ ā§āĻŦāĻĻā§āĻ§ āĻāĻāĻĄāĻŋāĻā§āĻ˛āĻŋāĻ° āĻ¸ā§āĻā§āĻ° āĻāĻĒāĻ° āĻāĻŋāĻ¤ā§āĻ¤āĻŋ āĻāĻ°ā§ "āĻŦāĻŋāĻāĻžāĻ" āĻ°ā§āĻāĻ°ā§āĻĄ āĻĒāĻĄāĻŧāĻŋ
- āĻāĻŽāĻ°āĻž āĻŽā§āĻ˛ āĻļā§āĻāĻā§āĻ˛āĻŋāĻ° "āĻ¸ā§āĻ" āĻāĻ° āĻ¸āĻžāĻĨā§ āĻŦāĻŋāĻ¯āĻŧā§āĻāĻā§āĻ¤ āĻŦāĻŋāĻāĻžāĻāĻā§āĻ˛āĻŋāĻ° āĻ¤ā§āĻ˛āĻ¨āĻž āĻāĻ°āĻŋ
- āĻ¸ā§āĻ-āĻ¸ā§āĻā§āĻ°āĻŋāĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ "āĻĒā§āĻ°āĻ¸āĻžāĻ°āĻŋāĻ¤ āĻāĻ°ā§āĻ¨"
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