Litsamaisong tse rarahaneng tsa ERP mekhatlo e mengata e na le sebopeho sa hierarchicalha lintho tse homogeneous li mela sefate sa likamano tsa badimo le badimo - ena ke sebopeho sa mokhatlo oa khoebo (makala ana kaofela, mafapha le lihlopha tsa mosebetsi), le lethathamo la thepa, le libaka tsa mosebetsi, le geography ea lintlha tsa thekiso, ...
Ha e le hantle, ha ho na letho , moo ho neng ho ke ke ha e-ba le bolaoli leha e le bofe ka lebaka leo. Empa le haeba o sa sebetse "bakeng sa khoebo," o ntse o ka kopana habonolo le likamano tsa maemo a holimo. Ke trite, esita le sefate sa lelapa la hau kapa moralo oa fatše oa meaho setsing sa mabenkele ke sebopeho se ts'oanang.
Ho na le mekhoa e mengata ea ho boloka sefate se joalo ho DBMS, empa kajeno re tla tsepamisa maikutlo ho khetho e le 'ngoe feela:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
'Me ha u ntse u nyarela botebong ba sehlopha sa maemo a holimo, se emetse ka mamello ho bona hore na mekhoa ea hau e "sa tsebeng" ea ho sebetsa ka sebopeho se joalo e tla sebetsa joang.
Ha re shebeng mathata a tloaelehileng a hlahang, ts'ebetsong ea bona ho SQL, 'me re leke ho ntlafatsa ts'ebetso ea bona.
#1. Mokoti oa mmutla o tebile hakae?
Ka ‘nete ha re amoheleng hore sebopeho sena se tla bonts’a hore mafapha a ka tlaasana ka sebopeho sa mokhatlo: mafapha, likarohano, mafapha, makala, lihlopha tsa tšebetso... – eng kapa eng eo le e bitsang.
Taba ea pele, ha re hlahiseng 'sefate' sa rona sa likarolo tse 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;Ha re qaleng ka mosebetsi o bonolo ka ho fetisisa - ho fumana basebetsi bohle ba sebetsang ka har'a lekala le itseng, kapa ho latela maemo a phahameng - fumana bana bohle ba node. Hape ho ka ba monate ho fumana "botebo" ba setloholo ... Sena sohle se ka 'na sa hlokahala, mohlala, ho haha mofuta o itseng oa .
Ntho e 'ngoe le e' ngoe e ka ba hantle haeba ho na le mekhahlelo e 'maloa feela ea litloholo tsena' me palo e ka tlase ho leshome le metso e 'meli, empa haeba ho na le maemo a fetang 5,' me ho se ho e-na le litloholo tse ngata, ho ka 'na ha e-ba le mathata. Ha re shebeng hore na likhetho tsa setso tsa ho batla fatše li ngotsoe joang (le ho sebetsa). Empa pele, a re boneng hore na ke li-node life tse tla khahla haholo lipatlisiso tsa rona.
Ka ho fetisisa "tebileng" subtrees:
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}
...Ka ho fetisisa "bophara" subtrees:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Bakeng sa lipotso tsena re ne re sebelisa e tloaelehileng recursive KENYA:
Ho hlakile hore ka mohlala ona oa kopo palo ea ho pheta-pheta e tla lumellana le palo eohle ea litloholo ('me ho na le tse' maloa tsa tsona), 'me sena se ka nka lisebelisoa tsa bohlokoa, ka lebaka leo, nako.
Ha re hlahlobeng "sefate se pharaletseng":
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;
Joalokaha ho ne ho lebelletsoe, re fumane lirekoto tsohle tse 30. Empa ba sebelisitse 60% ea nako eohle ho sena - hobane ba entse lipatlisiso tse 30 ho index. Na hoa khoneha ho etsa ho fokolang?
Ho bala ka bongata ka index
Na re hloka ho etsa potso e arohaneng ea index bakeng sa node ka 'ngoe? Hoa etsahala hore che - re ka bala ho tsoa ho index ho sebedisa dikonopo tse mmalwa ka nako e le nngwe pitsong e le nngwe ka thuso = ANY(array).
'Me sehlopheng se seng le se seng sa li-identifiers re ka nka li-ID tsohle tse fumanoang mohato o fetileng ka "nodes". Ke hore, mohatong o mong le o mong o latelang re tla batla ditloholo tsohle tsa boemo bo itseng hanghang.
Ke lehlohonolo feela, khethong e iphetang, u ke ke ua khona ho iphumanela potso e behiloeng, empa ho hlokahala hore ka tsela e itseng re khethe feela se fumanoeng boemong bo fetileng ... Hoa etsahala hore ho ke ke ha khoneha ho etsa potso ea sehlaha bakeng sa khetho eohle, empa bakeng sa tšimo ea eona e khethehileng e ka khoneha. Mme lebala lena le lona e ka ba letoto - e leng seo re hlokang ho se sebelisa ANY.
E utloahala e le bohlanya hanyenyane, empa setšoantšong ntho e 'ngoe le e' ngoe e bonolo.
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;
'Me mona ntho ea bohlokoa ka ho fetisisa ha e joalo hlola makhetlo a 1.5 ka nako, le hore re tlositse li-buffer tse fokolang, kaha re na le mehala e 5 feela ho index ho fapana le 30!
Bonase e eketsehileng ke 'nete ea hore ka mor'a ho se tsotelle ho qetela, li-identifiers li tla lula li laetsoe ke "maemo".
Letšoao la node
Monahano o latelang o tla thusa ho ntlafatsa ts'ebetso ke - "makhasi" ha a khone ho ba le bana, ke hore, ho bona ha ho hlokahale hore ba shebe "fatše" ho hang. Ha ho etsoa mosebetsi oa rona, taba ena e bolela hore haeba re ile ra latela ketane ea mafapha eaba re fihla ho mohiruoa, joale ha ho sa hlokahala hore ho shejoe hape lekaleng lena.
Ha re kene tafoleng ea rona tlatsetso boolean- lebala, e tla re bolella hang-hang hore na ho kena hona ha sefate sa rona ke "node" - ke hore, hore na e ka ba le litloholo ho hang.
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 мс.E kholo! Hoa fumaneha hore ke karolo e fetang 30% feela ea likarolo tsohle tsa lifate tse nang le litloholo.
Joale ha re sebeliseng mochini o fapaneng hanyane - likhokahano ho karolo e iphetang ka ho LATERAL, e tla re lumella hore re fihle hang-hang masimong a "tafole" e pheta-phetoang, 'me re sebelise ts'ebetso e akaretsang e nang le boemo ba ho sefa bo thehiloeng ho node ho fokotsa lihlopha tsa linotlolo:
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;
Re khonne ho fokotsa mohala o le mong hape oa index le e hapile makhetlo a fetang 2 ka molumo hlahlobisisa.
#2. Ha re khutleleng metsong
Algorithm ena e tla ba molemo haeba o hloka ho bokella lirekoto tsa likarolo tsohle "ho tloha sefateng", ha o ntse o boloka tlhahisoleseling mabapi le hore na ke leqephe lefe la mohloli (le ka matšoao afe) le entseng hore le kenyeletsoe sampuling - ka mohlala, ho hlahisa tlaleho ea kakaretso. ka ho kopanya ho etsa nodes.
Se latelang se lokela ho nkoa feela e le bopaki ba maikutlo, kaha kopo e bonahala e le boima haholo. Empa haeba e laola database ea hau, o lokela ho nahana ka ho sebelisa mekhoa e tšoanang.
Ha re qale ka lipolelo tse 'maloa tse bonolo:
- Rekoto e tšoanang ho tsoa ho database Ho molemo ho e bala hang feela.
- Litlaleho tse tsoang ho database Ho sebetsa hantle ho feta ho bala ka lihlophahofeta mong.
Joale ha re lekeng ho theha kopo eo re e hlokang.
hata 1
Ho hlakile hore, ha re qala recursion (re ka be re le kae ntle le eona!) re tla tlameha ho tlosa lirekoto tsa makhasi ka botsona ho ipapisitse le sehlopha sa li-identifiers tsa pele:
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
...
Haeba ho ne ho bonahala ho makatsa ho motho e mong hore "sete" se bolokoa e le khoele eseng letoto, joale ho na le tlhaloso e bonolo bakeng sa sena. Ho na le ts'ebetso e kopaneng ea "gluing" bakeng sa likhoele string_agg, empa eseng bakeng sa lihlopha. Leha a .
hata 2
Joale re tla fumana li-ID tsa karolo tse tla hloka ho baloa ho ea pele. Hoo e ka bang kamehla li tla kopitsoa lirekotong tse fapaneng tsa sete ea mantlha - ka hona re ne re tla etsa joalo lihlopha, ha u ntse u boloka boitsebiso bo mabapi le makhasi a mohloli.
Empa re letetsoe ke mathata a mararo:
- Karolo ea "subrecursive" ea potso e ke ke ea ba le mesebetsi e akaretsang le
GROUP BY. - Ha ho buuoa ka "tafole" e iphetang, e ke ke ea e-ba sebakeng se seng se le seng.
- Kopo karolong e phetoang e ke ke ea ba le CTE.
Ka lehlohonolo, ho bonolo ho rarolla mathata ana kaofela. Ha re qale ho tloha qetellong.
CTE ka karolo e pheta-phetoang
Mona ho joalo ha sebetsa:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)'Me kahoo ea sebetsa, masaka a etsa phapang!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Potso e hlahang khahlano le "tafole" e iphetang
Hmm... Recursive CTE e ke ke ea fumaneha ka potso e nyane. Empa e ka ba ka hare ho CTE! 'Me kopo e hlophisitsoeng e se e ka khona ho fumana CTE ena!
GROUP BY ka hare recursion
Ha ho monate, empa ... Re na le mokhoa o bonolo oa ho etsisa GROUP KA ho sebedisa DISTINCT ON le mesebetsi ea fensetere!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!'Me ke kamoo e sebetsang kateng!
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 NULLJoale rea bona hore na ke hobane'ng ha ID ea linomoro e ile ea fetoloa mongolo - e le hore li ka kopanngoa 'moho li arohanngoa ke lifeheloa!
hata 3
Bakeng sa ho qetela ha re na letho le setseng:
- re bala lirekoto tsa "karolo" ho latela sehlopha sa li-ID tse hlophisitsoeng
- re bapisa likarolo tse ntšitsoeng le "li-sete" tsa maqephe a pele
- “atolosa” setha-thale ka ho sebedisa
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