基于来自多行SQL Server的标志的时间总和

Question

我很难执行查询之一。我需要根据同一张表中的标志找到点火时间。表格如下

UnitId      eventtime               ign
----------- ----------------------- -----
356         2011-05-04 10:41:00.000 1
356         2011-05-04 10:42:00.000 1
356         2011-05-04 10:43:00.000 1
356         2011-05-04 10:45:00.000 1
356         2011-05-04 10:47:00.000 1
356         2011-05-04 10:48:00.000 0
356         2011-05-04 11:14:00.000 1
356         2011-05-04 11:14:00.000 1
356         2011-05-04 11:15:00.000 1
356         2011-05-04 11:15:00.000 1
356         2011-05-04 11:15:00.000 1
356         2011-05-04 11:16:00.000 0
356         2011-05-04 11:16:00.000 0
356         2011-05-04 11:16:00.000 0
356         2011-05-04 14:49:00.000 1
356         2011-05-04 14:50:00.000 1
356         2011-05-04 14:50:00.000 1
356         2011-05-04 14:51:00.000 1
356         2011-05-04 14:52:00.000 0
356         2011-05-04 14:52:00.000 0
356         2011-05-04 20:52:00.000 0

在此，Ign标志将确定ignition_on和iginition_off时间。所以上表我们可以得到点火对

2011-05-04 10:41:00.000 - 2011-05-04 10:48:00.000
2011-05-04 11:14:00.000 - 2011-05-04 11:16:00.000
2011-05-04 14:49:00.000 - 2011-05-04 14:52:00.000

因此，从上面的对来看，我可以说我的设备运行了7 + 2 + 3 = 12分钟。我不希望上述结果是对的，仅作为示例。我的目的是得到12分钟的结果。

我如何使用单个查询来实现它，现在我正在使用CURSOR循环，但是这件事需要花费更多的时间才能使用多天和多个单元。无论如何，在没有CURSOR的情况下我可以实现它吗？

Answer 1

如果还有其他标准可以区分具有相同ign值的连续事件序列，则可以从每个序列中获取ign=1最早的事件，并将其与相应ign=0序列的最早事件联系起来。

可以添加这样的标准，如下所示。我将首先发布该解决方案，然后解释其工作原理。

首先，设置：

DECLARE @atable TABLE (
  Id int IDENTITY,
  UnitId int,
  eventtime datetime,
  ign bit
);
INSERT INTO @atable (UnitId, eventtime, ign)
SELECT 356, '2011-05-04 10:41:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:42:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:43:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:45:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:47:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:48:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 11:14:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:14:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:15:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:15:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:15:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:16:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 11:16:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 11:16:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 14:49:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 14:50:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 14:50:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 14:51:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 14:52:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 14:52:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 20:52:00.000', 0;

现在查询：

WITH
marked AS (
  SELECT
    *,
    Grp = ROW_NUMBER() OVER (PARTITION BY UnitId ORDER BY eventtime) -
     ROW_NUMBER() OVER (PARTITION BY UnitId, ign ORDER BY eventtime)
  FROM @atable
),
ranked AS (
  SELECT
    *,
    seqRank = DENSE_RANK() OVER (PARTITION BY UnitId, ign ORDER BY Grp),
    eventRank = ROW_NUMBER() OVER (PARTITION BY UnitId, ign, Grp ORDER BY eventtime)
  FROM marked
),
final AS (
  SELECT
    s.UnitId,
    EventStart = s.eventtime,
    EventEnd   = e.eventtime
  FROM ranked s
    INNER JOIN ranked e ON s.UnitId = e.UnitId AND s.seqRank = e.seqRank
  WHERE s.ign = 1
    AND e.ign = 0
    AND s.eventRank = 1
    AND e.eventRank = 1
)
SELECT *
FROM final
ORDER BY
  UnitId,
  EventStart

这就是它的工作方式。

在marked公用表表达式（CTE）为我们提供了我说的是在开始的附加标准。它产生的结果集如下所示：

Id  UnitId  eventtime                ign  Grp
--  ------  -----------------------  ---  ---
1   356     2011-05-04 10:41:00.000  1    0
2   356     2011-05-04 10:42:00.000  1    0
3   356     2011-05-04 10:43:00.000  1    0
4   356     2011-05-04 10:45:00.000  1    0
5   356     2011-05-04 10:47:00.000  1    0
6   356     2011-05-04 10:48:00.000  0    5
7   356     2011-05-04 11:14:00.000  1    1
8   356     2011-05-04 11:14:00.000  1    1
9   356     2011-05-04 11:15:00.000  1    1
10  356     2011-05-04 11:15:00.000  1    1
11  356     2011-05-04 11:15:00.000  1    1
12  356     2011-05-04 11:16:00.000  0    10
13  356     2011-05-04 11:16:00.000  0    10
14  356     2011-05-04 11:16:00.000  0    10
15  356     2011-05-04 14:49:00.000  1    4
16  356     2011-05-04 14:50:00.000  1    4
17  356     2011-05-04 14:50:00.000  1    4
18  356     2011-05-04 14:51:00.000  1    4
19  356     2011-05-04 14:52:00.000  0    14
20  356     2011-05-04 14:52:00.000  0    14
21  356     2011-05-04 20:52:00.000  0    14

您可以自己了解如何ign通过其自己的键轻松地将具有相同事件的每个事件序列与其他事件区分开(UnitId, ign, Grp)。因此，现在我们可以对每个序列以及序列中的每个事件进行排名，这就是rankedCTE所做的。它产生以下结果集：

Id  UnitId  eventtime                ign  Grp  seqRank  eventRank
--  ------  -----------------------  ---  ---  -------  ---------
1   356     2011-05-04 10:41:00.000  1    0    1        1
2   356     2011-05-04 10:42:00.000  1    0    1        2
3   356     2011-05-04 10:43:00.000  1    0    1        3
4   356     2011-05-04 10:45:00.000  1    0    1        4
5   356     2011-05-04 10:47:00.000  1    0    1        5
6   356     2011-05-04 10:48:00.000  0    5    1        1
7   356     2011-05-04 11:14:00.000  1    1    2        1
8   356     2011-05-04 11:14:00.000  1    1    2        2
9   356     2011-05-04 11:15:00.000  1    1    2        3
10  356     2011-05-04 11:15:00.000  1    1    2        4
11  356     2011-05-04 11:15:00.000  1    1    2        5
12  356     2011-05-04 11:16:00.000  0    10   2        1
13  356     2011-05-04 11:16:00.000  0    10   2        2
14  356     2011-05-04 11:16:00.000  0    10   2        3
15  356     2011-05-04 14:49:00.000  1    4    3        1
16  356     2011-05-04 14:50:00.000  1    4    3        2
17  356     2011-05-04 14:50:00.000  1    4    3        3
18  356     2011-05-04 14:51:00.000  1    4    3        4
19  356     2011-05-04 14:52:00.000  0    14   3        1
20  356     2011-05-04 14:52:00.000  0    14   3        2
21  356     2011-05-04 20:52:00.000  0    14   3        3

您可以看到，借助，ign=1现在可以将ign=0序列与序列匹配seqRank。并且仅从每个序列中选择最早的事件（按过滤eventRank=1），我们将获得所有ign=1序列的开始时间和结束时间。因此，finalCTE的结果是：

UnitId  EventStart               EventEnd
------  -----------------------  -----------------------
356     2011-05-04 10:41:00.000  2011-05-04 10:48:00.000
356     2011-05-04 11:14:00.000  2011-05-04 11:16:00.000
356     2011-05-04 14:49:00.000  2011-05-04 14:52:00.000

显然，如果最后一个ign=1序列后面没有ign=0事件，则不会在最终结果中显示它，因为使用上述方法，最后一个ign=1序列将没有匹配的ign=0序列。

在一种可能的情况下，此查询将无法正常使用。这是事件列表以ign=0事件而不是开头的时间ign=1。如果确实可行，则只需将以下过滤器添加到rankedCTE：

WHERE NOT (ign = 0 AND Grp = 0)
-- Alternatively: WHERE ign <> 0 OR Grp <> 0

利用以下事实：的第一个值Grp将始终为0。因此，如果0将分配给具有ign=0的事件，则应排除这些事件。