Postgresql 的递归查询

最近,在业务上遇到一个问题:公司的会员卡号要支持合并了,一位顾客注册了A、B两张会员卡,系统支持将A会员卡合并到B会员卡,A会员卡的会员权益和相关记录将在合并发生后转移到B会员卡。同时,A到B的合并关系会记录在一张数据表里。现在,要实现通过sql查询出A会员卡合并到B会员卡这样的关系。

若仅仅是由A合并到B,则很容易实现。偏偏这里的合并未做限制,由于羊毛党猖獗,加上营销部门有时为了数据好看,现实中往往存在这样的合并路径:A合并到B,B合并到C、E合并到C、C合并到D,这样A最后合并到了D,E最后也合并到了D。通过SQL查询这样的合并结构,这里需要用到递归查询。

with 查询

使用递归查询前先了解下 with 查询,with 提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE,它们可以被看成是定义只在一个查询中存在的临时表。例如:

with a as ( --先用 with 创建一个名为a的对班级男同学查询的临时表
select stu.stu_num,stu.stu_name from student as stu
where stu.stu_sex='男'
) stu
--接着引用这个临时表找到名字叫“李岩”的这位同学
select stu_num,stu_name from a where stu_name='李岩';

在查询中使用 with 有诸多好处:

  1. 使sql语句结构简单,层次分明:特别是零售数据类的查询,动辄几百行,这种情况下出现几个长嵌套,不要说给别人看了,半个月之后连自己都搞不清楚了,这时若使用 with 查询则模块清晰,层次分明,易于阅读和理解;
  2. 提升效率:with 查询的一个有用的特性是在每一次父查询的执行中它们通常只被计算一次,即使它们被父查询或兄弟 with 查询被多次引用。 因此,在多个地方需要的昂贵计算可以被放在一个 with 查询中来避免冗余工作。比如要对某组指定的会员做不同维度的数据分析,那么,对“这组指定人员”就可用使用 with 查询,其他兄弟查询直接引用结果即可,当需要对“这组指定人员”进行调整时,单独调整 with 查询就行了。

递归查询

通过对 with 语句增加 RECURSIVE 修饰符实现递归查询的目的,常用于层级结构查询,如组织层级、人员层级、物料清单等场景,递归查询格式如下:

with RECURSIVE recursive_name as (
非递归查询 --必须
union --必须,也可以是 union all
递归查询 --必须
)
select * from recursive_name;

下面以本次面对的问题展开:
创建会员合并记录表 cust_merge_dtl

CREATE table if not EXISTS cust_merge_dtl (
merge_id integer, --id
src_cust_id integer, --合并前的会员卡
desc_cust_id integer, --合并后的会员卡
op_time TIMESTAMP --合并时间
);

写入几条会员合并记录

insert into cust_merge_dtl (merge_id,src_cust_id,desc_cust_id,op_time)
VALUES ('1','10056','10071','2019-09-21 8:38:17');
insert into cust_merge_dtl (merge_id,src_cust_id,desc_cust_id,op_time)
VALUES ('2','10071','10092','2020-03-12 16:18:55');
insert into cust_merge_dtl (merge_id,src_cust_id,desc_cust_id,op_time)
VALUES ('3','10088','10092','2020-11-11 10:08:27');
insert into cust_merge_dtl (merge_id,src_cust_id,desc_cust_id,op_time)
VALUES ('4','10092','10112','2020-12-30 18:30:06');
insert into cust_merge_dtl (merge_id,src_cust_id,desc_cust_id,op_time)
VALUES ('5','10010','10112','2021-03-12 12:44:46');

查询会员合并记录表 cust_merge_dtl

select * from cust_merge_dtl;


如上图所示,以10056会员卡为例,它只有一条合并记录,即10056->10071,但是10071紧接着又做了一次合并,10071->10092,最后10092合并到了10112,推算出10056最终合并到了10112,完整的合并路径是这样的:10056 --> 10071 --> 10092 --> 10112

现在用递归查询生成这种合并关系。

with recursive cust_merge as (
select dtl1.src_cust_id as original_cust_id,--初始会员卡
dtl1.src_cust_id,--合并前的会员卡
dtl1.desc_cust_id,--合并后的会员卡
dtl1.op_time
from cust_merge_dtl dtl1 where dtl1.src_cust_id='10056' --非递归查询,此处的查询结果构成整个递归查询的基本结果形式
union
select cm.original_cust_id,dtl2.src_cust_id,dtl2.desc_cust_id,dtl2.op_time
from cust_merge_dtl dtl2
inner join cust_merge cm on cm.desc_cust_id = dtl2.src_cust_id --递归查询
)
select * from cust_merge;

得到原始会员卡及过程中每一次的变动记录。

递归查询的执行过程是这样的:

  1. 非递归查询的查询结果在union去重后作为输出(cust_merge输出),供递归查询部分引用;
  2. 供递归查询引用上一步的cust_merge输出作为输入,进行递归查询,生成新的cust_merge输出(每次递归查询生成的cust_merge输出都将被保存在一个中间表中,并做union去重处理);
  3. 新的cust_merge输出不为空时,则回到步骤2,作为输入,循环执行,直到cust_merge输出为空时停止执行;
  4. 最后的结果是步骤1的查询结果+步骤2的中间表的结果的并集,即两者union去重复(如使用 union all 整个过程不做去重处理)。

同理如果不关注中间的过程只想知道这些合并的会员卡最终合并到哪个会员卡了,稍作调整即可:

with recursive cust_merge as (
select dtl1.src_cust_id as original_cust_id,dtl1.src_cust_id,dtl1.desc_cust_id,dtl1.op_time
from cust_merge_dtl dtl1 --where dtl1.src_cust_id='10056'
union all
select cm.original_cust_id,dtl2.src_cust_id,dtl2.desc_cust_id,dtl2.op_time
from cust_merge_dtl dtl2
inner join cust_merge cm on cm.desc_cust_id = dtl2.src_cust_id
),
last_record as (
--原始会员对应的最新迭代合并记录
select cm.original_cust_id,max(op_time) as last_merge_time
from cust_merge cm
GROUP BY cm.original_cust_id
)
select cm.original_cust_id,cm.desc_cust_id
from cust_merge cm
where exists (
select 1 from last_record lr where lr.last_merge_time=cm.op_time and lr.original_cust_id=cm.original_cust_id
);

参考:http://postgres.cn/docs/12/queries-with.html

发表评论

评论(2)

  1. 叶开楗

    界面有点熟悉 用的 Navicat Premium ?

    1. 秩秩斯干

      @叶开楗 嗯 就是它