sql server“桥接”数据

Question

我有以下mssql表：

+-------------------------+---+---+---+---+---+
|          date           | A | B | C | D | E |
+-------------------------+---+---+---+---+---+
| 2017-02-02 00:00:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:01:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:02:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:03:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:04:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:05:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:06:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:07:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:08:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:09:00.000 | 1 | 0 | 0 | 1 | 0 |
+-------------------------+---+---+---+---+---+

如果D的状态小于5分钟，我需要编写一个查询，将D列上的0更改为1s。换句话说，如果状态0小于10分钟，我需要在0的末端“桥接”连续的两个1s。

是否可以使用to ( SERVER，2014)执行此操作？谢谢。

Example1：

+-------------------------+---+---+---+---+---+
|          date           | A | B | C | D | E |
+-------------------------+---+---+---+---+---+
| 2017-02-02 00:00:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:01:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:02:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:03:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:04:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:05:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:06:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:07:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:08:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:09:00.000 | 1 | 0 | 0 | 1 | 0 |
+-------------------------+---+---+---+---+---+

查询应该返回

+-------------------------+---+---+---+---+---+
|          date           | A | B | C | D | E |
+-------------------------+---+---+---+---+---+
| 2017-02-02 00:00:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:01:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:02:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:03:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:04:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:05:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:06:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:07:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:08:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:09:00.000 | 1 | 0 | 0 | 1 | 0 |
+-------------------------+---+---+---+---+---+

例2：

+-------------------------+---+---+---+---+---+
|          date           | A | B | C | D | E |
+-------------------------+---+---+---+---+---+
| 2017-02-02 00:00:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:01:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:02:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:03:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:04:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:05:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:06:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:07:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:08:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:09:00.000 | 1 | 0 | 0 | 1 | 0 |
+-------------------------+---+---+---+---+---+

查询应该返回

+-------------------------+---+---+---+---+---+
|          date           | A | B | C | D | E |
+-------------------------+---+---+---+---+---+
| 2017-02-02 00:00:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:01:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:02:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:03:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:04:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:05:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:06:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:07:00.000 | 1 | 0 | 0 | 0 | 0 |
| 2017-02-02 00:08:00.000 | 1 | 0 | 0 | 1 | 0 |
| 2017-02-02 00:09:00.000 | 1 | 0 | 0 | 1 | 0 |
+-------------------------+---+---+---+---+---+

Answer 1

UPDATE --您可能从最初的版本中获得了这个想法，但有时我使用了错误的聚合函数；我想我现在已经把它解开了。

所以..。如果某一行的值为0，但最近的一行为1，而后一行最早为1的时间小于10分钟，则希望将该行的值更改为1。在所有其他情况下，您都保留该值。对吗？

最近带有1的行的时间可以表示为max(case when D = 1 then date end) over (order by date rows unbounded preceding)。

同样，最早的带有1的后继行的时间可以表示为min(case when D = 1 then date end) over (order by date rows unbounded following)。

找出它们之间的间隔；如果日期都对齐为偶数分钟，那么只需使用datediff

datediff(minute, max(case when D=1 then date end) over (order by date rows unbounded preceding),
                 min(case when D=1 then date end) over (order by date rows unbounded following))

然后应用案例逻辑。

case when -- the above expression
          < 10 then 1 else D end