SQL基于时间线的统计

Question

现有如下的表(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如何实现呢？

Answer 1

假设你的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

Answer 2

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