sql – 创建一个在Teradata中具有“with recursive”语句的递归视图

我想从以下可重现的示例中在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.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!