现在有这样的一个问题
有这样的一个表hist 表里有三个字段 一个是id (就是人的id) 一个是effdat (登记开始的时间) 一个是expdat(登记结束时间) 一个人可能有多次登记 ,而且登记的时间可能是包含的关系。也可能是不连续的。现在就是判断都有那些人在一个特定的时间段内登记是连续的。比如说从‘2005-01-01’到‘2006-01-01’ 下面是一段查询的记录 select * from hist1 2005-6-14 2005-8-31 -32169
2 2005-7-1 2005-8-31 -32169
3 2005-9-1 2005-12-31 -32169
4 2006-1-1 2006-3-31 -32169
5 2006-1-1 2006-8-31 -32169
6 2006-9-1 2006-9-30 -32169
7 2006-10-1 2006-12-31 -32169
8 2005-6-21 2005-6-30 -32203
9 2005-7-1 2005-12-31 -32203
10 2005-7-1 2006-3-31 -32203
11 2005-7-1 2006-6-30 -32203
12 2006-7-1 2006-12-31 -32203
13 2005-7-3 2005-12-31 -32318
14 2005-7-3 2006-2-28 -32318
15 2006-3-1 2006-3-31 -32318
16 2006-3-1 2006-12-31 -32318
17 2005-7-7 2005-12-31 -32396
18 2005-7-7 2006-3-31 -32396
19 2005-7-7 2006-6-30 -32396
20 2006-7-1 2006-11-15 -32396
21 2005-7-13 2005-12-31 -32501
22 2006-1-1 2006-3-31 -32501
23 2006-1-1 2006-12-31 -32501 大家看看这个查询的结果 我解释一下 第一个1 那是行号 后面的2005-6-14 是登记的开始时间,2005-8-31是登记的结束时间,意思就是他在2005-6-14 到 2005-8-31 是连续登记的。后面的-32169 是人的id。但是第二条记录的开始时间和登记的结束时间分别是 2005-7-1 2005-8-31 这个包含在第一个时间里面,说明他在2005-6-14 2005-8-31是连续的。
现在我就想求各位帮我写一个方法。无论是通过什么方法。可以是程序(限pb程序)能够求出这个表内在某个时间段内哪些人是连续登记的……
求求各位了……………………首先谢谢各位!!
有这样的一个表hist 表里有三个字段 一个是id (就是人的id) 一个是effdat (登记开始的时间) 一个是expdat(登记结束时间) 一个人可能有多次登记 ,而且登记的时间可能是包含的关系。也可能是不连续的。现在就是判断都有那些人在一个特定的时间段内登记是连续的。比如说从‘2005-01-01’到‘2006-01-01’ 下面是一段查询的记录 select * from hist1 2005-6-14 2005-8-31 -32169
2 2005-7-1 2005-8-31 -32169
3 2005-9-1 2005-12-31 -32169
4 2006-1-1 2006-3-31 -32169
5 2006-1-1 2006-8-31 -32169
6 2006-9-1 2006-9-30 -32169
7 2006-10-1 2006-12-31 -32169
8 2005-6-21 2005-6-30 -32203
9 2005-7-1 2005-12-31 -32203
10 2005-7-1 2006-3-31 -32203
11 2005-7-1 2006-6-30 -32203
12 2006-7-1 2006-12-31 -32203
13 2005-7-3 2005-12-31 -32318
14 2005-7-3 2006-2-28 -32318
15 2006-3-1 2006-3-31 -32318
16 2006-3-1 2006-12-31 -32318
17 2005-7-7 2005-12-31 -32396
18 2005-7-7 2006-3-31 -32396
19 2005-7-7 2006-6-30 -32396
20 2006-7-1 2006-11-15 -32396
21 2005-7-13 2005-12-31 -32501
22 2006-1-1 2006-3-31 -32501
23 2006-1-1 2006-12-31 -32501 大家看看这个查询的结果 我解释一下 第一个1 那是行号 后面的2005-6-14 是登记的开始时间,2005-8-31是登记的结束时间,意思就是他在2005-6-14 到 2005-8-31 是连续登记的。后面的-32169 是人的id。但是第二条记录的开始时间和登记的结束时间分别是 2005-7-1 2005-8-31 这个包含在第一个时间里面,说明他在2005-6-14 2005-8-31是连续的。
现在我就想求各位帮我写一个方法。无论是通过什么方法。可以是程序(限pb程序)能够求出这个表内在某个时间段内哪些人是连续登记的……
求求各位了……………………首先谢谢各位!!
解决方案 »
- sql plus 无效数字???
- oracle 同比和环比 怎么写
- 请教一个关于ORACLE删除用户重建后无法连接的问题(ORA-01017 invalid username/password;logon denied)
- 请高手指点select count(*)的问题
- 高分求助:在sql语句中如何在字段之间增加特定的分隔符?
- 数据库中的点问题
- ORA-08002: 序列PLANID.CURRVAL 尚未在此进程中定义 悬赏100
- 怎么用一条sql语句更新所有符合条件的记录?
- 那裡可以下載完整的<<oracle 初學者指南>>.
- 那位大哥告诉一下PL/SQL developer5 正式版的下载地址(全的),和注册码! 100分
- 帮忙找一下问题。
- Oracle怎么让两条插入语句同时插入到各子表中
select distinct id from hist a where not exist(
select 1 from(
select id,effdat,expdat,
lag(expdat)over(partition by id order by expdate)lg from hist)
where id=a.id and effdat>lg)
and effdat>date'2005-01-01'
and expdat<date'2006-01-01'
2个问题
14 2005-7-3 2006-2-28 -32318
15 2006-3-1 2006-3-31 -32318
这两天记录算连续吗?
还有:
比如你要判断从‘2005-01-01’到‘2006-01-01’ 之间连续的id
有一个id从2005-05-01到2006年5月一直都连续,但2005-05-01以前,表里没有这个id的相关记录
这个id算从‘2005-01-01’到‘2006-01-01’ 之间连续吗
select id from hist a where not exists(
select 1 from(
select id,effdat,expdat,
lag(expdat)over(partition by id order by expdat)lg from hist)
where id=a.id and effdat>lg+1
and (effdat<date'2006-1-1' and expdat>date'2005-1-1'))
group by id
having min(a.effdat)<=date'2005-1-1'
and max(a.expdat)>=date'2006-1-1'
若相隔一天也算连续,而2005-1-1到2006-1-1并不要求id的起始和结束时间必须包含整个2005-1-1到2006-1-1的话
select distinct id from hist a where not exists(
select 1 from(
select id,effdat,expdat,
lag(expdat)over(partition by id order by expdat)lg from hist)
where id=a.id and effdat>lg+1
and (effdat<date'2006-1-1' and expdat>date'2005-1-1'))
and
a.effdat>=date'2005-1-1'
and a.expdat<date'2006-1-1'
我的意思是 从2005-01-01 这一天起就必须有这个id 而且这个id 必须是到2006-01-01 这一年内是连续的
在2005-01-01 之前和 2006-01-01 之后有没有这个id我们就不管了
select 1 from(
select id,effdat,expdat,
lag(expdat)over(partition by id order by expdat)lg from hist)
where id=a.id and effdat>lg+1
and (effdat<date'2006-1-1' and expdat>date'2005-1-1'))
and
a.expdat>=date'2005-1-1'
and a.effdat<date'2006-1-1'
group by id
having min(effdat)<=date'2005-1-1'
and max(expdat)>=date'2006-1-1'
增加两个字段,将与这个时间段有关系的起始时间和截至时间都列出,试试
a.expdat>=date'2005-1-1'
and a.effdat<date'2006-1-1'可以省掉,但是留着性能上会更好
select id,sum(case when effdat>lg+1 then 1 else 0 end)s,min(effdat)mi,max(expdat)ma
from(
select id,effdat,expdat,
lag(expdat)over(partition by id order by expdat)lg from hist)
where effdat<date'2006-1-1' and expdat>date'2005-1-1'--)
group by id)
where s=0
and mi<=date'2005-1-1'
and ma>=date'2006-1-1'
select distinct id
from (
select id,min(the_dt) effdat,max(the_dt) expdat
from (
select id,the_dt,row_number()over(partition by id order by the_dt) rnn
from (
select distinct b.id,b.effdat+a.rn-1 the_dt
from (select rownum rn
from dual
connect by rownum<=1000) a,hist b
where b.effdat+rn-1<=b.expdat ) )
group by id,the_dt-rnn )
where effdat>=to_date('2005-01-01','YYYY-MM-DD') and expdat<=to_date('2006-01-01','YYYY-MM-DD')
;
where effdat>=to_date('2005-01-01','YYYY-MM-DD') and expdat <=to_date('2006-01-01','YYYY-MM-DD')修改为
where effdat<=to_date('2005-01-01','YYYY-MM-DD') and expdat >=to_date('2006-01-01','YYYY-MM-DD')另外想了一种思路,是将各个比较小的日期片段合并成比较大的日期片段,这种用connect by 来实现,效率上会高一些,但是比较难懂。
select distinct id
from (
select id,min(effdat) effdat,max(expdat) expdat
from (
select aa.id,aa.effdat,aa.expdat,level lv,rownum rn,CONNECT_BY_ROOT effdat rt_point
from (
select id,effdat,expdat,row_number()over(partition by id order by effdat,expdat) rn
from hist
) aa
connect by nocycle prior aa.expdat>=aa.effdat-1 and prior aa.effdat<=aa.effdat and prior aa.expdat<aa.expdat
and prior id=id )
group by id,rt_point )
where effdat<=to_date('2005-01-01','YYYY-MM-DD') and expdat>=to_date('2006-01-01','YYYY-MM-DD')
;这里由于只需要判断2005-01-01至2006-01-01在连接出来的某个时间段中即可,所以对于最后连接的时间片段没有做进一步的处理。
from (select id, min(effdate) effdate, expdate
from (select id, effdate, max(expdate) expdate
from his
where effdate >= '2005-01-01'
and expdate <= '2005-12-31'
group by id, effdate)
group by id, expdate) order by id asc, effdate asc, expdate asc)) a start with a.effdate = '2005-01-01' connect by prior expdate>=effdate and prior rn<rn and prior expdate>=effdate and prior id=id and expdate<='2006-01-01') group by id having(max(expdate)) >= '2005-12-31'
学到nocycle了, 呵呵呵, 解决不了这个问题,所以我只好加了个rownum来实现的。
select id,min(effdat) effdat,max(expdat) expdat
from (
select aa.id,aa.effdat,aa.expdat,level lv,rownum rn,CONNECT_BY_ROOT effdat rt_point
from (
select id,effdat,expdat,row_number()over(partition by id order by effdat,expdat) rn
from hist
) aa
connect by nocycle prior aa.expdat>=aa.effdat-1 and prior aa.effdat<=aa.effdat and prior aa.expdat<aa.expdat
and prior id=id )
group by id,rt_point )
select a.id,a.effdat,a.expdat
from tmp a left join tmp b on a.id=b.id and a.effdat>b.effdat and a.effdat<b.expdat
where b.expdat is null
;这样子就可以将所有支离破碎的小时间片段合成各不相互有重复的大时间片段了。
里面你的23条数据查询的结果就是:
ID EFFDAT EXPDAT
-------------------- ------------------- -------------------
-32501 2005-07-13 00:00:00 2006-12-31 00:00:00
-32318 2005-07-03 00:00:00 2006-12-31 00:00:00
-32396 2005-07-07 00:00:00 2006-11-15 00:00:00
-32203 2005-06-21 00:00:00 2006-12-31 00:00:00
-32169 2005-06-14 00:00:00 2006-12-31 00:00:0添加4条数据
insert into hist values( 24,to_date('2005-04-01','YYYY-MM-DD'),to_date('2005-4-30','YYYY-MM-DD'),'-32501');
insert into hist values( 25,to_date('2005-04-01','YYYY-MM-DD'),to_date('2005-5-30','YYYY-MM-DD'),'-32501');
insert into hist values( 26,to_date('2005-05-31','YYYY-MM-DD'),to_date('2005-6-30','YYYY-MM-DD'),'-32501');insert into hist values(27,to_date('2005-03-01','YYYY-MM-DD'),to_date('2005-03-20','YYYY-MM-DD'),'-32501');
结果如下:
ID EFFDAT EXPDAT
-------------------- ------------------- -------------------
-32501 2005-04-01 00:00:00 2005-06-30 00:00:00
-32501 2005-07-13 00:00:00 2006-12-31 00:00:00
-32501 2005-03-01 00:00:00 2005-03-20 00:00:00
-32318 2005-07-03 00:00:00 2006-12-31 00:00:00
-32396 2005-07-07 00:00:00 2006-11-15 00:00:00
-32203 2005-06-21 00:00:00 2006-12-31 00:00:00
-32169 2005-06-14 00:00:00 2006-12-31 00:00:00是不是这样的数据基本上就是你想看到的了?
select a.id,a.effdat,a.expdat
from tmp a left join tmp b on a.id=b.id and a.effdat>b.effdat and a.effdat <b.expdat
where b.expdat is null
这条语句的话,估计是很难出数,仅能在少量测试数据下看结果,当然也要看你机器的性能。
只是统计从2005-05-01开始的记录,所以加上了这个过滤条件,
日期上有误解,能说细一些么。create table his (id int, effdate varchar2(12), expdate varchar2(12));insert into his (effdate, expdate) values('2005-06-14', '2005-08-31');
insert into his (effdate, expdate) values('2005-07-01', '2005-08-31');
insert into his (effdate, expdate) values('2005-09-01', '2005-12-31');
insert into his (effdate, expdate) values('2006-01-01', '2006-03-31');
insert into his (effdate, expdate) values('2006-01-01', '2006-08-31');
insert into his (effdate, expdate) values('2006-09-01', '2006-09-30');
insert into his (effdate, expdate) values('2006-10-01', '2006-12-31');
insert into his (effdate, expdate) values('2005-06-21', '2005-06-30');
insert into his (effdate, expdate) values('2005-07-01', '2005-12-31');
insert into his (effdate, expdate) values('2005-07-01', '2006-03-31');
insert into his (effdate, expdate) values('2005-07-01', '2006-06-30');
insert into his (effdate, expdate) values('2006-07-01', '2006-12-31');
insert into his (effdate, expdate) values('2005-07-03', '2005-12-31');
insert into his (effdate, expdate) values('2005-07-03', '2006-02-28');
insert into his (effdate, expdate) values('2006-03-01', '2006-03-31');
insert into his (effdate, expdate) values('2006-03-01', '2006-12-31');
insert into his (effdate, expdate) values('2005-07-07', '2005-12-31');
insert into his (effdate, expdate) values('2005-07-07', '2006-03-31');
insert into his (effdate, expdate) values('2005-07-07', '2006-06-30');
insert into his (effdate, expdate) values('2006-07-01', '2006-11-15');
insert into his (effdate, expdate) values('2005-07-13', '2005-12-31');
insert into his (effdate, expdate) values('2006-01-01', '2006-03-31');
insert into his (effdate, expdate) values('2006-01-01', '2006-12-31');
######### Add My data
insert into his (effdate, expdate) values('2005-01-01', '2005-01-30');
insert into his (effdate, expdate) values('2005-01-14', '2005-06-18');
insert into his (effdate, expdate) values('2005-06-15', '2005-08-30');insert into his select 2, effdate, expdate from his;commit;这是我的测试数据。
SQL> select * from his;
ID EFFDATE EXPDATE
-- ------------ ------------
1 2005-06-14 2005-08-31
1 2005-07-01 2005-08-31
1 2005-09-01 2005-12-31
1 2006-01-01 2006-03-31
1 2006-01-01 2006-08-31
1 2006-09-01 2006-09-30
1 2006-10-01 2006-12-31
1 2005-06-21 2005-06-30
1 2005-07-01 2005-12-31
1 2005-07-01 2006-03-31
1 2005-07-01 2006-06-30
1 2006-07-01 2006-12-31
1 2005-07-03 2005-12-31
1 2005-07-03 2006-02-28
1 2006-03-01 2006-03-31
1 2006-03-01 2006-12-31
1 2005-07-07 2005-12-31
1 2005-07-07 2006-03-31
1 2005-07-07 2006-06-30
1 2006-07-01 2006-11-15
ID EFFDATE EXPDATE
-- ------------ ------------
1 2005-07-13 2005-12-31
1 2006-01-01 2006-03-31
1 2006-01-01 2006-12-31
1 2005-01-01 2005-01-30
1 2005-01-14 2005-06-19
1 2005-06-15 2005-08-30
2 2005-06-14 2005-08-31
2 2005-07-01 2005-08-31
2 2005-09-01 2005-12-31
2 2006-01-01 2006-03-31
2 2006-01-01 2006-08-31
2 2006-09-01 2006-09-30
2 2006-10-01 2006-12-31
2 2005-06-21 2005-06-30
2 2005-07-01 2005-12-31
2 2005-07-01 2006-03-31
2 2005-07-01 2006-06-30
2 2006-07-01 2006-12-31
2 2005-07-03 2005-12-31
2 2005-07-03 2006-02-28
2 2006-03-01 2006-03-31
ID EFFDATE EXPDATE
-- ------------ ------------
2 2006-03-01 2006-12-31
2 2005-07-07 2005-12-31
2 2005-07-07 2006-03-31
2 2005-07-07 2006-06-30
2 2006-07-01 2006-11-15
2 2005-07-13 2005-12-31
2 2006-01-01 2006-03-31
2 2006-01-01 2006-12-31
2 2005-01-01 2005-01-30
2 2005-01-14 2005-06-19
2 2005-06-15 2005-08-30
52 rows selected
ID EFFDAT EXPDAT
-------------------- ------------------- -------------------
-32501 2004-07-13 00:00:00 2007-12-31 00:00:00
这样的记录是符合楼主想要的,但是直接被你过滤掉了。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[sequen_time] @startdate DATETIME, @enddate DATETIME
AS
/*
SELECT * FROM hist;
EXEC sequen_time '2005-07-01','20060101'
*/
BEGIN
DECLARE @count INT --用以查看是否有符合给定日期条件的记录
DECLARE @count2 INT --用以查看是否有符合给定日期条件的记录输出
SELECT @count=COUNT(Id) FROM hist
WHERE ( effdat<=@startdate AND expdat >=@startdate )
OR ( effdat<=@enddate AND expdat >=@enddate ) IF(@count>0) --如果:有符合给定日期条件的记录
BEGIN CREATE TABLE #DBTempA(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) INSERT INTO #DBTempA(effdat,expdat,userID)
SELECT effdat,expdat,userID FROM hist
WHERE ( effdat<=@startdate AND expdat >=@startdate )
OR ( effdat<=@enddate AND expdat >=@enddate ) --用以保存符合给定时间段要求的用户记录
CREATE TABLE #DBTempB(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) )
--如果存在直接记录行符合给定日期条件:
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat,expdat,userID FROM #DBTempA WHERE effdat>@startdate AND expdat<@enddate; DELETE FROM #DBTempA WHERE userID IN (SELECT userID FROM #DBTempB); SELECT @count=COUNT(Id) FROM #DBTempA; WHILE(@count>=1) --如果还有记录:游标判断间接符合条件的记录
BEGIN
DECLARE @userID INT --从I号最小的用户判断起
DECLARE @ID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @oldStartdate DATETIME
DECLARE @oldEnddate DATETIME
DECLARE @newStartdate DATETIME
DECLARE @newEnddate DATETIME SELECT @Id=Id, @oldStartdate=effdat, @oldEnddate=expdat, @userID=userId FROM #DBTempA WHERE ID=(SELECT MIN(ID) FROM #DBTempA) Declare @MyData Cursor
Set @MyData = Cursor FOR
Select effdat, expdat FROM #DBTempA WHERE userId=@userID and Id<>@Id
Open @MyData
Fetch next from @MyData Into @newStartdate,@newEnddate
While @@FETCH_STATUS = 0
BEGIN
IF(@newStartdate>@oldStartdate AND @newStartdate<@oldEnddate AND @newEnddate>@oldEnddate)
SET @oldEnddate=@newEnddate
IF(@newEnddate<@oldEnddate AND @newEnddate>@oldStartdate AND @newStartdate<@oldStartdate)
SET @oldStartdate=@newStartdate
Fetch next from @MyData Into @newStartdate, @newEnddate
END
Close @MyData
Deallocate @MyData IF (@oldStartdate<=@startdate AND @oldEnddate>=@enddate) --找到符合条件的记录
BEGIN
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat, expdat, userID FROM #DBTempA WHERE userID=@userID;
DELETE FROM #DBTempA WHERE userId=@userID;
SELECT @count=COUNT(Id) FROM #DBTempB; --重置记数变量
END
ELSE --没有找到符合条件的记录
BEGIN
DELETE FROM #DBTempA WHERE Id=@Id; --删除已经循环过的Id
SET @count=@count-1; --重置记数变量
END END
SELECT @count2=COUNT(ID) FROM #DBTempB;
IF(@count2>0)
SELECT * FROM #DBTempB;
ELSE
SELECT '对不起:没有您要的给定时间段内条件判断连续的记录!' '无符条件判断的记录';
DROP TABLE #DBTempA;
DROP TABLE #DBTempB;
END ELSE
SELECT '对不起:没有您要的给定时间段内连续的记录!' '无符合初始条件记录';END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[sequen_time] @startdate DATETIME, @enddate DATETIME
AS
/*
SELECT * FROM hist;
EXEC sequen_time '20050701','20060101'
*/
BEGIN
DECLARE @count INT --用以查看是否有符合给定日期条件的记录
DECLARE @count2 INT --用以查看是否有符合给定日期条件的记录输出
SELECT @count=COUNT(Id) FROM hist
WHERE ( effdat<=@startdate AND expdat >=@startdate )
OR ( effdat<=@enddate AND expdat >=@enddate ) IF(@count>0) --如果:有符合给定日期条件的记录
BEGIN CREATE TABLE #DBTempA(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) INSERT INTO #DBTempA(effdat,expdat,userID)
SELECT effdat,expdat,userID FROM hist
WHERE ( effdat<=@startdate AND expdat >=@startdate )
OR ( effdat<=@enddate AND expdat >=@enddate ) --用以保存符合给定时间段要求的用户记录
CREATE TABLE #DBTempB(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) )
--如果存在直接记录行符合给定日期条件:
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat,expdat,userID FROM #DBTempA WHERE effdat>@startdate AND expdat<@enddate; DELETE FROM #DBTempA WHERE userID IN (SELECT userID FROM #DBTempB); SELECT @count=COUNT(Id) FROM #DBTempA; WHILE(@count>=1) --如果还有记录:游标判断间接符合条件的记录
BEGIN
DECLARE @userID INT --从I号最小的用户判断起
DECLARE @ID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @oldStartdate DATETIME
DECLARE @oldEnddate DATETIME
DECLARE @newStartdate DATETIME
DECLARE @newEnddate DATETIME SELECT @Id=Id, @oldStartdate=effdat, @oldEnddate=expdat, @userID=userId FROM #DBTempA WHERE ID=(SELECT MIN(ID) FROM #DBTempA) Declare @MyData Cursor
Set @MyData = Cursor FOR
Select effdat, expdat FROM #DBTempA WHERE userId=@userID and Id<>@Id
Open @MyData
Fetch next from @MyData Into @newStartdate,@newEnddate
While @@FETCH_STATUS = 0
BEGIN
IF(@newStartdate>@oldStartdate AND @newStartdate<@oldEnddate AND @newEnddate>@oldEnddate)
SET @oldEnddate=@newEnddate
IF(@newEnddate<@oldEnddate AND @newEnddate>@oldStartdate AND @newStartdate<@oldStartdate)
SET @oldStartdate=@newStartdate
Fetch next from @MyData Into @newStartdate, @newEnddate
END
Close @MyData
Deallocate @MyData IF (@oldStartdate<=@startdate AND @oldEnddate>=@enddate) --找到符合条件的记录
BEGIN
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat, expdat, userID FROM #DBTempA WHERE userID=@userID;
DELETE FROM #DBTempA WHERE userId=@userID;
SELECT @count=COUNT(Id) FROM #DBTempB; --重置记数变量
END
ELSE --没有找到符合条件的记录
BEGIN
DELETE FROM #DBTempA WHERE Id=@Id; --删除已经循环过的Id
SET @count=@count-1; --重置记数变量
END END
SELECT @count2=COUNT(ID) FROM #DBTempB;
IF(@count2>0)
SELECT * FROM #DBTempB;
ELSE
SELECT '对不起:没有您要的给定时间段内条件判断连续的记录!' '无符条件判断的记录';
DROP TABLE #DBTempA;
DROP TABLE #DBTempB;
END ELSE
SELECT '对不起:没有您要的给定时间段内连续的记录!' '无符合初始条件记录';END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
不过inthirties的代码中有借鉴的地方
select id,min(effdat) effdat,expdat
from (
select id, effdat, max(expdat) expdat
from hist
group by id,effdat)
group by id,expdat
这样来筛选数据,这样应该可以使1500W的记录降到1000W左右吧。
至于我上面的
with tmp as (
select id,min(effdat) effdat,max(expdat) expdat
from (
select aa.id,aa.effdat,aa.expdat,level lv,rownum rn,CONNECT_BY_ROOT effdat rt_point
from (
select id,effdat,expdat,row_number()over(partition by id order by effdat,expdat) rn
from hist
) aa
connect by nocycle prior aa.expdat>=aa.effdat-1 and prior aa.effdat <=aa.effdat and prior aa.expdat <aa.expdat
and prior id=id )
group by id,rt_point )
select a.id,a.effdat,a.expdat
from tmp a left join tmp b on a.id=b.id and a.effdat>b.effdat and a.effdat <b.expdat
where b.expdat is null
;
可以优化为:
with tmp as (
select id,min(effdat) effdat,max(expdat) expdat
from (
select aa.id,aa.effdat,aa.expdat,level lv,rownum rn,CONNECT_BY_ROOT effdat rt_point
from (
select id,effdat,expdat,row_number()over(partition by id order by effdat,expdat) rn
from hist
) aa
connect by nocycle prior aa.expdat>=aa.effdat-1 and prior aa.effdat <=aa.effdat and prior aa.expdat <aa.expdat
and prior id=id )
group by id,rt_point )
select a.id,min(a.effdat) effdat,a.expdat
from tmp a
group by a.id,a.expdat
;
不过没有测试,明天回公司了再测试看看。
---连续时间问题(终结版)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[sequen_time] @startdate DATETIME, @enddate DATETIME
AS
/*
SELECT * FROM hist;
EXEC sequen_time '20060101','20061231'
*/
BEGIN--select CONVERT(char(10),getdate(),121)
IF (isnull(@startdate,'')='')
set @startdate=CONVERT(char(10),getdate()-15,121)
IF (isnull(@enddate,'')='')
set @enddate=CONVERT(char(10),getdate(),121) DECLARE @count INT --用以查看是否有符合给定日期条件的记录
DECLARE @count2 INT --用以查看是否有符合给定日期条件的记录输出
SELECT @count=COUNT(Id) FROM hist
WHERE ( effdat<=CONVERT(char(10),@startdate,121) AND expdat >=CONVERT(char(10),@startdate,121) )
OR ( effdat<=CONVERT(char(10),@enddate,121) AND expdat >=CONVERT(char(10),@enddate,121) )
OR ( effdat<CONVERT(char(10),@enddate,121) AND expdat >CONVERT(char(10),@enddate,121) ) IF(@count>0) --如果:有符合给定日期条件的记录
BEGIN CREATE TABLE #DBTempA(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) INSERT INTO #DBTempA(effdat,expdat,userID)
SELECT effdat,expdat,userID FROM hist
WHERE ( effdat<=CONVERT(char(10),@startdate,121) AND expdat >=CONVERT(char(10),@startdate,121) )
OR ( effdat<=CONVERT(char(10),@enddate,121) AND expdat >=CONVERT(char(10),@enddate,121) ) --用以保存符合给定时间段要求的用户记录
CREATE TABLE #DBTempB(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) )
--如果存在直接记录行符合给定日期条件:
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat,expdat,userID FROM #DBTempA WHERE effdat>CONVERT(char(10),@startdate,121) AND expdat<CONVERT(char(10),@enddate,121); DELETE FROM #DBTempA WHERE userID IN (SELECT userID FROM #DBTempB); SELECT @count=COUNT(Id) FROM #DBTempA; WHILE(@count>0) --如果还有记录:游标判断间接符合条件的记录
BEGIN
DECLARE @userID INT --从I号最小的用户判断起
DECLARE @oldID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @newID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @num INT --循环测试
DECLARE @oldStartdate DATETIME
DECLARE @oldEnddate DATETIME
DECLARE @newStartdate DATETIME
DECLARE @newEnddate DATETIME SET @num=1 SELECT TOP 1 @oldId=Id, @oldStartdate=effdat, @oldEnddate=expdat, @userID=userId FROM #DBTempA Declare @MyData Cursor
Set @MyData = Cursor FOR
Select Id,effdat, expdat FROM #DBTempA WHERE userId=@userID and Id<>@oldId
Open @MyData
Fetch next from @MyData Into @newID, @newStartdate,@newEnddate
While @@FETCH_STATUS = 0
BEGIN
IF(@newStartdate>=@oldStartdate AND @newStartdate<=@oldEnddate AND @newEnddate>@oldEnddate)
BEGIN
SET @oldEnddate=@newEnddate;
DELETE FROM #DBTempA WHERE Id=@newId;
END
IF(@newEnddate<=@oldEnddate AND @newEnddate>=@oldStartdate AND @newStartdate<@oldStartdate)
BEGIN
SET @oldStartdate=@newStartdate;
DELETE FROM #DBTempA WHERE Id=@newId;
END
Fetch next from @MyData Into @newID, @newStartdate, @newEnddate
END
Close @MyData
Deallocate @MyData IF (@oldStartdate<=CONVERT(char(10),@startdate,121) AND @oldEnddate>=CONVERT(char(10),@enddate,121)) --找到符合条件的记录
BEGIN
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT @oldStartdate,@oldEnddate,@userId;
DELETE FROM #DBTempA WHERE userId=@userID;
SELECT @count=COUNT(Id) FROM #DBTempB; --重置记数变量
END
ELSE --没有找到符合条件的记录
DELETE FROM #DBTempA WHERE Id=@oldId; --删除已经循环过的Id
SET @count=@count-1; --重置记数变量
END
SELECT @count2=COUNT(ID) FROM #DBTempB;
IF(@count2>0)
SELECT * FROM #DBTempB;
ELSE
SELECT '对不起:没有您要的给定时间段内条件判断连续的记录!' '无符条件判断的记录';
DROP TABLE #DBTempA;
DROP TABLE #DBTempB;
END ELSE
SELECT '对不起:没有您要的给定时间段内连续的记录!' '无符合初始条件记录';END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
with tmp as (
select id,min(effdat) effdat,max(expdat) expdat
from (
select id,effdat,expdat,CONNECT_BY_ROOT effdat rt_point
from (
select id,min(effdat) effdat,expdat
from (
select id, effdat, max(expdat) expdat
from hist
group by id,effdat)
group by id,expdat ) --双重筛选,记录过滤到1000万以下
connect by nocycle prior expdat>=effdat-1 and prior effdat <=effdat and prior expdat <expdat
and prior id=id ) --connect by连接后估计生成3-4千万记录
group by id,rt_point ) --分组后记录大概为800万以下
select a.id,min(a.effdat) effdat,a.expdat
from tmp a
group by a.id,a.expdat
; --初步估计分组后记录为300万,建议insert到另一张表保存你提供的测试数据查询结果如下:
ID EFFDAT EXPDAT
-------------------- ------------------- -------------------
-32169 2005-06-14 00:00:00 2006-12-31 00:00:00
-32318 2005-07-03 00:00:00 2006-12-31 00:00:00
-32203 2005-06-21 00:00:00 2006-12-31 00:00:00
-32396 2005-07-07 00:00:00 2006-11-15 00:00:00
-32501 2005-07-13 00:00:00 2006-12-31 00:00:00
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[sequen_time] @startdate DATETIME, @enddate DATETIME
AS
/*
---连续时间问题(终结、优化版)
SELECT * FROM hist;
EXEC sequen_time '20060101','20070301'
*/
BEGIN --如果输入(起始时间断)参数为空,则判断近半个月以来的连续用户
IF (ISNULL(@startdate,'')='')
SET @startdate=CONVERT(CHAR(10),GETDATE()-15,121)
IF (ISNULL(@enddate,'')='')
SET @enddate=CONVERT(CHAR(10),GETDATE(),121) DECLARE @count INT --用以查看是否有符合给定日期条件的记录
DECLARE @count2 INT --用以查看是否有符合给定日期条件的记录输出 SELECT @count=COUNT(Id) FROM hist
WHERE ( effdat<=CONVERT(CHAR(10),@startdate,121) AND expdat >=CONVERT(CHAR(10),@startdate,121) )
OR ( effdat<=CONVERT(CHAR(10),@enddate,121) AND expdat >=CONVERT(CHAR(10),@enddate,121) ) IF(@count>0) --如果:有符合给定日期条件的记录
BEGIN CREATE TABLE #DBTempA(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) INSERT INTO #DBTempA(effdat,expdat,userID)
SELECT effdat,expdat,userID FROM hist
WHERE ( effdat<=CONVERT(CHAR(10),@startdate,121) AND expdat >=CONVERT(CHAR(10),@startdate,121) )
OR ( effdat<=CONVERT(CHAR(10),@enddate,121) AND expdat >=CONVERT(CHAR(10),@enddate,121) ) --用以保存符合给定时间段要求的用户记录
CREATE TABLE #DBTempB(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) --如果存在直接记录行符合给定日期条件:
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat,expdat,userID FROM #DBTempA
WHERE effdat>CONVERT(CHAR(10),@startdate,121) AND expdat<CONVERT(CHAR(10),@enddate,121); --删除已经存在直接记录符合指定时间段的用户
DELETE FROM #DBTempA WHERE userID IN (SELECT userID FROM #DBTempB); --记录将要判断的记录行数,用以每次循环后递减,当@count为0时,退出While循环
SELECT @count=COUNT(Id) FROM #DBTempA; WHILE(@count>0) --如果还有记录:游标判断间接符合条件的记录
BEGIN
DECLARE @userID INT --保存用户名,在游标中将根据用户名选择记录进行循环判断
DECLARE @oldID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @newID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @oldStartdate DATETIME
DECLARE @oldEnddate DATETIME
DECLARE @newStartdate DATETIME
DECLARE @newEnddate DATETIME SELECT TOP 1 @oldId=Id, @oldStartdate=effdat, @oldEnddate=expdat, @userID=userId FROM #DBTempA DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id,effdat, expdat FROM #DBTempA WHERE userId=@userID and Id<>@oldId
OPEN @MyData
FETCH next FROM @MyData INTO @newID, @newStartdate,@newEnddate
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@newStartdate>@oldStartdate AND @newStartdate<@oldEnddate AND @newEnddate>@oldEnddate)
BEGIN
SET @oldEnddate=@newEnddate;
END
IF(@newEnddate<@oldEnddate AND @newEnddate>@oldStartdate AND @newStartdate<@oldStartdate)
BEGIN
SET @oldStartdate=@newStartdate;
END
FETCH next FROM @MyData INTO @newID, @newStartdate, @newEnddate
END
CLOSE @MyData
DEALLOCATE @MyData --如果找到符合条件的记录
IF (@oldStartdate<=CONVERT(CHAR(10),@startdate,121) AND @oldEnddate>=CONVERT(CHAR(10),@enddate,121))
BEGIN
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT @oldStartdate,@oldEnddate,@userId;
DELETE FROM #DBTempA WHERE userId=@userID;
SELECT @count=COUNT(Id) FROM #DBTempB; --重置记数变量
END
ELSE --如果没有找到符合条件的记录
DELETE FROM #DBTempA WHERE Id=@oldId; --删除已经循环过的Id
SET @count=@count-1; --重置记数变量
END
SELECT @count2=COUNT(ID) FROM #DBTempB;
IF(@count2>0)
SELECT * FROM #DBTempB;
ELSE
SELECT '对不起:没有您要的给定时间段内条件判断连续的记录!' '无符条件判断的记录';
DROP TABLE #DBTempA,#DBTempB;
END ELSE
SELECT '对不起:没有您要的给定时间段内连续的记录!' '无符合初始条件记录';END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SELECT STATEMENT, GOAL = ALL_ROWS Cost=202221 Cardinality=7177 Bytes=251195
FILTER
HASH GROUP BY Cost=202221 Cardinality=7177 Bytes=251195
VIEW Object owner=HIT Cost=147087 Cardinality=15979499 Bytes=559282465
WINDOW SORT Cost=147087 Cardinality=15979499 Bytes=463405471
TABLE ACCESS FULL Object owner=HIT Object name=HIST Cost=17103 Cardinality=15979499 Bytes=463405471这个就是你的那个执行计划 都是什么意思啊 ?
怎么能看出来数否用到了索引啊??
那就这样select id
from (select a.*, level
from (select id,
effdate,
expdate,
row_number() over(partition by id order by effdate asc, expdate asc) rn
from (select id, effdate, expdate
from (select id, min(effdate) effdate, expdate
from (select id, effdate, max(expdate) expdate
from his
group by id, effdate)
group by id, expdate)
order by id asc, effdate asc, expdate asc)) a
start with rn = 1
connect by prior expdate >= effdate
and prior rn < rn
and prior expdate >= effdate
and prior id = id
and expdate <= '2005-12-31')
group by id
having(max(expdate)) >= '2005-12-31'
prior expdate >= effdate 出现了2次?
另外and expdate <= '2005-12-31'为什么要加这个条件?另外start with rn = 1这个条件是不行的。比如同1个ID志有
2005-01-01 2005-01-20
2005-02-01 2005-02-20
2005-03-01 2005-03-20这3条记录的话,只能统计到2005-01-01 2005-01-20这1条,会舍弃下面2条数据。
Cost=202221 跟数据量有关
你看看其他的写法allrows的cost有多少
1. hist表里面每条记录的effdat(起始时间)大于expdat(结束时间), 楼主:对吧?2. 我们得先明白:我们到底怎么选择时间区间? ----比如说:我们要选择在 2005-01-01(起始时间段) 到 2006-01-01(结束时间段)这段时间内的连续用户
----是不是表里面的每条记录应该满足:effdat<='2006-01-01' and expdat>='2005-01-01'
----每行记录的effdat应该小于等于 2006-01-01(结束时间段),并且:expdat应该大于等于2005-01-01(起始时间段),
----各位:好好想想:我说的对吗? 比如说:用户-32396在表中共有以下四条记录,
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
我要求解:用户在2006-01-01到2006-11-12日,是否是连续的,我们应该只考虑2、3、4三条记录就够了(正如我上面所说的)3. 用户Id不是序列Id,是后面的-32169.....才是真正的用户ID,楼主:对吧?
---当然:这一点好多人没注意,迷惑了!
1. hist表里面每条记录的effdat(起始时间)大于expdat(结束时间), 楼主:对吧?2. 我们得先明白:我们到底怎么选择时间区间? ----比如说:我们要选择在 2006-01-01(起始时间段) 到 2006-11-12(结束时间段)这段时间内的连续用户
----是不是表里面的每条记录应该满足:effdat<='2006-11-12' and expdat>='2006-01-01'
----每行记录的effdat应该小于等于 2006-11-12(结束时间段),并且:expdat应该大于等于2006-01-01(起始时间段),
----各位:好好想想:我说的对吗? 比如说:用户-32396在表中共有以下四条记录,
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
我要求解:用户在2006-01-01到2006-11-12日,是否是连续的,我们应该只考虑2、3、4三条记录就够了(正如我上面所说的)3. 用户Id不是序列Id,是后面的-32169.....才是真正的用户ID,楼主:对吧?
---当然:这一点好多人没注意,迷惑了!
1 LS的为什么连接处 prior expdate >= effdate 出现了2次?
笔误
这里是多加了一个,去掉一个即可。换成prior expdate < expdate2 另外and expdate <= '2005-12-31'为什么要加这个条件?加这个主要是后面的不需要遍历了,已经到了'2005-12-31'表示已经到达要求了,如果不加这个后面的超过2005--12-31的也会遍历下去,没有必要了。3 另外start with rn = 1这个条件是不行的。比如同1个ID志有2005-01-01 2005-01-20
2005-02-01 2005-02-20
2005-03-01 2005-03-20
这3条记录的话,只能统计到2005-01-01 2005-01-20这1条,会舍弃下面2条数据。 不是过滤,是这两个已经参加不了遍历了,也就是说这里,没有连接起来,所以只有一条记录在遍历的结果了。最后取expdate的最大值,只要最大值max(expdate)>= '2005-12-31' 大于就表示 连到最后的一天了,就就达到目标了。
select id, effdate, max(expdate) expdate
from his
group by id, effdate目的很简单,就是把时间全面分段 把记录缩小。这样同样的effdate都统一到一个记录里了。第二步
select id, min(effdate) effdate, expdate
from (select id, effdate, max(expdate) expdate
from his
group by id, effdate)
group by id, expdate这一步目的和上面一样,把记录集在压缩,刚才没有effdate重复的了,但是有可能expdate是重复的,
就是把第一步的结果集拿到用时间全面分段 把记录缩小。这样同样的expdate都统一到一个最小effdate记录里了。
上面两部,我们把所有的时间段都进行了压缩,现在的结果集里没有effdate重复的,也没有expdate重复的了。
我这里准备用connect来遍历了,思路就是从用户的最小的effdate开始做遍历,然后用这个前面的expdate和后面的effdate来比较,
如果是expdate大于或者等于后面记录的effdate,说明上面的记录和下面的记录就连续了, 这样就可以继续遍历后面的记录,
优化一个条件,如果前面记录的expdate大于后面的expdate。后面这个也没有必要递归了。这样递归所有的记录,选择出来的时间链
一定是连续的,直到连接不起来而断掉,
select a.*, level
from (select id,
effdate,
expdate,
row_number() over(partition by id order by effdate asc, expdate asc) rn
from (select id, effdate, expdate
from (select id, min(effdate) effdate, expdate
from (select id, effdate, max(expdate) expdate
from his
group by id, effdate)
group by id, expdate)
order by id asc, effdate asc, expdate asc)) a
start with rn = 1
connect by prior expdate >= effdate
and prior expdate < expdate
and prior rn < rn
and prior id = id
and expdate <= '2005-12-31'这里的connect by就是递归条件,start with rn =1 就是从最小的effdate开始,注意我们的rn是row_number() over(partition by id order by effdate asc, expdate asc)
已经是按effdate排好序了。connct by的条件 解释一下
1. prior expdate >= effdate 前面的expdate必须大于后面的effdate,否则不连续
2. and prior expdate < expdate 前面的expdate必须小于后面的effdate,否则忽略该记录,前记录已经包含该记录了,该记录无效,可以忽略
3. and prior rn < rn 按时间排序了,所以递归的时候,按顺序来。
4. and prior id = id 用户的id都是相同的记录
5. and expdate <= '2005-12-31' expdate大于或者等于目标的最后日期就不需要遍历了,已经满足要求了,后面的记录无意义。第四步
如果我们的时间链里出现了大于2005-12-31的记录,也就已经达到要求了。
这样也就出来最后的答案了
用上面的结果集做个group by id,检查最大的expdate的时间,
select id
from (select a.*, level
from (select id,
effdate,
expdate,
row_number() over(partition by id order by effdate asc, expdate asc) rn
from (select id, effdate, expdate
from (select id, min(effdate) effdate, expdate
from (select id, effdate, max(expdate) expdate
from his
group by id, effdate)
group by id, expdate)
order by id asc, effdate asc, expdate asc)) a
start with rn = 1
connect by prior expdate >= effdate
and prior expdate < expdate
and prior rn < rn
and prior id = id
and expdate <= '2005-12-31')
group by id
having(max(expdate)) >= '2005-12-31'
大家可以在
“连续时间的判断,类似于区位连续的问题”
找到测试数据,最后的sql,已经每个分步sql的逻辑
将原记录按id分组,按结束时间排序,若相邻两条记录中,前一条记录的结束时间小于后一条记录的开始时间-1天,那么这两条不连续
在一个时间区间中没有这样的两条记录的id,判断它在这个时间区间内连续
我认为这个效率是最高的,2个分析函数加一个group by
特别是楼主那么大的一张表这个写法有个缺点:若这种排序,相邻两条记录不连续,但他们都包含在更下面一条记录的起始和结束时间中
不过发生的几率比较小,如果会出现这种情况的话
这种算法就不可取
不过再加一个分析函数lag按起始时间排序再判定一次应该可以解决
1. hist表里面每条记录的effdat(起始时间)大于expdat(结束时间), 楼主:对吧?2. 我们得先明白:我们到底怎么选择时间区间? ----比如说:我们要选择在 2006-01-01(起始时间段) 到 2006-11-12(结束时间段)这段时间内的连续用户
----是不是表里面的每条记录应该满足:effdat<='2006-11-12' and expdat>='2006-01-01'
----每行记录的effdat应该小于等于 2006-11-12(结束时间段),并且:expdat应该大于等于2006-01-01(起始时间段),
----各位:好好想想:我说的对吗? 比如说:用户-32396在表中共有以下四条记录,
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
我要求解:用户在2006-01-01到2006-11-12日,是否是连续的,我们应该只考虑2、3、4三条记录就够了(正如我上面所说的)3. 用户Id不是序列Id,是后面的-32169.....才是真正的用户ID,楼主:对吧?
---当然:这一点好多人没注意,迷惑了!4. 整了几天:各位还没有把连续的概念给整明白:
比如说:下面四条记录:
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
第一条记录:无可否认:表示用户从2005-7-7 到 2005-12-31 是连续的,当在判断第二记录(前提:userid得与前一条记录的userid相等)时,
我们先把第一记录的effdat看作oldeffdat,expdat看作oldexpdat(可以理解吧)如果它后面存在任意一条记录,这条的effdat看作neweffdat,expdat看作newexpdat(可以理解吧)此时:记录存在三种情况:
1: neweffdat 介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,newexpdat > oldexpdat
那么这个用户在时间区间 oldeffdat与newexpdat之间是连续的2:newexpdat介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,neweffdat < oldexpdat
那么这个用户在时间区间 neweffdat与oldexpdat之间是连续的3:neweffdat <= 先前一条记录的effdat(oldeffdat),并且 newexpdat >= 先前一条记录的expdat(oldeffdat)
也就是说先前一条记录的时间区间真包含于新记录的时间区间,
此时:那么说明这个用户在时间区间:neweffdat与newexpdat之间是连续的。--同样的判断用于第一条记录后面的任意记录,依此类推!不知道我说的,大家能不能明白,也不知道楼主是不是这个意思?
----是不是表里面的每条记录应该满足:effdat<='2006-11-12' and expdat>='2006-01-01'
----每行记录的effdat应该小于等于 2006-11-12(结束时间段),并且:expdat应该大于等于2006-01-01(起始时间段),
----各位:好好想想:我说的对吗? 比如说:用户-32396在表中共有以下四条记录,
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
我要求解:用户在2006-01-01到2006-11-12日,是否是连续的,我们应该只考虑2、3、4三条记录就够了(正如我上面所说的)3. 用户Id不是序列Id,是后面的-32169.....才是真正的用户ID,楼主:对吧?
---当然:这一点好多人没注意,迷惑了!4. 整了几天:各位还没有把连续的概念给整明白:
比如说:下面四条记录:
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
第一条记录:无可否认:表示用户从2005-7-7 到 2005-12-31 是连续的,当在判断第二记录(前提:userid得与前一条记录的userid相等)时,
我们先把第一记录的effdat看作oldeffdat,expdat看作oldexpdat(可以理解吧)如果它后面存在任意一条记录,这条的effdat看作neweffdat,expdat看作newexpdat(可以理解吧)此时:记录存在三种情况:
1: neweffdat 介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,newexpdat > oldexpdat
那么这个用户在时间区间 oldeffdat与newexpdat之间是连续的2:newexpdat介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,neweffdat < oldeffdat
那么这个用户在时间区间 neweffdat与oldexpdat之间是连续的3:neweffdat <= 先前一条记录的effdat(oldeffdat),并且 newexpdat >= 先前一条记录的expdat(oldeffdat)
也就是说先前一条记录的时间区间真包含于新记录的时间区间,
此时:那么说明这个用户在时间区间:neweffdat与newexpdat之间是连续的。--同样的判断用于第一条记录后面的任意记录,依此类推!不知道我说的,大家能不能明白,也不知道楼主是不是这个意思?
2. 我们得先明白:我们到底怎么选择时间区间? ----比如说:我们要选择在 2006-01-01(起始时间段) 到 2006-11-12(结束时间段)这段时间内的连续用户
----是不是表里面的每条记录应该满足:effdat<='2006-11-12' and expdat>='2006-01-01'
----每行记录的effdat应该小于等于 2006-11-12(结束时间段),并且:expdat应该大于等于2006-01-01(起始时间段),
----各位:好好想想:我说的对吗? 比如说:用户-32396在表中共有以下四条记录,
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
我要求解:用户在2006-01-01到2006-11-12日,是否是连续的,我们应该只考虑2、3、4三条记录就够了(正如我上面所说的)3. 用户Id不是序列Id,是后面的-32169.....才是真正的用户ID,楼主:对吧?
---当然:这一点好多人没注意,迷惑了!4. 整了几天:各位还没有把连续的概念给整明白:
比如说:下面四条记录:
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
第一条记录:无可否认:表示用户从2005-7-7 到 2005-12-31 是连续的,当在判断第二记录(前提:userid得与前一条记录的userid相等)时,
我们先把第一记录的effdat看作oldeffdat,expdat看作oldexpdat(可以理解吧)如果它后面存在任意一条记录,这条的effdat看作neweffdat,expdat看作newexpdat(可以理解吧)此时:记录存在三种情况:
1: neweffdat 介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,newexpdat > oldexpdat
那么这个用户在时间区间 oldeffdat与newexpdat之间是连续的2:newexpdat介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,neweffdat < oldeffdat
那么这个用户在时间区间 neweffdat与oldexpdat之间是连续的3:neweffdat <= 先前一条记录的effdat(oldeffdat),并且 newexpdat >= 先前一条记录的expdat(oldeffdat)
也就是说先前一条记录的时间区间真包含于新记录的时间区间,
此时:那么说明这个用户在时间区间:neweffdat与newexpdat之间是连续的。--同样的判断用于第一条记录后面的任意记录,依此类推!不知道我说的,大家能不能明白,也不知道楼主是不是这个意思?
这个贴颇有点“以sql会友”的气派哟。
----是不是表里面的每条记录应该满足:effdat <='2006-11-12' and expdat>='2006-01-01'
----每行记录的effdat应该小于等于 2006-11-12(结束时间段),并且:expdat应该大于等于2006-01-01(起始时间段),
----各位:好好想想:我说的对吗? 比如说:用户-32396在表中共有以下四条记录,
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
我要求解:用户在2006-01-01到2006-11-12日,是否是连续的,我们应该只考虑2、3、4三条记录就够了(正如我上面所说的) 3. 用户Id不是序列Id,是后面的-32169.....才是真正的用户ID,楼主:对吧?
---当然:这一点好多人没注意,迷惑了! 4. 整了几天:各位还没有把连续的概念给整明白:
比如说:下面四条记录:
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
第一条记录:无可否认:表示用户从2005-7-7 到 2005-12-31 是连续的, 当在判断第二记录(前提:userid得与前一条记录的userid相等)时,
我们先把第一记录的effdat看作oldeffdat,expdat看作oldexpdat(可以理解吧) 如果它后面存在任意一条记录,这条的effdat看作neweffdat,expdat看作newexpdat(可以理解吧) 此时:记录存在三种情况:
1: neweffdat 介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,newexpdat > oldexpdat
那么这个用户在时间区间 oldeffdat与newexpdat之间是连续的 2:newexpdat介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,neweffdat < oldeffdat
那么这个用户在时间区间 neweffdat与oldexpdat之间是连续的 3:neweffdat <= 先前一条记录的effdat(oldeffdat),并且 newexpdat >= 先前一条记录的expdat(oldeffdat)
也就是说先前一条记录的时间区间真包含于新记录的时间区间,
此时:那么说明这个用户在时间区间:neweffdat与newexpdat之间是连续的。 --同样的判断用于第一条记录后面的任意记录,依此类推! 不知道我说的,大家能不能明白,也不知道楼主是不是这个意思?
--比如说:求2005-07-07 到 2007-02-01时间段内连续的用户:
--呵呵:不知道效率怎么样?select a1.userid, a1.idnum, a2.idnum
from (
SELECT t1.userid, count(t1.userid) idnum
from hist t1
where t1.effdat<=DATE'2007-02-01' and t1.expdat>=DATE'2005-07-07'
group by t1.userid ) a1 join
(
select t1.userid, count(t1.id)/2 idnum
from hist t1 join hist t2
on t1.userid=t2.userid
and t1.effdat<=DATE'2007-02-01' and t1.expdat>=DATE'2005-07-07'
and ( (t2.effdat>=t1.effdat and t2.effdat<=t1.expdat and t2.expdat>t1.expdat)
or t2.effdat<=t1.effdat and t2.expdat>=t1.expdat )
group by t1.userid ) a2
on a1.userid=a2.userid and a1.idnum=a2.idnum;--你如果要改日期再测试的话,只需要把里面的四个日期更改一下即可
---不过快了,先给其他人一个机会,看有没有其他人能用存储过程整出来
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[sequen_time] @startdate DATETIME, @enddate DATETIME
AS
/*
---时间段连续问题(SQL Server终结、优化版)
SELECT * FROM hist WHERE userId='-32396' and effdat<='20070101' and expdat>='20050707' order by effdat;
EXEC sequen_time '2006-07-01','2007-02-24'
*/
BEGIN
--select convert(datetime,'20061125',121) --如果输入(起始时间断)参数为空,则判断近半个月以来的连续用户
IF (ISNULL(@startdate,'')='')
SET @startdate=CONVERT(CHAR(10),GETDATE()-15,121)
IF (ISNULL(@enddate,'')='')
SET @enddate=CONVERT(CHAR(10),GETDATE(),121) DECLARE @count1 INT --用以查看是否有符合给定日期条件的记录
DECLARE @count2 INT --用以查看是否有符合给定日期条件的记录输出
DECLARE @count3 INT --用以查看是否有符合给定日期条件的记录输出 SELECT @count1=COUNT(Id) FROM hist
WHERE effdat<=@enddate AND expdat>=@startdate IF(@count1>0) --如果:有符合给定日期条件的记录
BEGIN CREATE TABLE #DBTempA(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) INSERT INTO #DBTempA(effdat,expdat,userID)
SELECT effdat,expdat,userID FROM hist
WHERE effdat<=@enddate AND expdat>=@startdate --用以保存符合给定时间段要求的用户记录
CREATE TABLE #DBTempB(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) --如果存在直接记录行符合给定日期条件:
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat,expdat,userID FROM #DBTempA
WHERE userid='-32396' and effdat<=CONVERT(CHAR(10),@startdate,121) AND expdat>=CONVERT(CHAR(10),@enddate,121); --删除已经存在直接记录符合指定时间段的用户
DELETE FROM #DBTempA WHERE userID IN (SELECT userID FROM #DBTempB); --记录将要判断的记录行数,用以每次循环后递减,当@count为0时,退出While循环
SELECT @count2=COUNT(Id) FROM #DBTempA; WHILE(@count2>0) --如果还有记录:游标判断间接符合条件的记录
BEGIN DECLARE @userID INT --保存用户名,在游标中将根据用户名选择记录进行循环判断
DECLARE @oldID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @newID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @oldStartdate DATETIME
DECLARE @oldEnddate DATETIME
DECLARE @newStartdate DATETIME
DECLARE @newEnddate DATETIME SELECT TOP 1 @oldId=Id, @oldStartdate=effdat, @oldEnddate=expdat, @userID=userId FROM #DBTempA DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id,effdat, expdat FROM #DBTempA WHERE userId=LTRIM(RTRIM(@userID)) --AND Id<>@oldId
OPEN @MyData
FETCH next FROM @MyData INTO @newID, @newStartdate,@newEnddate
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@newStartdate>=@oldStartdate AND @newStartdate<=@oldEnddate AND @newEnddate>@oldEnddate)
BEGIN
SET @oldEnddate=@newEnddate;
END
IF(@newEnddate<=@oldEnddate AND @newEnddate>=@oldStartdate AND @newStartdate<@oldStartdate)
BEGIN
SET @oldStartdate=@newStartdate;
END
IF(@newStartdate<@oldStartdate AND @newEnddate>@oldEnddate )
BEGIN
SET @oldStartdate=@newStartdate;
SET @oldEnddate=@newEnddate;
END
FETCH next FROM @MyData INTO @newID, @newStartdate, @newEnddate
END
CLOSE @MyData
DEALLOCATE @MyData --如果找到符合条件的记录
IF (@oldStartdate<=@startdate AND @oldEnddate>=@enddate)
BEGIN
INSERT INTO #DBTempB(effdat, expdat, userID) SELECT @oldStartdate,@oldEnddate,@userId;
DELETE FROM #DBTempA WHERE userID=@userID;
SELECT @count2=COUNT(Id) FROM #DBTempA; --重置记数变量
END
ELSE
BEGIN
DELETE FROM #DBTempA WHERE userID=@userID;
SELECT @count2=COUNT(Id) FROM #DBTempA; --重置记数变量
END
END SELECT @count3=COUNT(ID) FROM #DBTempB;
IF(@count3>0)
SELECT * FROM #DBTempB;
ELSE
SELECT '对不起:没有符合要求的给定时间段内连续的记录!' '无符符合要求记录';
DROP TABLE #DBTempA,#DBTempB;
END ELSE
SELECT '对不起:没有您要的给定时间段内连续的记录!' '无符合初始条件记录';END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-----求出连续时间段(日期之前相差一天不算连续)
select
a.userID,a.effdat,min(b.expdat) as expdat
from
(select
userID,effdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and t.effdat>effdat and t.effdat<=expdat)) a
inner join
(select
userID,expdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and t.expdat>=effdat and t.expdat<expdat)) b
on
a.userID=b.userID and a.effdat<=b.expdat
group by
a.userID,a.effdat
order by
a.userID,a.effdat-----求出连续时间段(日期之前相差一天算连续)select
a.userID,a.effdat,min(b.expdat) as expdat
from
(select
userID,effdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and t.effdat>effdat and t.effdat<=dateadd(dd,1,expdat))) a
inner join
(select
userID,expdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and dateadd(dd,1,t.expdat)>=effdat and t.expdat<expdat)) b
on
a.userID=b.userID and a.effdat<=b.expdat
group by
a.userID,a.effdat
order by
a.userID,a.effdat---最后:在此基础上加一个时间区间的条件就OK了!
---呵呵...................谢谢!
-----求出连续时间段(日期之前相差一天不算连续)
select
a.userID,a.effdat,min(b.expdat) as expdat
from
(select
userID,effdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and t.effdat>effdat and t.effdat<=expdat)) a
inner join
(select
userID,expdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and t.expdat>=effdat and t.expdat<expdat)) b
on
a.userID=b.userID and a.effdat<=b.expdat
group by
a.userID,a.effdat
order by
a.userID,a.effdat-----求出连续时间段(日期之前相差一天算连续)select
a.userID,a.effdat,min(b.expdat) as expdat
from
(select
userID,effdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and t.effdat>effdat and t.effdat<=expdat+1)) a
inner join
(select
userID,expdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and t.expdat+1>=effdat and t.expdat<expdat)) b
on
a.userID=b.userID and a.effdat<=b.expdat
group by
a.userID,a.effdat
order by
a.userID,a.effdat---最后:在此基础上加一个时间区间的条件就OK了!
---呵呵...................谢谢!
其实这里判断一个起始日期就够了,一条记录的起始日期不包含在别的时间段里,只要这个起始日期不是最早的那个起始日期,就必然有一条记录的结束日期不包含在别的时间段中
在这里,这个连接条件也并不准确
按这种思路
select id from( select id,
sum(case when
exists(select 1 from hist
where ID=t.ID and t.e1>effdat and t.e1<=expdat+1
)
or t.e1<=date'2005-01-01'
then 0 else 1 end) s ,max(t.e2)m
from (
select id,min(effdat)e1,e2 from(
select id,effdat,max(expdat)e2 from hist
where effdat<=date'2006-01-01' and expdat>=date'2005-01-01'
group by id,effdat)
group by id,e2)t
group by id)
where m>=date'2006-01-01' and s=0这样应该更优化
select id from( select id,
sum(case when
exists(select 1 from hist
where ID=t.ID and t.effdat>effdat and t.effdat<=expdat+1
)
or t.effdat<=date'2005-01-01'
then 0 else 1 end) s ,max(t.expdat)m
from hist t
where t.effdat<=date'2006-01-01' and t.expdat>=date'2005-01-01'
group by id)
where m>=date'2006-01-01' and s=0用connect by再写一个
select id from ( select id,effdat,expdat,row_number()over(partition by id order by effdat,expdat)rn from hist
where effdat<date'2006-1-1' and expdat>date'2005-1-1')t
start with rn=1 and effdat<=date'2005-1-1' connect by rn>prior rn
and id=prior id
and expdat>prior expdat
and effdat<=prior expdat+1
group by id
having max(expdat)>=date'2006-1-1'
--出不了数据
--我的数据如下:EFFDAT EXPDAT USERID
---------- ---------- -----------------
2005-06-14 2005-08-31 -32169
2005-07-01 2005-08-31 -32169
2005-09-01 2005-12-31 -32169
2006-01-01 2006-03-31 -32169
2006-01-01 2006-08-31 -32169
2006-09-01 2006-09-30 -32169
2006-10-01 2006-12-31 -321692005-06-21 2005-06-30 -32203
2005-07-01 2005-12-31 -32203
2005-07-01 2006-03-31 -32203
2005-07-01 2006-06-30 -32203
2006-07-01 2006-12-31 -322032005-07-03 2005-12-31 -32318
2005-07-03 2006-02-28 -32318
2006-03-01 2006-12-31 -32318
2006-03-01 2006-03-31 -323182005-03-01 2005-06-30 -32396
2005-07-07 2006-03-31 -32396
2005-07-07 2005-12-31 -32396
2005-07-07 2006-06-30 -32396
2006-07-01 2006-11-15 -32396
2006-08-24 2006-11-24 -32396
2006-11-11 2007-02-25 -32396
2006-11-25 2006-11-27 -323962005-07-13 2005-12-31 -32501
2006-01-01 2006-03-31 -32501
2006-01-01 2006-12-31 -32501select id from (
select id,effdat,expdat,row_number()over(partition by id order by effdat,expdat)rn from hist
where effdat<date'2006-1-1' and expdat>date'2005-1-1')t
start with rn=1 and effdat<=date'2006-8-25'
connect by rn>prior rn
and id=prior id
and expdat>prior expdat
and effdat<=prior expdat+1
group by id
having max(expdat)>=date'2007-2-15';--按照你给的代码,应该至少用户'-32396'是符合要求的
select id,effdat,expdat,row_number()over(partition by id order by effdat,expdat)rn from hist
where effdat<date'2007-2-15' and expdat>date'2006-8-25')t
start with rn=1 and effdat<=date'2006-8-25'
connect by rn>prior rn
and id=prior id
and expdat>prior expdat
and effdat<=prior expdat+1
group by id
having max(expdat)>=date'2007-2-15';--这样:操作也出不了数据!
--悲哀!
from hist h1, hist h2
where ( (h1.effdat < h2.effdat and h1.expdat < h2.effdat)
or (h1.effdat > h2.expdat and h1.expdat > h2.expdat)
or ( (h1.effdat > h2.effdat and h1.effdat < h2.expdat) and (h1.expdat > h2.effdat and h1.expdat < h2.expdat) )
)
group by h1.id, h1.effdat, h1.expdat
having count(*) = (select count(*) from hist ) - 1
;
请看 #79楼 的!
还可以用Exists去写SQL,
如果数据太多那就比较麻烦,但也可以根据时间段去划分成。