I roto i nga punaha ERP matatini he maha nga hinonga he ahua hierarchicali te wa e noho ana nga mea riterite ki roto rakau o te whanaungatanga tupuna-whakaheke - koinei te hanganga whakahaere o te hinonga (enei peka katoa, tari me nga roopu mahi), me te rarangi o nga taonga, me nga waahi mahi, me te matawhenua o nga waahi hokohoko,...
Ko te mea pono, kaore he mea , i reira karekau he nakahitanga hei hua. Engari ahakoa kaore koe e mahi "mo te pakihi," ka taea tonu e koe te whakatau i nga hononga hierarchical. He maamaa noa, ara to whanau whanau, mahere papa ranei o te whare i tetahi pokapu hokohoko he rite tonu te hanganga.
He maha nga huarahi ki te penapena i taua rakau ki roto i te DBMS, engari i tenei ra ka arotahi tatou ki tetahi waahanga anake:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
A, i a koe e titiro ana ki te hohonutanga o te hierarchy, kei te tatari marie ki te kite me pehea te whai hua o o huarahi "waatea" ki te mahi me taua hanganga.
Kia titiro tatou ki nga raruraru angamaheni ka puta ake, to raatau whakatinanatanga ki te SQL, ka ngana ki te whakapai ake i o raatau mahi.
#1. He pehea te hohonu o te poka rapeti?
Ma tatou, mo te tino mohio, ka whakaae ko tenei hanganga ka whakaatu i te whakahekenga o nga tari i roto i te hanganga o te whakahaere: nga tari, nga wehenga, nga waahanga, nga manga, nga roopu mahi ... - ahakoa ka kiia e koe.
Tuatahi, me whakaputa ta tatou 'rakau' 10K huānga
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;Me timata ma te mahi ngawari - te kimi i nga kaimahi katoa e mahi ana i roto i tetahi waahanga motuhake, i runga ranei i nga tikanga o te hierarchy - kimihia nga tamariki katoa o te node. He pai ano te tiki i te "hohonutanga" o te uri... Ko enei mea katoa ka tika, hei tauira, ki te hanga i etahi momo .
Ka pai nga mea katoa mena he tokorua noa nga reanga o enei uri, a, kei roto i te tekau ma rua nga reanga, engari mena ka nui ake i te 5 nga reanga, a kua maha nga uri kua puta, ka raru pea. Kia titiro tatou ki te tuhi i nga whiringa rapu-a-rakau tuku iho (me te mahi). Engari ko te tuatahi, me whakatau ko nga waahanga ka tino pai ki a maatau rangahau.
Ko te nuinga "hohonu" rakau iti:
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}
...Ko te nuinga "whānui" rakau iti:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Mo enei patai i whakamahia e matou te tikanga recursive Hono:
Ko te tikanga, me tenei tauira tono ka ōrite te maha o ngā whitiwhiti ki te tapeke o ngā uri (a he maha nga tatini o ratou), a he nui rawa nga rauemi ka pau tenei, ka mutu, he wa.
Kia tirohia te "whanui" iti rakau:
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;
Ka rite ki te tumanako, i kitea e matou nga rekoata 30 katoa. Engari i whakapaua e ratou te 60% o te wa katoa mo tenei - na te mea i mahia e ratou 30 nga rapunga i roto i te taurangi. Ka taea te mahi iti?
Panuitanga nui ma te taurangi
Me hanga he uiui taupū motuhake mo ia node? Ka puta mai kaore - ka taea e tatou te panui mai i te taurangi te whakamahi i etahi taviri i te wa kotahi i te waea kotahi me te awhina = ANY(array).
A i roto i ia roopu tohu tohu ka taea e tatou te tango i nga ID katoa i kitea i te taahiraa o mua ma nga "nodes". Arā, i ia taahiraa ka whai ake rapua nga uri katoa o tetahi taumata i te wa kotahi.
Ko tenei anake te raruraru, i roto i te kowhiringa recursive, kaore e taea e koe te uru atu ki a koe ano i roto i te uiui kohanga, engari me whiriwhiri noa i nga mea i kitea i te taumata o mua... Te ahua nei kaore e taea te hanga i te uiui kohanga mo te katoa o te kowhiringa, engari mo tana mara motuhake ka taea. A ko tenei mara ka taea ano he huinga - koinei te mea hei whakamahi ANY.
He ahua porangi te ahua, engari i roto i te hoahoa he ngawari nga mea katoa.
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;
Na konei ko te mea tino nui ehara i te mea toa 1.5 wa i roto i te wa, a he iti ake nga parepare i tangohia, i te mea e 5 noa nga waea ki te taurangi, kaua ki te 30!
Ko te bonus taapiri ko te meka i muri i te koretake whakamutunga, ka noho tonu nga kaitautohu ma te "taumata".
Tohu node
Ko te whakaaro e whai ake nei hei whakapai ake i te mahi ko − Kaore e taea e "rau" te whanau tamariki, ara, mo ratou kaore he take ki te titiro "ki raro" rawa. I roto i te whakatakotoranga o a maatau mahi, ko te tikanga mena ka whai taatau i nga mekameka o nga tari ka tae ki tetahi kaimahi, karekau he take ki te titiro atu ki te taha o tenei peka.
Kia tomo tatou ki to tatou tepu taapiri boolean-marae, ka whakaatu tonu mai mena he "node" tenei urunga kei roto i ta tatou rakau - ara, mena ka whai uri ranei.
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 мс.Rawe! Te ahua nei he iti ake i te 30% o nga huānga rakau katoa he uri.
Inaianei me whakamahi tetahi miihini rereke - nga hononga ki te waahanga recursive LATERAL, ka taea e tatou te uru wawe ki nga mara o te "tepu" recursive, me te whakamahi i tetahi mahi whakahiato me te ahua tātari i runga i te node hei whakaiti i te huinga o nga taviri:
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;
I taea e matou te whakaiti i tetahi atu waea taupū me i toa neke atu i te 2 nga wa i te rōrahi whakatika.
#2. Kia hoki ki nga putake
Ka whai hua tenei algorithm mena ka hiahia koe ki te kohi rekoata mo nga huānga katoa "i runga i te rakau", me te pupuri i nga korero e pa ana ki te rau puna (me nga tohu tohu) i uru ai ki roto i te tauira - hei tauira, ki te whakaputa i te ripoata whakarāpopoto. me te whakahiato ki roto i nga pona.
Ko nga mea e whai ake nei me waiho hei tohu-a-arii anake, na te mea he tino uaua te tono. Engari ki te mea kei runga i to papaunga raraunga, me whakaaro koe ki te whakamahi i nga tikanga rite.
Me timata ma nga korero ngawari e rua:
- Ko taua rekoata mai i te paataka raraunga He pai ake kia kotahi noa te panui.
- Nga rekoata mai i te paataka raraunga He pai ake te panui i nga roopui te anake.
Inaianei me ngana ki te hanga i te tono e hiahiatia ana.
te taahi 1
Ko te tikanga, i te wa e timata ana i te recursion (kei hea ka kore!) me tango e tatou nga rekoata o nga rau ake i runga i te huinga o nga tohu tohu tuatahi:
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
...
Mena he ahua ke ki tetahi ko te "huinga" kei te rongoa hei aho, ehara i te raupapa, he whakamarama ngawari mo tenei. He mahinga "whakapiri" kua hangaia mo nga aho string_agg, engari kaua mo nga rarangi. Ahakoa ko ia .
te taahi 2
Inaianei ka whiwhi tatou i tetahi huinga ID waahanga me panui ano. Tata ki nga wa katoa ka taritehia ki nga rekoata rereke o te huinga taketake - na maatau whakarōpūtia ratou, i te wa e pupuri ana i nga korero mo nga rau puna.
Engari i konei e toru nga raru kei te tatari mai:
- Ko te waahanga "subrecursive" o te patai kaore e taea te whakauru i nga mahi whakahiato
GROUP BY. - Ko te tohutoro ki te "tepu" recursive e kore e taea ki roto i te waahanga ohanga.
- Ko te tono kei roto i te waahanga recursive kaore e taea te whakauru i te CTE.
Waimarie, he tino ngawari enei raru katoa ki te mahi. Me timata mai i te mutunga.
CTE i roto i te waahanga recursive
Pēnei e kore mahi:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)Na ka whai hua, ko nga reu he rerekee!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Uiui kohanga ki te "ripanga" recursive
Hmm... Kaore e taea te uru atu ki tetahi CTE recursive i roto i tetahi patai. Engari kei roto pea i te CTE! Ka taea e te tono kohanga te uru atu ki tenei CTE!
RUPAPA NA roto recursion
He kino, engari ... He huarahi ngawari ta matou ki te whai i te GROUP BY te whakamahi DISTINCT ON me nga mahi matapihi!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!A koinei te mahi!
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 NULLInaianei kua kite tatou he aha te take i hurihia ai te ID tau hei kupu - kia honoa ai e wehea ana e nga piko!
te taahi 3
Mo te whiringa toa kaore he mea i toe:
- ka panuihia e matou nga rekoata "waahanga" i runga i te huinga o nga ID kua whakarōpūhia
- ka whakatauritehia nga waahanga kua tangohia ki nga "huinga" o nga pepa taketake
- "whakawhanui" i te aho huinga ma te whakamahi
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: will.com