Di pergalên ERP yên tevlihev de gelek pêkhate xwedî cewherê hiyerarşîk indema ku tiştên homojen dikevin rêzê dara têkiliyên bav û kalan - ev sazûmana rêxistinî ya pargîdaniyê ye (hemû ev şax, beş û komên xebatê), û kataloga tiştan, û qadên xebatê, û erdnîgariya xalên firotanê, ...
Bi rastî, tune ye , ku di encamê de dê hîyerarşiyek tune be. Lê her çend hûn "ji bo karsaziyê" nexebitin, hûn dîsa jî dikarin bi hêsanî bi têkiliyên hiyerarşîk re rû bi rû bibin. Tiştek e, tewra dara malbata we an plansaziya qata cîhên li navendek kirrûbirrê jî heman avahî ye.
Gelek awayên hilanîna darek wusa di DBMS-ê de hene, lê îro em ê li ser yek vebijarkê bisekinin:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
Û dema ku hûn li kûrahiya hiyerarşiyê dinêrin, ew bi sebir li bendê ye ku hûn bibînin ka awayên weyên "naîf" ên xebata bi avahiyek wusa re dê çiqas [bê] bibandor bin.
Ka em li pirsgirêkên tîpîk ên ku derdikevin, bicîhkirina wan di SQL de binihêrin û hewl bidin ku performansa wan baştir bikin.
#1. Kuna kêvroşkê çiqas kûr e?
Werin em, ji bo teqez, qebûl bikin ku ev avahî dê bindestiya beşan di avahiya rêxistinê de nîşan bide: beş, beş, beş, şax, komên xebatê... - hûn ji wan re çi dibêjin.
Pêşîn, bila em 'dara' xwe ya ji 10K hêmanan çêbikin
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 em bi karê herî hêsan dest pê bikin - dîtina hemî karmendên ku di nav sektorek taybetî de dixebitin, an jî di warê hiyerarşiyê de - hemû zarokên girêkekê bibînin. Di heman demê de dê xweş be ku meriv "kûrahiya" nijada bi dest bixe... Dibe ku ev hemî hewce be, wek nimûne, ji bo avakirina cûreyek .
Her tişt dê baş be heke tenê çend ast ji van dûndanan hebin û jimar di nav dehekê de be, lê heke ji 5 astan zêdetir bin, û jixwe bi dehan ji dûndan hebin, dibe ku pirsgirêk hebin. Ka em binihêrin ka vebijarkên lêgerînê yên kevneşopî çawa têne nivîsandin (û dixebitin). Lê pêşî, em destnîşan bikin ka kîjan girêk dê ji bo lêkolîna me ya herî balkêş be.
Pir "kûr" binerd:
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}
...Pir "bi ber" binerd:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Ji bo van pirsan me tîpîk bikar anîn recursive JOIN:
Diyar e, bi vê modela daxwazê hejmara dubareyan dê bi hejmara giştî ya dûndanan re li hev bike (û bi dehan ji wan hene), û ev dikare çavkaniyên pir girîng, û, wekî encam, dem bigire.
Ka em li jêrdara "herî fireh" bisekinin:
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;
Wekî ku tê hêvî kirin, me hemî 30 tomar dîtin. Lê wan 60% ji tevahiya wextê li ser vê yekê derbas kir - ji ber ku wan di navnîşan de 30 lêgerîn jî kirin. Ma gengaz e ku meriv kêm bike?
Rastkirina girseyî ji hêla index
Ma em hewce ne ku ji bo her girêkek vekolînek veqetandî çêbikin? Derket holê ku na - em dikarin ji navnîşê bixwînin di yek bangê de çend bişkokan bi kar tînin bi alîkariya = ANY(array).
Û di her komek wiha ya nasnavan de em dikarin hemî nasnameyên ku di gava berê de hatine dîtin ji hêla "girêkan" ve bistînin. Yanî di her gava din de em ê di yekcar de li hemî neviyên astekê bigerin.
Tenê, pirsgirêk li vir e, di hilbijartina vegerî de, hûn nikarin xwe bigihînin pirsek hêlîn, lê pêdivî ye ku em bi rengekî tenê tiştê ku di asta berê de hatî dîtin hilbijêrin... Derket holê ku ne gengaz e ku meriv ji bo tevaya hilbijartinê pirsek hêlînek çêbike, lê ji bo qada wê ya taybetî ew gengaz e. Û ev zevî jî dikare bibe array - ya ku divê em bikar bînin ev e ANY.
Ew hinekî dîn xuya dike, lê di diagramê de her tişt hêsan e.
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;
Û li vir ya herî girîng jî ne win 1.5 car di wextê de, û ku me kêm tampon derxistin, ji ber ku li şûna 5 tenê 30 bangên me hene!
Bonûsek zêde ev e ku piştî nehêlana dawîn, nasname dê li gorî "asta" rêzkirî bimînin.
Nîşana girêk
Nîqaşa din a ku dê alîkariya baştirkirina performansê bike - e "pel" nikare zarokan bike, ango ji bo wan qet ne hewce ye ku meriv li "jêr" binêre. Di amadekirina peywira me de, ev tê vê wateyê ku ger me zincîra beşan bişopîne û bigihîje karmendek, wê hingê ne hewce ye ku li vê şaxê bêtir binihêrin.
Ka em bikevin sifreya xwe biserre boolean-erd, ya ku dê tavilê ji me re vebêje gelo ev navnîşa taybetî ya di dara me de "girêk" e - ango, gelo ew bi tevahî dikare xwediyê dûvkaniyê be.
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 мс.Ecêb! Derket holê ku tenê ji% 30-ê hemî hêmanên darê neviyên wan hene.
Naha werin em mekanîzmek piçûktir bikar bînin - girêdanên bi beşa vegerê re bi rê ve LATERAL, ya ku dê bihêle ku em tavilê xwe bigihînin qadên "tabloya" vegerî, û fonksiyonek hevgirtî bi şertek fîlterkirinê ya li ser bingeha girêkek bikar bînin da ku komek bişkokan kêm bikin:
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;
Me karîbû yek bangek din kêm bikin û di cildê de ji 2 caran zêdetir qezenc kir rastnivîsandin.
#2. Em vegerin ser koka xwe
Ev algorîtma dê kêrhatî be heke hûn hewce ne ku ji bo hemî hêmanên "li ser darê" tomaran berhev bikin, di heman demê de agahdariya li ser kîjan pelika çavkaniyê (û bi kîjan nîşanan) bû sedem ku ew di nav nimûneyê de cih bigire - mînakî, ji bo çêkirina raporek kurt. bi berhevkirina nav girêkan.
Tiştê ku li jêr tê divê tenê wekî delîlek têgînê were girtin, ji ber ku daxwaz pir giran dibe. Lê heke ew databasa we serdest e, divê hûn li ser karanîna teknîkên wekhev bifikirin.
Ka em bi çend gotinên hêsan dest pê bikin:
- Heman tomar ji databasê Çêtir e ku meriv wê tenê carekê bixwîne.
- Qeydên ji databasê Xwendina bi koman bikêrtir eji tenê.
Naha em hewl bidin ku daxwaza ku em hewce ne ava bikin.
gav 1
Eşkere ye, dema destpêkirina vegerê (em ê bêyî wê li ku bin!) em neçar in ku tomarên pelan bixwe li ser bingeha komek nasnavên destpêkê derxînin:
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
...
Ger ji kesek re xerîb xuya bû ku "set" wekî rêzek û ne rêzek tê hilanîn, wê hingê ji bo vê yekê ravekirinek hêsan heye. Ji bo têlan fonksiyonek komkirina "zeliqandinê" ya çêkirî heye string_agg, lê ne ji bo rêzan. Her çend ew .
gav 2
Naha em ê komek nasnameyên beşê bistînin ku hewce ne ku bêtir werin xwendin. Hema hema her gav ew ê di tomarên cihêreng ên set orjînal de bêne dubare kirin - ji ber vê yekê em ê bikin wan kom bikin, dema ku agahdariya li ser pelên çavkaniyê diparêze.
Lê li vir sê pirsgirêk li benda me ne:
- Beşa "binavber" ya pirsê nikare fonksiyonên hevgirtî bi xwe re bigire
GROUP BY. - Referansek ji "tabloya" vegerî re nikare di jêrpirsek hêlînê de be.
- Daxwazek di beşa vegerê de nikare CTE-yê bigire.
Bi bextewarî, hemî van pirsgirêkan bi hêsanî têne xebitandin. Ka em ji dawiyê dest pê bikin.
CTE di beşa vegerî de
Va ye ne dixebitin:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)Û ji ber vê yekê ew dixebite, parantez cûdahiyê dike!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Pirsa hêlînkirî li hember "tabloyek" vegerî
Hmm... CTE-ya paşverû di jêrpirsekê de nayê gihandin. Lê ew dikare di hundurê CTE de be! Û daxwazek hêlînkirî dikare berê xwe bide vê CTE!
GROUP BY hundirê vegerê
Ne xweş e, lê... Rêbazek me ya hêsan heye ku em GROUP BY BI karanîn bikin DISTINCT ON û fonksiyonên pencereyê!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!Û ev çawa dixebite!
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 NULLNaha em dibînin ka çima ID-ya jimareyî veguherî nivîsê - da ku ew bi hev re werin veqetandin û bi koman werin veqetandin!
gav 3
Ji bo fînalê tiştek me nema:
- em qeydên "beş" li ser bingeha komek nasnameyên komkirî dixwînin
- em beşên jêkirî bi "komên" pelên orîjînal re bidin ber hev
- "berfireh" set-string bi kar tînin
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