Na mgbagwoju ERP usoro ọtụtụ ụlọ ọrụ nwere ọdịdị nhazi ọkwamgbe ihe jikọrọ ọnụ na-abanye n'ahịrị osisi nke nna-nna-mmadụ mmekọrịta - nke a bụ nhazi nhazi nke ụlọ ọrụ (alaka ndị a niile, ngalaba na otu ọrụ), na katalọgụ nke ngwongwo, na ebe ọrụ, na ọdịdị ala nke ebe ahịa, ...
N'ezie, ọ dịghị nke , ebe a na-agaghị enwe ọkwa ọ bụla n'ihi ya. Ma ọbụlagodi na ị naghị arụ ọrụ "maka azụmahịa," ị ka nwere ike izute mmekọrịta ndị isi. Ọ bụ ihe na-adịghị mma, ọbụna osisi ezinụlọ gị ma ọ bụ atụmatụ ala ụlọ dị na ebe ịzụ ahịa bụ otu ihe ahụ.
Enwere ọtụtụ ụzọ isi chekwaa osisi dị otú ahụ na DBMS, mana taa, anyị ga-elekwasị anya naanị otu nhọrọ:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
Ma ka ị na-eleba anya na omimi nke ndị isi, ọ na-eji ndidi na-echere ịhụ ka ụzọ “enweghị uche” gị si arụ ọrụ n'ụdị a ga-esi dị irè.
Ka anyị leba anya n'ụdị nsogbu ndị na-ebilite, mmejuputa ha na SQL, ma gbalịa imeziwanye arụmọrụ ha.
#1. Kedu omimi nke oghere oke bekee?
Ka anyị, n'ezie, nabata na usoro a ga-egosipụta subordination nke ngalaba na nhazi nke nzukọ: ngalaba, nkewa, ngalaba, alaka, ọrụ otu ... - ihe ọ bụla ị na-akpọ ha.
Mbụ, ka anyị mepụta 'osisi' anyị nke ihe 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;Ka anyị jiri ọrụ kachasị mfe malite - ịchọta ndị ọrụ niile na-arụ ọrụ n'ime otu ngalaba, ma ọ bụ n'ihe gbasara ọkwa - chọta ụmụ niile nke ọnụ ọnụ. Ọ ga-adịkwa mma ịnweta "omimi" nke ụmụ ... Ihe a niile nwere ike ịdị mkpa, dịka ọmụmaatụ, iji wuo ụdị ụfọdụ. .
Ihe niile ga-adị mma ma ọ bụrụ na ọ bụ naanị ọkwa ole na ole nke ụmụ ndị a na ọnụ ọgụgụ dị n'ime iri na abụọ, ma ọ bụrụ na e nwere ihe karịrị ọkwa 5, ma enweelarị ọtụtụ ụmụ, enwere ike inwe nsogbu. Ka anyị leba anya ka esi dee nhọrọ nchọ ala ala ọdịnala (ma na-arụ ọrụ). Mana nke mbụ, ka anyị chọpụta ọnụ ụzọ ga-akacha amasị nyocha anyị.
Kasị "miri" osisi subita:
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}
...Kasị "obosara" osisi subita:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Maka ajụjụ ndị a anyị na-eji ahụkarị recursive JOIN:
N'ụzọ doro anya, na ụdị arịrịọ a ọnụ ọgụgụ nke mbigharị ga-adakọ na ọnụ ọgụgụ ụmụ ụmụ (ma e nwere ọtụtụ iri na abuo n'ime ha), nke a nwere ike were ezigbo ihe onwunwe, na, n'ihi ya, oge.
Ka anyị lelee na subtree "kachasị ukwuu":
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;
Dị ka a tụrụ anya ya, anyị chọtara ihe ndekọ 30 niile. Mana ha nọrọ 60% nke ngụkọta oge na nke a - n'ihi na ha mekwara nyocha 30 na ndeksi. Ọ ga-ekwe omume ime obere ihe?
Iji ndeksi agụgharị nnukwu ihe
Anyị kwesịrị ịme ajụjụ ndetu dị iche maka ọnụ ọ bụla? Ọ na-apụta na mba - anyị nwere ike ịgụ site na ndeksi na-eji ọtụtụ igodo n'otu oge n'otu oku site n'enyemaka = ANY(array).
Na n'ime ụdị njirimara nke ọ bụla, anyị nwere ike were ID niile dị na nzọụkwụ gara aga site na "ọnụ". Ya bụ, na nke ọ bụla nzọụkwụ ọzọ anyị ga- chọọ ụmụ ụmụ niile nke otu ọkwa otu oge.
Naanị, ebe a bụ nsogbu, na nhọrọ recursive, ị nweghị ike ịnweta onwe ya na ajụjụ akwụkwụ, ma anyị kwesịrị n'ụzọ ụfọdụ họrọ naanị ihe a hụrụ na gara aga larịị ... Ọ na-apụta na ọ gaghị ekwe omume ime a nested ajụjụ maka dum nhọrọ, ma maka ya kpọmkwem ubi ọ ga-ekwe omume. Na mpaghara a nwekwara ike ịbụ ihe n'usoro - nke bụ ihe anyị kwesịrị iji ANY.
Ọ na-ada ụda ntakịrị, ma na eserese ahụ ihe niile dị mfe.
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 ebe a ihe kacha mkpa abụghị ọbụna merie ugboro 1.5 na oge, na na anyị wepụrụ ihe ntanye ole na ole, ebe ọ bụ na anyị nwere naanị oku 5 na index kama 30!
Ihe mgbakwunye ọzọ bụ eziokwu na mgbe ọgba aghara ikpeazụ gasịrị, ndị na-achọpụta ga-anọgide na-enye iwu site na "ọkwa".
Akara ọnụ
Ntụle ọzọ nke ga-enyere aka melite arụmọrụ bụ - "akwụkwọ" enweghị ike ịmụ ụmụ, ya bụ, maka ha ọ dịghị mkpa ileda “ala” anya ma ọlị. Na nhazi nke ọrụ anyị, nke a pụtara na ọ bụrụ na anyị gbasoro agbụ nke ngalaba wee ruo onye ọrụ, mgbe ahụ ọ dịghị mkpa ileba anya n'ihu n'akụkụ alaka a.
Ka anyị banye na tebụl anyị mgbakwunye boolean- ubi, nke ga-agwa anyị ozugbo ma ntinye a dị na osisi anyị bụ "ọnụ" - ya bụ, ma ọ nwere ike ịmụ ụmụ.
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 мс.Nnukwu! Ọ na-apụta na ọ bụ naanị ntakịrị ihe karịrị 30% nke ihe niile osisi nwere ụmụ.
Ugbu a, ka anyị jiri obere igwe dị iche iche - njikọ na akụkụ nke recursive site na LATERAL, nke ga-enye anyị ohere ịbanye ozugbo n'ọhịa nke "tebụl" na-emegharị ugboro ugboro, ma jiri ọrụ nchịkọta na ọnọdụ nzacha dabere na ọnụ iji belata nhazi igodo:
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;
Anyị nwere ike ibelata otu oku index ọzọ na meriri ihe karịrị ugboro 2 na olu gụgharia.
#2. Ka anyị laghachi na mgbọrọgwụ
Algọridim a ga-aba uru ma ọ bụrụ na ịchọrọ ịnakọta ndekọ maka ihe niile "n'elu osisi", ebe ị na-ejigide ozi gbasara akwụkwọ mpempe akwụkwọ (yana ihe ndị na-egosi) mere ka etinye ya na nlele - dịka ọmụmaatụ, ịmepụta akụkọ nchịkọta. na mkpokọta n'ime ọnụ.
Ekwesịrị iwere ihe na-esote naanị dị ka ihe akaebe nke echiche, ebe ọ bụ na arịrịọ a na-eme ka ọ dị oke egwu. Ma ọ bụrụ na ọ na-achịkwa nchekwa data gị, ị kwesịrị iche echiche maka iji usoro ndị yiri ya.
Ka anyị jiri okwu ole na ole dị mfe bido:
- Otu ndekọ sitere na nchekwa data Ọ kacha mma ịgụ ya naanị otu ugboro.
- Ihe ndekọ sitere na nchekwa data Ọ ka mma ịgụ na batcheskarịa naanị ya.
Ugbu a, ka anyị gbalịa ịmepụta arịrịọ anyị chọrọ.
kwụpụ 1
N'ụzọ doro anya, mgbe ịmalite ịmaliteghachi (ebee ka anyị ga-anọ na-enweghị ya!) Anyị ga-ewepụ ihe ndekọ nke akwụkwọ ahụ n'onwe ha dabere na nhazi nke njirimara mbụ:
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
...
Ọ bụrụ na ọ dị onye ka ihe ijuanya na a na-echekwa "set" ahụ dị ka eriri ma ọ bụghị nhazi, mgbe ahụ enwere nkọwa dị mfe maka nke a. Enwere arụkọtara arụkọ ọrụ “gluing” maka eriri string_agg, ma ọ bụghị maka nhazi. N'agbanyeghị na ọ .
kwụpụ 2
Ugbu a, anyị ga-enweta akara NJ ngalaba nke ga-achọ ka agụkwuo ya. Ọ fọrọ nke nta ka ọ bụrụ mgbe niile, a ga-atụgharị ha na ndekọ dị iche iche nke ntọala mbụ - yabụ anyị ga-eme ya kpokọta ha, ka ị na-echekwa ozi gbasara akwụkwọ isi mmalite.
Ma ebe a nsogbu atọ na-echere anyị:
- Akụkụ "subrecursive" nke ajụjụ a enweghị ike ịnwe ọrụ nchịkọta
GROUP BY. - Ntụ aka na “tebụl” na-emegharị emegharị enweghị ike ịdị na mpaghara akwu.
- Arịrịọ dị n'akụkụ ntugharịgharị enweghị ike ịnwe CTE.
Ọ dabara nke ọma, nsogbu ndị a niile dị mfe ịrụ ọrụ na gburugburu. Ka anyị malite n'ikpeazụ.
CTE na akụkụ nke recursive
N'ebe a bụghị na-arụ ọrụ:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)Ya mere ọ na-arụ ọrụ, akara aka na-eme ihe dị iche!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Ajuju gbara megide "tebụl" na-emegharị emegharị.
Hmm... Enweghị ike ịnweta CTE recursive na subquery. Mana ọ nwere ike ịbụ n'ime CTE! Arịrịọ akwụ ụgwọ nwere ike ịnweta CTE a!
GROUP BY n'ime recursion
Ọ dịghị mma, mana ... Anyị nwere ụzọ dị mfe iji ṅomie GROUP site n'iji DISTINCT ON na ọrụ windo!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!Ma nke a bụ otú o si arụ ọrụ!
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 NULLUgbu a, anyị na-ahụ ihe mere e ji tụgharịa ID ọnụọgụ ka ọ bụrụ ederede - ka e wee jikọta ha ọnụ site na rịkọm!
kwụpụ 3
Maka ikpeazụ anyị enweghị ihe fọdụrụ:
- anyị na-agụ ndekọ "ngalaba" dabere na nhazi nke ID agbakọtara
- anyị na-atụnyere akụkụ ndị ewepụrụ na “set” nke mpempe akwụkwọ mbụ
- Jiri “gbasaa” eriri setịpụ
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;
isi: www.habr.com