I nā ʻōnaehana ERP paʻakikī he ʻano hierarchical nā hui he nuii ka wā e laina ai nā mea homogeneous lāʻau pili kupuna-moopuna - ʻo kēia ka hoʻonohonoho hoʻonohonoho o ka ʻoihana (ʻo kēia mau lālā āpau, nā keʻena a me nā pūʻulu hana), a me ka papa inoa o nā waiwai, a me nā wahi hana, a me ka ʻāina o nā wahi kūʻai, ...
ʻOiaʻiʻo,ʻaʻohe mea , kahi i loaʻa ʻole kahi hierarchy ma muli o ka hopena. Akā inā ʻaʻole ʻoe e hana "no ka ʻoihana," hiki iā ʻoe ke hālāwai maʻalahi i nā pilina hierarchical. He mea maʻamau, ʻo kāu lāʻau ʻohana a i ʻole ka papahele papahele o kahi hale kūʻai ke ʻano like.
Nui nā ala e mālama ai i kēlā lāʻau i loko o kahi DBMS, akā i kēia lā e nānā mākou i hoʻokahi wale nō koho:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
A ʻoiai ʻoe e nānā ana i ka hohonu o ka hierarchy, ke kali hoʻomanawanui nei ʻoe e ʻike i ka maikaʻi o kāu mau ʻano "naive" e hana ai me ia ʻano hale.
E nānā i nā pilikia maʻamau e kū mai ana, kā lākou hoʻokō ʻana ma SQL, a hoʻāʻo e hoʻomaikaʻi i kā lākou hana.
#1. Pehea ka hohonu o ka lua rabbit?
E ʻae mākou, no ka maopopo ʻana, e hōʻike ana kēia ʻano i ka subordination o nā keʻena i ke ʻano o ka hui: nā keʻena, nā māhele, nā ʻāpana, nā lālā, nā pūʻulu hana ... - nā mea āu i kapa ai iā lākou.
ʻO ka mua, e hoʻohua i kā mākou 'lāʻau' o 10K mau mea
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;E hoʻomaka kākou me ka hana maʻalahi - e ʻimi ana i nā limahana a pau e hana ana i loko o kahi ʻāpana kikoʻī, a i ʻole ma ke ʻano o ka hierarchy - e huli i na keiki a pau o ka node. He mea maikaʻi nō hoʻi e kiʻi i ka "hohonu" o ka mamo ... Pono paha kēia mau mea a pau, no ka laʻana, e kūkulu i kekahi ʻano. .
Maikaʻi nā mea a pau inā ʻelua wale nō pae o kēia mau mamo a aia ka helu i loko o ke kakini, akā inā ʻoi aku ka nui ma mua o 5 pae, a he mau kakini o nā mamo, aia paha nā pilikia. E nānā kākou pehea i kākau ʻia ai nā koho hulina ma lalo o ka lāʻau (a me ka hana). Akā ʻo ka mea mua, e hoʻoholo kākou i nā nodes e hoihoi loa i kā mākou noiʻi.
ʻO ka nui loa "hohonu" nā kumu lāʻau:
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}
...ʻO ka nui loa "ākea" nā kumu lāʻau:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
No kēia mau nīnau ua hoʻohana mākou i ka maʻamau recursive HUI:
ʻIke loa, me kēia hiʻohiʻona noi ʻo ka helu o nā hoʻomaʻamaʻa e like me ka huina o nā mamo (a he mau kakini o lakou), a hiki i keia ke lawe i na kumu waiwai nui, a, no ka hopena, he manawa.
E nānā kākou i ka subtree "ākea loa":
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;
E like me ka mea i manaʻo ʻia, loaʻa iā mākou nā moʻolelo 30 āpau. Akā ua hoʻohana lākou i 60% o ka manawa āpau ma kēia - no ka mea ua hana lākou i 30 mau hulina ma ka papa kuhikuhi. Hiki ke hana emi?
Heluhelu nui ma ka index
Pono mākou e hana i kahi hulina kuhikuhi no kēlā me kēia node? ʻIke ʻia ʻaʻole - hiki iā mākou ke heluhelu mai ka index e hoʻohana ana i kekahi mau kī i ka manawa hoʻokahi i hoʻokahi kelepona me ke kōkuaʻana o = ANY(array).
A i loko o kēlā me kēia pūʻulu o nā mea hōʻike hiki iā mākou ke lawe i nā ID āpau i loaʻa ma ka pae mua e nā "nodes". ʻO ia hoʻi, ma kēlā me kēia pae aʻe mākou e hana ai huli i nā mamo a pau o kekahi pae i ka manawa hoʻokahi.
Eia wale nō ka pilikia, i ke koho recursive, ʻaʻole hiki iā ʻoe ke komo iā ia iho i kahi nīnau nested, akā pono mākou e koho wale i ka mea i loaʻa ma ka pae mua ... Ua ʻike ʻia ʻaʻole hiki ke hana i kahi nīnau nested no ke koho holoʻokoʻa, akā no kāna kahua kikoʻī hiki ke hiki. A hiki i kēia kahua ke hoʻonohonoho - ʻo ia ka mea e pono ai mākou e hoʻohana ANY.
He mea pupule iki ia, akā ma ke kiʻikuhi he mea maʻalahi nā mea a pau.
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;
A eia ka mea nui ʻaʻole like lanakila 1.5 manawa i ka manawa, a ua unuhi mākou i nā mea hoʻopaʻa liʻiliʻi, ʻoiai he 5 wale nō kā mākou kelepona i ka papa kuhikuhi ma mua o 30!
ʻO kahi bonus ʻē aʻe ka ʻoiaʻiʻo ma hope o ka unnest hope loa, e hoʻomau ʻia nā mea ʻike e nā "pae".
hōʻailona node
ʻO ka manaʻo hou aʻe e kōkua i ka hoʻomaikaʻi ʻana i ka hana ʻo − ʻAʻole hiki i nā "lau" ke hānau keiki, ʻo ia hoʻi, no lākou ʻaʻole pono e nānā "i lalo". I ka hoʻokumu ʻana i kā mākou hana, ʻo ia ka mea inā mākou e hahai i ke kaulahao o nā keʻena a hiki i kahi limahana, a laila ʻaʻohe pono e nānā hou aku ma kēia lālā.
E komo kāua i loko o kā mākou papaʻaina hou aku boolean- kahua, e haʻi koke mai iā mākou inā he "node" kēia komo kikoʻī i loko o kā mākou kumulāʻau - ʻo ia hoʻi, inā hiki iā ia ke loaʻa nā mamo.
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 мс.Nui! ʻIke ʻia he ʻoi aku ka liʻiliʻi ma mua o 30% o nā kumu lāʻau āpau i loaʻa nā mamo.
I kēia manawa, e hoʻohana kākou i kahi mīkini ʻokoʻa ʻē aʻe - pili i ka ʻāpana recursive ma LATERAL, e ʻae iā mākou e komo koke i nā kahua o ka "papa" recursive, a hoʻohana i kahi hana aggregate me kahi kūlana kānana e pili ana i kahi node e hōʻemi i ka hoʻonohonoho o nā kī:
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;
Ua hiki iā mākou ke hōʻemi i hoʻokahi kelepona kuhikuhi a lanakila ma mua o 2 manawa i ka leo hoʻoponopono.
#2. E hoʻi kāua i ke aʻa
Pono kēia algorithm inā pono ʻoe e hōʻiliʻili i nā moʻolelo no nā mea āpau "i luna o ka lāʻau", ʻoiai e mālama ana i ka ʻike e pili ana i ka pepa kumu (a me nā mea hōʻailona) i hoʻokomo ʻia i loko o ka laʻana - no ka laʻana, e hana i kahi hōʻike hōʻuluʻulu. me ka houluulu ana i na node.
Pono e lawe wale ʻia nā mea e hiki mai ana ma ke ʻano he hōʻoia-o-manaʻo, no ka mea, lilo ka noi i mea paʻakikī loa. Akā inā ʻoi aku ka mana o kāu waihona, pono ʻoe e noʻonoʻo e pili ana i ka hoʻohana ʻana i nā ʻenehana like.
E hoʻomaka kākou me kekahi mau ʻōlelo maʻalahi:
- ʻO ka moʻolelo like mai ka waihona ʻOi aku ka maikaʻi e heluhelu hoʻokahi wale nō.
- Nā moʻolelo mai ka waihona ʻOi aku ka maikaʻi o ka heluhelu ʻana i nā pūʻuluma mua o ka hoʻokahi.
I kēia manawa e ho'āʻo mākou e kūkulu i ka noi a mākou e pono ai.
pani 1
ʻIke loa, i ka hoʻomaka ʻana i ka recursion (i hea mākou me ka ʻole!) pono mākou e unuhi i nā moʻolelo o nā lau iā lākou iho ma muli o ka hoʻonohonoho o nā mea hōʻike mua:
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
...
Inā he mea ʻē aʻe i kekahi e mālama ʻia ka "set" ma ke ʻano he kaula a ʻaʻole he ʻano, a laila aia kahi wehewehe maʻalahi no kēia. Aia kahi hana "gluing" i kūkulu ʻia no nā kaula string_agg, ʻaʻole naʻe no nā papa kuhikuhi. ʻOiai ʻo ia .
pani 2
I kēia manawa e loaʻa iā mākou kahi hoʻonohonoho o nā ID ʻāpana e pono e heluhelu hou ʻia. Aneane i nā manawa a pau e hoʻopaʻi ʻia lākou i nā moʻolelo like ʻole o ka hoʻonohonoho kumu - pēlā mākou e hana ai hui iā lākou, ʻoiai e mālama ana i ka ʻike e pili ana i nā lau kumu.
Akā eia nā pilikia ʻekolu e kali nei iā mākou:
- ʻAʻole hiki i ka ʻāpana "subrecursive" o ka nīnau ke loaʻa nā hana aggregate me
GROUP BY. - ʻAʻole hiki ke kuhikuhi ʻia i kahi "papa" recursive ma kahi subquery pūnana.
- ʻAʻole hiki ke loaʻa kahi CTE i kahi noi ma ka ʻāpana recursive.
ʻO ka mea pōmaikaʻi, ua maʻalahi kēia mau pilikia āpau e hana a puni. E hoʻomaka kākou mai ka hopena.
CTE ma ka ʻāpana recursive
Eia nō ole hana:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)A pela no ka hana ana, na ka makua ka mea i okoa!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Huli hoʻopaʻa ʻia e pili ana i kahi "papakaukau" recursive
Hmm... ʻAʻole hiki ke kiʻi ʻia kahi CTE recursive ma kahi subquery. Akā aia paha i loko o CTE! A hiki i kahi noi pūnana ke komo i kēia CTE!
GROUP BY loko recursion
He mea leʻaleʻa, akā ... Loaʻa iā mākou kahi ala maʻalahi e hoʻohālike i ka GROUP BY ka hoʻohana ʻana DISTINCT ON a me nā hana pukaaniani!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!A penei ka hana ana!
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 NULLI kēia manawa, ʻike mākou i ke kumu i hoʻololi ʻia ai ka ID helu i kikokikona - i hiki ke hoʻohui pū ʻia i ka hoʻokaʻawale ʻia e nā koma!
pani 3
No ka hope ʻaʻohe mea i koe:
- heluhelu mākou i nā moʻolelo "ʻāpana" e pili ana i kahi pūʻulu o nā ID hui
- hoʻohālikelike mākou i nā ʻāpana i unuhi ʻia me nā "set" o nā pepa kumu
- "hoʻonui" i ke kaula hoʻonohonoho me ka hoʻohana ʻana
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