Нарийн төвөгтэй ERP системд олон аж ахуйн нэгжүүд шаталсан шинж чанартай байдагнэгэн төрлийн объектууд эгнэх үед өвөг дээдсийн харилцааны мод - энэ бол аж ахуйн нэгжийн зохион байгуулалтын бүтэц (эдгээр бүх салбар, хэлтэс, ажлын хэсэг), бараа бүтээгдэхүүний каталог, ажлын чиглэл, борлуулалтын цэгүүдийн газарзүй,...
Үнэндээ бол байхгүй , үр дүнд нь ямар ч шатлал байхгүй болно. Гэхдээ та "бизнесийн төлөө" ажилладаггүй ч гэсэн шаталсан харилцаатай амархан тулгардаг. Таны овгийн мод эсвэл худалдааны төвийн байрны төлөвлөгөө ч гэсэн адилхан бүтэцтэй.
Ийм модыг DBMS-д хадгалах олон арга байдаг боловч өнөөдөр бид зөвхөн нэг сонголт дээр анхаарлаа хандуулах болно.
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
Мөн та шатлалын гүн рүү харж байхад, ийм бүтэцтэй ажиллах таны “гэнэн” арга хэр үр дүнтэй болохыг харахыг тэвчээртэй хүлээж байна.
Ердийн асуудлууд, тэдгээрийн SQL-д хэрэгжсэн байдал, гүйцэтгэлийг сайжруулахыг хичээцгээе.
#1. Туулайн нүх хэр гүн вэ?
Энэ бүтэц нь байгууллагын бүтцэд хэлтэс, хэлтэс, салбар, салбар, ажлын хэсэг гэх мэт харъяаллыг тусгах болно гэдгийг тодорхой хүлээн зөвшөөрье.
Эхлээд 10К элементээс бүрдсэн "мод"-оо үүсгэцгээе
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 BY дотор рекурс
Энэ нь тааламжгүй, гэхдээ... Бидэнд 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;
Эх сурвалж: www.habr.com