使用递归子句的替代项

Question

雪花DB不支持带子句函数的递归，需要帮我怎么实现下面的查询。下面的查询在 Teradata 中效果很好

如果有人也可以帮助我实现使用Python，那就太好了

WITH RECURSIVE RECURTEMP(ID,KCODE,LVL)
AS(SELECT ID, MIN(KCODE) AS KCODE,1
FROM TABLE_A
GROUP BY 1
UNION ALL
SELECT b.ID, trim(a.KCODE)|| ';'||trim(b.KCODE), LVL+1
FROM TABLE_A a
INNER JOIN RECURTEMP b ON a.ID = b.ID AND a.KCODE > b.KCODE
)
SELECT * FROM RECURTEMP

！[结果]：https://i.stack.imgur.com/9PsbK.jpg

CREATE TABLE MYTABLE (
ID VARCHAR2(50),
KCODE VARCHAR2(50)
);
INSERT INTO MYTABLE VALUES  ('ABCD','K10');
INSERT INTO MYTABLE VALUES  ('ABCD','K53');
INSERT INTO MYTABLE VALUES  ('ABCD','K55');
INSERT INTO MYTABLE VALUES  ('ABCD','K56');
COMMIT;

输出如下

ID               KCODE          LEVEL  
--------------------------------------
ABCD             K10               1
ABCD             K53;K10           2
ABCD             K55;K10           2
ABCD             K56;K10           2
ABCD             K55;K53;K10       3
ABCD             K56;K53;K10       3
ABCD             K56;K55;K10       3
ABCD             K56;K55;K53;K10   4

Answer 1

Snowflake 现在支持递归 WITH。

您的查询

WITH RECURSIVE RECURTEMP(ID,KCODE,LVL) AS(
SELECT 
  ID, 
  MIN(KCODE) AS KCODE,
  1
FROM 
  TABLE_A
GROUP BY
  1
UNION ALL
SELECT 
  b.ID, 
  trim(a.KCODE) || ';' || trim(b.KCODE)  AS KCODE, 
  LVL+1
FROM 
  TABLE_A a
  INNER JOIN RECURTEMP b ON (a.ID = b.ID AND a.KCODE > b.KCODE)
)
SELECT * FROM RECURTEMP

文章链接如下。

https://docs.snowflake.net/manuals/user-guide/queries-cte.html#overview-of-recursive-cte-syntax