【PGSQL】使用Recursive Common Table Expressions（递归公用表表达式）设计简单的推荐关注用户算法 & 从简入繁讲解 CTE

比如有个类似于某音的app，有一个发现用户功能，算法是怎么实现的呢【pgsql】

我们可以设计一个简单的推荐关注用户算法：（假设有个深度[depth]）

如图深度1为我关注的人，他们关注的人会被推荐给我（by instagram），他们关注的人关注的人又会被推荐给我（by instagram），隔着套娃呢xD，然后深度一层层如上图所示

以此一直往下直至真的真的没人了，instagram才会推荐随机的user或是依靠另外的算法。

1. 了解递归CTE的直接上代码：

-- 生成表名为suggestions, 有leader_id，follower_id，depth(衡量递进深度)三列
with recursive suggestions(leader_id, follower_id, depth) as (
	select leader_id, follower_id, 1 as depth
	from followers
	-- 给id为1000的user进行推荐
	where follower_id = 1000
	union
	select followers.leader_id, followers.follower_id, depth + 1
	from followers
	join suggestions on suggestions.leader_id = followers.follower_id
	where depth < 3
)
select distinct users.id, users.username
from suggestions
join users on users.id = suggestions.leader_id
where depth > 1
limit 30;

步步分解：

1. 定义结果表和工作表：

suggestions(leader_id, follower_id, depth)，三列详细的列名都被定义，最后结果表名字为suggestions

2. 执行非递归语句:

select leader_id, follower_id, 1 as depth from followers -- 给id为1的user进行推荐 where follower_id = 1

3. 执行递归语句 ：

tips. 后台现在suggestions为working table，替换一下更显眼

（并不是实际的）

followers.leader_id, followers.follower_id, depth + 1

--先不看上面那行

from followers ​ join WORKINGTABLE on WORKINGTABLE.leader_id = followers.follower_id ​ where depth < 2

这里得到：

然后如下语句的结果：

followers.leader_id, followers.follower_id, depth + 1

与results table进行union：

而现在的working table：

然后继续执行递归，最终发现depth = 2 < 2不成立，

停止递归；

返回results table(suggestions)，即

然后在执行：

select distinct users.id, users.username from suggestions join users on users.id = suggestions.leader_id where depth > 1 limit 30;

获取推荐名单

2. 简单的CTE

ex.要求，显示username和其tag的创建时刻(小于2010-01-07)

相当于我们先要把两种tags合一块，在和users-table JOIN

-- 执行如下代码(使用子查询)
select username, tags.created_at
from users
join (
	select user_id, created_at from caption_tags
    -- union all 把两表并一起,但不删除重复行
	union all
	select user_id, created_at from photo_tags
) as tags on tags.user_id = users.id
where tags.created_at < '2010-01-07';

执行代码后：

然后得到:

而使用CTE的方式达成相同效果更为简易：

with tags as (
	select user_id, created_at from caption_tags
	union all
	select user_id, created_at from photo_tags
)
select username, tags.created_at
from users
join tags on tags.user_id = users.id
where tags.created_at < '2010-01-07';

CTE 使用 WITH 语句来定义，它允许你将一个查询的结果集赋予一个名字（即 CTE 的名称），这个名字可以在主查询（紧跟在 WITH 语句之后的查询）或者在同一 WITH 语句中定义的另一个 CTE 中被引用。它在 SQL 语句的执行期间存在，并且可以被后续的 SQL 查询引用。CTE 提供了一种编写、阅读和维护复杂 SQL 查询的便利方式，因为它们可以将复杂的查询分解成更小、更易于管理的部分。但是explain一下，发现其实cte与原来的没有任何差别 ​​​​​​

3. Recursive(递归) CTE

Recursive CTE（递归公用表表达式）是SQL中的一种强大工具，它允许用户编写涉及递归查询的表达式。递归CTE通常用于处理具有层次结构或树形结构的数据，如组织架构、分类树、文件系统等。下面将详细讲解Recursive CTE的概念、原理、应用场景以及优缺点，并配以具体例子。

1. 概念

递归CTE是一种在SQL查询中定义的临时结果集，它可以引用自身来执行递归查询。通过递归方式，CTE可以遍历整个层次结构或树形结构的数据。

2. 原理

递归CTE由两个主要部分组成：锚点成员（Anchor Member）和递归成员（Recursive Member）。

• 锚点成员：是递归查询的起点，它返回一个初始的结果集，这个结果集是递归查询的基础。
• 递归成员：是引用CTE自身进行查询的部分，它基于锚点成员的结果集和上一轮递归的结果集进行进一步的查询。递归成员会不断重复执行，直到满足终止条件（即没有更多的行被返回）为止。

递归CTE的查询过程通常遵循以下步骤：

1. 执行锚点成员查询，生成初始结果集（R0）。
2. 将R0作为输入，执行递归成员查询，生成第一轮递归的结果集（R1）。
3. 将R1作为输入，再次执行递归成员查询，生成第二轮递归的结果集（R2），以此类推。
4. 重复执行递归成员查询，直到没有更多的行被返回为止。
5. 使用UNION ALL（或UNION DISTINCT，但通常使用UNION ALL以保留重复行并提高效率）将R0到Rn的结果集合并成一个最终的结果集。

3. 应用场景

递归CTE在多个应用场景中非常有用，包括但不限于：

• 组织架构查询：查询整个公司的组织架构，包括员工、经理及其上下级关系。
• 分类树查询：查询商品的分类树，包括顶级分类、子分类及其层级关系。
• 文件系统查询：查询文件系统的目录结构，包括根目录、子目录及其文件。

4. 优点

• 灵活性高：递归CTE允许用户编写复杂的递归查询，以处理具有层次结构的数据。
• 可读性好：与复杂的嵌套查询相比，递归CTE的语法更清晰、更易于理解。
• 性能优化：递归CTE允许数据库优化器对查询进行优化，以提高查询性能。

5. 再次分析例子

with recursive suggestions(leader_id, follower_id, depth) as (

with recursive：这是定义递归CTE的关键字。recursive关键字表明这是一个递归的CTE，允许CTE在查询中引用自己。
suggestions(leader_id, follower_id, depth)：这里定义了CTE的名称（suggestions）和它的列（leader_id、follower_id、depth）。leader_id是被推荐的用户ID，follower_id是发起推荐请求的用户ID（在这个例子中为1000），depth表示推荐的递进深度。

select leader_id, follower_id, 1 as depth  
    from followers  
    -- 给id为1000的user进行推荐  
    where follower_id = 1000

这部分是递归的起点（锚点成员）。它从followers表中选取所有follower_id为1000的记录，并将这些记录的leader_id作为潜在的推荐对象，follower_id保持为1000，depth设为1，表示这是第一级推荐。

union  
    select followers.leader_id, followers.follower_id, depth + 1  
    from followers  
    join suggestions on suggestions.leader_id = followers.follower_id  
    where depth < 3  
)

union：这个操作符用于合并锚点成员和递归成员的结果集。注意，在递归CTE中，通常使用UNION ALL而不是UNION，因为UNION会去除重复行，这可能会意外地阻止递归的继续。但在这个例子中，如果followers表中没有重复的follower_id到leader_id映射，使用UNION也不会有问题。
递归成员从followers表中选取记录，但这些记录是通过与CTE suggestions进行连接得到的。连接条件是suggestions.leader_id = followers.follower_id，这意味着它查找那些已经作为推荐（即之前某个步骤的leader_id）出现在suggestions中的用户的跟随者。
对于每个找到的跟随者，它将其leader_id和follower_id添加到结果集中，并将depth增加1，以表示这是更深一级的推荐。
where depth < 3：这是递归的终止条件。它确保递归不会超过两级的推荐（因为从1开始计数，depth < 3只允许到2）。没有这个条件，递归将会无限进行下去（如果followers表中存在循环引用的话）。

select distinct users.id, users.username  
from suggestions  
join users on users.id = suggestions.leader_id  
where depth > 1  
limit 30;

这个查询从suggestions CTE中选择推荐结果，并通过与users表连接来获取被推荐用户的ID和用户名。
where depth > 1：这个条件排除了初始的推荐（即深度为1的推荐），只选择更深层次的推荐。
limit 30：限制结果集最多返回30条记录。
综上所述，这个查询为ID为1000的用户推荐了最多两级深度的潜在联系人，并排除了直接的跟随者（即深度为1的），只返回了更深层次的推荐结果。