比如有个类似于某音的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的查询过程通常遵循以下步骤:
- 执行锚点成员查询,生成初始结果集(R0)。
- 将R0作为输入,执行递归成员查询,生成第一轮递归的结果集(R1)。
- 将R1作为输入,再次执行递归成员查询,生成第二轮递归的结果集(R2),以此类推。
- 重复执行递归成员查询,直到没有更多的行被返回为止。
- 使用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的),只返回了更深层次的推荐结果。