Ni eka ERP awọn ọna šiše ọpọlọpọ awọn ẹya ni iseda akosoagbasomodenigbati isokan ohun laini soke ni igi ti awọn ibatan baba-nla - Eyi ni eto iṣeto ti ile-iṣẹ (gbogbo awọn ẹka wọnyi, awọn ẹka ati awọn ẹgbẹ iṣẹ), ati katalogi ti awọn ẹru, ati awọn agbegbe iṣẹ, ati ilẹ-aye ti awọn aaye tita,…
Ni otitọ, ko si , nibiti ko ba si ipo-ipo kankan bi abajade. Ṣugbọn paapaa ti o ko ba ṣiṣẹ “fun iṣowo naa,” o tun le ni irọrun pade awọn ibatan akoso. O jẹ alailẹtọ, paapaa igi ẹbi rẹ tabi ero ilẹ ti awọn agbegbe ile ni ile-itaja rira jẹ eto kanna.
Awọn ọna pupọ lo wa lati tọju iru igi kan ni DBMS, ṣugbọn loni a yoo dojukọ aṣayan kan nikan:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
Ati pe lakoko ti o n wo inu awọn ijinle ti awọn logalomomoise, o n duro ni sùúrù lati rii bi [ni] ṣe munadoko awọn ọna “alaiṣe” ti ṣiṣẹ pẹlu iru eto kan yoo jẹ.
Jẹ ki a wo awọn iṣoro aṣoju ti o dide, imuse wọn ni SQL, ati gbiyanju lati mu iṣẹ wọn dara si.
#1. Bawo ni iho ehoro ti jinna?
Jẹ ki a, fun pato, gba pe eto yii yoo ṣe afihan ifarabalẹ ti awọn ẹka ni iṣeto ti ajo: awọn ẹka, awọn ẹka, awọn ẹka, awọn ẹka, awọn ẹgbẹ iṣẹ ... - ohunkohun ti o pe wọn.
Ni akọkọ, jẹ ki a ṣe ipilẹṣẹ 'igi' wa ti awọn eroja 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;Jẹ ki a bẹrẹ pẹlu iṣẹ-ṣiṣe ti o rọrun julọ - wiwa gbogbo awọn oṣiṣẹ ti o ṣiṣẹ laarin eka kan pato, tabi ni awọn ofin ti awọn ipo - ri gbogbo awọn ọmọ ti a ipade. O tun yoo dara lati gba "ijinle" ti iru-ọmọ ... Gbogbo eyi le jẹ pataki, fun apẹẹrẹ, lati kọ iru iru kan. .
Ohun gbogbo yoo dara ti o ba jẹ awọn ipele meji ti awọn iru-ọmọ wọnyi ati pe nọmba naa wa laarin mejila, ṣugbọn ti o ba wa ju awọn ipele 5 lọ, ati pe ọpọlọpọ awọn ọmọ ti wa tẹlẹ, awọn iṣoro le wa. Jẹ ki a wo bii awọn aṣayan wiwa isalẹ-igi ti aṣa ṣe kọ (ati ṣiṣẹ). Ṣugbọn ni akọkọ, jẹ ki a pinnu iru awọn apa ti yoo jẹ igbadun julọ fun iwadii wa.
Pupọ julọ "jinle" awọn igi abẹlẹ:
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}
...Pupọ julọ "fife" awọn igi abẹlẹ:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Fun awọn ibeere wọnyi a lo aṣoju recursive Da:
O han ni, pẹlu awoṣe ibeere yii nọmba awọn aṣetunṣe yoo baramu lapapọ nọmba awọn ọmọ (ati pe ọpọlọpọ awọn mejila ti wọn wa), ati pe eyi le gba awọn orisun pataki pupọ, ati, bi abajade, akoko.
Jẹ ki a ṣayẹwo lori “igi ti o gbooro julọ”:
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;
Gẹgẹbi a ti ṣe yẹ, a rii gbogbo awọn igbasilẹ 30. Ṣugbọn wọn lo 60% ti akoko lapapọ lori eyi - nitori wọn tun ṣe awọn iwadii 30 ni atọka. Ṣe o ṣee ṣe lati ṣe kere si?
Atunse olopobobo nipasẹ atọka
Njẹ a nilo lati ṣe ibeere atọka lọtọ fun ipade kọọkan? O wa ni jade wipe ko si - a le ka lati atọka lilo awọn bọtini pupọ ni ẹẹkan ninu ipe kan pẹlu iranlọwọ = ANY(array).
Ati ninu ẹgbẹ kọọkan ti awọn idamọ a le mu gbogbo awọn ID ti a rii ni igbesẹ ti tẹlẹ nipasẹ “awọn apa”. Iyẹn ni, ni igbesẹ kọọkan ti o tẹle a yoo wa gbogbo awọn ọmọ ti ipele kan ni ẹẹkan.
Nikan, eyi ni iṣoro naa, ni yiyan loorekoore, o ko le wọle si ararẹ ni ibeere itẹ-ẹiyẹ kan, ṣugbọn a nilo lati yan nikan ohun ti a ri ni ipele ti tẹlẹ ... O wa ni pe ko ṣee ṣe lati ṣe ibeere itẹ-ẹiyẹ fun gbogbo aṣayan, ṣugbọn fun aaye rẹ pato o ṣee ṣe. Ati aaye yii tun le jẹ apẹrẹ - eyiti o jẹ ohun ti a nilo lati lo ANY.
O dabi aṣiwere diẹ, ṣugbọn ninu aworan atọka ohun gbogbo rọrun.
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;
Ati nibi ohun pataki julọ kii ṣe paapaa win 1.5 igba ni akoko, ati pe a yọkuro awọn ifipamọ diẹ, nitori pe a ni awọn ipe 5 nikan si atọka dipo 30!
Ajeseku afikun ni otitọ pe lẹhin aisun ikẹhin, awọn idanimọ yoo wa ni aṣẹ nipasẹ “awọn ipele”.
ami ipade
Iyẹwo atẹle ti yoo ṣe iranlọwọ ilọsiwaju iṣẹ jẹ - "ewe" ko le bi awọn ọmọde, iyẹn, fun wọn ko si iwulo lati wo “isalẹ” rara. Ninu iṣeto ti iṣẹ-ṣiṣe wa, eyi tumọ si pe ti a ba tẹle awọn ẹwọn ti awọn ẹka ati de ọdọ oṣiṣẹ kan, lẹhinna ko si ye lati wo siwaju sii pẹlu ẹka yii.
Jẹ ki a wọ inu tabili wa afikun boolean- aaye, eyi ti yoo sọ fun wa lẹsẹkẹsẹ boya titẹ sii pato ninu igi wa jẹ "ipade" - iyẹn ni, boya o le ni iru-ọmọ rara.
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 мс.Nla! O wa ni pe diẹ diẹ sii ju 30% ti gbogbo awọn eroja igi ni awọn ọmọ.
Bayi jẹ ki ká lo kan die-die o yatọ si mekaniki - awọn isopọ si awọn recursive apakan nipasẹ LATERAL, eyi ti yoo gba wa laaye lati wọle si lẹsẹkẹsẹ awọn aaye ti "tabili" atunṣe, ati lo iṣẹ apapọ pẹlu ipo sisẹ kan ti o da lori ipade lati dinku ṣeto awọn bọtini:
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;
A ni anfani lati din ipe atọka diẹ sii ati gba diẹ sii ju 2 igba ni iwọn didun àtúnyẹ̀wò.
#2. Jẹ ki a pada si awọn gbongbo
Algorithm yii yoo wulo ti o ba nilo lati gba awọn igbasilẹ fun gbogbo awọn eroja “oke igi”, lakoko ti o ṣe idaduro alaye nipa iru iwe orisun (ati pẹlu awọn itọkasi wo) jẹ ki o wa ninu apẹẹrẹ - fun apẹẹrẹ, lati ṣe agbekalẹ ijabọ akojọpọ kan. pẹlu apapọ sinu awọn apa.
Ohun ti o tẹle yẹ ki o gba nikan bi ẹri-ti-imọran, niwọn igba ti ibeere naa ti jade lati jẹ ẹru pupọ. Ṣugbọn ti o ba jẹ gaba lori ibi ipamọ data rẹ, o yẹ ki o ronu nipa lilo awọn ilana ti o jọra.
Jẹ ká bẹrẹ pẹlu kan tọkọtaya ti o rọrun gbólóhùn:
- Igbasilẹ kanna lati ibi ipamọ data O dara julọ lati ka ni ẹẹkan.
- Awọn igbasilẹ lati ibi ipamọ data O munadoko diẹ sii lati ka ni awọn ipeleju nikan.
Bayi jẹ ki a gbiyanju lati kọ ibeere ti a nilo.
Igbesẹ 1
O han ni, nigba ti o ba bẹrẹ isọdọtun (nibo ni a yoo wa laisi rẹ!) A yoo ni lati yọkuro awọn igbasilẹ ti awọn leaves funrara wọn da lori ṣeto awọn idamọ akọkọ:
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
...
Ti o ba dabi ẹnipe ajeji si ẹnikan pe “ṣeto” ti wa ni ipamọ bi okun kan kii ṣe opo, lẹhinna alaye ti o rọrun wa fun eyi. Iṣẹ-ṣiṣe “gluing” ti a ṣe sinu rẹ wa fun awọn okun string_agg, ṣugbọn kii ṣe fun awọn akojọpọ. Bó tilẹ jẹ pé ó .
Igbesẹ 2
Bayi a yoo gba ṣeto awọn ID apakan ti yoo nilo lati ka siwaju. Fere nigbagbogbo wọn yoo ṣe pidánpidán ni oriṣiriṣi awọn igbasilẹ ti iṣeto atilẹba - nitorinaa a yoo ẹgbẹ wọn, lakoko ti o tọju alaye nipa awọn leaves orisun.
Ṣugbọn nibi awọn wahala mẹta n duro de wa:
- Apa “subrecursive” ti ibeere naa ko le ni awọn iṣẹ apapọ ninu pẹlu
GROUP BY. - Itọkasi si “tabili” loorekoore ko le wa ni abẹlẹ itẹ-ẹiyẹ.
- Ibeere ti o wa ni apakan atunṣe ko le ni CTE kan ninu.
O da, gbogbo awọn iṣoro wọnyi jẹ ohun rọrun lati ṣiṣẹ ni ayika. Jẹ ká bẹrẹ lati opin.
CTE ni apakan loorekoore
Bi eleyi kii ṣe ṣiṣẹ:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)Ati nitorinaa o ṣiṣẹ, awọn akọmọ ṣe iyatọ!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Ibeere ti o ni itẹlọrun lodi si “tabili” loorekoore
Unh... CTE loorekoore ko le wọle si ni abẹlẹ kan. Ṣugbọn o le jẹ inu CTE! Ati pe ibeere itẹ-ẹiyẹ kan le wọle si CTE yii tẹlẹ!
GROUP BY inu recursion
Ko dun, ṣugbọn... A ni ọna ti o rọrun lati farawe GROUP NIPA lilo DISTINCT ON ati awọn iṣẹ window!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!Ati pe eyi ni bii o ṣe n ṣiṣẹ!
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 NULLBayi a rii idi ti ID nomba ti yipada si ọrọ - ki wọn le darapọ mọ wọn niya nipasẹ aami idẹsẹ!
Igbesẹ 3
Fun ipari a ko ni nkankan ti o kù:
- a ka awọn igbasilẹ "apakan" ti o da lori ipilẹ ti awọn ID ti a ṣe akojọpọ
- a ṣe afiwe awọn apakan iyokuro pẹlu awọn “awọn ipilẹ” ti awọn iwe atilẹba
- “faagun” okun ṣeto nipa lilo
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;
orisun: www.habr.com