SQL基于時間線的統(tǒng)計

你好，我看到你回答一個關(guān)于網(wǎng)站中英文切換的問題，你做過這樣的網(wǎng)站嗎？能幫幫我嗎？

假設(shè)你的id都是連續(xù)的，且發(fā)生的動作也是連續(xù)的，那么比較復(fù)雜就是某個status跨天問題。
比如id2的stop就要分兩段，一段是10號的時間，一段是11號也有部分時間，下面是我寫的一個例子，如果的數(shù)據(jù)庫版本高于等于2012，可以使用LAG/LEAD簡化語句，但仍然面臨第一條和最后一條臨界怎處理的問題。
我這里對于第一條起點前的時間認(rèn)為是在另外一個狀態(tài)，比如我的例子是熊0點20分開始run，那么之前20分認(rèn)為是stop，結(jié)尾沒有這么處理.統(tǒng)計時間以分鐘為單位
11號250的來源：在2017-10-11 02:00:00 之前兩小時認(rèn)為是run，在2017-10-11 02:20:00至2017-10-11 04:30:00認(rèn)為是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