- he mīkini ikaika loa a maʻalahi inā hana ʻia nā hana "hohonu" ma nā ʻikepili pili. Akā ʻo ka hoʻihoʻi ʻole ʻia he mea ʻino e hiki ke alakaʻi i kekahi hoʻokō pau ʻole kaʻina hana, a i ʻole (e hana pinepine ʻia) i "ʻai" nā mea hoʻomanaʻo āpau i loaʻa.
ʻO ka DBMS e pili ana i kēia hana ma nā loina like - "Ua ʻōlelo mai lākou iaʻu e ʻeli, no laila ʻeli wau". ʻAʻole hiki i kāu noi ke hoʻolohi wale i nā kaʻina hana pili, e lawe mau i nā kumuwaiwai processor, akā "hoʻolei" i ka waihona holoʻokoʻa, "ʻai" i nā hoʻomanaʻo āpau i loaʻa. pale i ka hoʻihoʻi pau ʻole - ke kuleana o ka mea hoʻomohala ponoʻī.
Ma PostgreSQL, hiki ke hoʻohana i nā nīnau recursive ma o ʻike ʻia i ka wā kahiko o ka mana 8.4, akā hiki iā ʻoe ke hālāwai pinepine i nā noi "pale ʻole". Pehea e hoʻopau ai iā ʻoe iho i nā pilikia o kēia ʻano?
Mai kākau i nā nīnau recursive
A kākau i nā mea recursive ʻole. Me ka mahalo, ko oukou K.O.
I ka ʻoiaʻiʻo, hāʻawi ʻo PostgreSQL i nā hana he nui āu e hoʻohana ai ole hoʻopili i ka recursion.
E hoʻohana i kahi ala like ʻole i ka pilikia
I kekahi manawa hiki iā ʻoe ke nānā wale i ka pilikia mai ka "ʻaoʻao ʻokoʻa". Ua hāʻawi wau i kahi hiʻohiʻona o ia kūlana ma ka ʻatikala - ka hoʻonui ʻana i kahi pūʻulu helu me ka hoʻohana ʻole ʻana i nā hana aggregate maʻamau:
WITH RECURSIVE src AS (
SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr
)
, T(i, val) AS (
SELECT
1::bigint
, 1
UNION ALL
SELECT
i + 1
, val * arr[i]
FROM
T
, src
WHERE
i <= array_length(arr, 1)
)
SELECT
val
FROM
T
ORDER BY -- отбор финального результата
i DESC
LIMIT 1;Hiki ke pani ʻia kēia noi me kahi koho mai ka poʻe akamai makemakika:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;E hoʻohana i ka genera_series ma kahi o nā puka lou
E ʻōlelo kākou, ke alo nei kākou i ka hana o ka hana ʻana i nā prefix a pau no kahi kaula 'abcdefgh':
WITH RECURSIVE T AS (
SELECT 'abcdefgh' str
UNION ALL
SELECT
substr(str, 1, length(str) - 1)
FROM
T
WHERE
length(str) > 1
)
TABLE T;
Makemake ʻoe i ka recursion ma aneʻi?.. Inā ʻoe e hoʻohana LATERAL и generate_series, a laila ʻaʻole pono ʻoe iā CTE:
SELECT
substr(str, 1, ln) str
FROM
(VALUES('abcdefgh')) T(str)
, LATERAL(
SELECT generate_series(length(str), 1, -1) ln
) X;Hoʻololi i ka ʻōnaehana waihona
No ka laʻana, loaʻa iā ʻoe kahi papa o nā memo forum me nā pilina mai ka mea i pane aku iā wai, a i ʻole kahi pae i loko :
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
ʻAe, ʻo kahi noi maʻamau e hoʻoiho i nā memo āpau ma kahi kumuhana e like me kēia:
WITH RECURSIVE T AS (
SELECT
*
FROM
message
WHERE
message_id = $1
UNION ALL
SELECT
m.*
FROM
T
JOIN
message m
ON m.reply_to = T.message_id
)
TABLE T;Akā, no ka mea makemake mau mākou i ke kumuhana holoʻokoʻa mai ka memo kumu, no ke aha mākou ʻaʻole hoʻohui i kāna ID i kēlā me kēia komo ʻakomi?
-- добавим поле с общим идентификатором темы и индекс на него
ALTER TABLE message
ADD COLUMN theme_id uuid;
CREATE INDEX ON message(theme_id);
-- инициализируем идентификатор темы в триггере при вставке
CREATE OR REPLACE FUNCTION ins() RETURNS TRIGGER AS $$
BEGIN
NEW.theme_id = CASE
WHEN NEW.reply_to IS NULL THEN NEW.message_id -- берем из стартового события
ELSE ( -- или из сообщения, на которое отвечаем
SELECT
theme_id
FROM
message
WHERE
message_id = NEW.reply_to
)
END;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER ins BEFORE INSERT
ON message
FOR EACH ROW
EXECUTE PROCEDURE ins();
I kēia manawa hiki ke hoʻemi ʻia kā mākou hulina recursive a pau i kēia wale nō:
SELECT
*
FROM
message
WHERE
theme_id = $1;E hoʻohana i nā "palena"
Inā ʻaʻole hiki iā mākou ke hoʻololi i ke ʻano o ka waihona no kekahi kumu, e ʻike kākou i ka mea e hiki ai iā mākou ke hilinaʻi a hiki i ka hele ʻana o kahi hewa i ka ʻikepili ʻaʻole e alakaʻi i ka recursion pau ʻole.
Helu hōhonu hoʻihoʻi
Hoʻonui wale mākou i ka counter i hoʻokahi ma kēlā me kēia kaʻina recursion a hiki i kahi palena a mākou e manaʻo nei he lawa ʻole:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: Ke hoʻāʻo nei mākou e hoʻopaʻa, ʻaʻole mākou e hana i nā mea ʻē aʻe ma mua o ka palena i ʻōlelo ʻia o nā iterations "hohonu".
Con: ʻAʻohe mea hōʻoiaʻiʻo ʻaʻole mākou e hana hou i ka moʻolelo like - no ka laʻana, ma ka hohonu o 15 a me 25, a laila kēlā me kēia +10. A ʻaʻohe mea i hoʻohiki i kekahi mea e pili ana i ka "ākea".
ʻO ka mea maʻamau, ʻaʻole e pau ka recursion, akā inā ma kēlā me kēia ʻanuʻu ka piʻi nui ʻana o ka helu o nā moʻolelo, ʻike maopopo mākou a pau i ka hopena ...
Kiaʻi o ke "ala"
Hoʻohui hou mākou i nā mea ʻike a pau a mākou i hālāwai ai ma ke ala hoʻihoʻi i loko o kahi ʻano, he "ala" kūʻokoʻa iā ia:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Inā he pōʻaiapuni i ka ʻikepili, ʻaʻole mākou e hana pinepine i ka moʻolelo like i loko o ke ala like.
Con: Akā i ka manawa like, hiki iā mākou ke kāpae maoli i nā moʻolelo āpau me ka ʻole e hana hou iā mākou iho.
Ka palena o ke alahele
I mea e pale aku ai i ke kūlana o ka recursion "auwana" i kahi hohonu hiki ʻole ke hoʻomaopopo ʻia, hiki iā mākou ke hoʻohui i nā ʻano ʻelua ma mua. A i ʻole, inā ʻaʻole mākou makemake e kākoʻo i nā kahua kūpono ʻole, e hoʻohui i ke ʻano no ka hoʻomau ʻana i ka recursion me kahi koho o ka lōʻihi o ke ala:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) AND
array_length(T.path, 1) < 10
)
E koho i kahi ala e like me kou makemake!
Source: www.habr.com