SQL 递归思想
目前很缺递归思维，主要是算法代码写得少，本篇记录下最近思考的内容。以 PostgreSQL 代码举例（主要是非常喜欢这款性能小钢炮数据库）。
树状查询不多说，很简单大家基本都会，主要讲 cte 代码递归实现不同需求。
以下所有内容都是我个人理解，不对之处请各位读者多指教！
cte 语法简介
以PG举例，如果是ORACLE的话需要去掉RECURSIVE关键字
WITH RECURSIVE cte_tb (column_list) AS (
-- 开始条件
SELECT ...
UNION ALL
-- 递归逻辑代码和结束递归逻辑的代码
SELECT ... FROM cte_tb WHERE ...
)
SELECT * FROM cte_tb;
PG使用 cte 递归实现层级查询
scott=> WITH RECURSIVE T(LV, EMPNO, ENAME, MGR) AS (
scott(> SELECT 1 AS LV, EMPNO, ENAME, MGR FROM EMP WHERE MGR IS NULL -- 根节点，（开始条件）
scott(> UNION ALL
scott(> SELECT T.LV + 1, E.EMPNO, E.ENAME, E.MGR FROM EMP E
scott(> INNER JOIN T ON T.EMPNO = E.MGR -- e表属于子节点，t表属于上层节点 t.EMPNO = e.MGR 相当于 prior empno = mgr; （递归条件），如果 t.EMPNO = e.MGR 匹配不上了就返回NULL （递归结束条件）
scott(> )
scott-> SELECT *
scott-> FROM T;
lv | empno | ename | mgr
----+-------+--------+------
1 | 7839 | KING |
2 | 7566 | JONES | 7839
2 | 7698 | BLAKE | 7839
2 | 7782 | CLARK | 7839
3 | 7499 | ALLEN | 7698
3 | 7521 | WARD | 7698
3 | 7654 | MARTIN | 7698
3 | 7788 | SCOTT | 7566
3 | 7844 | TURNER | 7698
3 | 7900 | JAMES | 7698
3 | 7902 | FORD | 7566
3 | 7934 | MILLER | 7782
4 | 7369 | SMITH | 7902
4 | 7876 | ADAMS | 7788
(14 rows)
Time: 0.396 ms
cte 递归核心思想
一、使用 cte 递归，一定要满足以下三个条件：
- 开始条件。
- 递归条件。
- 递归结束条件。
二、递归重要的思想：
- 大问题拆小问题，这个比较难，（怎么拆、小问题之间的逻辑如何关联上，递归结束条件如何满足）等， 这也主要是我缺乏递归思维原因。
- 递归和循环的思路是高度相似：
- 循环需要 开始条件、结束条件、循环逻辑。
- 递归需要 开始条件、结束条件、递归逻辑+调用自身逻辑。
案例一、cte 递归实现数字递增：
with RECURSIVE x(seq) as (
SELECT 1 as seq -- SELECT 1 as seq from DUAL 递归开始条件
UNION ALL
SELECT x.seq + 1 as seq from x -- x.seq + 1 from x 递归条件（每次执行 + 1 ） 调用自身
WHERE x.seq < 10 -- x.seq < 10 递归结束条件
)
SELECT * FROM x ORDER BY 1;
seq
-----
1
2
3
4
5
6
7
8
9
10
(10 rows)
Time: 0.700 ms
上面这个案例很像循环，但是总体实现起来整体的思路会比循环稍微复杂那么一丢丢。
其实在 PG 来说实现数字递增的方式很多，例如：序列、SERIAL 、PLPG/SQL for 循环， 均能实现类似效果，上面案例案例让各位读者初步感受下。
案例二、cte 递归实现distinct效果
distinct sql
select distinct col from tt2;
col
--------
C
JAVA
PL/SQL
Python
(4 rows)
Time: 255.794 ms
使用CTE递归的方式实现
WITH RECURSIVE t(col) as (
(SELECT col from tt2 ORDER BY col LIMIT 1) -- 递归开始条件。
UNION ALL
SELECT (SELECT col FROM tt2 WHERE tt2.COL > t.COL order by tt2.COL LIMIT 1) -- tt2.COL > t.COL 大问题拆小问题 ，递归逻辑
FROM t WHERE t.COL IS NOT NULL -- 递归结束条件
)
SELECT * FROM t WHERE t.COL is not NULL ;
col
--------
C
JAVA
PL/SQL
Python
(4 rows)
Time: 0.871 ms
这个案例引用的是德哥的思路，PG 15 上对 distinct 算子优化过（支持并行），一千万行数据 265 ms 就能跑出结果。
但是如果使用 cte 递归的话，根本不需要并行，0.8 ms 便能出结果，秒杀优化器算法。
这个 order by tt2.col 非常牛逼，神来之笔，相当于进一步优化了整个递归的算法模型。
基于德哥的思路做了修改
WITH RECURSIVE t(col) as (
(SELECT col from tt2 ORDER BY col LIMIT 1)
UNION ALL
SELECT (SELECT col FROM tt2 WHERE tt2.COL > t.COL GROUP BY tt2.COL LIMIT 1) FROM t WHERE t.COL IS NOT NULL
)
SELECT * FROM t WHERE t.COL is not NULL ;
col
--------
C
JAVA
PL/SQL
Python
(4 rows)
Time: 0.432 ms
order by 改成 group by 是借鉴德哥思路，我自己想的改良版，速度提升了 0.4ms , 不过总体来说差不多，有真实案例看场景使用。
案例三、cte 递归实现阶乘算法：
WITH RECURSIVE factorial (n, factorial_val) AS (
(SELECT 1 n, 1 factorial_val ) -- 递归开始条件 : 1的阶乘为1
UNION ALL
SELECT f.n + 1, (f.n + 1) * f.factorial_val /* 递归逻辑 (1 + 1) * 1 = 2
(2 + 1) * 2 = 6
(3 + 1) * 6 = 24
(4 + 1) * 24 = 120
*/
FROM factorial f
WHERE f.n < 5 -- 结束递归条件，算 5 的阶乘
)
SELECT max(factorial_val) FROM factorial;
max
-----
120
(1 row)
Time: 0.395 ms
CTE 递归也能实现阶乘的逻辑，由于 PG 上是没阶乘函数的，可以将上面逻辑封装到一个函数里面进行使用，代码如下：
CREATE OR REPLACE FUNCTION factorial(num BIGINT)
RETURNS BIGINT AS $$
DECLARE
result BIGINT;
BEGIN
WITH RECURSIVE factorial (n, factorial_val) AS (
(SELECT 1::INT as n , 1::int as factorial_val)
UNION ALL
SELECT f.n + 1, (f.n + 1) * f.factorial_val
FROM factorial f
WHERE f.n < num
)
SELECT max(factorial_val) INTO result FROM factorial;
RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
结束语
cte 递归的技巧在任何数据库都通用，我这里只是使用了PG作为演示案例，递归不仅仅是树状查询，理论上来说，只要能拆解逻辑（这也是最难的），所有SQL逻辑都能使用递归来表达。
但是这玩意是个双刃剑，不是所有场景都能使用，假如一个列的选择性很高，例如主键，如果使用递归来进行匹配查找的话，那绝对是SB行为，线性递归的时间复杂度是O(n)，速度取决于你的数据量。
没有最好的算法，只有最合适的算法。不过有递归思维的话，确实能解决很多日常和工作中不同类型的事物。
