Я думаю, что лучший способ справиться с этим - сначала сгенерировать свои интервалы, а затем присоединиться к вашим данным, так как это, во-первых, значительно упрощает группировку для переменных интервалов, а также означает, что вы по-прежнему получаете результаты для интервалов без данных. Для этого вам понадобится таблица чисел, так как у многих людей ее нет, ниже вы можете быстро создать ее на лету:
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;
Если ваши значения могут увеличиваться и уменьшаться в пределах инвертора, тогда вам нужно будет использовать функцию ранжирования, чтобы получить первую и последнюю запись для каждого часа, а не min и max:
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-минутными интервалами
Пример скрипта SQL с интервалом в 1 день
ИЗМЕНИТЬ
Как указано в комментариях, ни одно из вышеперечисленных решений не учитывает опережение границ периода, ниже это будет учтено:
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;
person
GarethD
schedule
22.04.2015