依据时间段分组统计

2013-07-16

根据时间段分组统计ta(用户等级日志表)user_id user_level log_datetime122013-06-26 10:54:22132013-06-2

根据时间段分组统计
ta(用户等级日志表)
user_id user_level log_datetime
1       2          2013-06-26 10:54:22
1       3          2013-06-26 10:54:35
1       4          2013-06-26 10:54:47
1       5          2013-06-26 10:55:07
......................................
tb(用户银币使用表)
user_id  silver_use  log_datetime
1        40          2013-06-26 10:54:22
1        60          2013-06-26 10:54:25
1        20          2013-06-26 10:54:35
1        30          2013-06-26 10:54:39
1        80          2013-06-26 10:54:47
1        70          2013-06-26 10:54:51
1        80          2013-06-26 10:55:05
........................................
统计用户在升级过程中各消耗多少银币如
user_id user_level  silver_use
1       2~3         120
1       3~4         110
1       4~5         150
...................................

[解决办法]

with ta(user_id,user_level,log_datetime)as(
select 1,2,'2013-06-26 10:54:22' union
select 1,3,'2013-06-26 10:54:35' union
select 1,4,'2013-06-26 10:54:47' union
select 1,5,'2013-06-26 10:55:07')

,tb(user_id,silver_use,log_datetime)as(
select 1,40,'2013-06-26 10:54:22' union
select 1,60,'2013-06-26 10:54:25' union
select 1,20,'2013-06-26 10:54:35' union
select 1,30,'2013-06-26 10:54:39' union
select 1,80,'2013-06-26 10:54:47' union
select 1,70,'2013-06-26 10:54:51' union
select 1,80,'2013-06-26 10:55:05')
select a.user_id,user_level,SUM(silver_use) from (
select a.user_id,
user_level=CONVERT(varchar,a.user_level)+'~'+CONVERT(varchar,b.user_level),
a.log_datetime stime,b.log_datetime etime
from ta a,ta b where b.user_level-a.user_level=1)a
left join tb on tb.log_datetime >stime and tb.log_datetime<=etime
group by a.user_id,user_level

TB表的第一条数据应该不能算在2级里吧...应该是1~2的吧
[解决办法]
select a.user_id, '2~3' as user_level,sum(b.silver_use) as silver_use
from ta a,tb b
where a.user_level>=2 and a.user_level <=3 and log_datetime in (select log_datetime from ta where user_level>=2 and user_level <=3)
group by a.user_id
union all
select a.user_id, '3~4' as user_level,sum(b.silver_use) as silver_use
from ta a,tb b
where a.user_level>3 and a.user_level <=4 and log_datetime in (select log_datetime from ta where user_level>3 and user_level <=4)
group by a.user_id
union all
select a.user_id, '4~5' as user_level,sum(b.silver_use) as silver_use
from ta a,tb b
where a.user_level>=4 and a.user_level <=5 and log_datetime in (select log_datetime from ta where user_level>=4 and user_level <=5)
group by a.user_id
[解决办法]

create table ta
(user_id int, user_level int, log_datetime datetime)

insert into ta
  select 1, 2, '2013-06-26 10:54:22' union all
  select 1, 3, '2013-06-26 10:54:35' union all
  select 1, 4, '2013-06-26 10:54:47' union all
  select 1, 5, '2013-06-26 10:55:07'

create table tb
(user_id int, silver_use int, log_datetime datetime)

insert into tb
  select 1, 40, '2013-06-26 10:54:22' union all
  select 1, 60, '2013-06-26 10:54:25' union all
  select 1, 20, '2013-06-26 10:54:35' union all
  select 1, 30, '2013-06-26 10:54:39' union all
  select 1, 80, '2013-06-26 10:54:47' union all
  select 1, 70, '2013-06-26 10:54:51' union all
  select 1, 80, '2013-06-26 10:55:05'


select a.user_id,
       rtrim(a.user_level)+'~'+rtrim(b.user_level) 'user_level',
       (select sum(silver_use) from tb c
        where c.user_id=a.user_id
        and c.log_datetime>a.log_datetime and c.log_datetime<=b.log_datetime) 'silver_use'
from ta a
inner join ta b on a.user_id=b.user_id and a.user_level=b.user_level-1

/*
user_id     user_level                silver_use
----------- ------------------------- -----------
1           2~3                       80
1           3~4                       110
1           4~5                       150

(3 row(s) affected)
*/

热点排行

SQL Server

依据时间段分组统计