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;

[explanation.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;

[explanation.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;

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

ਹੁਣ ਸਾਨੂੰ ਸੈਕਸ਼ਨ ਆਈਡੀ ਦਾ ਇੱਕ ਸੈੱਟ ਮਿਲੇਗਾ ਜਿਸਨੂੰ ਅੱਗੇ ਪੜ੍ਹਨ ਦੀ ਲੋੜ ਹੋਵੇਗੀ। ਲਗਭਗ ਹਮੇਸ਼ਾ ਉਹ ਅਸਲੀ ਸੈੱਟ ਦੇ ਵੱਖ-ਵੱਖ ਰਿਕਾਰਡਾਂ ਵਿੱਚ ਡੁਪਲੀਕੇਟ ਕੀਤੇ ਜਾਣਗੇ - ਇਸ ਲਈ ਅਸੀਂ ਕਰਾਂਗੇ ਉਹਨਾਂ ਨੂੰ ਸਮੂਹ, ਸਰੋਤ ਪੱਤਿਆਂ ਬਾਰੇ ਜਾਣਕਾਰੀ ਨੂੰ ਸੁਰੱਖਿਅਤ ਕਰਦੇ ਹੋਏ।

ਪਰ ਇੱਥੇ ਤਿੰਨ ਮੁਸੀਬਤਾਂ ਸਾਡੀ ਉਡੀਕ ਕਰ ਰਹੀਆਂ ਹਨ:

1. ਪੁੱਛਗਿੱਛ ਦੇ "ਸਬਰੇਕਰਸਿਵ" ਹਿੱਸੇ ਵਿੱਚ ਇਸ ਨਾਲ ਕੁੱਲ ਫੰਕਸ਼ਨ ਸ਼ਾਮਲ ਨਹੀਂ ਹੋ ਸਕਦੇ ਹਨ GROUP BY.
2. ਇੱਕ ਆਵਰਤੀ "ਸਾਰਣੀ" ਦਾ ਹਵਾਲਾ ਇੱਕ ਨੇਸਟਡ ਸਬਕਵੇਰੀ ਵਿੱਚ ਨਹੀਂ ਹੋ ਸਕਦਾ ਹੈ।
3. ਆਵਰਤੀ ਭਾਗ ਵਿੱਚ ਇੱਕ ਬੇਨਤੀ ਵਿੱਚ CTE ਸ਼ਾਮਲ ਨਹੀਂ ਹੋ ਸਕਦਾ ਹੈ।

ਖੁਸ਼ਕਿਸਮਤੀ ਨਾਲ, ਇਹ ਸਾਰੀਆਂ ਸਮੱਸਿਆਵਾਂ ਆਲੇ ਦੁਆਲੇ ਕੰਮ ਕਰਨ ਲਈ ਕਾਫ਼ੀ ਆਸਾਨ ਹਨ. ਆਉ ਅੰਤ ਤੋਂ ਸ਼ੁਰੂ ਕਰੀਏ.

ਆਵਰਤੀ ਭਾਗ ਵਿੱਚ ਸੀ.ਟੀ.ਈ

ਇੱਥੇ ਏ ਨਾ ਕੰਮ ਕਰਦਾ ਹੈ:

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

ਅਤੇ ਇਸ ਲਈ ਇਹ ਕੰਮ ਕਰਦਾ ਹੈ, ਬਰੈਕਟ ਫਰਕ ਪਾਉਂਦੇ ਹਨ!

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

ਇੱਕ ਆਵਰਤੀ "ਸਾਰਣੀ" ਦੇ ਵਿਰੁੱਧ ਨੇਸਟਡ ਪੁੱਛਗਿੱਛ

ਹਮ... ਸਬਕਵੇਰੀ ਵਿੱਚ ਇੱਕ ਆਵਰਤੀ CTE ਤੱਕ ਪਹੁੰਚ ਨਹੀਂ ਕੀਤੀ ਜਾ ਸਕਦੀ। ਪਰ ਇਹ CTE ਦੇ ਅੰਦਰ ਹੋ ਸਕਦਾ ਹੈ! ਅਤੇ ਇੱਕ ਨੇਸਟਡ ਬੇਨਤੀ ਪਹਿਲਾਂ ਹੀ ਇਸ CTE ਤੱਕ ਪਹੁੰਚ ਕਰ ਸਕਦੀ ਹੈ!

ਆਵਰਤੀ ਅੰਦਰਲੇ ਦੁਆਰਾ ਗਰੁੱਪ ਕਰੋ

ਇਹ ਕੋਝਾ ਹੈ, ਪਰ... ਸਾਡੇ ਕੋਲ GROUP BY ਦੀ ਵਰਤੋਂ ਕਰਨ ਦਾ ਇੱਕ ਸਧਾਰਨ ਤਰੀਕਾ ਹੈ 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

ਫਾਈਨਲ ਲਈ ਸਾਡੇ ਕੋਲ ਕੁਝ ਨਹੀਂ ਬਚਿਆ ਹੈ:

• ਅਸੀਂ ਸਮੂਹ ਆਈਡੀ ਦੇ ਸੈੱਟ ਦੇ ਆਧਾਰ 'ਤੇ "ਸੈਕਸ਼ਨ" ਰਿਕਾਰਡ ਪੜ੍ਹਦੇ ਹਾਂ
• ਅਸੀਂ ਘਟਾਏ ਗਏ ਭਾਗਾਂ ਦੀ ਮੂਲ ਸ਼ੀਟਾਂ ਦੇ "ਸੈਟਾਂ" ਨਾਲ ਤੁਲਨਾ ਕਰਦੇ ਹਾਂ
• ਦੀ ਵਰਤੋਂ ਕਰਕੇ ਸੈੱਟ-ਸਟ੍ਰਿੰਗ ਦਾ "ਪਸਾਰ" ਕਰੋ 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;

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਸਰੋਤ: www.habr.com