PostgreSQL Antipatterns: ຂຸມກະຕ່າຍເລິກເທົ່າໃດ? ໃຫ້ໄປໂດຍຜ່ານລໍາດັບຊັ້ນ

ໃນລະບົບ ERP ທີ່ສັບສົນ ຫຼາຍຫົວໜ່ວຍມີລັກສະນະເປັນລຳດັບເມື່ອວັດຖຸທີ່ເປັນອັນດຽວກັນຕັ້ງຢູ່ໃນ ຕົ້ນ​ໄມ້​ຂອງ​ສາຍ​ພົວ​ພັນ​ບັນ​ພະ​ບຸ​ລຸດ​ແລະ​ລູກ​ຫລານ​ - ນີ້​ແມ່ນ​ໂຄງ​ສ້າງ​ການ​ຈັດ​ຕັ້ງ​ຂອງ​ວິ​ສາ​ຫະ​ກິດ (ສາ​ຂາ​ທັງ​ຫມົດ​ເຫຼົ່າ​ນີ້​, ພະ​ແນກ​ແລະ​ກຸ່ມ​ການ​ເຮັດ​ວຽກ​)​, ແລະ​ລາຍ​ການ​ສິນ​ຄ້າ​, ແລະ​ພື້ນ​ທີ່​ການ​ເຮັດ​ວຽກ​, ແລະ​ພູມ​ສາດ​ຂອງ​ຈຸດ​ຂາຍ​, ...

ໃນຄວາມເປັນຈິງ, ບໍ່ມີ ເຂດອັດຕະໂນມັດທຸລະກິດ, ບ່ອນທີ່ຈະບໍ່ມີລໍາດັບຊັ້ນເປັນຜົນໄດ້ຮັບ. ແຕ່ເຖິງແມ່ນວ່າທ່ານບໍ່ໄດ້ເຮັດວຽກ "ສໍາລັບທຸລະກິດ", ທ່ານຍັງສາມາດພົບກັບການພົວພັນແບບລໍາດັບຊັ້ນໄດ້ຢ່າງງ່າຍດາຍ. ມັນເປັນເລື່ອງທີ່ໜ້າສົນໃຈ, ແມ່ນແຕ່ຕົ້ນໄມ້ຄອບຄົວ ຫຼືແຜນຊັ້ນຂອງສະຖານທີ່ຢູ່ໃນສູນການຄ້າກໍມີໂຄງສ້າງດຽວກັນ.

ມີຫຼາຍວິທີທີ່ຈະເກັບຮັກສາຕົ້ນໄມ້ດັ່ງກ່າວໄວ້ໃນ DBMS, ແຕ່ມື້ນີ້ພວກເຮົາຈະສຸມໃສ່ທາງເລືອກດຽວ:

CREATE TABLE hier(
  id
    integer
      PRIMARY KEY
, pid
    integer
      REFERENCES hier
, data
    json
);

CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK

ແລະໃນຂະນະທີ່ທ່ານກໍາລັງເບິ່ງເຂົ້າໄປໃນຄວາມເລິກຂອງລໍາດັບຊັ້ນ, ມັນລໍຖ້າຢ່າງອົດທົນເພື່ອເຂົ້າໄປເບິ່ງວິທີການເຮັດວຽກທີ່ "ໂງ່" ຂອງເຈົ້າກັບໂຄງສ້າງດັ່ງກ່າວຈະເປັນແນວໃດ.

ໃຫ້ເບິ່ງບັນຫາທົ່ວໄປທີ່ເກີດຂື້ນ, ການປະຕິບັດຂອງພວກເຂົາໃນ SQL, ແລະພະຍາຍາມປັບປຸງການປະຕິບັດຂອງພວກເຂົາ.

#1. ຂຸມກະຕ່າຍເລິກເທົ່າໃດ?

ໃຫ້ພວກເຮົາ, ສໍາລັບຄໍານິຍາມ, ຍອມຮັບວ່າໂຄງສ້າງນີ້ຈະສະທ້ອນໃຫ້ເຫັນ subordination ຂອງພະແນກໃນໂຄງສ້າງຂອງອົງການຈັດຕັ້ງ: ພະແນກການ, ພະແນກ, ຂະແໜງການ, ສາຂາ, ກຸ່ມເຮັດວຽກ ... - ໃດກໍ່ຕາມທີ່ທ່ານໂທຫາພວກເຂົາ.

ທໍາອິດ, ໃຫ້ສ້າງ 'ຕົ້ນໄມ້' ຂອງພວກເຮົາຂອງອົງປະກອບ 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;

ໃຫ້ເລີ່ມຕົ້ນດ້ວຍວຽກງານທີ່ງ່າຍດາຍທີ່ສຸດ - ຊອກຫາພະນັກງານທັງຫມົດທີ່ເຮັດວຽກຢູ່ໃນຂະແຫນງການສະເພາະ, ຫຼືໃນເງື່ອນໄຂຂອງລໍາດັບຊັ້ນ - ຊອກຫາເດັກນ້ອຍທັງຫມົດຂອງ node. ມັນກໍ່ຈະເປັນການດີທີ່ຈະໄດ້ຮັບ "ຄວາມເລິກ" ຂອງລູກຫລານ ... ທັງຫມົດນີ້ອາດຈະມີຄວາມຈໍາເປັນ, ສໍາລັບການຍົກຕົວຢ່າງ, ການກໍ່ສ້າງບາງປະເພດ. ການຄັດເລືອກທີ່ສັບສົນໂດຍອີງໃສ່ບັນຊີລາຍຊື່ຂອງບັດປະຈໍາຕົວຂອງພະນັກງານເຫຼົ່ານີ້.

ທຸກຢ່າງຄົງຈະດີຖ້າມີລູກຫລານເຫຼົ່ານີ້ພຽງແຕ່ສອງສາມລະດັບ ແລະຕົວເລກແມ່ນພາຍໃນສິບໆຄົນ, ແຕ່ຖ້າມີຫຼາຍກວ່າ 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

ສໍາລັບການສອບຖາມເຫຼົ່ານີ້ພວກເຮົາໄດ້ໃຊ້ແບບປົກກະຕິ ເຂົ້າຮ່ວມ recursive:


ແນ່ນອນ, ດ້ວຍຮູບແບບການຮ້ອງຂໍນີ້ ຈໍານວນການຊໍ້າຄືນຈະກົງກັບຈໍານວນລູກຫລານທັງໝົດ (ແລະມີຫຼາຍອາຍແກັສຂອງພວກເຂົາ), ແລະນີ້ສາມາດໃຊ້ເວລາຊັບພະຍາກອນທີ່ສໍາຄັນ, ແລະ, ດັ່ງນັ້ນ, ເວລາ.

ໃຫ້ກວດເບິ່ງຕົ້ນໄມ້ຍ່ອຍ "ກວ້າງທີ່ສຸດ":

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 ຄົ້ນຫາໃນດັດສະນີ. ມັນເປັນໄປໄດ້ທີ່ຈະເຮັດຫນ້ອຍລົງບໍ?

ກວດ​ສອບ​ຫຼາຍ​ໂດຍ​ດັດ​ຊະ​ນີ​

ພວກເຮົາຈໍາເປັນຕ້ອງເຮັດການສອບຖາມດັດສະນີແຍກຕ່າງຫາກສໍາລັບແຕ່ລະ node? ມັນ turns ໃຫ້ເຫັນວ່າບໍ່ - ພວກເຮົາສາມາດອ່ານຈາກດັດຊະນີ ໃຊ້ກະແຈຫຼາຍອັນພ້ອມກັນໃນສາຍດຽວ ໂດຍການຊ່ວຍເຫຼືອ = ANY(array).

ແລະໃນແຕ່ລະກຸ່ມຕົວລະບຸດັ່ງກ່າວພວກເຮົາສາມາດເອົາ ID ທັງຫມົດທີ່ພົບໃນຂັ້ນຕອນທີ່ຜ່ານມາໂດຍ "nodes". ນັ້ນແມ່ນ, ໃນແຕ່ລະຂັ້ນຕອນຕໍ່ໄປພວກເຮົາຈະ ຊອກ​ຫາ​ລູກ​ຫລານ​ທັງ​ຫມົດ​ຂອງ​ລະ​ດັບ​ໃດ​ຫນຶ່ງ​ໃນ​ເວ​ລາ​ດຽວ​.

ພຽງແຕ່, ນີ້ແມ່ນບັນຫາ, ໃນການຄັດເລືອກ recursive, ທ່ານບໍ່ສາມາດເຂົ້າເຖິງຕົວມັນເອງໃນການສອບຖາມທີ່ຊ້ອນກັນ, ແຕ່ພວກເຮົາຈໍາເປັນຕ້ອງ somehow ເລືອກພຽງແຕ່ສິ່ງທີ່ພົບເຫັນຢູ່ໃນລະດັບທີ່ຜ່ານມາ ... ມັນ turns ໃຫ້ເຫັນວ່າມັນເປັນໄປບໍ່ໄດ້ທີ່ຈະເຮັດການສອບຖາມ nested ສໍາລັບການຄັດເລືອກທັງຫມົດ, ແຕ່ສໍາລັບພາກສະຫນາມສະເພາະຂອງມັນເປັນໄປໄດ້. ແລະພາກສະຫນາມນີ້ຍັງສາມາດເປັນ 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;

[ເບິ່ງທີ່ອະທິບາຍ.tensor.ru]

ແລະໃນທີ່ນີ້ສິ່ງທີ່ສໍາຄັນທີ່ສຸດບໍ່ແມ່ນແຕ່ ຊະນະ 1.5 ຄັ້ງໃນເວລາ, ແລະວ່າພວກເຮົາລົບ buffers ຫນ້ອຍລົງ, ເພາະວ່າພວກເຮົາມີພຽງແຕ່ 5 ໂທໄປຫາດັດຊະນີແທນທີ່ຈະເປັນ 30!

ໂບນັດເພີ່ມເຕີມແມ່ນຄວາມຈິງທີ່ວ່າຫຼັງຈາກ unnest ສຸດທ້າຍ, ຕົວກໍານົດຈະຍັງຄົງຄໍາສັ່ງໂດຍ "ລະດັບ".

ປ້າຍ node

ການພິຈາລະນາຕໍ່ໄປທີ່ຈະຊ່ວຍໃຫ້ການປັບປຸງການປະຕິບັດແມ່ນ − "ໃບ" ບໍ່ສາມາດມີລູກໄດ້, ນັ້ນແມ່ນ, ສໍາລັບພວກເຂົາບໍ່ຈໍາເປັນຕ້ອງເບິ່ງ "ລົງ" ເລີຍ. ໃນການກໍານົດວຽກງານຂອງພວກເຮົາ, ນີ້ຫມາຍຄວາມວ່າຖ້າພວກເຮົາປະຕິບັດຕາມລະບົບຕ່ອງໂສ້ຂອງພະແນກການແລະບັນລຸພະນັກງານ, ຫຼັງຈາກນັ້ນບໍ່ຈໍາເປັນຕ້ອງຊອກຫາສາຂານີ້ຕື່ມອີກ.

ໃຫ້ເຂົ້າໄປໃນຕາຕະລາງຂອງພວກເຮົາ ເພີ່ມເຕີມ 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 мс.

ຍິ່ງໃຫຍ່! ມັນ turns ໃຫ້​ເຫັນ​ວ່າ​ພຽງ​ເລັກ​ນ້ອຍ​ຫຼາຍ​ກ​່​ວາ 30​% ຂອງ​ອົງ​ປະ​ກອບ​ຂອງ​ຕົ້ນ​ໄມ້​ທັງ​ຫມົດ​ມີ​ລູກ​ຫລານ​.

ຕອນນີ້ໃຫ້ໃຊ້ກົນໄກທີ່ແຕກຕ່າງກັນເລັກນ້ອຍ - ການເຊື່ອມຕໍ່ກັບພາກສ່ວນ recursive ຜ່ານ LATERAL, ເຊິ່ງຈະຊ່ວຍໃຫ້ພວກເຮົາເຂົ້າເຖິງທົ່ງນາຂອງ "ຕາຕະລາງ" recursive ທັນທີ, ແລະນໍາໃຊ້ຫນ້າທີ່ລວມທີ່ມີເງື່ອນໄຂການກັ່ນຕອງໂດຍອີງໃສ່ node ເພື່ອຫຼຸດຜ່ອນຊຸດຂອງຄີ:

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. ໃຫ້ກັບຄືນໄປຫາຮາກ

ສູດການຄິດໄລ່ນີ້ຈະເປັນປະໂຫຍດຖ້າທ່ານຕ້ອງການເກັບກໍາຂໍ້ມູນສໍາລັບອົງປະກອບທັງຫມົດ "ຂຶ້ນຕົ້ນໄມ້", ໃນຂະນະທີ່ເກັບຮັກສາຂໍ້ມູນກ່ຽວກັບເອກະສານແຫຼ່ງໃດ (ແລະມີຕົວຊີ້ວັດອັນໃດ) ທີ່ເຮັດໃຫ້ມັນຖືກລວມເຂົ້າໃນຕົວຢ່າງ - ຕົວຢ່າງ, ການສ້າງບົດລາຍງານສະຫຼຸບ. ດ້ວຍການລວບລວມເຂົ້າໄປໃນ nodes.

ສິ່ງທີ່ຕໍ່ໄປນີ້ຄວນຈະຖືກປະຕິບັດພຽງແຕ່ເປັນຫຼັກຖານສະແດງແນວຄວາມຄິດ, ນັບຕັ້ງແຕ່ຄໍາຮ້ອງຂໍໄດ້ກາຍເປັນເລື່ອງທີ່ຫຍຸ້ງຍາກຫຼາຍ. ແຕ່ຖ້າມັນຄອບງໍາຖານຂໍ້ມູນຂອງເຈົ້າ, ທ່ານຄວນຄິດກ່ຽວກັບການໃຊ້ເຕັກນິກທີ່ຄ້າຍຄືກັນ.

ໃຫ້ເລີ່ມຕົ້ນດ້ວຍສອງສາມຄໍາທີ່ງ່າຍດາຍ:

• ບັນທຶກດຽວກັນຈາກຖານຂໍ້ມູນ ມັນດີທີ່ສຸດທີ່ຈະອ່ານມັນເທື່ອດຽວ.
• ບັນທຶກຈາກຖານຂໍ້ມູນ ມັນມີປະສິດທິພາບຫຼາຍໃນການອ່ານເປັນຊຸດກວ່າຄົນດຽວ.

ຕອນນີ້ໃຫ້ພະຍາຍາມສ້າງຄໍາຮ້ອງຂໍທີ່ພວກເຮົາຕ້ອງການ.

ຂັ້ນຕອນ 1

ແນ່ນອນ, ເມື່ອເລີ່ມຕົ້ນ recursion (ບ່ອນທີ່ພວກເຮົາຈະບໍ່ມີມັນ!) ພວກເຮົາຈະຕ້ອງລົບບັນທຶກຂອງໃບດ້ວຍຕົນເອງໂດຍອີງໃສ່ຊຸດຂອງຕົວລະບຸເບື້ອງຕົ້ນ:

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
  ...

ຖ້າມັນເບິ່ງຄືວ່າແປກປະຫລາດກັບຜູ້ໃດຜູ້ນຶ່ງທີ່ "ຊຸດ" ຖືກເກັບໄວ້ເປັນສະຕຣິງແລະບໍ່ແມ່ນ array, ຫຼັງຈາກນັ້ນມີຄໍາອະທິບາຍງ່າຍໆສໍາລັບເລື່ອງນີ້. ມີຟັງຊັນ "gluing" ປະສົມປະສານໃນຕົວສໍາລັບສາຍ string_agg, ແຕ່ບໍ່ແມ່ນສໍາລັບ arrays. ເຖິງແມ່ນວ່ານາງ ງ່າຍ​ທີ່​ຈະ​ປະ​ຕິ​ບັດ​ດ້ວຍ​ຕົນ​ເອງ​.

ຂັ້ນຕອນ 2

ຕອນນີ້ພວກເຮົາຈະໄດ້ຮັບຊຸດ ID ພາກສ່ວນທີ່ຈະຕ້ອງອ່ານຕື່ມອີກ. ເກືອບສະເຫມີພວກເຂົາຈະຖືກຊ້ໍາກັນໃນບັນທຶກທີ່ແຕກຕ່າງກັນຂອງຊຸດຕົ້ນສະບັບ - ດັ່ງນັ້ນພວກເຮົາຈະ ຈັດກຸ່ມໃຫ້ເຂົາເຈົ້າ, ໃນຂະນະທີ່ຮັກສາຂໍ້ມູນກ່ຽວກັບໃບແຫຼ່ງ.

ແຕ່ນີ້ສາມບັນຫາລໍຖ້າພວກເຮົາ:

1. ພາກສ່ວນ "subrecursive" ຂອງການສອບຖາມບໍ່ສາມາດມີຫນ້າທີ່ລວມກັບ GROUP BY.
2. ການອ້າງອີງເຖິງ "ຕາຕະລາງ" recursive ບໍ່ສາມາດຢູ່ໃນ subquery ຊ້ອນ.
3. ການຮ້ອງຂໍຢູ່ໃນພາກສ່ວນ recursive ບໍ່ສາມາດມີ CTE ໄດ້.

ໂຊກດີ, ບັນຫາທັງຫມົດເຫຼົ່ານີ້ແມ່ນຂ້ອນຂ້າງງ່າຍທີ່ຈະເຮັດວຽກປະມານ. ໃຫ້ເລີ່ມຕົ້ນຈາກທີ່ສຸດ.

CTE ໃນສ່ວນ recursive

ນີ້ຄືດັ່ງນັ້ນ ບໍ່ ເຮັດວຽກ:

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

ແລະດັ່ງນັ້ນມັນເຮັດວຽກ, ວົງເລັບເຮັດໃຫ້ຄວາມແຕກຕ່າງ!

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

Nested query ຕໍ່ກັບ "ຕາຕະລາງ" recursive

Hmm... CTE recursive ບໍ່ສາມາດເຂົ້າເຖິງໄດ້ໃນການສອບຖາມຍ່ອຍ. ແຕ່ມັນອາດຈະຢູ່ໃນ CTE! ແລະການຮ້ອງຂໍທີ່ຊ້ອນກັນສາມາດເຂົ້າເຖິງ CTE ນີ້ແລ້ວ!

GROUP BY ພາຍໃນ recursion

ມັນບໍ່ເປັນທີ່ພໍໃຈ, ແຕ່... ພວກເຮົາມີວິທີງ່າຍໆທີ່ຈະເຮັດຕາມແບບກຸ່ມໂດຍການໃຊ້ 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