提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  前端

当前栏目

SQL递归实现循环判断

循环SQL递归 实现 判断
2023-06-13 09:17:52 时间

SQL递归实现循环判断

以前的文章Python小案例（五）循环判断进行分组介绍了如何使用python解决循环判断的问题。现在重新回顾一下这个问题背景：有一列按照某规则排序后的产品，想打包进行组合售卖。要求按顺序进行价格累积，当价格累积超过2000后，需要从下一个产品重新开始打包。

这个问题困扰了我很久，一直觉得应该可以用SQL写出来，但奈何每次尝试都失败了。直到最近看了一篇关于SQL递归查询的文章，躁动的DNA又动了～

SQL递归查询简介

首先，简单介绍下什么是SQL递归查询。

递归查询是通过CTE(表表达式)来实现，至少包含两个查询，第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点；第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

常见的示例如下：

with cte as 
    (
        select column1,column2 from tablename where conditions

        union all

        select column1,column2 from tablename inner join cte on conditions 
    )

通过自己调用自己，直到返回为空（或者指定终止条件）时才结束。

目前Hive和MySQL是不支持递归查询的，Hive直接报错FAILED: SemanticException Recursive cte opc detected (cycle: opc -> opc).，MySQL没有with语法。

不过Oracle和SQL Server是支持递归查询的，可以在一些在线网站上进行尝试。常见的如下：

在线SQL

支持类型

备注

SQL Fiddle[1]

MySQL、Oracle、PostgreSQL、SQLite、SQL Server

全!

db-fiddle[2]

MySQ、PostgreSQL、SQLite

支持MySQ8.0

db<>fiddle[3]

MySQL、MariaDB、Oracle、PostgreSQL、DB2、Firebird、SQLite、SQL Server

更全！且有高版本

SQL OnLine[4]

SQLite、MariaDB、PostgreSQL、SQL Server

颜值高

Oracle Live SQL[5]

Oracle

需要注册

简单的SQL递归案例

原理都是抽象或枯燥的，直接拿最常见的例子演示一遍。

案例一：SQL递归查询部门架构

co l

desc

sample

id

部门ID

1

pid

上级部门ID

0

name

部门名称

总部

如上，有一张存储部门架构的信息表department，目前不知道具体有多少层级关系，这时就可以通过SQL递归实现了。

在SQL Fiddle中选择oracle测试，主要是在线的SQL Server不支持中文。在测试中也有些坑，大部分都是语法坑（详见代码注释），还有一个坑就是网站通过Text to DDL建表，默认给字段加了引号，导致在查询的时候显示无效字段。这么坑都不修复，无语～

with cte (id,pid,name,depart_calss)as -- oracle要求cte子表必须有列名
    (
        select 
            id
            ,pid
            ,name -- 部门名称
            ,name as depart_calss -- 部门结构
        from 
            department
        where 
            pid = 0

        union all

        select 
            d.id
            ,d.pid
            ,d.name
            ,concat(concat(c.depart_calss,'->'),d.name) as depart_calss -- oracle的concat只支持两个参数
        from 
            cte c join department d on c.id=d.pid
        where
            d.pid <= 1221 -- 设定终止条件(部门层级太长，导致oracle报错result of string concatenation is too long)
)

select 
    id
    ,pid
    ,name
    ,depart_calss
from cte

image-20230224185521017

案例二：SQL递归实现斐波那契数列

这里用的SQL OnLine中的SQL Server，界面是真的清爽。

with fibonacci(st,n,next_n) as
    (
        select 
            1 as st -- 初始值
            ,0 as n
            ,1 as next_n

        union all
    
        select 
            st+1 as st -- 利用初始值生成自增id
            ,next_n as n -- 将上个next_n作为下一个的n存储
            ,n+next_n as next_n -- 实现n+上个next_n
        from 
            fibonacci 
        where st < 10
    )
select * from fibonacci

image-20230225161256619

利用SQL递归实现循环判断

从上面的案例我们知道，每次调用自己的时候做一些判断就能实现循环判断了。这个打包销售的案例最重要的是每次累计价格到2000时就需要从下一次重新累积，那是不是只要每次取出达到2000的组合，将剩余的放到下面的union all再进行累积判断就行了呢？于是我做了下面的尝试（cte部分代码）：

-- 此为尝试性代码，不可用

opd (o, p, cp) as
    (-- 取出初始的2000组合包
        select 
            o 
            ,p 
            ,cp
        from
            (
                select
                    o
                    ,p
                    ,cp
                    ,sum(case when cp<2000 then 0 else 1 end) over(order by o) as if_keep -- 判断是否首次到达2000
                from
                    opc
            )a 
        where 
            if_keep <= 1

        union all

        -- 通过剔除上面的初始2000组合包，然后重新累积并再次取2000组合包
        select
            o 
            ,p 
            ,cp
        from
            (
                select
                    o
                    ,p
                    ,cp
                    ,sum(case when cp<2000 then 0 else 1 end) over(order by o) as if_keep
                from
                    (
                        select
                            c.o
                            ,c.p
                            ,sum(c.p) over(order by c.o) as cp
                        from
                            opc c left join opd d on d.o=c.o
                        where
                            d.o is null
                    )a
            )a 
        where
            if_keep <= 1
    )

虽然思路说的通，但不幸的是，oracle的cte表里不支持嵌套（即复杂嵌套查询），SQL Server也不支持外连接（left）。这个思路走不通，只能换个方式了。

现在我们重新看一下案例二的斐波那契数列，这个实现过程是不是很像sum() over()，那是不是只要重新复现累积过程就可以进行循环判断了，最终实现的代码如下：

hive的sum() over()写习惯了，所以形成了思维定势。当你跳出来就发现，这个实现更为简洁，逻辑也更清晰

with op as  
    (
        select 0 as o,1000 as p
        union all
        select 1 as o,500 as p
        union all
        select 2 as o,600 as p
        union all
        select 3 as o,800 as p
        union all
        select 4 as o,1000 as p
        union all
        select 5 as o,700 as p
        union all
        select 6 as o,400 as p
        union all
        select 7 as o,300 as p
        union all
        select 8 as o,200 as p
        union all
        select 9 as o,500 as p
        union all
        select 10 as o,200 as p
        union all
        select 11 as o,100 as p
    ),
opc as
    (
        select 
            o
            ,p 
            ,p as cum -- 初始累积值
            ,0 as start -- 自增ID
            ,0 as class -- 初始组
        from
            op
        where
            o = 0 -- 选择首个数据

        union all

        select 
            op.o
            ,op.p
         -- SQL Server不支持if，所以改成了case when
            ,case when cum>=2000 then op.p else op.p+cum end as cum -- 累积过程达到2000则重新累积
            ,start+1 as start
            ,case when cum>=2000 then class+1 else class end as class -- 累积过程达到2000组别增1
        from
            op,opc
        where
            op.o = opc.start+1 -- 自增关联
    )

select * from opc

image-20230225163720892

总结

SQL递归查询属于高级语法，虽然目前hive还不支持，但保不齐以后会支持呢？现在大部分互联网企业很少用oracle和SQL Server，所以SQL递归查询就当是一次思维拓展吧，不折腾下你怎么知道自己的上限呢～

参考资料

[1]

SQL Fiddle: http://sqlfiddle.com/

[2]

db-fiddle: https://www.db-fiddle.com/

[3]

db<>fiddle: https://dbfiddle.uk/

[4]

SQL OnLine: https://sqliteonline.com/

[5]

Oracle Live SQL: https://livesql.oracle.com/

相关文章