时间戳

首页 > 解决方案 > 根据过去一小时内另一个标志的值设置一个标志

sql - 根据过去一小时内另一个标志的值设置一个标志

问题描述

我有一张设计如下的桌子：

+------+-------------------------+-------------+
| Shop | Date                    | SafetyEvent |
+------+-------------------------+-------------+
| 1    | 2018-06-25 10:00:00.000 |      0      |
| 1    | 2018-06-25 10:30:00.000 |      1      |
| 1    | 2018-06-25 10:45:00.000 |      0      |
| 2    | 2018-06-25 11:00:00.000 |      0      |
| 2    | 2018-06-25 11:30:00.000 |      0      |
| 2    | 2018-06-25 11:45:00.000 |      0      |
| 3    | 2018-06-25 12:00:00.000 |      1      |
| 3    | 2018-06-25 12:30:00.000 |      0      |
| 3    | 2018-06-25 12:45:00.000 |      0      |
+------+-------------------------+-------------+

基本上在每家商店，我们都会跟踪维修的日期/时间，并在发生安全事件时进行标记。我想添加一个额外的列来跟踪每个商店在过去 8 小时内是否发生了安全事件。最终结果将是这样的：

+------+-------------------------+-------------+-------------------+
| Shop | Date                    | SafetyEvent | SafetyEvent8Hours |
+------+-------------------------+-------------+-------------------+
| 1    | 2018-06-25 10:00:00.000 |      0      |        0          |
| 1    | 2018-06-25 10:30:00.000 |      1      |        1          |
| 1    | 2018-06-25 10:45:00.000 |      0      |        1          |
| 2    | 2018-06-25 11:00:00.000 |      0      |        0          |
| 2    | 2018-06-25 11:30:00.000 |      0      |        0          |
| 2    | 2018-06-25 11:45:00.000 |      0      |        0          |
| 3    | 2018-06-25 12:00:00.000 |      1      |        1          |
| 3    | 2018-06-25 12:30:00.000 |      0      |        1          |
| 3    | 2018-06-25 12:45:00.000 |      0      |        1          |
+------+-------------------------+-------------+-------------------+

我试图使用 DATEDIFF 但无法弄清楚如何让它发生在每一行。

标签： sqlsql-server

解决方案

这不是特别有效，但您可以使用apply或相关子查询：

select t.*, t8.SafetyEvent8Hours
from t apply
     (select max(SafetyEvent) as SafetyEvent8Hours
      from t t2
      where t2.shop = t.shop and
            t2.date <= t.date and
            t2.date > dateadd(hour, -8, t.date)
     ) t8;

如果您可以依赖每 15 分钟记录一次事件，那么更有效的方法是使用窗口函数：

select t.*,
       max(SafetyEvent) over (partition by shop order by date rows between 31 preceding and current row) as SafetyEvent8Hours
from t

推荐阅读