SQL 查询 - 根据条件对连续项目进行分组

Question

我不知道该给这个问题起什么名字。如果标题与问题不符，请见谅。我有生产和计划外维护事件的数据，按设备排序，然后按时刻排序。请参阅下表(请参阅问题底部的文本版本):

数据应根据指示的颜色进行分组，并对持续时间列进行求和。结果见下文。基本上，计划外维护时间应与生产时间相加，直到出现新的计划外维护事件(从而进行分组)。

我已经能够通过使用非常复杂的 RANK() 和连接过程来完成一些事情，但是完成 500 000 条记录需要一个小时，速度非常慢。我需要一些能在 5 分钟内起作用的东西。请注意，我无法使用领先或滞后函数，因为需要执行查询的服务器正在使用旧版本的 SQL (SQL Server 2008)

结果应该是这样的:

任何帮助将不胜感激。

包含大量数据的建表语句可以在这里下载:

https://drive.google.com/file/d/0B8xKLs3osIfcVGRCVGJMQnBYWXc/view?usp=sharing

开始日期 = 时刻

忽略结束日期列

+---------------------+-----------+-----------+-------------------------+
| Moment              | Duration  | Equipment | DowntimeType            |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 08:34:03 | 2.734444  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 11:39:26 | 0.015833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 11:41:23 | 0.4925    | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 12:10:56 | 0.679444  | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 12:51:42 | 0.628888  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 15:23:48 | 0.650833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 16:05:19 | 3.341111  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 19:44:01 | 7.292777  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 03:18:15 | 5.954722  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 09:50:54 | 3.899722  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 19:33:11 | 1.760277  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 21:18:48 | 0.637222  | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 21:57:02 | 3.109722  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-16 01:14:15 | 4.128611  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-16 18:33:01 | 0.004166  | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-16 19:19:38 | 2.580833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 01:23:56 | 0.111388  | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 01:30:37 | 0.293333  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 01:48:13 | 0.99      | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 03:26:10 | 3.805833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 07:14:49 | 1.435833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 01:18:43 | 1.283611  | DT63      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 02:47:50 | 0.224166  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 03:17:09 | 7.085277  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 11:12:14 | 2.519722  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 18:36:54 | 3.239166  | DT63      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-29 03:20:04 | 1.735833  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-29 05:07:52 | 8.631944  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-29 23:53:44 | 6.074444  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-30 23:04:51 | 14.720555 | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-12-02 01:06:50 | 0.001111  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-12-02 01:07:28 | 4.540277  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+

Answer 1

试试这个分组

select min(Moment), Equipment, sum(duration)
from (
  select *,
    case DowntimeType when 'Unscheduled Maintenance' 
      then row_number() over(partition by Equipment, DowntimeType order by Moment) 
      else row_number() over(partition by Equipment order by Moment) - row_number() over(partition by Equipment, DowntimeType order by Moment) end r
   from myTable
) t
where r > 0 -- must start with 'Unscheduled Maintenance' 
group by Equipment, r
order by Equipment, r