查询以将时间点更改为时间跨度

Question

我有一个简单的数据集，有3个属性。看起来像这样

Time            flag          count
9/10/2017 1:00  Blue          0
9/10/2017 1:05  Blue          1
9/10/2017 1:10  Blue          2
9/10/2017 2:00  Blue          1
9/14/2017 1:00  Red           0
9/14/2017 2:00  Red           1
9/14/2017 2:30  Red           0
9/15/2017 1:00  Green         1
9/15/2017 1:10  Green         0
9/15/2017 2:00  Green         1
9/15/2017 2:50  Green         2
9/15/2017 2:55  Green         1
9/15/2017 3:00  Green         0
9/15/2017 3:30  Green         1
9/15/2017 4:00  Green         0

我需要一个 sql 查询，给我以下输出

total duration(minute)  flag    number of times >=1 duration when it's equal 
                                                        or higher than 1
60                      Blue          3              (10.09.2017  02:00:00 - 10.09.2017  01:05:00 )=55
90                      Red           1              (14.09.2017  02:30:00 -14.09.2017  02:00:00)=30
180                    Green          5              (15.09.2017  03:00:00 - 15.09.2017  01:00:00) + (15.09.2017  04:00:00 - 15.09.2017  03:30:00)=120+30=150

感谢您的帮助，谢谢

Answer 1

下面显示了如何窗口数据。时间跨度是难题的另一部分。

SELECT flag, SUM(durationSinceLast) AS totalRelevantDuration, SUM(Count) AS relevantCount
FROM(
 SELECT flag, currentTime - prevTime AS durationSinceLast, Count
 FROM(
  SELECT flag, 
       LAG(Time) OVER (PARTION BY flag ORDER BY Time) AS prevTime,
       Time AS currentTime, 
       Count
  FROM T
 )x
)y
WHERE durationSinceLast > 1
GROUP BY flag

每条评论的修订

SELECT flag, 
     MIN(Time) AS Begin,
     MAX(Time) AS End,
     SUM(1) Count --count 1 per record
FROM T
WHERE Count >= 1
GROUP BY flag