I gcórais ERP casta tá nádúr ordlathach ag go leor eintiteasnuair a thagann rudaí aonchineálacha suas i crann caidrimh sinsear-shliocht - is é seo struchtúr eagraíochtúil an fhiontair (na brainsí, na ranna agus na grúpaí oibre seo go léir), agus catalóg na n-earraí, agus na réimsí oibre, agus tíreolaíocht na bpointí díolacháin, ...
Go deimhin, níl aon cheann ann , i gcás nach mbeadh aon ordlathas ann dá bharr. Ach fiú mura n-oibríonn tú “don ghnó,” is féidir leat teacht ar chaidreamh ordlathach go héasca. Tá sé trite, is é an struchtúr céanna fiú do chrann teaghlaigh nó do phlean urláir áitribh in ionad siopadóireachta.
Tá go leor bealaí ann chun crann den sórt sin a stóráil i DBMS, ach inniu díreoimid ar rogha amháin:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
Agus tú ag breathnú isteach i doimhneacht an ordlathais, tá sé ag fanacht go foighneach le feiceáil cé chomh héifeachtach agus a bheidh do bhealaí “naive” chun oibriú le struchtúr dá leithéid.
Breathnaímid ar fhadhbanna tipiciúla a thagann chun cinn, a gcur chun feidhme i SQL, agus déanaimis iarracht a gcuid feidhmíochta a fheabhsú.
#1. Cé chomh domhain atá an poll coinín?
Glacaimis, le cinnt, go léireoidh an struchtúr seo fo-ordú na ranna i struchtúr na heagraíochta: ranna, ranna, earnálacha, craobhacha, grúpaí oibre... - is cuma cad a ghlaonn tú orthu.
Ar dtús, déanaimis ár ‘crann’ d’eilimintí 10K a ghiniúint
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;Cuirimis tús leis an tasc is simplí – gach fostaí a oibríonn laistigh d’earnáil ar leith a aimsiú, nó i dtéarmaí ordlathais - teacht ar gach leanbh de nód. Bheadh sé go deas freisin “doimhneacht” an tsliocht a fháil... D’fhéadfadh sé seo go léir a bheith riachtanach, mar shampla, chun cineál éigin a thógáil .
Bheadh gach rud go breá mura bhfuil ach cúpla leibhéal de na sliocht seo agus go bhfuil an líon laistigh de dhosaen, ach má tá níos mó ná 5 leibhéal ann, agus go bhfuil mórán sliocht ann cheana féin, d'fhéadfadh fadhbanna a bheith ann. Breathnaímid ar an gcaoi a scríobhtar (agus a n-oibríonn) roghanna cuardaigh traidisiúnta síos an chrainn. Ach ar dtús, déanaimis a chinneadh cé na nóid a bheidh is suimiúla dár dtaighde.
An chuid is mó "domhain" fochrainn:
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}
...An chuid is mó "leathan" fochrainn:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Le haghaidh na gceisteanna seo d'úsáideamar an gnáthcheist JOIN athchúrsach:
Ar ndóigh, leis an tsamhail iarratais beidh líon na n-atriallta mar aon le líon iomlán na sliocht (agus tá roinnt dosaen acu), agus féadfaidh sé seo acmhainní suntasacha go leor a ghlacadh, agus, mar thoradh air sin, am.
Déanaimis seiceáil ar an bhfochrann “is leithne”:
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;
Mar a bheifí ag súil leis, fuaireamar na 30 taifead ar fad. Ach chaith siad 60% den am iomlán ar seo - toisc go ndearna siad 30 cuardach san innéacs freisin. An féidir níos lú a dhéanamh?
Mórléamh profaí de réir innéacs
An gá dúinn ceist innéacs ar leith a dhéanamh do gach nód? Tharlaíonn sé go raibh aon - is féidir linn a léamh as an innéacs ag baint úsáide as roinnt eochracha ag an am céanna in aon ghlao amháin leis an gcabhair = ANY(array).
Agus i ngach grúpa aitheantóirí den sórt sin is féidir linn na haitheantais go léir a fuarthas sa chéim roimhe seo a ghlacadh trí “nóid”. Is é sin, ag gach chéad chéim eile a bheidh againn cuardach a dhéanamh ar gach sliocht de leibhéal áirithe ag an am céanna.
Ach, seo an fhadhb, sa roghnú athchúrsach, ní féidir leat rochtain a fháil air féin i bhfiosrúchán neadaithe, ach ní mór dúinn a roghnú ar bhealach ach an méid a fuarthas ag an leibhéal roimhe sin... Tharlaíonn sé go raibh sé dodhéanta a dhéanamh ceist neadaithe le haghaidh an roghnú ar fad, ach le haghaidh a réimse sonrach is féidir. Agus is féidir an réimse seo a bheith ina sraith freisin - agus is é sin an méid is gá dúinn a úsáid ANY.
Fuaimeann sé beagán dÚsachtach, ach sa léaráid tá gach rud simplí.
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;
Agus anseo nach bhfuil an rud is tábhachtaí fiú buaigh 1.5 uair in am, agus gur bhaineamar níos lú maoláin amach, ós rud é nach bhfuil againn ach 5 ghlao ar an innéacs in ionad 30!
Bónas breise is ea an fhíric go bhfanfaidh na haitheantóirí ordaithe de réir “leibhéil” tar éis na corraíola deiridh.
Comhartha nód
Is é an chéad bhreithniú eile a chabhróidh le feidhmíocht a fheabhsú ná − Ní féidir le "duilleoga" leanaí a bheith acu, is é sin, ní gá dóibh breathnú “síos” ar chor ar bith. Le linn ár dtasc a fhoirmiú, ciallaíonn sé seo má leanamar slabhra na ranna agus má shroicheamar fostaí, ní gá breathnú níos faide ar an mbrainse seo.
A ligean ar dul isteach inár tábla breise boolean-Gort, a inseoidh dúinn láithreach an “nód” é an iontráil áirithe seo inár gcrann - is é sin, an féidir le sliocht a bheith aige ar chor ar bith.
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 мс.Go hiontach! Tharlaíonn sé go raibh sliocht ach beagán níos mó ná 30% de na heilimintí crann.
Anois bainimis úsáid as meicneoir beagán difriúil - naisc leis an gcuid athfhillteach tríd LATERAL, a ligfidh dúinn rochtain a fháil láithreach ar réimsí an “tábla” athchúrsach, agus feidhm chomhiomlán a úsáid le coinníoll scagtha bunaithe ar nód chun an tacar eochracha a laghdú:
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;
Bhíomar in ann glao innéacs amháin eile a laghdú agus bhuaigh níos mó ná 2 uair i toirt léamh profaí.
#2. A ligean ar dul ar ais go dtí na fréamhacha
Beidh an t-algartam seo úsáideach má theastaíonn uait taifid a bhailiú do na heilimintí go léir “suas an crann”, agus faisnéis á choinneáil agat faoin mbunleathanach (agus cé na táscairí) ba chúis leis a bheith san áireamh sa sampla - mar shampla, chun tuairisc achomair a ghiniúint le comhiomlánú i nóid.
Ba cheart an méid seo a leanas a ghlacadh mar chruthúnas ar choincheap amháin, ó tharla go bhfuil an t-iarratas an-deacair. Ach má tá sé i gceannas ar do bhunachar sonraí, ba cheart duit smaoineamh ar theicnící comhchosúla a úsáid.
Cuirimis tús le cúpla ráiteas simplí:
- An taifead céanna ón mbunachar sonraí Is fearr é a léamh uair amháin.
- Taifid ón mbunachar sonraí Tá sé níos éifeachtaí léamh i mbaisceannaná ina n-aonar.
Anois déanaimis iarracht an t-iarratas a theastaíonn uainn a thógáil.
Céim 1
Ar ndóigh, nuair a bheidh athchúlú á thosú (cá mbeimis gan é!) beidh orainn taifid na nduilleog a dhealú iad féin bunaithe ar thacar na n-aitheantóirí tosaigh:
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
...
Más rud é go raibh cuma aisteach ar dhuine gur mar shreang atá an “tacar” stóráilte agus ní mar eagar, tá míniú simplí air seo. Tá feidhm chomhiomlánaithe “gluing” ionsuite le haghaidh teaghráin string_agg, ach ní le haghaidh eagair. Cé go bhfuil sí .
Céim 2
Anois gheobhaimid sraith IDanna rannóg a chaithfear a léamh tuilleadh. Beagnach i gcónaí déanfar iad a mhacasamhlú i dtaifid éagsúla den bhunsraith - mar sin ba mhaith linn iad a ghrúpáil, agus faisnéis faoi na duilleoga foinse á gcaomhnú.
Ach anseo tá trí thrioblóidí ag fanacht linn:
- Ní féidir feidhmeanna comhiomlána le
GROUP BY. - Ní féidir tagairt do “tábla” athfhillteach a bheith i bhfocheist neadaithe.
- Ní féidir CTE a bheith in iarratas sa chuid athfhillteach.
Ar ámharaí an tsaoil, is furasta na fadhbanna seo go léir a oibriú timpeall. Tosaímid ón deireadh.
CTE sa chuid athfhillteach
Anseo mar sin aon ag obair:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)Agus mar sin oibríonn sé, déanann na lúibíní an difríocht!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Ceist neadaithe i gcoinne "tábla" athchúrsach
Hmm... Ní féidir teacht ar CTE athfhillteach i bhfocheist. Ach d'fhéadfadh sé a bheith taobh istigh CTE! Agus is féidir le hiarratas neadaithe rochtain a fháil ar an CTE seo cheana féin!
GRÚPA AG athchúrsaíocht istigh
Tá sé míthaitneamhach, ach... Tá bealach simplí againn chun aithris a dhéanamh ar GRÚPA TRÍ úsáid DISTINCT ON agus feidhmeanna fuinneoige!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!Agus seo é mar a oibríonn sé!
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 NULLAnois feicimid cén fáth ar cuireadh an ID uimhriúil ina théacs - ionas go bhféadfaí iad a cheangal le chéile agus camóga!
Céim 3
Don chluiche ceannais níl aon rud fágtha againn:
- Léimid taifid “rannóige” bunaithe ar thacar aitheantais ghrúpa
- cuirimid na codanna dealaithe i gcomparáid le “tacair” na mbileog bunaidh
- “leathnaigh” an teaghrán tacair ag baint úsáide as
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;
Foinse: will.com