SQL基于时间线的统计

Feng_Yu
  • 9.4k

现有如下的表(SQL Server):

table: status_timeline

id date status
1 2017-10-10 00:00:00 run
2 2017-10-10 01:00:00 stop
3 2017-10-10 02:10:00 run
4 2017-10-11 00:00:00 stop
... ... ...

每条记录一个时间戳,记录当前的status,现在统计一段时间内(如前一天)run和stop的总时长,在SQL Server如何实现呢?

回复
阅读 2.5k
2 个回答
✓ 已被采纳

假设你的id都是连续的,且发生的动作也是连续的,那么比较复杂就是某个status跨天问题。
比如id2的stop就要分两段,一段是10号的时间,一段是11号也有部分时间,下面是我写的一个例子,如果的数据库版本高于等于2012,可以使用LAG/LEAD简化语句,但仍然面临第一条和最后一条临界怎处理的问题。
我这里对于第一条起点前的时间认为是在另外一个状态,比如我的例子是熊0点20分开始run,那么之前20分认为是stop,结尾没有这么处理.统计时间以分钟为单位
11号250的来源:在2017-10-11 02:00:00 之前两小时认为是run,在2017-10-11 02:20:00至2017-10-11 04:30:00认为是run所有是250分钟


;WITH status_timeline(id,[Date],status)AS(
   SELECT 1,CONVERT(DATETIME,'2017-10-10 00:20:00'),'run' UNION
   SELECT 2,CONVERT(DATETIME,'2017-10-10 01:00:00'),'stop' UNION
   SELECT 3,CONVERT(DATETIME,'2017-10-10 02:10:00'),'run' UNION
   SELECT 4,CONVERT(DATETIME,'2017-10-11 02:00:00'),'stop' UNION
   SELECT 5,CONVERT(DATETIME,'2017-10-11 02:20:00'),'run' UNION
   SELECT 6,CONVERT(DATETIME,'2017-10-11 04:30:00'),'stop' 
)
SELECT t.Date,c.status,SUM(c.span) FROM (
    SELECT st.id,CONVERT(DATE,st.Date) AS [Date],
         DATEDIFF(MINUTE,
             CASE WHEN pst.Date IS NULL OR DATEDIFF(d,pst.Date,st.Date)=1 THEN CONVERT(DATE,st.Date) ELSE pst.Date END 
            ,st.Date 
       ) AS Span1
       ,DATEDIFF(MINUTE,
            st.Date 
           ,CASE WHEN nst.id IS NOT NULL AND  DATEDIFF(d,st.Date,nst.Date)=1 THEN CONVERT(DATETIME, CONVERT(DATE,nst.Date)) ELSE st.Date END-- AS To2Time
       ) AS Span2
        ,st.status AS status2, CASE WHEN st.status='run' THEN 'stop' WHEN st.status='stop' THEN 'run' END AS status1  
    FROM status_timeline AS st
    LEFT JOIN status_timeline AS pst ON st.id=pst.id+1 
    LEFT JOIN status_timeline AS nst ON st.id=nst.id-1
) AS t
CROSS APPLY(VALUES(t.Span1,t.status1),(t.Span2,t.status2))c(span,status)
WHERE c.span>0
GROUP BY t.Date,c.status
ORDER BY t.Date,c.status
Date status
2017-10-10 run 1350
2017-10-10 stop 90
2017-10-11 run 250
2017-10-11 stop 20
陈学文
  • 1
新手上路,请多包涵

你好,我看到你回答一个关于网站中英文切换的问题,你做过这样的网站吗?能帮帮我吗?

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏