分享三种学习过的间断和孤岛问题的解决方案

开着拖拉机泡妞 2012-03-25 05:17:57
加精 


--缺失范围和现有范围（也称间断和孤岛问题）

--1、缺失范围（间断）

/*

收集人：TravyLee

时间：2012-03-25

如有引用，请标明“此内容源自MSSQL2008技术内幕之T-SQL”

*/

/*

求解间断问题有几种方法，小弟我选择性能较高的三种（使用游标的方法省略

有兴趣不全的大哥大姐请回复）

---------------------------------------------------------------------

间断问题的解决方案1；使用子查询

step 1：找到间断之前的值，每个值增加一个间隔

step 2：对于没一个间断的起点，找出序列中现有得值，再减去一个间隔

本人理解为：找到原数据表中的值加一减一是否存在，若有不妥，望纠正

生成测试数据:

go

if object_id('tbl')is not null 

drop table tbl

go

create table tbl(

id int not null

)

go

insert tbl

values(2),(3),(11),(12),(13),(27),(33),(34),(35),(42)

要求：找到上表数据中的不存在的id的范围，

--实现输出结果：

/*

开始范围 结束范围

 4        10

 14       26

 28       32

 36       41

 */

 按照每个步骤实现：

 step 1：找到间断之前的值，每个值增加一个间隔

 我们可以清楚的发现，要找的间断范围的起始值实际上就是我们

 现有数据中的某些值加1后存不存在现有数据表中的问题，通过

 子查询实现：

 

 select id+1 as start_range from tbl as a

 where not exists(select 1 from tbl as b

 where b.id=a.id+1)and id<(select max(id) from tbl)

 --此查询语句实现以下输出：

 /*

 start_range

 4

 14

 28

 36

 */

 step 2：对于没一个间断的起点，找出序列中现有得值，再减去一个间隔

 

 select id+1 as start_range,(select min(b.id) from tbl as b

 where b.id>a.id)-1 as end_range

 from tbl a where not exists(select 1 from tbl as b

                        where b.id=a.id+1)

     and id<(select max(id) from tbl)

 --输出结果：

 /*

   start_range	end_range

   4	10

   14	26

   28	32

   36	41

 */

通过以上的相关子查询我们实现了找到原数据表中的间断范围。

而且这种方式的效率较其他方式有绝对的优势





间断问题的解决方案2；使用子查询（主意观察同1的区别）

step 1:对每个现有的值匹配下一个现有的值，生成一对一对的当

       前值和下一个值

step 2:只保留下一个值减当前值大于1的间隔值对

step 3:对剩下的值对，将每个当前值增加1个间隔，将每个下一

       个值减去一个间隔



--转换成T-SQL语句实现：

--step 1:

select id as cur,(select min(b.id) from tbl b where

         b.id>a.id) as nxt from tbl a

--此子查询生成的结果：

/*

 cur	nxt

 2	 3

 3	 11

 11	 12

 12	 13

 13	 27

 27	 33

 33	 34

 34	 35

 35	 42

 42	 NULL

 */

 step 2 and step 3:

 select cur+1 as start_range,nxt-1 as end_range

 from (select id as cur,(select min(b.id) from tbl b 

 where b.id>a.id) as nxt from tbl a ) as d

      where nxt-cur>1

--生成结果：

/*

 start_range	 end_range

 4	 10

 14	 26

 28	 32

 36	 41

*/

 间断问题的解决方案3；使用排名函数实现

 

 此种方法与第二种类似,这里我一步实现：

 

 ;with c as

 (

   select id,row_number()over(order by id) as rownum

   from tbl

 )

 select cur.id+1 as strat_range,nxt.id-1 as end_range

        from c as cur join c as nxt

   on nxt.rownum=cur.rownum+1

  where nxt.id-cur.id>1



--输出结果：

 /*

 strat_range	end_range

 4	 10

 14	 26

 28	 32

 36	 41

 */

 

*/

--2、现有范围（孤岛）

/*

以上测试数据，试下如下输出：

/*

开始范围 结束范围

2        3

11       13

27       27

33       35

42       42

*/

和间断问题一样，孤岛问题也有集中解决方案，这里也只介绍三种

省略了用游标的实现方案：



孤岛问题解决方案1：使用子查询和排名计算

step 1:找出间断之后的点，为他们分配行号（这是孤岛的起点）

step 2:找出间断之前的点，为他们分配行号（这是孤岛的终点）

step 3:以行号相等作为条件，匹配孤岛的起点和终点



--实现代码:

    with startpoints as

    (

      select id,row_number()over(order by id) as rownum

           from tbl as a where not exists(

        select 1 from tbl as b where b.id=a.id-1) 

     /*

     此查询语句单独运行的结果：

     id	rownum

     2	1

     11	2

     27	3

     33	4

     42	5

     */

    ),

    endpoinds as

    (

      select id,row_number()over(order by id) as rownum

          from tbl as a where not exists(

        select 1 from tbl as b where b.id=a.id+1)

   /*

     此查询语句单独运行的结果：

     id	rownum

     3	1

     13	2

     27	3

     35	4

     42	5

    */

    )

    select s.id as start_range,e.id as end_range

    from startpoints as s

    inner join endpoinds as e

    on e.rownum=s.rownum

--运行结果:   

/*

 start_range	end_range

 2	3

 11	13

 27	27

 33	35

 42	42

*/



孤岛问题解决方案2：使用基于子查询的组标识符



--直接给出代码：



with d as

(

  select id,(select min(b.id) from tbl b where b.id>=a.id

      and not exists (select * from tbl c where c.id=b.id+1)) as grp

  from tbl a

)

select min(id) as start_range,max(id) as end_range

from d group by grp

/*

start_range	end_range

2	3

11	13

27	27

33	35

42	42

*/





孤岛问题解决方案3：使用基于子查询的组标识符:



step 1:按照id顺序计算行号:

   select id ,row_number()over(order by id) as rownum from tbl

/*

id	rownum

2	1

3	2

11	3

12	4

13	5

27	6

33	7

34	8

35	9

42	10

*/

step 2：生成id和行号的差:

   select id,id-row_number()over(order by id) as diff from tbl

/*

id	diff

2	1

3	1

11	8

12	8

13	8

27	21

33	26

34	26

35	26

42	32

*/

这里解释一下这样做的原因；

   因为在孤岛范围内，这两个序列都以相同的时间间隔来保持增长，所以

   这时他们的差值保持不变。只要遇到一个新的孤岛，他们之间的差值就

   会增加。这样做的目的为何，第三步将为你说明。

step 3:分别取出第二个查询中生成的相同的diff的值的最大id和最小id

    with t as(

      select id,id-row_number()over(order by id) as diff from tbl

    )

    select min(id) as start_range,max(id) as end_range from t

       group by diff

/*

start_range	end_range

2	3

11	13

27	27

33	35

42	42

*/



求孤岛问题，低三种方法效率较前两种较高，具有比较强的技巧性

希望在实际运用中采纳。

*/
...全文
2676 32 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
32 条回复
切换为时间正序
请发表友善的回复…
发表回复
管理员 2012-05-04
  • 打赏
  • 举报
 回复
补充下有重复日期情况下，我的方法，呵呵


CREATE TABLE A

(

	ID INT NOT NULL,

	Name VARCHAR(100) NOT NULL

)



GO



INSERT INTO A 

SELECT 1,'haha' UNION

SELECT 2,'hoho' UNION

SELECT 3,'hehe' UNION

SELECT 4,'hihi'





CREATE TABLE B

(

	ID INT NOT NULL,

	ShoppingDate VARCHAR(100)

)



GO



INSERT INTO B

SELECT 1,'2012-04-01 14:00:00' UNION

SELECT 1,'2012-04-01 13:00:00' UNION

SELECT 1,'2012-04-03 12:00:00' UNION

SELECT 1,'2012-04-04 12:00:00' UNION

SELECT 1,'2012-04-05 12:00:00' UNION

SELECT 1,'2012-04-06 12:00:00' UNION

SELECT 1,'2012-04-07 12:00:00' UNION

SELECT 1,'2012-04-08 12:00:00' UNION

SELECT 1,'2012-04-10 12:00:00' UNION

SELECT 1,'2012-04-11 12:00:00' UNION

SELECT 1,'2012-04-13 12:00:00' UNION

SELECT 1,'2012-04-14 12:00:00' UNION

SELECT 1,'2012-04-15 12:00:00' UNION

SELECT 1,'2012-04-16 12:00:00' UNION

SELECT 1,'2012-04-17 12:00:00' UNION

SELECT 1,'2012-04-18 12:00:00' UNION

SELECT 1,'2012-04-19 12:00:00' UNION

SELECT 1,'2012-04-20 12:00:00' UNION

SELECT 1,'2012-04-21 12:00:00' UNION

SELECT 1,'2012-04-22 12:00:00' UNION

SELECT 1,'2012-04-23 12:00:00' UNION

SELECT 1,'2012-04-24 12:00:00' UNION

SELECT 1,'2012-04-25 12:00:00' UNION

SELECT 1,'2012-04-26 12:00:00' UNION

SELECT 1,'2012-04-27 12:00:00' UNION

SELECT 3,'2012-04-01 12:00:00' UNION

SELECT 3,'2012-04-02 12:00:00' UNION

SELECT 3,'2012-04-03 12:00:00' UNION

SELECT 3,'2012-04-04 12:00:00' UNION

SELECT 3,'2012-04-05 12:00:00' UNION

SELECT 3,'2012-04-06 12:00:00' UNION

SELECT 3,'2012-04-07 12:00:00' UNION

SELECT 3,'2012-04-08 12:00:00' UNION

SELECT 2,'2012-04-10 12:00:00' UNION

SELECT 2,'2012-04-11 12:00:00' UNION

SELECT 4,'2012-04-13 12:00:00' UNION

SELECT 4,'2012-04-14 12:00:00' UNION

SELECT 4,'2012-04-15 12:00:00' UNION

SELECT 4,'2012-04-16 12:00:00' UNION

SELECT 4,'2012-04-17 12:00:00' UNION

SELECT 4,'2012-04-18 12:00:00' UNION

SELECT 4,'2012-04-19 12:00:00' UNION

SELECT 4,'2012-04-20 12:00:00' UNION

SELECT 4,'2012-04-21 12:00:00' UNION

SELECT 4,'2012-04-22 12:00:00' UNION

SELECT 4,'2012-04-23 12:00:00' UNION

SELECT 4,'2012-04-24 12:00:00' UNION

SELECT 4,'2012-04-25 12:00:00' UNION

SELECT 4,'2012-04-26 12:00:00' UNION

SELECT 4,'2012-04-27 12:00:00' 



SELECT C.ID,A.Name,MIN(shoppingdate),MAX(shoppingdate),COUNT(C.ID)

FROM (SELECT ID,shoppingdate,rn=ROW_NUMBER()OVER (ORDER BY Id,shoppingdate) FROM (SELECT DISTINCT ID,CONVERT(VARCHAR(10),shoppingdate,23) AS shoppingdate FROM B) AS D) AS C,A

WHERE C.ID = A.ID

GROUP BY C.ID,A.Name,DATEADD(DAY,0-rn,shoppingdate)

ORDER BY C.ID


这是magician547写的，我转发
潇洒王子 2012-04-18
  • 打赏
  • 举报
 回复
楼主写的很好，继续努力
zero_ww 2012-04-16
  • 打赏
  • 举报
 回复
学习了^_^
一二三五六 2012-04-14
  • 打赏
  • 举报
 回复
看得云里来 雾里去
kyow2 2012-03-31
  • 打赏
  • 举报
 回复
学习了！谢谢分享！！！
Barton 2012-03-31
  • 打赏
  • 举报
 回复
up。很好啊，学习了
fly4free 2012-03-31
  • 打赏
  • 举报
 回复
虽然目前看不懂，但还是mark一下，
D_B_H 2012-03-31
  • 打赏
  • 举报
 回复
谢谢楼主,希望多多发这样的帖子
wyfer_jo 2012-03-30
  • 打赏
  • 举报
 回复
学习学习下载。。。
流星陨落 2012-03-29
  • 打赏
  • 举报
 回复
谢谢楼主分享
kongfanneng 2012-03-28
  • 打赏
  • 举报
 回复
谢谢分享
gaobinbinbin 2012-03-28
  • 打赏
  • 举报
 回复
学习，围观。。。看的我头晕。。。
holicc 2012-03-27
  • 打赏
  • 举报
 回复
学习了，谢谢楼主分享
bkzxp 2012-03-27
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 liangck 的回复:]
那个什么数学归纳法呢？
[/Quote]

我也不清楚呢
开着拖拉机泡妞 2012-03-26
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 liangck 的回复:]

那个什么数学归纳法呢？
[/Quote]

撤了，还是不要归纳的好
liangCK 2012-03-26
  • 打赏
  • 举报
 回复
那个什么数学归纳法呢？
水族杰纶 2012-03-26
  • 打赏
  • 举报
 回复
zhouixi 2012-03-26
  • 打赏
  • 举报
 回复


我来看看，
qcx680608qcx 2012-03-26
  • 打赏
  • 举报
 回复
好好好好1111
寡亾 2012-03-26
  • 打赏
  • 举报
 回复

我想说，其实我都没看懂。在顶一下，押宝要赢啊，本来就是裤衩哥，输不起啊。
加载更多回复（12）
施耐德电信基站和IT网络的整体解决方案.pdf
施耐德电信基站和IT网络的整体解决方案pdf,施耐德电信基站和IT网络的整体解决方案
【经典】大数据智慧税务解决方案.pdf
【经典】大数据智慧税务解决方案.pdf
点击科技石油化工行业解决方案
通过点击科技在石油化工行业解决方案，您可获得专门针对石油化工行业设计的各类管理工具和业务流程，以协助您更有效地经营石油和天然气业务，最终解决共享能力资源、流程配置、流程管理、信息传递以及利益与成本分摊都是跨组织流程核心的问题。
Veritas 热点解决方案
客户痛点： ➢ 严苛的RPO&RTO要求 ➢ 复杂IT架构及庞大的数据量 ➢ 安全、稳定、可定制的技术与架构 Veritas解决方案： ➢ NetBackup ➢ NetBackup Appliance 客户收益： ➢ 满足企业自身RPO&RTO要求，同时符 合行业规范 ➢ 确保企业在混合云架构下的数据&业务 安全，助力数字化转型 ➢ 打造属于企业自己的统一备份管理平台， 降低维护成本和管理成本 客户画像： ➢ 全行业客户 （包括不限于使用以下产品和技术客户：异 构系统、大数据平台、开源系统、混合云架 构、K8s以及非标准自研业务系统平台）
基于BIM智慧建筑监控平台解决方案.pdf
基于BIM智慧建筑监控平台解决方案.pdf
疑难问题

22,301

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章