Sql编程--有点烧脑的，看看你能做到第几题

巧妙运用null

1. Null的含义

例如，以下sql查不到第三条数据：

with t as(

select 1  cid from  sysibm.dual union all

select 2 from  sysibm.dual union all

select null from  sysibm.dual

)

select * from t where cid!=1

1. Not In中存在null值会导致查询语句失效：

例如：

with t as(

select 1  cid from  sysibm.dual union all

select 2 from  sysibm.dual union all

select null from  sysibm.dual

),

t1 as(

select 'a' name ,1 id from sysibm.dual union all

select 'b',2 from sysibm.dual union all

select 'c',6 from sysibm.dual

)

select * from t1 where id not in(select cid from t)

1. 利用Count(null)不计数的特性

with t as(

select 1  cid from  dual union all

select 2 from  dual union all

select null from  dual

)

--select count(1) from t where cid is not null --等价于

select count(cid) from t

但是在where条件上直接加过滤，有不适用的条件，例如我要同时计算总条数和CID非空的条数，在同时处理多指标加工时，需要扫描一次表计算出所有的指标，语句二显得非常有用，例如

1. Null导致索引失效

根据实际情况分析执行计划，索引不存储NULL值。

巧妙运用with

with子句大部分情况下等同于子查询，优点是with结果集可以复用，另外牢记with的一个特点，很多时候with可以改变整个大sql的执行计划，所以有时候可以尝试用with改变执行计划来优化sql。

巧妙运用自连接

自连接有时候能帮助我们完成很多有趣的业务场景，比如说，查看济南公交车所有站点的22组合，查看济南市内公交转一次车能从一个站点达到另外一个站点。

有如下表：

站点表：

公交车途经站点表：

要求查询所有站点22组合：

转一次车可以实现的站点到达：

答案：

with station(id,name) as(--站点表

select 1 ,'济南东' from sysibm.dual union all

select 2 ,'济南西' from sysibm.dual union all

select 3 ,'济南南' from sysibm.dual union all

select 4 ,'济南北' from sysibm.dual union all

select 5 ,'济南中' from sysibm.dual

),

bus_station_ref(bus_num,station_id) as(--公交车站点表

select 115 ,1 from sysibm.dual union all

select 115 ,2 from sysibm.dual union all

select 116 ,1 from sysibm.dual union all

select 116 ,4 from sysibm.dual union all

select 119 ,2 from sysibm.dual union all

select 119 ,3 from sysibm.dual

),

all_station_comps(id1,name1,id2,name2) as(--所有站点组合，自连接实现

select * from station t1,station t2 where t1.id!=t2.id --and t1.id<t2.id

),

zc_lines(bus_num1 ,id1,name1, bus_num2,id2, name2) as (--转车线路组合 自连接实现

select bef.bus_num ,bef.station_id,s1.name, r2.bus_num ,s2.id, s2.name

from bus_station_ref bef,bus_station_ref r1,bus_station_ref r2,bus_station_ref aft,station s1,station s2  

where 

r1.bus_num!=r2.bus_num AND r1.station_id=r2.station_id--转车条件

AND r1.station_id!=bef.station_id AND r2.station_id!=aft.station_id--排除转车那一站两车衔接的组合

AND bef.bus_num=r1.bus_num

AND aft.bus_num=r2.bus_num

AND bef.station_id=s1.id

AND aft.station_id=s2.id

)

SELECT * FROM all_station_comps

--SELECT * FROM zc_lines

巧妙运用笛卡尔

以上公交车场景，也巧妙的运用了笛卡尔。

经典行转列

原理：通常使用聚合函数+分组，结合decode函数或者case表达式等等。

例如：有客户经理存款/贷款额度如下：

转换为如下结果：

答案：

with t as(

select '001' mgr,'ck' tp ,10000000 bal from sysibm.dual

UNION ALL

select '001' ,'dk'  ,30000000  from sysibm.dual

UNION ALL

select '001' ,'dk'  ,30000000  from sysibm.dual

UNION ALL

select '002' ,'ck'  ,60000000  from sysibm.dual

UNION ALL

select '002' ,'dk'  ,60000000  from sysibm.dual

UNION ALL

select '002' ,'dk'  ,50000000  from sysibm.dual

)

SELECT mgr,

sum(DECODE(tp,'ck',bal,0)) ckye,

sum(DECODE(tp,'dk',bal,0)) dkye

FROM t

GROUP BY mgr

列转不定行

原理：递归，剥洋葱

有如下表格：

将C2列根据逗号拆分成多行，转成如下结果：

答案：

with t1 as

(

select 3 c1,'eee,fff,ggg,yyy,jjj,lll' c2 from sysibm.dual union all

select 1 c1,'aaa,bbb' c2 from sysibm.dual union all

select 2 c1,'ppp' c2 from  sysibm.dual

),

t2(c1,c2,f) as(

select c1,c2,instr(c2,',') f from t1

union all

select c1,substr(c2,1,instr(c2,',')-1) c2,0 f from t2 where f>0

union all

select c1,substr(c2,instr(c2,',')+1) c2,instr(substr(c2,instr(c2,',')+1) ,',') f from t2 where f>0

)

select * from t2 where f=0 order by c1

行转不定列

因为列不定，因此sql的select表达式部分不确定，是sql定义部分，只能用plsql来写。

Sql递归

运算层级不定的，大多会用到递归。

举例：凭证项目一sql，需求如下：

场景描述：

凭证项目中有这么一个需求，查询机构层级关系，表里存着当前机构与父级机构，要实现机构与本机构、机构与父级机构、机构与爷爷级机构、……、机构与顶级机构，父级机构与父级机构、父级机构与爷爷级机构、……、父级机构与顶级机构，依次组合，直到顶级（注意，机构层级不确定）。

例如，数据如下（C1表示当前级机构，C2表示当前级的直接父级机构）：

从机构层级上看是这样：

a—b—c—d

a—b—e—f

想得到如下结果集：dd dc db da cc cb ca bb ba   ff fe fb fa ee eb ea

思路：往外需要结合算法推导

答案：

WITH t as(

SELECT 'd' org,'c' parentorg FROM SYSIBM.dual      --dc

UNION ALL

SELECT 'c' org,'b' parentorg FROM SYSIBM.dual      --cb

UNION ALL

SELECT 'b' org,'a' parentorg FROM SYSIBM.dual      --ba

UNION ALL

SELECT 'f' org,'e' parentorg FROM SYSIBM.dual      --fe

UNION ALL

SELECT 'e' org,'b' parentorg FROM SYSIBM.dual      --eb

),

t2(org,parentorg) as(

SELECT org,parentorg FROM t

UNION ALL

SELECT a.org,aa.parentorg FROM t a,t2 aa WHERE a.parentorg=aa.org

)

SELECT * FROM t2

答案怎么来的？？？

第0层：

t表:

dc

cb

ba

fe

Eb

t2表无数据

第1层：

T表:

dc

cb

ba

fe

eb

t2表:

db

ca

ea

fb

第2层：

T表:

dc

cb

ba

fe

eb

第一层结果

db

ca

ea

fb

本次新增

da

fa

第3层：

t:

dc

cb

ba

fe

eb

第一层结果

db

ca

ea

fb

第二层结果

da

Fa

本次结果无数据，

循环退出。

Sql中使用算法

这个例子太多啦，主要考验灵活性，简单举几个吧！

举例1：

有如下表：

规则：按照acctno分组，按照seq排序，遇到diffdays>31，则重新排序

思路：重新规划分组字段

结果如下：

测试库截图：

举例2：

数据加工平台实际业务需求，很多个 是否** 的标记，例如：

是否吃香菜 是否加葱花  是否加糖 是否加盐 是否加酱油 是否放辣椒 等等等等，而且这些条件可以任意组合，后面查询根据各种条件组合来查的情况非常多。

使用硬编码配置和数据组合，类似于linux权限 1 2 4分别表示可执行、可写、可读。

要学会做框架

前段时间，在项目上有这样一个场景，计算某段时间的日均余额，这个时间段有本月、本季、本年。。。

逻辑实现就是累计这个时间段内每一天的余额，除以这个时间段内的天数。

其中源表结构是这样的（已去掉其他业务逻辑，只抽取算累计值相关代码）：

create table ll_account_bal(--余额拉链表，记录这个时间段的余额，区间段是左闭右开

    account_no varchar(18),--账号

    begin_date date,--余额开始日期

    end_date date,--余额结束日期

    bal decimal(18,2));--余额

累计的时间段假如是本年，目标表如下：

create table cumu_bal(

    account_no varchar(18),--账号

    y_cumu_bal decimal(18,2));--本年余额累计值

y_cumu_bal需要按天累计从本年1月1日的余额，到当前日期的余额。

那么问题来了，刚上线的时候，这部分数据怎么初始化呢，数据可是很庞大，时间跨度也很大啊。

本场景中，检索一次余额拉链表时间复杂度为O(1)，决定执行效率的有这几个因素：

表中数据量 r：

决定O(1)的成本，我们可以通过增加where条件尽量缩小捞库的范围，优化sql提高执行效率等方式，提高效率，但是无法降低一个度

指标数量N (实际开发中有 本月/本季/本年 的 种类1余额/种类2余额/...... 本例中已简化只用了本年累计余额， 这些个指标根据乘法一交叉，需要计算的指标数量是N)：

如果对每个指标检索一次余额拉链表，那时间复杂度就是O(N),我们尽量把所有指标放到一个逻辑中去时间，将时间复杂度降为O(1)

累计天数 d

如果分批量每天往上累一次，那时间负责度就是O(d)，我们尝试是否能把这个因素抹去，将时间复杂度降为O(1)

如下几种方案：

1. 按照每个指标每天往上累计，时间复杂度O(ND)

2. 在一个逻辑中计算指标，并按天累计，也就是目前传统的处理方式，时间复杂度O(d)

3. 将天数也封装到逻辑中，搭建一个算法的框架，推导出一套公式，套用公式计算，时间复杂度O(1)，这也是本帖下面要说的内容。

若要实现方案3，必须在检索的时候计算每行的累计值，最后通过sum group by来实现对每个阶段结果的累计。

步骤一：计算每行累计的阶段结果

对于表中任意一行数据，我们引入4个变量：

期初日期i_date，如本年是2021年1月1日

本行开始日期b_date

本行结束日期e_date

当天日期t_date

对于每行数据，有效累计余额为，本行开始日期到本行结束日期，与当期日期即本年求交集，乘本行余额，因此对于每行数据，我们有：

r_cumu_bal=[b_date,e_date)∩[i_date,t_date]*bal  (r_cumu_bal表示行累计值的阶段结果值)

对于以上四个变量，我们有如下永恒规则：

e_date>b_date 

t_date>=i_date

t_date>=b_date

以上命题等价于：

r_cumu_bal={min(e_date-1,t_date)-[max(b_date,i_date)-1]}*bal

带入边界条件验证公式，如图所示

彩色线为表中每行数据可能得时间段

1. 对于整个逻辑i_date和t_date是定值，i_date永远不会变，t_date由初始化日期确定，因此边界条件为：

t_date=i_date

2. 对于每行数据，边界条件有：

i_date=e_date

i_date=b_date

e_date=t_date

b_date=t_date

带入公式验证后，公式均试用。

但有一种特殊情况，实际开发的时候，我们有本年累计，本月累计等N个指标，因为要一次检索计算这N个指标，所以捞库的where条件应以最大时间段的过滤，但是在计算本月累计时，可能存在如图灰色的线段条情况，与当期并无交集，比如初始化时间为2021年2月27日，表中存在2021年1月1日-2021年1月10日的数据，计算本月累计时显然应该算成0。但是带入公式后，计算的累计值为负数，因此需要对以上公式进行调整：

r_cumu_bal={min(e_date-1,t_date)-[max(b_date,i_date)-1]}*bal>0？{min(e_date-1,t_date)-[max(b_date,i_date)-1]}*bal:0

将公式转化为sql为：

select

account_no,

decode(sign(min(days(end_date)-1,days(t_date))-(days(max(begin_date,i_date))-1)),-1,0,min(days(end_date)-1,days(t_date))-(days(max(begin_date,i_date))-1)  )*bal  r_cumu_bal

from ll_account_bal

假如表中数据为：

假如初始化日期为2021-02-27，那执行的sql语句为：

select

account_no,

decode(sign(min(days(end_date)-1,days('2021-02-27'))-(days(max(begin_date,'2021-01-01'))-1)),-1,0,min(days(end_date)-1,days('2021-02-27'))-(days(max(begin_date,'2021-01-01'))-1)  )*bal  r_cumu_bal

from ll_account_bal

结果为：

验证正确，实际开发中要造几条数据验证边界条件，做好充分验证确保正确性。

步骤二：使用sum group by 计算累计值

修改sql如下：

select

account_no,

sum(decode(sign(min(days(end_date)-1,days('2021-02-27'))-(days(max(begin_date,'2021-01-01'))-1)),-1,0,min(days(end_date)-1,days('2021-02-27'))-(days(max(begin_date,'2021-01-01'))-1)  )*bal ) r_cumu_bal

from ll_account_bal

group by account_no

结果为：

验证成功，ok，这样上线后发现自己某天累计错了，业务想改规则，也不怕了，有框架套公式重新一执行就ok了。