时间戳

首页 > 解决方案 > 获取开始和结束标志之间的所有行

sql-server - 获取开始和结束标志之间的所有行

问题描述

我有一个类似的数据结构

Parameter | Value | DateTime
----------------------------
Switch    | "on"  | 2019-10-13 15:01:25
Temp      | 25    | 2019-10-13 15:01:37
Pressure  | 1006  | 2019-10-13 15:01:53
...
Temp      | 22    | 2019-10-13 15:04:41
Switch    | "off" | 2019-10-13 15:04:59
...
Switch    | "on"  | 2019-10-13 17:14:51
Temp      | 27    | 2019-10-13 17:15:07
...
Switch    | "off" | 2019-10-13 17:17:43

在每对开关“开”和“关”之间，我必须计算参数的值，即平均值或最大值/最小值等。如何让不同的数据集具有多个用于计算的组？

我认为这应该可以通过
- 存储过程（语句？）
- SSIS 包（如何？）
- .NET 应用程序来解决。

解决此问题的最佳方法可能是什么？

提前致谢。

更新

这是表的完整结构。

CREATE TABLE [schema].[foo]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    [Group] VARCHAR(20) NOT NULL,
    [Parameter] VARCHAR(50) NOT NULL,
    [Type] VARCHAR(50) NOT NULL,
    [Timestamp] DATETIME NOT NULL,
    [Value] NVARCHAR(255) NOT NULL,
    [Unit] VARCHAR(10) NOT NULL,

    // Only for logging. No logic for the use case.
    [InsertedTimestampUtc] DATETIME NOT NULL DEFAULT(GetUtcDate()),
    [IsProcessed] INT NOT NULL DEFAULT(0)
)

标签： sql-servertsqlgroupingwindow-functions

解决方案

如果我正确理解您的问题，则下一种方法可能有助于获得预期结果：

桌子：

CREATE TABLE #Data (
    [DateTime] datetime,
    [Parameter] varchar(50),
    [Value] varchar(10)
)
INSERT INTO #Data
    ([DateTime], [Parameter], [Value])
VALUES
    ('2019-10-13T15:01:25', 'Switch',   'on'),
    ('2019-10-13T15:01:37', 'Temp',     '25'),
    ('2019-10-13T15:01:53', 'Pressure', '1006'),
    ('2019-10-13T15:04:41', 'Temp',     '22'),
    ('2019-10-13T15:04:59', 'Switch',   'off'),
    ('2019-10-13T17:14:51', 'Switch',   'on'),
    ('2019-10-13T17:15:07', 'Temp',     '27'),
    ('2019-10-13T17:17:43', 'Switch',   'off')

陈述：

;WITH ChangesCTE AS (
    SELECT
        *,
        CASE WHEN [Parameter] = 'Switch' AND [Value] = 'on' THEN 1 ELSE 0 END AS ChangeIndex
    FROM #Data
), GroupsCTE AS (
    SELECT
        *,
        SUM(ChangeIndex) OVER (ORDER BY [DateTime]) AS GroupIndex
    FROM ChangesCTE
)
SELECT [GroupIndex], [Parameter], AVG(TRY_CONVERT(int, [Value]) * 1.0) AS [AvgValue]
FROM GroupsCTE
WHERE [Parameter] <> 'Switch'
GROUP BY [GroupIndex], [Parameter]

结果：

GroupIndex  Parameter   AvgValue
1           Pressure    1006.000000
1           Temp        23.500000
2           Temp        27.000000

推荐阅读