I faiga lavelave ERP o le tele o fa'alapotopotoga o lo'o i ai se natura fa'atonupe a laina mea tutusa i totonu laau o sootaga a tuaa-tupuga - o le faʻatulagaga faʻatulagaina lea o le atinaʻe (o nei lala uma, matagaluega ma vaega o galuega), ma le lisi o oloa, ma vaega o galuega, ma le faʻafanua o faʻatauga, ...
O le mea moni, e leai se mea , lea o le a leai se fa'atonuga o se taunuuga. Ae e tusa lava pe e te le galue "mo le pisinisi," e faigofie lava ona e faʻafeiloaʻi i sootaga faʻapitoa. E fa'atauva'a, e o'o lava i lou gafa po'o le fola o le fale i totonu o se faleoloa e tutusa lava le fausaga.
E tele auala e teu ai sea ituaiga laau i totonu o le DBMS, ae o aso nei o le a tatou taulai atu i le tasi filifiliga:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
Ma a'o e va'ava'ai i le loloto o le fa'atonuga, o lo'o fa'atali ma le onosa'i e va'ai pe fa'afefea ona aoga au "fa'avalea" auala e galue ai i sea fausaga.
Sei o tatou vaʻavaʻai i faʻafitauli masani e tulaʻi mai, o latou faʻatinoga i SQL, ma taumafai e faʻaleleia a latou faʻatinoga.
#1. O le a le loloto o le pu lapiti?
Sei o tatou, mo le mautinoa, talia o lenei fausaga o le a atagia ai le subordination o matagaluega i le faatulagaga o le faalapotopotoga: matagaluega, vaevaega, vaega, lala, vaega galulue ... - po o le a lava le igoa latou.
Muamua, se'i o tatou fa'atupuina a tatou 'laau' o elemene 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;Tatou amata i le galuega sili ona faigofie - saili tagata faigaluega uma o loʻo galulue i totonu o se vaega faʻapitoa, poʻo le tulaga o le faʻatulagaina - su'e tamaiti uma o se node. E manaia foi le mauaina o le "loloto" o le suli ... O nei mea uma atonu e tatau, mo se faʻataʻitaʻiga, e fausia ai se ituaiga o .
O le a lelei mea uma pe afai e na o ni nai vaega o nei suli ma o le numera e i totonu o le sefulu, ae afai e sili atu i le 5 tulaga, ma ua uma ona i ai le tele o suli, atonu e iai ni faʻafitauli. Se'i o tatou va'ava'ai pe fa'apefea ona tusia (ma galue) filifiliga su'esu'e i lalo o le la'au masani. Ae muamua, seʻi o tatou fuafua po o fea nodes o le a sili ona manaia mo a tatou suʻesuʻega.
Le tele "loloto" la'au laiti:
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}
...Le tele "lautele" la'au laiti:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Mo nei fesili na matou faʻaaogaina le masani recursive SOI:
E manino lava, faʻatasi ai ma lenei faʻataʻitaʻiga talosaga o le numera o fa'asologa o le a fetaui ma le aofa'i atoa o suli (ma o loʻo i ai ni nai taseni o latou), ma e mafai ona tele ni punaoa taua, ma, o se taunuuga, taimi.
Sei o tatou siaki le "lautele" la'au laiti:
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;
E pei ona faʻamoemoeina, na matou mauaina uma faʻamaumauga e 30. Ae na latou faʻaaluina le 60% o le taimi atoa i lenei mea - aua na latou faia foʻi suʻesuʻega 30 i le faasino igoa. E mafai ona fai sina mea itiiti?
Fa'atonuga tele e fa'asino
E mana'omia ona tatou faia se su'esu'ega fa'asinomaga mo node ta'itasi? E foliga mai e leai - e mafai ona tatou faitau mai le faasino igoa fa'aaoga nisi ki i le taimi e tasi i le telefoni e tasi faatasi ai ma le fesoasoani = ANY(array).
Ma i vaega taʻitasi o faʻamatalaga e mafai ona tatou ave uma ID o loʻo maua i le laasaga muamua e "nodes". O lona uiga, o isi laasaga taitasi o le a tatou faia saili mo suli uma o se tulaga faapitoa i le taimi e tasi.
Pau lava, o le faafitauli lea, i le filifiliga toe fa'afo'i, e le mafai ona e fa'afeso'ota'i i totonu o se fesili fa'aputu, ae e manaʻomia ona tatou filifilia naʻo mea na maua i le tulaga muamua ... E foliga mai e le mafai ona faia se faʻailoga faʻafefe mo le filifiliga atoa, ae mo lona fanua faʻapitoa e mafai. Ma o lenei fanua e mafai foi ona avea ma se laina - o le mea lea e tatau ona tatou faʻaaogaina ANY.
E foliga valea, ae i le ata e faigofie mea uma.
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;
Ma o le mea pito sili ona taua e le o se mea manumalo 1.5 taimi i le taimi, ma na matou toesea ni nai pa puipui, talu ai e na o le 5 a matou valaau i le faasino igoa nai lo le 30!
O se ponesi faaopoopo o le mea moni lea pe a maeʻa le faʻasalaga mulimuli, o le a faʻatonuina faʻamatalaga e "tulaga".
Faailoga node
O le isi iloiloga o le a fesoasoani e faʻaleleia le faʻatinoga o le − "lau" e le mafai ona maua ni fanau, o lona uiga, mo i latou e leai se mea e tatau ai ona tilotilo "i lalo". I le fa'atulagaina o le tatou galuega, o lona uiga afai tatou te mulimulita'i i le faasologa o matagaluega ma o'o atu i se tagata faigaluega, e le mana'omia la ona toe su'esu'e i le lala lea.
Tatou ulu atu i la tatou laulau fa'aopoopo boolean- fanua, lea o le a vave taʻu mai ia i tatou pe o lenei faʻamatalaga faapitoa i totonu o la tatou laau o se "node" - o lona uiga, pe mafai ona i ai ni suli.
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 мс.Matagofie! E aliali mai e na o sina sili atu nai lo le 30% o elemene uma o laʻau e tupuga mai.
Se'i o tatou fa'aogaina se fa'ainisinia teisi - feso'ota'iga i le vaega fa'asolosolo e ala i LATERAL, lea o le a faʻatagaina ai i matou e vave faʻafeiloaʻi fanua o le recursive "table", ma faʻaoga se galuega faʻapipiʻi faʻatasi ma se tulaga faʻamamaina e faʻavae i luga o se node e faʻaitiitia ai le seti o ki:
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;
Na mafai ona matou faʻaititia le tasi le isi faʻasino igoa ma manumalo sili atu i le 2 taimi i le voluma toe faitau.
#2. Tatou toe foi i a'a
O lenei algorithm o le a aoga pe afai e te manaʻomia le aoina o faʻamaumauga mo elemene uma "i luga o le laau", aʻo taofi faʻamatalaga e uiga i le puna puna (ma faʻatasi ai ma faʻailoga) na mafua ai ona aofia i totonu o le faʻataʻitaʻiga - mo se faʻataʻitaʻiga, e faia se lipoti otooto. fa'atasi ma fa'atasi i nodes.
O mea o lo'o mulimuli mai e tatau ona avea na'o se fa'amaoniga-o-manatu, talu ai o le talosaga e foliga mai e matua faigata lava. Ae afai e pulea lau faʻamaumauga, e tatau ona e mafaufau i le faʻaaogaina o auala tutusa.
Sei o tatou amata i ni nai faamatalaga faigofie:
- Le fa'amaumauga tutusa mai le fa'amaumauga E sili le faitau tasi.
- Fa'amaumauga mai le fa'amaumauga E sili atu ona lelei le faitau faʻatasinai lo le toatasi.
Ia tatou taumafai nei e fausia le talosaga tatou te manaʻomia.
laa 1
E manino lava, pe a amataina le toe faʻafoʻi (o fea o le a tatou i ai e aunoa ma lea!) e tatau ona tatou toesea faʻamaumauga o laulaʻau latou lava faʻavae i luga o le seti o faʻamatalaga muamua:
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
...
Afai e foliga ese i se tasi o le "set" o loʻo teuina o se manoa ae le o se faʻasologa, o loʻo i ai se faʻamatalaga faigofie mo lenei mea. O loʻo i ai se faʻapipiʻi faʻapipiʻi "faʻapipiʻi" galuega mo manoa string_agg, ae le mo fa'asologa. E ui lava o ia .
laa 2
O lea o le a matou maua se seti o vaega ID e manaʻomia ona faitau atili. Toeitiiti lava o taimi uma o le a fa'aluaina i latou i fa'amaumauga eseese o le seti muamua - o lea matou te faia fa'avasega i latou, a'o fa'asaoina fa'amatalaga e uiga i lau puna.
Ae o faʻafitauli nei e tolu o loʻo faʻatali mai ia i tatou:
- O le vaega "subrecursive" o le fesili e le mafai ona aofia ai galuega faʻatasi ma
GROUP BY. - O se fa'asinomaga i se "la'ai" toe fa'afo'i e le mafai ona i totonu o se fa'aaufa'asolo.
- O se talosaga i le vaega recursive e le mafai ona iai se CTE.
O le mea e lelei ai, o nei faʻafitauli uma e faigofie lava ona faʻaogaina. Tatou amata mai le faaiuga.
CTE i se vaega toe fa'afo'i
O iinei lē galue:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)Ma o lea e aoga, o puipui e faia ai le eseesega!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Fa'atatauga fesili e faasaga i se "laufa'atasi"
Hmm... E le mafai ona maua se CTE toe fa'afo'i i se subquery. Ae e mafai ona i totonu o le CTE! Ma o se talosaga fa'aputu ua mafai ona maua lenei CTE!
VAEGA E FAI totonu recursion
E le manaia, ae ... E i ai se matou auala faigofie e faʻataʻitaʻi ai GROUP E ala i le faʻaaogaina DISTINCT ON ma galuega fa'amalama!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!Ma o le auala lenei e galue ai!
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 NULLO lea ua tatou va'ai pe aisea ua liua ai le ID numera i tusitusiga - ina ia mafai ona tu'ufa'atasia fa'atasi e tu'ueseese i koma!
laa 3
Mo le faaiuga e leai se mea o totoe:
- matou te faitau "vaega" faʻamaumauga e faʻavae i luga o se seti o ID faʻavae
- matou te faʻatusatusa vaega toesea ma "seti" o uluai laupepa
- “faalautele” le seti manoa e faaaoga ai
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;
puna: www.habr.com