问题计算每个区间增长的SQL语句

在我们的数据库中，我们有一个表来跟踪设备的功耗。插入新值的速率不固定，只有在确实存在变化时才会写入，因此值之间的时间距离会发生变化，可能会达到1秒到几分钟。条目包括时间戳和值。每增加一行，该值总是会增加，因为它计算的是kWh。

我想要实现的目标如下：我想指定一个开始和结束日期时间，比方说一个月。我还想指定一个15分钟，1小时，1天或类似的间隔。我需要得到的结果是[间隔开始为日期时间]，[该间隔内的功耗]，例如，像这样（间隔设置为1小时）：

2015-01.01 08:00:00   -   65
2015-01.01 09:00:00   -   43
2015-01.01 10:00:00   -   56

这就是表格的样子：

TimeStamp            Value
-------------------------
2015-01-08 08:29:47, 5246
2015-01-08 08:36:15, 5247
2015-01-08 08:37:10, 5248
2015-01-08 08:38:01, 5249
2015-01-08 08:38:38, 5250
2015-01-08 08:38:51, 5251
2015-01-08 08:39:33, 5252
2015-01-08 08:40:20, 5253
2015-01-08 08:41:10, 5254
2015-01-09 08:56:25, 5255
2015-01-09 08:56:43, 5256
2015-01-09 08:57:31, 5257
2015-01-09 08:57:36, 5258
2015-01-09 08:58:02, 5259
2015-01-09 08:58:57, 5260
2015-01-09 08:59:27, 5261
2015-01-09 09:00:06, 5262
2015-01-09 09:00:59, 5263
2015-01-09 09:01:54, 5265
2015-01-09 09:02:44, 5266
2015-01-09 09:03:39, 5267
2015-01-09 09:04:22, 5268
2015-01-09 09:05:11, 5269
2015-01-09 09:06:08, 5270

我觉得我必须把它结合起来 SUM() 功能与 GROUP BY，但我不知道如何做到这一点，因为据我所知，我也必须只考虑发展每个间隔而不是该间隔内绝对值的总和。如果有人能把我带到正确的轨道上会很棒。

5468

2018-04-22 07:33

起源

每间隔增长？与同一区间中最后一个区间的最高值或第一个值相比？逻辑将决定第一个。你使用的是哪个版本的sqlserver？ - t-clausen.dk

价值列就像一个不断增加的计数器，可以节省总消耗量，就像你家里的水表一样。因此，每个间隔的增长是最大（或最后）值减去给定间隔内的最小（=第一）值。 - Robert

答案:

您的样本数据与结果间隔不匹配，因此您可能会错过结束或开始时间间隔内的增加。因此，我假设样本数据行之间呈线性增长，并将它们与结果间隔相匹配。

declare @start datetime2 = '2015-01-09 09:00:00'
declare @end datetime2 = '2015-01-09 09:30:00'
declare @intervalMinutes int = 5

;with intervals as (
      select @start iStart, dateadd(minute, @intervalMinutes, @start) iEnd

      union all

      select iEnd, dateadd(minute, @intervalMinutes, iEnd) from intervals
      where iEnd < @end

), increases as (
        select 
             T.Timestamp sStart, 
             lead(T.Timestamp, 1, null ) over (order by T.Timestamp) sEnd, -- the start of the next period if there is one, null else
             lead(T.value, 1, null ) over (order by T.Timestamp) - T.value increase  -- the increase within this period
        from @T T
), rates as (
        select 
           sStart rStart, 
           sEnd rEnd, 
           (cast(increase as float))/datediff(second, sStart, sEnd) rate -- increase/second
        from increases where increase is not null
), samples as (
        select *, 
            case when iStart > rStart then iStart else rStart end sStart, -- debug
            case when rEnd>iEnd then iEnd else rEnd end sEnd,  -- debug                
            datediff(second, case when iStart > rStart then iStart else rStart end, case when rEnd>iEnd then iEnd else rEnd end)*rate x -- increase within the period within the interval
        from intervals i
        left join rates r on rStart between iStart and iEnd or rEnd between iStart and iEnd or iStart between rStart and rEnd -- overlaps
)
select iStart, iEnd, isnull(sum(x), 0) from samples 
group by iStart, iEnd

CTE：

intervals 保存您想要数据的intervales
increaese 计算样本数据周期内的增量
rates 计算样本数据周期中每秒的增量
samples 通过考虑边界之间的重叠，将结果间隔与样本间隔相匹配

最后，选择总结了与单个间隔匹配的采样周期。

笔记：

对于间隔量> [您的最大递归深度]，您必须使用另一个解决方案来创建 intervals CTE（参见@GarethD解决方案）
调试提示：只需使用 select * from samples 您可以看到与结果间隔匹配的采样周期

2018-04-22 09:02

谢谢！该解决方案提供可靠且非常精确的值。 - Robert

我认为解决这个问题的最好方法是先生成你的间隔，然后再加入你的数据，因为这首先使得变量间隔的分组变得更加复杂，并且还意味着你仍然得到没有数据的间隔的结果。要做到这一点，你需要一个数字表，因为很多人下面没有一个是快速生成一个数字的方法：

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT *
FROM Numbers;

这只是生成1到10,000的序列。有关此内容的更多信息，请参阅以下系列：

然后，您可以定义开始时间，间隔和要显示的记录数，以及您的数字表，您可以生成数据：

DECLARE @Start DATETIME2 = '2015-01-09 08:00',
        @Interval INT = 60,     -- INTERVAL IN MINUTES
        @IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2)

SELECT  TOP (@IntervalCount)
        Interval = DATEADD(MINUTE, (N - 1) * @Interval, @Start)
FROM    Numbers;

最后，您可以将此LEFT JOIN连接到您的数据，以获得每个间隔的最小值和最大值

DECLARE @Start DATETIME2 = '2015-01-09 08:00',
        @Interval INT = 60,     -- INTERVAL IN MINUTES
        @IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
(   SELECT  TOP (@IntervalCount)
            IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
            IntervalEnd = DATEADD(MINUTE, N  * @Interval, @Start)
    FROM    Numbers AS n
)
SELECT  i.IntervalStart,
        MinVal = MIN(t.Value),
        MaxVal = MAX(t.Value),
        Difference = ISNULL(MAX(t.Value)  - MIN(t.Value), 0)
FROM    Intervals AS i
        LEFT JOIN T AS t
            ON t.timestamp >= i.IntervalStart
            AND t.timestamp < i.IntervalEnd
GROUP BY i.IntervalStart;

如果您的值可以在inverval中上下移动，那么您将需要使用排名函数来获取每小时的第一个和最后一个记录，而不是最小值和最大值：

DECLARE @Start DATETIME2 = '2015-01-09 08:00',
        @Interval INT = 60,     -- INTERVAL IN MINUTES
        @IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
(   SELECT  TOP (@IntervalCount)
            IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
            IntervalEnd = DATEADD(MINUTE, N  * @Interval, @Start)
    FROM    Numbers AS n
), RankedData AS
(   SELECT  i.IntervalStart,
            t.Value,
            t.timestamp,
            RowNum = ROW_NUMBER() OVER(PARTITION BY i.IntervalStart ORDER BY t.timestamp),
            TotalRows = COUNT(*) OVER(PARTITION BY i.IntervalStart)
    FROM    Intervals AS i
            LEFT JOIN T AS t
                ON t.timestamp >= i.IntervalStart
                AND t.timestamp < i.IntervalEnd
)
SELECT  r.IntervalStart,
        Difference = ISNULL(MAX(CASE WHEN RowNum = TotalRows THEN r.Value END) - 
                            MAX(CASE WHEN RowNum = 1 THEN r.Value END), 0)
FROM    RankedData AS r
WHERE   RowNum = 1
OR      TotalRows = RowNum
GROUP BY r.IntervalStart;

关于SQL小提琴的示例，间隔为1小时

关于SQL小提琴的示例，间隔为15分钟

有关1天间隔的SQL小提琴示例

编辑

正如评论中所指出的，上述解决方案都没有考虑到超过期限的预付款，下面将说明这一点：

DECLARE @Start DATETIME2 = '2015-01-09 08:25',
        @Interval INT = 5,     -- INTERVAL IN MINUTES
        @IntervalCount INT = 18; -- NUMBER OF INTERVALS TO SHOW

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
(   SELECT  TOP (@IntervalCount)
            IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
            IntervalEnd = DATEADD(MINUTE, (N - 0) * @Interval, @Start)
    FROM    Numbers AS n
), LeadData AS
(   SELECT  T.timestamp,
            T.Value,
            NextValue = nxt.value,
            AdvanceRate = ISNULL(1.0 * (nxt.Value - T.Value) / DATEDIFF(SECOND, T.timestamp, nxt.timestamp), 0),
            NextTimestamp = nxt.timestamp
    FROM    T AS T
            OUTER APPLY 
            (   SELECT  TOP 1 T2.timestamp, T2.value
                FROM    T AS T2
                WHERE   T2.timestamp > T.timestamp
                ORDER BY T2.timestamp
            ) AS nxt
)
SELECT  i.IntervalStart,
        Advance = CAST(ISNULL(SUM(DATEDIFF(SECOND, d.StartTime, d.EndTime) * t.AdvanceRate), 0) AS DECIMAL(10, 4))
FROM    Intervals AS i
        LEFT JOIN LeadData AS t
            ON t.NextTimestamp >= i.IntervalStart 
            AND t.timestamp < i.IntervalEnd
        OUTER APPLY
        (   SELECT  CASE WHEN t.timestamp > i.IntervalStart THEN t.timestamp ELSE i.IntervalStart END,
                    CASE WHEN t.NextTimestamp < i.IntervalEnd THEN t.NextTimestamp ELSE i.IntervalEnd END
        ) AS d (StartTime, EndTime)
GROUP BY i.IntervalStart;

2018-04-22 08:04

需要提及的一点是：区间增长并不总结为第一个和最后一个数据行之间的总差异。这是因为在间隔1的最后一个数据行和下一个间隔2内的第一个数据行之间通常会有增加。这种增长既不在i1也不在i2中考虑。 - flo

我也有一点说法：为了获得连续的间隔列表（没有间隙），我不得不将LEFT JOIN更改为LEFT OUTER JOIN并添加一个ON条件，该条件按我的消耗值中的设备ID列进行过滤表，我在我的问题中没有提到。 - Robert

@flo：你是对的，价值观不正确。在快速测试中，导致累计消耗11千瓦时，持续48小时，这实际上是19千瓦时的正确消耗（期末日期时间的总消耗量减去期间开始日期时间的值）。 - Robert

我添加了一个版本来解释这个进展，它与接受的答案非常相似，但我看到你的一个问题被标记为SQL-Server-2008所以发布了一个可以在该版本的SQL Server中运行的解决方案。 FWIW a LEFT JOIN 和a LEFT OUTER JOIN 是完全一样的。 - GarethD

@Gareth：感谢您提高答案，现在就可以了。你当然对LEFT OUTER JOIN是正确的，我认为我混淆了事情，因为我并不习惯这种DB“魔法”;） - Robert

一种快速的方法是从TimeStamp获得日期+小时，而不是GROUP BY，并且功耗值将是MAX（值） - MIN（值）。您可以通过其他方式操作TimeStamp以获得不同的间隔，此示例仅适用于每小时消耗。

SELECT 
    CONVERT(datetime, CONVERT(varchar(10), TimeStamp, 120) + ' ' + CONVERT(varchar(2), DATEPART(hour, TimeStamp)) + ':00:00'),
    MAX(Value) - MIN(Value) AS Value
FROM [Table]
GROUP BY CONVERT(datetime, CONVERT(varchar(10), TimeStamp, 120) + ' ' + CONVERT(varchar(2), DATEPART(hour, TimeStamp)) + ':00:00')

2018-04-22 08:08

问题 计算每个区间增长的SQL语句

答案:

热门问题

问题计算每个区间增长的SQL语句