In sistemi ERP cumplessi parechje entità anu una natura gerarchicaquandu l'uggetti omogenei si allineanu arbre di relazioni antenati-discendente - questu hè a struttura urganisazione di l'impresa (tutti questi rami, dipartimenti è gruppi di travagliu), è u catalogu di merchenzie, è spazii di travagliu, è a geografia di i punti di vendita, ...
In fatti, ùn ci hè nimu , induve ùn ci saria nisuna gerarchia per quessa. Ma ancu s'ellu ùn travaglia micca "per l'affari", pudete ancu facilmente scontru relazioni gerarchiche. Hè trite, ancu u vostru arbulu di famiglia o u pianu di u locu in un centru cummerciale hè a stessa struttura.
Ci hè parechje manere di almacenà un tali arburu in un DBMS, ma oghje ci focalizemu solu nantu à una sola opzione:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
È mentre sguardi in a prufundità di a ghjerarchia, aspetta cun pacienza di vede quantu [in] efficaci seranu i vostri modi "ingenu" di travaglià cù una tale struttura.
Fighjemu i prublemi tipici chì si presentanu, a so implementazione in SQL, è pruvate à migliurà a so prestazione.
#1. Quantu hè a prufundità di a tana di u cunigliu?
Accittemu, per a definizione, chì sta struttura rifletterà a subordinazione di i dipartimenti in a struttura di l'urganisazione : dipartimenti, divisioni, settori, rami, gruppi di travagliu... - cum'è vo chjamate.
Prima, generemu u nostru "arbre" di elementi 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;Cuminciamu cù u travagliu più simplice - truvà tutti l'impiegati chì travaglianu in un settore specificu, o in termini di ierarchia - truvà tutti i figlioli di un node. Saria ancu bellu di ottene a "prufundità" di u discendenti... Tuttu chistu pò esse necessariu, per esempiu, per custruisce un tipu di .
Tuttu saria bè s'ellu ci sò solu un paru di livelli di sti discendenti è u numeru hè in una duzina, ma s'ellu ci hè più di 5 livelli, è ci sò digià decine di discendenti, pò esse prublemi. Fighjemu cumu l'opzioni tradiziunali di ricerca in l'arbulu sò scritte (è travaglianu). Ma prima, determinemu quali nodi seranu i più interessanti per a nostra ricerca.
U più "profunda" subarburi:
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}
...U più "largu" subarburi:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Per queste dumande avemu usatu u tipicu JOIN recursive:
Ovviamente, cù stu mudellu di dumanda u numeru di iterazioni currisponde à u numeru tutale di discendenti (è ci sò parechje decine di elli), è questu pò piglià risorse assai significativu, è, in u risultatu, u tempu.
Cuntrollamu nantu à u subtree "più largu":
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;
Comu aspittatu, avemu trovu tutti i 30 records. Ma anu passatu 60% di u tempu tutale nantu à questu - perchè anu ancu fattu 30 ricerche in l'indici. Hè pussibule di fà menu?
Lecture en masse par indice
Avemu bisognu di fà una dumanda d'indici separata per ogni node? Risulta micca - pudemu leghje da l'indici usendu parechje chjave à una volta in una sola chjama cun l'aiutu di = ANY(array).
È in ogni tali gruppu di identificatori pudemu piglià tutti l'ID truvati in u passu precedente da "nodes". Vale à dì, à ogni passu prossimu avemu cercate tutti i discendenti di un certu livellu à una volta.
Solu, quì hè u prublema, in a selezzione recursiva, ùn pudete micca accede à sè stessu in una query nidificata, ma avemu bisognu di selezziunà di qualchì manera solu ciò chì hè stata trovata à u nivellu precedente ... Ci hè chì hè impussibile di fà una query nidificata per tutta a selezzione, ma per u so campu specificu hè pussibule. È questu campu pò ancu esse un array - chì hè ciò chì avemu bisognu di usà ANY.
Sembra un pocu loca, ma in u diagramma tuttu hè simplice.
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;
È quì u più impurtante ùn hè ancu vince 1.5 volte in u tempu, è chì avemu sottrattu menu buffers, postu chì avemu solu 5 chjama à l'indici invece di 30 !
Un bonus addiziale hè u fattu chì dopu à l'unnest finali, l'identificatori fermanu urdinati per "livelli".
Signu di nodu
A prussima considerazione chì aiutarà à migliurà u rendiment hè - "foglie" ùn pò micca avè figlioli, vale à dì, per elli ùn ci hè bisognu di guardà "down" in tuttu. In a formulazione di u nostru compitu, questu significa chì se avemu seguitu a catena di dipartimenti è ghjunghje à un impiigatu, allora ùn ci hè bisognu di circà più in questu ramu.
Entremu in a nostra tavula supplementu boolean- campu, chì ci dicerà immediatamente se questa entrata particulare in u nostru arbulu hè un "node" - vale à dì s'ellu pò avè discendenti in tuttu.
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 мс.Perfettu! Ci hè chì solu un pocu più di 30% di tutti l'elementi di l'arburu anu discendenti.
Avà usemu un meccanicu pocu sfarente - cunnessione à a parte recursiva attraversu LATERAL, chì ci permetterà di accede immediatamente à i campi di a "tavula" recursiva, è aduprà una funzione aggregata cù una cundizione di filtrazione basatu annantu à un node per riduce u settore di chjave:
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;
Pudemu riduzzione di una chjama d'indici più è vintu più di 2 volte in u voluminu correttu.
#2. Riturnemu à e radiche
Questu algoritmu serà utile s'ellu avete bisognu di cullà records per tutti l'elementi "up l'arbulu", mentre mantene l'infurmazioni nantu à quale fogliu di fonte (è cù quale indicatori) hà fattu esse inclusu in u sample - per esempiu, per generà un rapportu riassuntu. cun aggregazione in nodi.
Ciò chì seguita deve esse pigliatu solu cum'è una prova di cuncettu, postu chì a dumanda risulta assai ingombrante. Ma s'ellu domina a vostra basa di dati, duvete pensà à utilizà tecniche simili.
Cuminciamu cù un paru di dichjarazioni simplici:
- U listessu record da a basa di dati Hè megliu di leghje solu una volta.
- Records da a basa di dati Hè più efficaci di leghje in batchchè solu.
Avà pruvemu à custruisce a dumanda chì avemu bisognu.
mossa 1
Ovviamente, quandu si inizializza a ricursione (induve sariamu senza!) Avemu da sottrae i registri di e foglie stessu basatu annantu à l'inseme di identificatori iniziali:
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
...
Se pareva stranu à qualchissia chì u "set" hè guardatu cum'è una stringa è micca un array, allora ci hè una spiegazione simplice per questu. Ci hè una funzione integrata di "incollatura" di aggregazione per e corde string_agg, ma micca per arrays. Ancu ella .
mossa 2
Avà avemu avutu un inseme di ID di sezione chì deve esse leghje più. Quasi sempre seranu duplicati in diversi registri di u settore originale - cusì avemu raggruppali, mentri priservà l'infurmazioni nantu à e foglie fonte.
Ma quì ci aspetta trè guai:
- A parte "subrecursiva" di a dumanda ùn pò micca cuntene funzioni aggregate cù
GROUP BY. - Una riferenza à una "tavula" recursiva ùn pò micca esse in una subquery nidificata.
- Una dumanda in a parte recursiva ùn pò micca cuntene un CTE.
Fortunatamente, tutti sti prublemi sò abbastanza faciuli di travaglià. Cuminciamu da a fine.
CTE in parte recursiva
Eccu accussì ùn opere:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)È cusì funziona, i parentesi facenu a diferenza !
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Query nidificata contr'à una "tavula" recursiva
Hmm... Un CTE recursive ùn pò micca accede in una subquery. Ma puderia esse in CTE ! È una dumanda nidificata pò digià accede à questu CTE!
GROUP BY in a ricursione
Hè dispiacevule, ma... Avemu un modu simplice per emulà GROUP BY usendu DISTINCT ON e funzioni di finestra!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!È questu hè cumu funziona!
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 NULLAvà vedemu perchè l'ID numericu hè statu trasfurmatu in testu - per ch'elli ponu esse uniti separati da virgule!
mossa 3
Per a finale ùn ci hè più nunda:
- avemu lettu records "section" basatu annantu à un inseme di ID raggruppati
- paragunemu e rùbbriche sottratte cù i "setti" di i fogli originali
- "espansione" u set-string usendu
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