gpt4 book ai didi

sql - 创建一个在 Teradata 中包含 "with recursive"语句的递归 View

转载 作者:行者123 更新时间:2023-12-02 20:38:09 25 4
gpt4 key购买 nike

我想根据以下可重现示例在 Teradata 中创建递归 View (即 CREATE RECURSIVE VIEW):

CREATE VOLATILE TABLE vt1
(
foo VARCHAR(10)
, counter INTEGER
, bar INTEGER
)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt1 VALUES ('a', 1, '1');
INSERT INTO vt1 VALUES ('a', 2, '2');
INSERT INTO vt1 VALUES ('a', 3, '2');
INSERT INTO vt1 VALUES ('a', 4, '4');
INSERT INTO vt1 VALUES ('a', 5, '1');
INSERT INTO vt1 VALUES ('b', 1, '3');
INSERT INTO vt1 VALUES ('b', 2, '1');
INSERT INTO vt1 VALUES ('b', 3, '1');
INSERT INTO vt1 VALUES ('b', 4, '2');

WITH RECURSIVE cte (foo, counter, bar, rsum) AS
(
SELECT
foo
, counter
, bar
, bar AS rsum
FROM
vt1
QUALIFY ROW_NUMBER() OVER (PARTITION BY foo ORDER BY counter) = 1

UNION ALL

SELECT
t.foo
, t.counter
, t.bar
, CASE WHEN cte.rsum < 3 THEN t.bar + cte.rsum ELSE t.bar END
FROM
vt1 t JOIN cte ON t.foo = cte.foo AND t.counter = cte.counter + 1
)

SELECT
cte.*
, CASE WHEN rsum < 5 THEN 0 ELSE 1 END AS tester
FROM
cte
ORDER BY
foo
, counter
;

这将创建以下输出:

╔═════╦═════════╦═════╦══════╦════════╗
║ foo ║ counter ║ bar ║ rsum ║ tester ║
╠═════╬═════════╬═════╬══════╬════════╣
║ a ║ 1 ║ 1 ║ 1 ║ 0 ║
║ a ║ 2 ║ 2 ║ 3 ║ 0 ║
║ a ║ 3 ║ 2 ║ 5 ║ 1 ║
║ a ║ 4 ║ 4 ║ 4 ║ 0 ║
║ a ║ 5 ║ 1 ║ 5 ║ 1 ║
║ b ║ 1 ║ 3 ║ 3 ║ 0 ║
║ b ║ 2 ║ 1 ║ 4 ║ 0 ║
║ b ║ 3 ║ 1 ║ 5 ║ 1 ║
║ b ║ 4 ║ 2 ║ 2 ║ 0 ║
╚═════╩═════════╩═════╩══════╩════════╝

我最终想将其“保存”为 View 。我已经尝试过 CREATE RECURSIVE VIEW 和几种变体,但我认为我不明白如何绕过 WITH RECURSIVE cte 语句。

有关相关问题以了解发生的情况,请参阅 this question

最佳答案

好吧,这实际上比我想象的要难:

create recursive view db.test_view (
foo, counter,bar,rsum) as
(SELECT
foo,
counter,
bar,
bar AS rsum
FROM
vt1
QUALIFY ROW_NUMBER() OVER (PARTITION BY foo ORDER BY counter) = 1

UNION ALL
SELECT
t.foo,
t.counter,
t.bar,
CASE WHEN cte.rsum < 5 THEN
t.bar + cte.rsum
ELSE t.bar
END
FROM
vt1 t
JOIN test_view cte
ON t.foo = cte.foo
AND t.counter = cte.counter + 1

)

不要限定 View 的递归连接。 IE,JOIN test_view,而不是JOIN db.test_view

关于sql - 创建一个在 Teradata 中包含 "with recursive"语句的递归 View ,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/40940540/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com