我想从以下可重现的示例中在Teradata中创建一个递归视图(即,创建回归视图)：
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,4,'4');
INSERT INTO vt1 VALUES ('a',5,'1');
INSERT INTO vt1 VALUES ('b','3');
INSERT INTO vt1 VALUES ('b','2');
WITH RECURSIVE cte (foo,counter,bar,rsum) AS
(
SELECT
foo,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 ║ ╚═════╩═════════╩═════╩══════╩════════╝
我最终希望“保存”作为一种观点.我尝试过CREATE RECURSIVE VIEW和几个变种,但我想我不明白如何绕过WITH RECURSIVE cte语句.
有关相关问题以了解正在发生的情况,请参阅this question
解决方法
好吧,这实际上比我想象的更难：
create recursive view db.test_view (
foo,rsum) as
(SELECT
foo,bar AS rsum
FROM
vt1
QUALIFY ROW_NUMBER() OVER (PARTITION BY foo ORDER BY counter) = 1
UNION ALL
SELECT
t.foo,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
)
不要将递归连接限定为视图. IE,JOIN test_view,不是JOIN db.test_view.
