python - 替代WITH RECURSIVE 子句

Question

Snowflake DB不支持递归with子句功能，需要帮助我如何实现以下查询。以下查询在 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

![结果]: /image/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