సంక్లిష్ట ERP వ్యవస్థలలో అనేక ఎంటిటీలు క్రమానుగత స్వభావాన్ని కలిగి ఉంటాయిసజాతీయ వస్తువులు వరుసలో ఉన్నప్పుడు పూర్వీకుల-వారసత్వ సంబంధాల చెట్టు - ఇది సంస్థ యొక్క సంస్థాగత నిర్మాణం (ఈ అన్ని శాఖలు, విభాగాలు మరియు పని సమూహాలు), మరియు వస్తువుల కేటలాగ్, మరియు పని ప్రాంతాలు మరియు సేల్స్ పాయింట్ల భౌగోళికం,...
నిజానికి, ఏదీ లేదు
అటువంటి చెట్టును DBMS లో నిల్వ చేయడానికి అనేక మార్గాలు ఉన్నాయి, కానీ ఈ రోజు మనం ఒకే ఒక ఎంపికపై దృష్టి పెడతాము:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
మరియు మీరు సోపానక్రమం యొక్క లోతులను పరిశీలిస్తున్నప్పుడు, అటువంటి నిర్మాణంతో మీ "అమాయక" మార్గాలు ఎంత ప్రభావవంతంగా ఉంటాయో చూడటానికి ఇది ఓపికగా వేచి ఉంది.
ఉత్పన్నమయ్యే సాధారణ సమస్యలను, SQLలో వాటి అమలును చూద్దాం మరియు వాటి పనితీరును మెరుగుపరచడానికి ప్రయత్నించండి.
#1. కుందేలు రంధ్రం ఎంత లోతుగా ఉంది?
నిర్ధిష్టత కోసం, ఈ నిర్మాణం సంస్థ యొక్క నిర్మాణంలో విభాగాల అధీనతను ప్రతిబింబిస్తుందని అంగీకరిస్తాము: విభాగాలు, విభాగాలు, రంగాలు, శాఖలు, వర్కింగ్ గ్రూపులు ... - మీరు వాటిని ఏది పిలిచినా.
ముందుగా, 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;
ఒక నిర్దిష్ట విభాగంలో లేదా సోపానక్రమం పరంగా పనిచేసే ఉద్యోగులందరినీ కనుగొనడం - సరళమైన పనితో ప్రారంభిద్దాం - నోడ్ యొక్క పిల్లలందరినీ కనుగొనండి. వారసుడి యొక్క “లోతు” పొందడం కూడా మంచిది ... ఇవన్నీ అవసరం కావచ్చు, ఉదాహరణకు, ఒక రకమైన నిర్మాణానికి
ఈ వారసులలో రెండు స్థాయిలు మాత్రమే ఉంటే మరియు డజనులోపు సంఖ్య ఉంటే అంతా బాగానే ఉంటుంది, కానీ 5 కంటే ఎక్కువ స్థాయిలు ఉంటే మరియు ఇప్పటికే డజన్ల కొద్దీ వారసులు ఉంటే, సమస్యలు ఉండవచ్చు. సాంప్రదాయ డౌన్-ది-ట్రీ శోధన ఎంపికలు ఎలా వ్రాయబడతాయో చూద్దాం (మరియు పని చేస్తుంది). అయితే ముందుగా, మన పరిశోధనకు ఏ నోడ్లు అత్యంత ఆసక్తికరంగా ఉంటాయో తెలుసుకుందాం.
ఉత్తమ "లోతైన" ఉపవృక్షాలు:
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}
...
ఉత్తమ "వెడల్పు" ఉపవృక్షాలు:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;
id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
ఈ ప్రశ్నల కోసం మేము విలక్షణమైనదాన్ని ఉపయోగించాము పునరావృత JOIN:
సహజంగానే, ఈ అభ్యర్థన నమూనాతో పునరావృతాల సంఖ్య మొత్తం వారసుల సంఖ్యతో సరిపోలుతుంది (మరియు వాటిలో అనేక డజన్ల ఉన్నాయి), మరియు ఇది చాలా ముఖ్యమైన వనరులను తీసుకోవచ్చు మరియు ఫలితంగా సమయం పడుతుంది.
"విశాలమైన" సబ్ట్రీని తనిఖీ చేద్దాం:
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;
ఊహించినట్లుగానే, మేము మొత్తం 30 రికార్డులను కనుగొన్నాము. కానీ వారు మొత్తం సమయం లో 60% వెచ్చించారు - ఎందుకంటే వారు ఇండెక్స్లో 30 శోధనలు కూడా చేసారు. తక్కువ చేయడం సాధ్యమేనా?
ఇండెక్స్ ద్వారా బల్క్ ప్రూఫ్ రీడింగ్
ప్రతి నోడ్ కోసం మనం ప్రత్యేక ఇండెక్స్ క్వెరీని తయారు చేయాలా? ఇది లేదు అని మారుతుంది - మేము సూచిక నుండి చదవగలము ఒక కాల్లో ఒకేసారి అనేక కీలను ఉపయోగించడం సహాయంతో = ANY(array)
.
మరియు అటువంటి ప్రతి ఐడెంటిఫైయర్ల సమూహంలో మేము మునుపటి దశలో కనుగొనబడిన అన్ని IDలను “నోడ్లు” ద్వారా తీసుకోవచ్చు. అంటే, ప్రతి తదుపరి దశలో మేము చేస్తాము ఒక నిర్దిష్ట స్థాయి వారసులందరి కోసం ఒకేసారి శోధించండి.
సమస్య ఇక్కడ మాత్రమే ఉంది, పునరావృత ఎంపికలో, మీరు సమూహ ప్రశ్నలో స్వయంగా యాక్సెస్ చేయలేరు, కానీ మేము మునుపటి స్థాయిలో కనుగొనబడిన వాటిని మాత్రమే ఎంచుకోవాలి... మీరు మొత్తం ఎంపిక కోసం సమూహ ప్రశ్నను చేయలేరు, కానీ దాని నిర్దిష్ట ఫీల్డ్ కోసం మీరు చేయగలరు. మరియు ఈ ఫీల్డ్ కూడా ఒక శ్రేణి కావచ్చు - ఇది మనం ఉపయోగించాల్సిన అవసరం ఉంది ANY
.
ఇది కొద్దిగా వెర్రి అనిపిస్తుంది, కానీ రేఖాచిత్రంలో ప్రతిదీ సులభం.
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;
మరియు ఇక్కడ అతి ముఖ్యమైన విషయం కూడా కాదు సమయానికి 1.5 సార్లు గెలవండి, మరియు మేము ఇండెక్స్కి 5కి బదులుగా 30 కాల్లను మాత్రమే కలిగి ఉన్నందున మేము తక్కువ బఫర్లను తీసివేసాము!
అదనపు బోనస్ ఏమిటంటే, తుది అన్నెస్ట్ తర్వాత, ఐడెంటిఫైయర్లు “స్థాయిలు” ద్వారా ఆర్డర్ చేయబడి ఉంటాయి.
నోడ్ గుర్తు
పనితీరును మెరుగుపరచడంలో సహాయపడే తదుపరి పరిశీలన -- "ఆకులు" పిల్లలను కలిగి ఉండవు, అంటే, వారికి "క్రిందికి" చూడవలసిన అవసరం లేదు. మా విధిని రూపొందించడంలో, మేము విభాగాల గొలుసును అనుసరించి, ఉద్యోగిని చేరుకున్నట్లయితే, ఈ శాఖలో మరింత చూడవలసిన అవసరం లేదని దీని అర్థం.
మన పట్టికలోకి ప్రవేశిద్దాం అదనపు boolean
- ఫీల్డ్, ఇది మన చెట్టులోని ఈ ప్రత్యేక ప్రవేశం “నోడ్” కాదా అని వెంటనే మాకు తెలియజేస్తుంది - అంటే, దీనికి వారసులు ఉండగలరా.
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 мс.
గొప్ప! అన్ని చెట్ల మూలకాలలో 30% కంటే కొంచెం ఎక్కువ మాత్రమే వారసులు ఉన్నాయని తేలింది.
ఇప్పుడు కొద్దిగా భిన్నమైన మెకానిక్ని వుపయోగిద్దాం - రికర్సివ్ భాగానికి కనెక్షన్ల ద్వారా LATERAL
, ఇది రికర్సివ్ “టేబుల్” ఫీల్డ్లను తక్షణమే యాక్సెస్ చేయడానికి అనుమతిస్తుంది మరియు కీల సెట్ను తగ్గించడానికి నోడ్ ఆధారంగా ఫిల్టరింగ్ కండిషన్తో కంకర ఫంక్షన్ను ఉపయోగిస్తుంది:
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;
మేము మరో ఇండెక్స్ కాల్ని తగ్గించగలిగాము మరియు వాల్యూమ్లో 2 కంటే ఎక్కువ సార్లు గెలిచింది ప్రూఫ్ రీడ్.
#2. మూలాలకు తిరిగి వెళ్దాం
నమూనాలో చేర్చడానికి కారణమైన సోర్స్ షీట్ (మరియు ఏ సూచికలతో) అనే దాని గురించి సమాచారాన్ని నిలుపుకుంటూ, మీరు “అప్ ది ట్రీ” అన్ని మూలకాల కోసం రికార్డ్లను సేకరించాల్సిన అవసరం ఉన్నట్లయితే ఈ అల్గారిథమ్ ఉపయోగకరంగా ఉంటుంది - ఉదాహరణకు, సారాంశ నివేదికను రూపొందించడానికి నోడ్స్లో అగ్రిగేషన్తో.
అభ్యర్థన చాలా గజిబిజిగా మారినందున, కింది వాటిని కేవలం ప్రూఫ్-ఆఫ్-కాన్సెప్ట్గా తీసుకోవాలి. కానీ అది మీ డేటాబేస్లో ఆధిపత్యం చెలాయిస్తే, మీరు ఇలాంటి పద్ధతులను ఉపయోగించడం గురించి ఆలోచించాలి.
కొన్ని సాధారణ ప్రకటనలతో ప్రారంభిద్దాం:
- డేటాబేస్ నుండి అదే రికార్డ్ ఒక్కసారి చదవడం మంచిది.
- డేటాబేస్ నుండి రికార్డులు బ్యాచ్లలో చదవడం మరింత ప్రభావవంతంగా ఉంటుందిఒంటరిగా కంటే.
ఇప్పుడు మనకు అవసరమైన అభ్యర్థనను రూపొందించడానికి ప్రయత్నిద్దాం.
1 అడుగు
సహజంగానే, రికర్షన్ను ప్రారంభించేటప్పుడు (అది లేకుండా మనం ఎక్కడ ఉంటాం!) ప్రారంభ ఐడెంటిఫైయర్ల సెట్ ఆధారంగా మనం ఆకుల రికార్డులను తీసివేయాలి:
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
...
“సెట్” అనేది స్ట్రింగ్గా మరియు శ్రేణిగా నిల్వ చేయబడిందని ఎవరికైనా వింతగా అనిపిస్తే, దీనికి సాధారణ వివరణ ఉంది. స్ట్రింగ్స్ కోసం అంతర్నిర్మిత అగ్రిగేటింగ్ "గ్లూయింగ్" ఫంక్షన్ ఉంది string_agg
, కానీ శ్రేణుల కోసం కాదు. ఆమె అయినప్పటికీ
2 అడుగు
ఇప్పుడు మనం సెక్షన్ IDల సమితిని పొందుతాము, వాటిని మరింత చదవాలి. దాదాపు ఎల్లప్పుడూ అవి అసలైన సెట్ యొక్క విభిన్న రికార్డులలో నకిలీ చేయబడతాయి - కాబట్టి మేము చేస్తాము వాటిని సమూహం చేయండి, మూలం ఆకుల గురించి సమాచారాన్ని సంరక్షించేటప్పుడు.
కానీ ఇక్కడ మూడు ఇబ్బందులు మనకు ఎదురుచూస్తున్నాయి:
- ప్రశ్న యొక్క "సబ్రెకర్సివ్" భాగం మొత్తం ఫంక్షన్లను కలిగి ఉండదు
GROUP BY
. - పునరావృత “పట్టిక”కి సంబంధించిన సూచన సమూహ సబ్క్వెరీలో ఉండకూడదు.
- పునరావృత భాగంలోని అభ్యర్థన CTEని కలిగి ఉండకూడదు.
అదృష్టవశాత్తూ, ఈ సమస్యలన్నీ పరిష్కరించడం చాలా సులభం. ముగింపు నుండి ప్రారంభిద్దాం.
పునరావృత భాగంలో CTE
ఇలా కాదు పనిచేస్తుంది:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)
కాబట్టి ఇది పనిచేస్తుంది, కుండలీకరణాలు తేడా చేస్తాయి!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)
పునరావృత "పట్టిక"కి వ్యతిరేకంగా సమూహ ప్రశ్న
అయ్యో... సబ్క్వెరీలో రికర్సివ్ CTEని యాక్సెస్ చేయడం సాధ్యపడదు. కానీ అది CTE లోపల ఉండవచ్చు! మరియు సమూహ అభ్యర్థన ఇప్పటికే ఈ CTEని యాక్సెస్ చేయగలదు!
ఇన్సైడ్ రికర్షన్ ద్వారా గ్రూప్
ఇది అసహ్యకరమైనది, కానీ... GROUPని ఉపయోగించడం ద్వారా అనుకరించడానికి మాకు సులభమైన మార్గం ఉంది DISTINCT ON
మరియు విండో విధులు!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!
మరియు ఇది ఎలా పని చేస్తుంది!
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
సంఖ్యా ID ఎందుకు టెక్స్ట్గా మార్చబడిందో ఇప్పుడు మనం చూస్తాము - తద్వారా వాటిని కామాలతో వేరు చేయవచ్చు!
3 అడుగు
ఫైనల్ కోసం మాకు ఏమీ మిగిలి లేదు:
- మేము సమూహ IDల సెట్ ఆధారంగా “విభాగం” రికార్డ్లను చదువుతాము
- మేము తీసివేసిన విభాగాలను అసలు షీట్ల “సెట్లు”తో పోల్చాము
- ఉపయోగించి సెట్-స్ట్రింగ్ను "విస్తరించండి"
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;