PostgreSQL ضد نمونې: د خرگوش سوری څومره ژور دی؟ راځئ چې د درجه بندي له لارې لاړ شو

په پیچلي 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;

راځئ چې د ساده کار سره پیل وکړو - د ټولو کارمندانو موندل چې په یو ځانګړي سکتور کې کار کوي ، یا د درجې په شرایطو کې - د نوډ ټول ماشومان ومومئ. دا به ښه وي چې د نسل "ژورتیا" ترلاسه کړئ ... دا ټول ممکن اړین وي، د بیلګې په توګه، د یو ډول ډول جوړولو لپاره. د دې کارمندانو د IDs لیست پراساس پیچلي انتخاب.

هر څه به سم وي که د دې اولادونو یوازې دوه درجې وي او شمیر یې په درجنونو کې وي، مګر که له 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

د دې پوښتنو لپاره موږ عادي کاروو تکراري ګډون:


په ښکاره ډول، د دې غوښتنې ماډل سره د تکرار شمیر به د ټولو اولادونو شمیر سره سمون ولري (او د دوی څو درجن شتون لري)، او دا کولی شي د پام وړ سرچینې واخلي، او د پایلې په توګه، وخت.

راځئ چې "پراخه" فرعي وګورو:

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;

[ تشریح.tensor.ru ته وګورئ]

لکه څنګه چې تمه کیده، موږ ټول 30 ریکارډونه وموندل. مګر دوی په دې اړه د ټول وخت 60٪ مصرف کړي - ځکه چې دوی په شاخص کې 30 لټونونه هم کړي. ایا دا ممکنه ده چې لږ څه وکړو؟

د شاخص په واسطه لوی ثبوت لوستل

ایا موږ اړتیا لرو چې د هر نوډ لپاره جلا شاخص پوښتنه وکړو؟ دا معلومه شوه چې نه - موږ د شاخص څخه لوستلی شو په یو کال کې په یوځل کې د څو کیليونو کارول له لارې = ANY(array).

او د پیژندونکو په هر ګروپ کې موږ کولی شو ټول هغه IDs واخلو چې په تیرو مرحلو کې موندل شوي د "نوډونو" لخوا. دا دی، په هر بل ګام کې موږ به په یوځل کې د یوې ټاکلې کچې ټولو اولادونو لټون وکړئ.

یوازې، دلته ستونزه ده، په تکراري انتخاب کې، تاسو نشئ کولی ځان ته په نیست شوي پوښتنې کې لاسرسی ومومئ، مګر موږ باید په یو ډول یوازې هغه څه وټاکو چې په تیرو کچو کې موندل شوي ... دا معلومه شوه چې د ټول انتخاب لپاره د نیست شوي پوښتنې رامینځته کول ناممکن دي ، مګر د دې ځانګړي ساحې لپاره دا ممکنه ده. او دا ساحه هم یو صف کیدی شي - کوم چې موږ یې کارولو ته اړتیا لرو 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;

[ تشریح.tensor.ru ته وګورئ]

او دلته تر ټولو مهمه خبره هم نه ده په وخت کې 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;

[ تشریح.tensor.ru ته وګورئ]

موږ وکولی شو یو بل شاخص کال کم کړو او په حجم کې له 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 ګام

اوس به موږ د برخې IDs سیټ ترلاسه کړو چې نور لوستلو ته اړتیا لري. نږدې تل به دوی د اصلي سیټ مختلف ریکارډونو کې نقل شي - نو موږ به یې وکړو دوی ډلهپه داسې حال کې چې د سرچینې پاڼو په اړه معلومات ساتل.

مګر دلته درې ستونزې زموږ په تمه دي:

1. د پوښتنې "فرعي تعقیب" برخه نشي کولی ټولیز افعال ولري GROUP BY.
2. د تکراري "میز" حواله نشي کولی په نیست شوي فرعي پوښتن کې وي.
3. په تکراري برخه کې غوښتنه نشي کولی CTE ولري.

خوشبختانه، دا ټولې ستونزې د بریالیتوب لپاره خورا اسانه دي. راځئ چې له پای څخه پیل وکړو.

CTE په تکراري برخه کې

لکه دغه نه کار کول:

WITH RECURSIVE tree AS (
  ...
UNION ALL
  WITH T (...)
  SELECT ...
)

او نو دا کار کوي، قوسونه توپیر کوي!

WITH RECURSIVE tree AS (
  ...
UNION ALL
  (
    WITH T (...)
    SELECT ...
  )
)

د تکراري "میز" په وړاندې نیست شوي پوښتنه

هوم... یو تکراري CTE په فرعي پوښتنو کې نشي لاسرسی کیدی. مګر دا د CTE دننه کیدی شي! او یو نیست شوی غوښتنه دمخه دې CTE ته لاسرسی کولی شي!

د تکرار دننه ګروپ

دا ناخوښ دی، مګر ... موږ د کارولو په واسطه د ګروپ تقلید کولو لپاره یوه ساده لار لرو 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 ګام

د پای لپاره موږ هیڅ شی نه لرو:

• موږ د ګروپ شوي IDs د سیټ پراساس د "برخې" ریکارډونه لوستلو
• موږ تخفیف شوي برخې د اصلي شیټونو "سیټونو" سره پرتله کوو
• د سیټ تار په کارولو سره "پراخ کړئ". 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;

[ تشریح.tensor.ru ته وګورئ]

سرچینه: www.habr.com