рдЬрдЯрд┐рд▓ 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;
рд╕рдмреИ рднрдиреНрджрд╛ рд╕рд╛рдзрд╛рд░рдг рдХрд╛рд░реНрдпрдХреЛ рд╕рд╛рде рд╕реБрд░реБ рдЧрд░реМрдВ - рдПрдХ рд╡рд┐рд╢реЗрд╖ рдХреНрд╖реЗрддреНрд░ рднрд┐рддреНрд░ рдХрд╛рдо рдЧрд░реНрдиреЗ рд╕рдмреИ рдХрд░реНрдордЪрд╛рд░реАрд╣рд░реВ рдлреЗрд▓рд╛ рдкрд╛рд░реНрдиреБрд╣реЛрд╕реН, рд╡рд╛ рдкрджрд╛рдиреБрдХреНрд░рдордХреЛ рд╕рд░реНрддрдорд╛ - рдиреЛрдбрдХрд╛ рд╕рдмреИ рдмрдЪреНрдЪрд╛рд╣рд░реВ рдлреЗрд▓рд╛ рдкрд╛рд░реНрдиреБрд╣реЛрд╕реНред рдпреЛ рд╡рдВрд╢рдЬ рдХреЛ "рдЧрд╣рд┐рд░рд╛рдИ" рдкреНрд░рд╛рдкреНрдд рдЧрд░реНрди рдХреЛ рд▓рд╛рдЧреА рд░рд╛рдореНрд░реЛ рд╣реБрдиреЗрдЫ ... рдпреЛ рд╕рдмреИ рдЖрд╡рд╢реНрдпрдХ рд╣реБрди рд╕рдХреНрдЫ, рдЙрджрд╛рд╣рд░рдг рдХреЛ рд▓рд╛рдЧреА, рдХреЗрд╣рд┐ рдкреНрд░рдХрд╛рд░ рдХреЛ рдирд┐рд░реНрдорд╛рдг рдЧрд░реНрди рдХреЛ рд▓рд╛рдЧреАред
рдпреА рд╡рдВрд╢рдЬрд╣рд░реВрдХреЛ рдПрдХ рджреБрдИ рддрд╣ рдорд╛рддреНрд░реИ рднрдПрдорд╛ рд░ рд╕рдВрдЦреНрдпрд╛ рдПрдХ рджрд░реНрдЬрди рднрд┐рддреНрд░ рднрдПрдорд╛ рд╕рдмреИ рдХреБрд░рд╛ рдареАрдХ рд╣реБрдиреНрдЫ, рддрд░ 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)
.
рд░ рдкрд╣рд┐рдЪрд╛рдирдХрд░реНрддрд╛рд╣рд░реВрдХреЛ рдкреНрд░рддреНрдпреЗрдХ рд╕рдореВрд╣рдорд╛ рд╣рд╛рдореАрд▓реЗ рдЕрдШрд┐рд▓реНрд▓реЛ рдЪрд░рдгрдорд╛ рдлреЗрд▓рд╛ рдкрд░реЗрдХрд╛ рд╕рдмреИ рдЖрдИрдбреАрд╣рд░реВ "рдиреЛрдбрд╣рд░реВ" рджреНрд╡рд╛рд░рд╛ рд▓рд┐рди рд╕рдХреНрдЫреМрдВред рддреНрдпреЛ рд╣реЛ, рдкреНрд░рддреНрдпреЗрдХ рдЕрд░реНрдХреЛ рдЪрд░рдгрдорд╛ рд╣рд╛рдореА рдЧрд░реНрдиреЗрдЫреМрдВ рдПрдХреИрдЪреЛрдЯрд┐ рдирд┐рд╢реНрдЪрд┐рдд рд╕реНрддрд░рдХрд╛ рд╕рдмреИ рд╕рдиреНрддрд╛рдирд╣рд░реВ рдЦреЛрдЬреНрдиреБрд╣реЛрд╕реН.
рдорд╛рддреНрд░, рдпрд╣рд╛рдБ рд╕рдорд╕реНрдпрд╛ рдЫ, рдкреБрдирд░рд╛рд╡рд░реНрддреА рдЪрдпрдирдорд╛, рддрдкрд╛рдИрдВрд▓реЗ рдиреЗрд╕реНрдЯреЗрдб рдХреНрд╡реЗрд░реАрдорд╛ рдкрд╣реБрдБрдЪ рдЧрд░реНрди рд╕рдХреНрдиреБрд╣реБрдиреНрди, рддрд░ рд╣рд╛рдореАрд▓реЗ рдХреБрдиреИ рди рдХреБрдиреИ рд░реВрдкрдорд╛ рдЕрдШрд┐рд▓реНрд▓реЛ рд╕реНрддрд░рдорд╛ рдлреЗрд▓рд╛ рдкрд░реЗрдХреЛ рдорд╛рддреНрд░ рдЪрдпрди рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫ... рдпреЛ рдмрд╛рд╣рд┐рд░ рдЬрд╛рдиреНрдЫ рдХрд┐ рд╕рдореНрдкреВрд░реНрдг рдЪрдпрдирдХреЛ рд▓рд╛рдЧрд┐ рдиреЗрд╕реНрдЯреЗрдб рдХреНрд╡реЗрд░реА рдмрдирд╛рдЙрди рдЕрд╕рдореНрднрд╡ рдЫ, рддрд░ рдпрд╕рдХреЛ рд╡рд┐рд╢рд┐рд╖реНрдЯ рдХреНрд╖реЗрддреНрд░рдХреЛ рд▓рд╛рдЧрд┐ рдпреЛ рд╕рдореНрднрд╡ рдЫред рд░ рдпреЛ рдХреНрд╖реЗрддреНрд░ рдкрдирд┐ рдПрдХ 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 рдкрдЯрдХ рднрдиреНрджрд╛ рдмрдвреА рдЬрд┐рддреНрдпреЛ рдкреНрд░реВрдлрд░реАрдбред
#реиред рдЬрд░рд╛рд╣рд░реВрдорд╛ рдлрд░реНрдХреМрдВ
рдпреЛ рдПрд▓реНрдЧреЛрд░рд┐рдереНрдо рдЙрдкрдпреЛрдЧреА рд╣реБрдиреЗрдЫ рдпрджрд┐ рддрдкрд╛рдЗрдБ рд╕рдмреИ рддрддреНрд╡рд╣рд░реВрдХреЛ рд▓рд╛рдЧрд┐ "рд░реБрдЦрдХреЛ рдорд╛рдерд┐" рдХреЛ рд▓рд╛рдЧрд┐ рд░реЗрдХрд░реНрдбрд╣рд░реВ рд╕рдЩреНрдХрд▓рди рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫ, рдЬрдмрдХрд┐ рдХреБрди рд╕реНрд░реЛрдд рдкрд╛рдирд╛ (рд░ рдХреБрди рд╕реВрдЪрдХрд╣рд░реВрд╕рдБрдЧ) рдирдореВрдирд╛рдорд╛ рд╕рдорд╛рд╡реЗрд╢ рдЧрд░реНрдирдХреЛ рд▓рд╛рдЧрд┐ рдЬрд╛рдирдХрд╛рд░реА рд░рд╛рдЦреНрджреИ - рдЙрджрд╛рд╣рд░рдгрдХрд╛ рд▓рд╛рдЧрд┐, рд╕рд╛рд░рд╛рдВрд╢ рд░рд┐рдкреЛрд░реНрдЯ рдЙрддреНрдкрдиреНрди рдЧрд░реНрдиред рдиреЛрдбреНрд╕ рдорд╛ рдПрдХрддреНрд░реАрдХрд░рдг рд╕рдВрдЧред
рдирд┐рдореНрди рдХреБрд░рд╛рд╣рд░реВрд▓рд╛рдИ рдорд╛рддреНрд░ рдкреНрд░рдорд╛рдг-рдЕрд╡рдзрд╛рд░рдгрд╛рдХреЛ рд░реВрдкрдорд╛ рд▓рд┐рдиреБ рдкрд░реНрдЫ, рдХрд┐рдирдХрд┐ рдЕрдиреБрд░реЛрдз рдзреЗрд░реИ рдмреЛрдЭрд┐рд▓реЛ рд╣реБрдиреНрдЫред рддрд░ рдпрджрд┐ рдпреЛ рддрдкрд╛рдЗрдБрдХреЛ рдбрд╛рдЯрд╛рдмреЗрд╕ рд╣рд╛рд╡реА рдЫ рднрдиреЗ, рддрдкрд╛рдЗрдБ рд╕рдорд╛рди рдкреНрд░рд╡рд┐рдзрд┐рд╣рд░реВ рдкреНрд░рдпреЛрдЧ рдЧрд░реНрдиреЗ рдмрд╛рд░реЗ рд╕реЛрдЪреНрдиреБ рдкрд░реНрдЫред
рдХреЗрд╣реА рд╕рд░рд▓ рдХрдердирд╣рд░реВрдмрд╛рдЯ рд╕реБрд░реБ рдЧрд░реМрдВ:
- рдбрд╛рдЯрд╛рдмреЗрд╕рдмрд╛рдЯ рдПрдЙрдЯреИ рд░реЗрдХрд░реНрдб рдпреЛ рдПрдХ рдкрдЯрдХ рдорд╛рддреНрд░ рдкрдвреНрди рд░рд╛рдореНрд░реЛ рдЫ.
- рдбрд╛рдЯрд╛рдмреЗрд╕рдмрд╛рдЯ рд░реЗрдХрд░реНрдбрд╣рд░реВ рдмреНрдпрд╛рдЪрд╣рд░реВрдорд╛ рдкрдвреНрди рдпреЛ рдЕрдзрд┐рдХ рдХреБрд╢рд▓ рдЫрдПрдХреНрд▓реИ рднрдиреНрджрд╛ред
рдЕрдм рд╣рд╛рдореАрд▓рд╛рдИ рдЪрд╛рд╣рд┐рдиреЗ рдЕрдиреБрд░реЛрдз рдирд┐рд░реНрдорд╛рдг рдЧрд░реНрдиреЗ рдкреНрд░рдпрд╛рд╕ рдЧрд░реМрдВред
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
...
рдпрджрд┐ рдпреЛ рдХрд╕реИрд▓рд╛рдИ рдЕрдиреМрдареЛ рд▓рд╛рдЧреНрдереНрдпреЛ рдХрд┐ "рд╕реЗрдЯ" рд╕реНрдЯреНрд░рд┐рдЩрдХреЛ рд░реВрдкрдорд╛ рднрдгреНрдбрд╛рд░рдг рдЧрд░рд┐рдПрдХреЛ рдЫ рд░ рдПрд░реЗ рд╣реЛрдЗрди, рддреНрдпрд╕рдкрдЫрд┐ рддреНрдпрд╣рд╛рдБ рдпрд╕рдХреЛ рд▓рд╛рдЧрд┐ рдПрдХ рд╕рд░рд▓ рд╡реНрдпрд╛рдЦреНрдпрд╛ рдЫред рддреНрдпрд╣рд╛рдБ рд╕реНрдЯреНрд░рд┐рдЩрдХрд╛ рд▓рд╛рдЧрд┐ рдирд┐рд░реНрдорд┐рдд рдПрдХреАрдХреГрдд "gluing" рдкреНрд░рдХрд╛рд░реНрдп рдЫ string_agg
, рддрд░ arrays рдХреЛ рд▓рд╛рдЧреА рд╣реЛрдЗрдиред рдпрджреНрдпрдкрд┐ рдЙрдиреА
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 рднрд┐рддреНрд░ рд╣реБрди рд╕рдХреНрдЫ! рд░ рдиреЗрд╕реНрдЯреЗрдб рдЕрдиреБрд░реЛрдзрд▓реЗ рдкрд╣рд┐рд▓реЗ рдиреИ рдпреЛ 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
рдЕрдм рд╣рд╛рдореА рд╣реЗрд░реНрдЫреМрдВ рдХрд┐рди рд╕рдВрдЦреНрдпрд╛рддреНрдордХ ID рдкрд╛рдардорд╛ рдкрд░рд┐рдгрдд рднрдпреЛ - рддрд╛рдХрд┐ рддрд┐рдиреАрд╣рд░реВ рдЕрд▓реНрдкрд╡рд┐рд░рд╛рдорджреНрд╡рд╛рд░рд╛ рдЫреБрдЯреНрдпрд╛рдПрд░ рдЬреЛрдбрд┐рди рд╕рдХреВрдиреН!
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