根据条件将时间序列数据转换为事件

bxpogfeg 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(260)

我有一个sql server数据集，如下所示

+-------+---------------------+----------+---------+--+
| RowID | SensorTime          | SensorId | Reading |  |
+=======+=====================+==========+=========+==+
| 1     | 6/22/2020   0:00:06 | S1       | 5000    |  |
+-------+---------------------+----------+---------+--+
| 2     | 6/22/2020   0:00:12 | S1       | 5001    |  |
+-------+---------------------+----------+---------+--+
| 3     | 6/22/2020   0:00:19 | S1       | 5001    |  |
+-------+---------------------+----------+---------+--+
| 4     | 6/22/2020   0:00:25 | S1       | 5000    |  |
+-------+---------------------+----------+---------+--+
| 5     | 6/22/2020   0:06:54 | S1       | 5401    |  |
+-------+---------------------+----------+---------+--+
| 6     | 6/22/2020   0:07:00 | S1       | 5401    |  |
+-------+---------------------+----------+---------+--+
| 7     | 6/22/2020   0:00:19 | S4       | 5001    |  |
+-------+---------------------+----------+---------+--+
| 8     | 6/22/2020   0:00:25 | S4       | 5000    |  |
+-------+---------------------+----------+---------+--+
| 9     | 6/22/2020   0:00:36 | S2       | 5842    |  |
+-------+---------------------+----------+---------+--+
| 10    | 6/22/2020   0:00:42 | S2       | 5137    |  |
+-------+---------------------+----------+---------+--+
| 11    | 6/22/2020   0:00:49 | S2       | 5402    |  |
+-------+---------------------+----------+---------+--+
| 12    | 6/22/2020   0:17:32 | S2       | 5001    |  |
+-------+---------------------+----------+---------+--+
| 13    | 6/22/2020   0:17:39 | S2       | 5000    |  |
+-------+---------------------+----------+---------+--+
| 14    | 6/22/2020   0:17:45 | S2       | 5001    |  |
+-------+---------------------+----------+---------+--+
| 15    | 6/22/2020   0:00:49 | S2       | 5402    |  |
+-------+---------------------+----------+---------+--+
| 16    | 6/22/2020   0:00:06 | S3       | 5000    |  |
+-------+---------------------+----------+---------+--+
| 17    | 6/22/2020   0:00:12 | S3       | 5000    |  |
+-------+---------------------+----------+---------+--+
| 18    | 6/22/2020   0:00:18 | S3       | 5000    |  |
+-------+---------------------+----------+---------+--+
| 19    | 6/22/2020   0:04:53 | S3       | 5347    |  |
+-------+---------------------+----------+---------+--+
| 20    | 6/22/2020   0:04:59 | S3       | 5566    |  |
+-------+---------------------+----------+---------+--+

这是一组传感器读数，每个读数都有一个时间戳。
基于阅读随时间的变化，我想创建事件的开始和结束时间的基础上阅读时间
所需输出：

+-------+----------+---------------------+---------------------+--+
| Event | SensorId | Start Event         | End Event           |  |
+=======+==========+=====================+=====================+==+
| 1     | S1       | 6/22/2020   0:00:06 | 6/22/2020   0:00:25 |  |
+-------+----------+---------------------+---------------------+--+
| 2     | S1       | 6/22/2020   0:06:54 | 6/22/2020   0:07:00 |  |
+-------+----------+---------------------+---------------------+--+
| 3     | S4       | 6/22/2020   0:00:19 | 6/22/2020   0:00:25 |  |
+-------+----------+---------------------+---------------------+--+
| 4     | S2       | 6/22/2020   0:00:36 | 6/22/2020   0:00:49 |  |
+-------+----------+---------------------+---------------------+--+
| 5     | S2       | 6/22/2020   0:17:32 | 6/22/2020   0:17:45 |  |
+-------+----------+---------------------+---------------------+--+
| 6     | S2       | 6/22/2020   0:00:49 | 6/22/2020   0:00:49 |  |
+-------+----------+---------------------+---------------------+--+
| 7     | S3       | 6/22/2020   0:00:06 | 6/22/2020   0:00:18 |  |
+-------+----------+---------------------+---------------------+--+
| 8     | S3       | 6/22/2020   0:04:53 | 6/22/2020   0:04:59 |  |
+-------+----------+---------------------+---------------------+--+

到目前为止，我尝试使用滞后函数来确定以前的传感器读数：

Select 
  *, 
  LAG(EVENTType, 1) OVER (
    ORDER BY 
      RowID
  ) PrevEvent 
from 
  (
    Select 
      RowID, 
      SensorTime, 
      SensorId, 
      Param, 
      Reading, 
      CASE when Reading BETWEEN 500 
      AND 501 THEN 1 ELSE 0 END AS EVENTType 
    from 
      SensorReadings
  )

光标可以用于此吗？

sql sql-server

来源：https://stackoverflow.com/questions/62693242/convert-time-series-data-to-events-based-on-conditions

3条答案

按热度按时间

jq6vz3qz1#

下面的解决方案有点不寻常，但您可以尝试作为游标的替代方案（当然，您可以使用游标解决此问题）
此处演示

WITH CTE
AS
(
    SELECT *,
    CASE WHEN reading IN (5000,5001) THEN 1 ELSE 2 END GRP
FROM your_table
),

CTE2
AS
(
    SELECT *,
    ( 
        SELECT MAX(rowid) 
        FROM CTE B 
        WHERE B.rowid BETWEEN A.rowid
        AND 
        (
            ISNULL
            (
                (
                    SELECT MIN(rowid) - 1
                    FROM CTE C 
                    WHERE C.rowid > A.rowid
                    AND C.GRP <> A.GRP
                ),
                (
                    SELECT MAX(rowid) 
                    FROM CTE D
                    WHERE D.rowid >= A.rowid
                )
            )
        ) 
    ) GRP2
    FROM CTE A
)

SELECT sensorid,
MIN(sensortime) StartEvent,
MAX(sensortime) EndEvent
FROM CTE2
GROUP BY GRP2,sensorid

赞(0）回复(0）举报 2021-07-29

bxgwgixi2#

你似乎想定义“相邻”的读数，这些读数之间有一个间隙。差距究竟是什么还不清楚，但1分钟似乎对你的例子有用。
你可以使用 lag() 确定以前的时间戳并确定新分组的开始位置。然后，开始的累积和就是聚合所需的：

select sensorid, min(sensortime), max(sensortime)
from (select t.*,
             sum(case when prev_sensortime >= dateadd(minute, -1, sensortime) then 0 else 1 end) over (partition by sensorid order by sensortime) as grp
      from (select t.*,
                   lag(sensortime) over (partition by sensorid order by sensortime) as prev_sensortime
            from t
           ) t
     ) t
group by sensorid, grp;

如果您还需要活动编号：

row_number() over (order by min(sensortime))

赞(0）回复(0）举报 2021-07-29

toe950273#

为什么我们需要光标？我们不需要光标。

with
  a as (
    select
      case when reading in(5000, 5001)
        then sum(case when reading not in(5000, 5001) then 1 else 0 end) over(
               order by sensortime rows unbounded preceding
             )
        else sum(case when reading in(5000, 5001) then -1 else 0 end) over(
               order by rowid rows unbounded preceding
             )
      end as gr,
      *
    from sensors
  )
select
  sensorid,
  min(sensortime) as evt_start,
  max(sensortime) as evt_end
from a
group by gr, sensorid
order by sensorid, evt_start;

演示。

赞(0）回复(0）举报 2021-07-29

我来回答

根据条件将时间序列数据转换为事件

3条答案

相关问题

热门标签

最新问答