求助：关于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;

至此，卡住了。。。

 

绞尽了脑汁，搞不定啊，有哪位大神可以帮帮忙啊，谢谢了

转载于:https://my.oschina.net/u/3771617/blog/1627832