sql - 以时间戳的日期值为条件的二进制指标

Question

在 SQL Server 中，如何创建一个新列，该列value > 0.5在给定的一天只指示一次？

例如，高于 0.51 的值一天可能出现多次，但是在 24 小时内第一次出现高于 0.51 的值，我想指出这一点。在第二次出现高于 0.51 的值后，该指标必须为 0。

+---+-------------------+-----+
| id|               date|value|
+---+-------------------+-----+
| J1|2016-10-01 11:45:30| 0.49|
| J1|2016-10-01 12:30:30| 0.51|
| J1|2016-10-01 13:20:00| 0.49|
| J1|2016-10-02 11:45:30| 0.44|
| J1|2016-10-03 11:30:30| 0.52|
| J1|2016-10-03 12:20:00| 0.49|
| J9|2016-10-06 08:35:00| 0.51|
| J9|2016-10-06 09:20:00| 0.44|
| J9|2016-10-07 08:35:00| 0.44|
| J9|2016-10-07 09:20:00| 0.53|
+---+-------------------+-----+

所需的数据框：

+---+-------------------+-----+----------+
| id|               date|value| indicator|
+---+-------------------+-----+----------+
| J1|2016-10-01 11:45:30| 0.49|         0|
| J1|2016-10-01 12:30:30| 0.51|         1|
| J1|2016-10-01 13:20:00| 0.52|         0|
| J1|2016-10-01 14:45:30| 0.44|         0|
| J1|2016-10-02 11:30:30| 0.99|         1|
| J1|2016-10-02 12:20:00| 0.40|         0|
| J1|2016-10-02 08:35:00| 0.98|         0|
| J1|2016-10-02 09:20:00| 0.97|         0|
| J9|2016-10-07 08:35:00| 0.51|         1|
| J9|2016-10-07 09:20:00| 0.51|         0|
+---+-------------------+-----+----------+

标签： sqlsql-serverdatetimeconditional-statements

Answer 1

您可以使用窗口函数：

select t.*,
    case when value > 0.5
        and sum(case when value > 0.5 then 1 else 0 end) over(partition by id, convert(date, date) order by date) = 1
    then 1 else 0 end as indicator
from mytable t