සංකීර්ණ 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).
තවද එවැනි එක් එක් හඳුනාගැනීම් සමූහය තුළ අපට පෙර පියවරේදී සොයාගත් සියලුම හැඳුනුම්පත් “නෝඩ්” මගින් ගත හැකිය. එනම්, සෑම ඊළඟ පියවරකදීම අපි කරන්නෙමු යම් මට්ටමක සිට පැවත එන්නන් සඳහා එකවර සොයන්න.
මෙන්න මේකයි ප්‍රශ්නේ, පුනරාවර්තන තේරීමේදී, ඔබට කැදලි විමසුමකින් ප්‍රවේශ විය නොහැක, නමුත් අපි කෙසේ හෝ තෝරා ගත යුත්තේ පෙර මට්ටමින් සොයාගත් දේ පමණි ... සම්පූර්ණ තේරීම සඳහා කැදලි විමසුමක් කළ නොහැකි බව පෙනේ, නමුත් එහි නිශ්චිත ක්ෂේත්රය සඳහා එය කළ හැකිය. තවද මෙම ක්ෂේත්රය ද අරාවක් විය හැක - අප භාවිතා කිරීමට අවශ්ය වන්නේ එයයි 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ක් පමණක් බැවින් අපි අඩු බෆර අඩු කළෙමු!
අතිරේක ප්‍රසාද දීමනාවක් වන්නේ අවසාන unnest කිරීමෙන් පසුව, හඳුනාගැනීම් "මට්ටම්" අනුව ඇණවුම් කර පවතිනු ඇති බවයි.
නෝඩ් ලකුණ
කාර්ය සාධනය වැඩි දියුණු කිරීමට උපකාරී වන ඊළඟ සලකා බැලීම වන්නේ - "කොළ" වලට දරුවන් සිටිය නොහැක, එනම්, ඔවුන් සඳහා කිසිසේත් "පහළ" බැලීම අවශ්ය නොවේ. අපගේ කාර්යය සැකසීමේදී, මෙයින් අදහස් කරන්නේ අපි දෙපාර්තමේන්තු දාමය අනුගමනය කර සේවකයෙකු වෙත ළඟා වූවා නම්, මෙම ශාඛාව දිගේ තවදුරටත් බැලීමට අවශ්ය නොවන බවයි.
අපි අපේ මේසයට ඇතුල් වෙමු අතිරේක 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
දැන් අපට තව දුරටත් කියවිය යුතු කොටස් හැඳුනුම්පත් කට්ටලයක් ලැබෙනු ඇත. සෑම විටම පාහේ ඒවා මුල් කට්ටලයේ විවිධ වාර්තාවල අනුපිටපත් කරනු ලැබේ - එබැවින් අපි එසේ කරමු ඔවුන් කණ්ඩායම් කරන්න, මූලාශ්රය කොළ පිළිබඳ තොරතුරු සංරක්ෂණය කරන අතරතුර.
නමුත් මෙහි කරදර තුනක් අප බලා සිටී:
- විමසුමේ "subrecursive" කොටසෙහි සමස්ත ශ්‍රිත අඩංගු විය නොහැක
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
එය අප්රසන්නය, නමුත්... අපට 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සංඛ්‍යාත්මක හැඳුනුම්පත පෙළ බවට පත් කළේ මන්දැයි දැන් අපට පෙනේ - ඒවා කොමාවෙන් වෙන් කළ හැකි වන පරිදි!
පියවරක් 3
අවසාන තරගය සඳහා අපට කිසිවක් ඉතිරි නොවේ:
- අපි කණ්ඩායම් හැඳුනුම්පත් කට්ටලයක් මත පදනම්ව "කොටස්" වාර්තා කියවමු
- අපි අඩු කළ කොටස් මුල් පත්‍රවල “කට්ටල” සමඟ සංසන්දනය කරමු
- භාවිතා කරමින් set-string "පුළුල් කරන්න"
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;
මූලාශ්රය: www.habr.com