【100分】求根据时间分组的问题。
数据格式如下：
[sign] [date]
1 2012-01-01
1 2012-02-06
1 2012-05-03
1 2012-06-11
1 2012-09-01
2 2012-02-03
2 2012-04-19
2 2012-04-23
需求是，同一个标记，从最初的开始时间算起，
3个月以内同一个标记的算一组，下个日期依然按着个要求算，并标记出现次数
结果为
[sign] [date] [n]
1 2012-01-01 2
1 2012-05-03 2
1 2012-09-01 1
2 2012-02-03 3
求救
，谢谢！
[解决办法]
with a1 ([sign],[date]) as
(
select 1,'2012-01-01'
union all
select 1,'2012-02-06'
union all
select 1,'2012-05-03'
union all
select 1,'2012-06-11'
union all
select 1,'2012-09-01'
union all
select 2,'2012-02-03'
union all
select 2,'2012-04-19'
union all
select 2,'2012-04-23'
)
,a2 as
(
select [sign],min([date]) [date_min] from a1 group by [sign]
)
,a3 as
(
select a1.*,ceiling(case when DATEDIFF(day,a2.[date_min],a1.[date])=0 then 1 else
DATEDIFF(day,a2.[date_min],a1.[date])/90. end ) ms
from a1
inner join a2 on a1.[sign]=a2.[sign]
)
,a4 as
(
select [sign],ms,min(date) date,count(*) qty
from a3
group by [sign],ms
)
select [sign],date,qty from a4 order by 1,2
[解决办法]
对于每一行求出 与该行对应该id的最小日期相差的天数 ，如果天数除以90得到的数相同，则为一组
create table test
(
id int,
dt datetime
);
insert into test
select 1,'2012-01-01'
union all select 1, '2012-02-06'
union all select 1, '2012-05-03'
union all select 1, '2012-06-11'
union all select 1, '2012-09-01'
union all select 2, '2012-02-03'
union all select 2, '2012-04-19'
union all select 2, '2012-04-23'
;with cte as (select id,MIN(dt) as dt from test group by id)
select A.id,MIN(A.dt) as dt,COUNT(1) as N
from test as A
inner join cte as B on A.id=B.id
group by A.id,datediff(DAY,B.dt,A.dt)/90
order by A.id,dt;
drop table test;
id dt N
----------- ----------------------- -----------
1 2012-01-01 00:00:00.000 2
1 2012-05-03 00:00:00.000 2
1 2012-09-01 00:00:00.000 1
2 2012-02-03 00:00:00.000 3
(4 行受影响)
