求助:关于sql如何统计时间的问题
三、现在我们假设应用计时分为app应用和web应用,需要考虑如下几个方面:
(1)多时间段(2)表中有冗杂数据
(3)用户是在web端和app端都登陆,这种类型的重复时间段只能取其一
存在数据:
存在表:应用启动时间统计表Applic_Sessions:
字段:applic platform start_time end_time status rowid
X web 11:30 18:33 1 1
X app 11:10 17:50 1 2
X app 17:55 19:55 1 3
X app 20:31 22:31 1 4
X web 02:01 02:40 1 5
利用sql语句的方式获取到开始时间和结束时间,timestampdiff( )相减得到。
(1)取出X用户两平台中启动的最小时间
(2)取出X用户两平台中结束的最大时间
按照以上步骤,这样会带来一个问题:如rowid 为1和5的记录,相减所得结果明显大于实际结果。
尝试方案1:将不同平台的数据区分开单独计数:
(1)select t.* from Applic_Sessions t where t.platform = “web”;
视图:Web_View
applic platform start_time end_time status rowid
X web 11:30 18:33 1 1
X web 02:01 02:40 1 2
select sum(timestampdiff(second,start_time,end_time)) from Web_View;
(2)select t.* from Applic_Sessions t where t.platform = “app”;
视图:App_View
applic platform start_time end_time status rowid
X app 11:10 17:50 1 1
X app 17:55 19:55 1 2
X app 20:31 22:31 1 3
select sum(timestampdiff(second,start_time,end_time)) from Web_View;
这样求取出单个平台的使用时长,这种适用于:app使用时长可以换取双倍积分 等需求。但是至于中间的重复时间段更加干不掉了,这种方法pass。
方案2:
视图:Applic_Sessions
applic platform start_time end_time status rowid
Xweb 11:30 18:33 1 1
Xapp 11:10 17:50 1 2
Xapp 17:55 19:55 1 3
X app 20:31 22:31 1 4
X web 02:01 02:40 1 5
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
11:30............................................18:33(web)
11:10....................17:50(app)
17:55.................19:55(app)
02:01....02:40 20:31.......22:31
整理一下数据,发现其中的难点就是:web的使用时长段与app的使用时间段夹杂在一起,其他正常数据横向合并就成,那第一步就是把这些夹杂数据单独拎出来,然后取出最小启动值和最大结束值,相减就好拉:
写下这个sql,越看越别扭,能行么---显然不行
select t.* from Applic_Sessions t where
t.start_time between (select min(t.start_time) from Applic_Sessions t)
and (select max(t.end_time) from Applic_Sessions t)
and t.end_time between t.start_time and t.end_time;
至此,卡住了。。。
绞尽了脑汁,搞不定啊,有哪位大神可以帮帮忙啊,谢谢了