一招教你玩转SQL：通过找出数据的共同属性实现SQL需求

作者：云和恩墨交付工程师，盛杰。

DBA除了日常的运维工作之外，可能还需要对开发人员提出的SQL需求进行建议和实现。最近有遇到几个比较有意思的SQL，在解决的过程中发现，这些SQL的处理思路都是通过找出数据的共同属性来解决。

以下通过两个SQL案例，演示找出数据共同属性的思路。

问题一：

根据上方表格，第一列、第二列为原始数据，通过SQL实现算出第三列的值，即第二列相同数量连续出现的次数。

找出共性：

上面问题就是一个找出相同属性的问题，我们可以发现数量连续相同的记录存在一个共性：数量连续相同的任何一条记录 小于它的日期 并且 不等于它的数量 的最大日期 是一样的。

例如：2018/1/4和2018/1/5数量都为22，小于它们日期并且数量不等于22的记录都是2018/1/3这一条。

通过将以上共性转成语句：

select max(dt) from tb_cnt b where b.dt < 某条记录的日期 and b.qt != 某条记录的数量

将以上做为分区值，即可实现问题需求。

以下实现：

创建测试表：

create table tb_cnt as select to_date('2018/1/1','yyyy/mm/dd') dt, 34 qt from dual union all select to_date('2018/1/2','yyyy/mm/dd'), 434 from dual union all select to_date('2018/1/3','yyyy/mm/dd'), 34 from dual union all select to_date('2018/1/4','yyyy/mm/dd'), 22 from dual union all select to_date('2018/1/5','yyyy/mm/dd'), 22 from dual union all select to_date('2018/1/6','yyyy/mm/dd'), 22 from dual union all select to_date('2018/1/7','yyyy/mm/dd'), 3 from dual union all select to_date('2018/1/8','yyyy/mm/dd'), 45 from dual union all select to_date('2018/1/9','yyyy/mm/dd'), 56 from dual union all select to_date('2018/1/10','yyyy/mm/dd'), 54 from dual union all select to_date('2018/1/11','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/12','yyyy/mm/dd'), 4 from dual union all select to_date('2018/1/13','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/14','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/15','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/16','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/17','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/18','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/19','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/20','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/21','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/22','yyyy/mm/dd'), 1 from dual union all select to_date('2018/1/23','yyyy/mm/dd'), 66 from dual union all select to_date('2018/1/24','yyyy/mm/dd'), 9 from dual union all select to_date('2018/1/25','yyyy/mm/dd'), 9 from dual union all select to_date('2018/1/26','yyyy/mm/dd'), 9 from dual union all select to_date('2018/1/27','yyyy/mm/dd'), 9 from dual union all select to_date('2018/1/28','yyyy/mm/dd'), 9 from dual union all select to_date('2018/1/29','yyyy/mm/dd'), 6 from dual union all select to_date('2018/1/30','yyyy/mm/dd'), 67 from dual union all select to_date('2018/1/31','yyyy/mm/dd'), 623 from dual;

实现SQL:

select dt 日期, qt 数量, row_number() over(partition by dt_pre order by dt) 计数

from (select dt, qt, (select max(dt) from tb_cnt b where b.dt < a.dt and b.qt != a.qt) dt_pre from tb_cnt a) order by dt

查询结果：

上面这个问题比较容易出看记录的共同属性，下面来一条相对不容易看出来的。

问题二：

表中有数据q, qa, qq, qaq, qaaaaaaaaaa, aq, aqa, aqq, aaq。

用户通过前台界面，在搜索框输入一个q，则显示结果顺序为：

(1)q

(2)qa

(3)qq

(4)qaq

(5)qaaaaaaaaaa

(6)aq

(7)aqa

(8)aqq

(9)aaq

说明：输出顺序根据数字的从小到大，从先到后：

1.全文匹配。[(1)排在最前] 2.结果与输入内容从前向后开始比对，开始相同字符位越靠前，越排在前面。[(2)(3)(4)(5)在(6)(7)(8)前,(6)(7)(8)在(9)前] 3.看字符相同位后面的字符位数，字符位数少的排前面。[(2)(3)在(4)前，(4)在(5)前，同理(6)在(7)前] 4.字符位数相同时，看字符对应ASCII码，小的排前面。[(2)在(3)前，同理(7)在(8)前]

找出共性：

这是一个排序问题，我们通过以上排序逻辑的描述要求，找出以下共性：

匹配到的字符的左边可能有字符; 匹配到的字符的右边可能有字符; 匹配到字符的左边或右边的字符个数越少越好; 匹配到字符的左边或右边的字符个数相同对比字符大小。 进而产生以下排序条件： 1. 匹配到的字符左边字符的个数 2. 匹配到的字符左边的字符 3. 匹配到的字符右边字符的个数 4. 匹配到的字符右边的字符

通过将以上共性转成语句：

1. nvl(length(substr(x, 0, instr(x, 'q') - 1)), 0) 2. substr(x, 0, instr(x, 'q') - 1) 3. nvl(length(substr(x, instr(x, 'q') + 1, length(x))), 0) 4. substr(x, instr(x, 'q') + 1, length(x))

将以上做为排序条件，即可实现问题需求。

以下实现：

创建测试表：

create table tb_order as select 'q' x from dual union all select 'qa' from dual union all select 'qq' from dual union all select 'qaq' from dual union all select 'qaaaaaaaaaa' from dual union all select 'aq' from dual union all select 'aqa' from dual union all select 'aqq' from dual union all select 'abq' from dual union all select 'acq' from dual union all select 'acqb' from dual;

实现SQL:

select x from tb_order where x like '%q%' order by nvl(length(substr(x, 0, instr(x, 'q') - 1)), 0), substr(x, 0, instr(x, 'q') - 1), nvl(length(substr(x, instr(x, 'q') + 1, length(x))), 0), substr(x, instr(x, 'q') + 1, length(x));

查询结果：

资源下载

关注公众号：数据和云（OraNews）回复关键字获取

2018DTCC , 数据库大会PPT

2018DTC，2018 DTC 大会 PPT

DBALIFE ，“DBA 的一天”海报

DBA04 ，DBA 手记4 电子书

122ARCH ，Oracle 12.2体系结构图

2018OOW ，Oracle OpenWorld 资料

PRELECTION ，大讲堂讲师课程资料