Как усреднить данные за последние 5 минут?

Я хотел усреднить данные за 5-минутные интервалы в столбце J. Но мой запрос не усредняет данные за последние 5 минут, а делает наоборот:

Что я хочу:

ТЭ-01 ТЭ-02 ТЭ-03 ТЭ-04 ТЭ-05 ТЭ-06 ТЭ-07 timeStamp (необработанная временная метка UTC+0) GMT+8 (скорректировано на 5 минут) 25.1 25,7 24,9 26,2 25,9 0 0 2024-06-19 23:45:52.940 1 0,6 -0,6 0 30,9 0 0 2024-06-19 23:47:23.203 07:50:00 25.1 25,7 25 26,3 25,9 0 0 2024-06-19 23:51:05.867 25.1 25,7 25 26,3 25,9 0 0 2024-06-19 23:51:18.927 0,8 0,5 -0,4 0,8 32,2 0 0 2024-06-19 23:52:35.317 07:55:00 25.1 25,7 25 26,3 26 0 0 2024-06-19 23:56:30.233 1.2 0,7 0,2 0,9 31,8 0 0 2024-06-19 23:57:47.440 08:00:00 0,8 0,4 -0,5 0,6 32,5 0 0 2024-06-20 00:05:09.667 25,2 25,7 25.1 26,4 26 0 0 2024-06-20 00:05:11.447 0,8 0,4 -0,5 0,7 32,6 0 0 2024-06-20 00:05:22.670 08:10:00

против

Что у меня есть

ТЭ-01 ТЭ-02 ТЭ-03 ТЭ-04 ТЭ-05 ТЭ-06 ТЭ-07 timeStamp (необработанная временная метка UTC+0) GMT+8 (скорректировано на 5 минут) 25.1 25,7 24,9 26,2 25,9 0 0 2024-06-19 23:45:52.940 1 0,6 -0,6 0 30,9 0 0 2024-06-19 23:47:23.203 07:45:00 25.1 25,7 25 26,3 25,9 0 0 2024-06-19 23:51:05.867 25.1 25,7 25 26,3 25,9 0 0 2024-06-19 23:51:18.927 0,8 0,5 -0,4 0,8 32,2 0 0 2024-06-19 23:52:35.317 07:50:00 25.1 25,7 25 26,3 26 0 0 2024-06-19 23:56:30.233 1.2 0,7 0,2 0,9 31,8 0 0 2024-06-19 23:57:47.440 07:55:00 0,8 0,4 -0,5 0,6 32,5 0 0 2024-06-20 00:05:09.667 25,2 25,7 25.1 26,4 26 0 0 2024-06-20 00:05:11.447 0,8 0,4 -0,5 0,7 32,6 0 0 2024-06-20 00:05:22.670 08:05:00

Происходит следующее: вместо того, чтобы агрегировать данные за последние 5 минут, запрос агрегирует данные вперед до тех пор, пока они не достигнут следующей 5-минутной отметки. Вот запрос, отвечающий за агрегацию данных с интервалом в 5 минут. Я также включил образцы данных, чтобы вы могли повторить:

-- This is just a sample data from my SO question...
DECLARE @MyTableVar TABLE (
    [TE-01] FLOAT,
    [TE-02] FLOAT,
    [TE-03] FLOAT,
    [TE-04] FLOAT,
    [TE-05] FLOAT,
    [TE-06] FLOAT,
    [TE-07] FLOAT,
    [timestamp] DATETIME
)

INSERT INTO @MyTableVar ([TE-01], [TE-02], [TE-03], [TE-04], [TE-05], [TE-06], [TE-07], [timestamp])
VALUES
('25.1', '25.7', 24.9, 26.2, 25.9, 0, 0, '2024-06-19 23:45:52.940'),
('1', '0.6', -0.6, 0, 30.9, 0, 0, '2024-06-19 23:47:23.203'),
('25.1', '25.7', 25, 26.3, 25.9, 0, 0, '2024-06-19 23:51:05.867'),
('25.1', '25.7', 25, 26.3, 25.9, 0, 0, '2024-06-19 23:51:18.927'),
('0.8', '0.5', -0.4, 0.8, 32.2, 0, 0, '2024-06-19 23:52:35.317'),
('25.1', '25.7', 25, 26.3, 26, 0, 0, '2024-06-19 23:56:30.233'),
('1.2', '0.7', 0.2, 0.9, 31.8, 0, 0, '2024-06-19 23:57:47.440'),
('0.8', '0.4', -0.5, 0.6, 32.5, 0, 0, '2024-06-20 00:05:09.667'),
('25.2', '25.7', 25.1, 26.4, 26, 0, 0, '2024-06-20 00:05:11.447'),
('0.8', '0.4', -0.5, 0.7, 32.6, 0, 0, '2024-06-20 00:05:22.670');

-- MY QUERY STARTS HERE... IT AGGREGATES DATA AT 5 MINUTE INTERVALS

/*
IGNORE THIS, IT'S JUST NEEDED FOR MY QUERY AND IS NO 
LONGER RELEVANT DUE TO THE SAMPLE DATA
*/
DECLARE @hours INT = 24 

/*
UTC+0 timestamp IS converted TO UTC+08 so it is convinient to look at 
for the users living on that timezone...
*/
DECLARE @USER_TIMEZONE_OFFSET VARCHAR(10) = '+08:00'
DECLARE @CURRENT_DATE DATETIME = CAST(SWITCHOFFSET(TODATETIMEOFFSET(GETDATE() ,'+00:00'), @USER_TIMEZONE_OFFSET) AS DATETIME)

DECLARE @minutes INT = (@hours * 60)

/*
This is the main select statement, the averaging and aggregation of data at 5 minute 
intervals starts here...
*/
SELECT
    T1 = ROUND(AVG([TE-01]), 1),
    T2 = ROUND(AVG([TE-02]), 1),
    T3 = ROUND(AVG([TE-03]), 1),
    T4 = ROUND(AVG([TE-04]), 1),
    T5 = ROUND(AVG([TE-05]), 1),
    T6 = ROUND(AVG([TE-06]), 1),
    T7 = ROUND(AVG([TE-07]), 1),
    [TS] = dateadd(minute, datediff(minute, 0, SWITCHOFFSET(TODATETIMEOFFSET([timeStamp],'+00:00'), @USER_TIMEZONE_OFFSET)) / 5 * 5, 0)
FROM @MyTableVar
WHERE 
    DATEDIFF(MINUTE, CAST(SWITCHOFFSET(TODATETIMEOFFSET([timeStamp] ,'+00:00'), @USER_TIMEZONE_OFFSET) AS DATETIME), @CURRENT_DATE) >= 0
    AND DATEDIFF(MINUTE, CAST(SWITCHOFFSET(TODATETIMEOFFSET([timeStamp] ,'+00:00'), @USER_TIMEZONE_OFFSET) AS DATETIME), @CURRENT_DATE) <= @minutes
GROUP BY dateadd(minute, datediff(minute, 0, SWITCHOFFSET(TODATETIMEOFFSET([timeStamp],'+00:00'), @USER_TIMEZONE_OFFSET)) / 5 * 5, 0)
ORDER BY dateadd(minute, datediff(minute, 0, SWITCHOFFSET(TODATETIMEOFFSET([timeStamp],'+00:00'), @USER_TIMEZONE_OFFSET)) / 5 * 5, 0) ASC

Любая помощь будет оценена по достоинству. Спасибо!

Пожалуйста, не загружайте изображения кода/данных/ошибок, задавая вопрос. Также, если для вопроса важна информация, включите ее в вопрос; не предоставляйте образцы данных через сторонние ресурсы.
Thom A 20.06.2024 10:05

Используйте decimal и datetime2, если не хотите ошибок округления. Все эти манипуляции со строками также бессмысленны, поскольку datetime и datetime2 не имеют смещения. Насколько известно базе данных, временная метка уже установлена ​​в формате UTC. Используйте datetimeoffset, чтобы устранить двусмысленность. Если вы хотите получить результаты в другом часовом поясе, достаточно просто использовать timestamp AT TIME ZONE .... в предложении SELECT.

Panagiotis Kanavos 20.06.2024 10:12

@PanagiotisKanavos, можете ли вы привести мне пример того, как я могу использовать datetime2 для исправления группировки?

jdistro07 20.06.2024 10:22
datetime2 — это тип, а не функция. datetime имеет точность всего 0.003. Используемые вами типы приведут к ошибкам округления. Весь сценарий выглядит как различные ответы, сшитые вместе, например, dateadd(minute, datediff(minute, используется для округления даты и времени, но вы хотите группировать по минутам, а не округлять. Вы можете использовать DATEPART для извлечения различных частей даты, таких как час и минута, после чего `минута/5 возвращает нужный вам интервал.
Panagiotis Kanavos 20.06.2024 10:38

Использование строк для представления чисел также является ошибкой. Половина мира не использует . в качестве десятичного разделителя. И это только потому, что Китай следует в этом США. Откуда берутся данные?

Panagiotis Kanavos 20.06.2024 10:39

@ThomA, я обновил вопрос, чтобы он соответствовал политике SO

jdistro07 20.06.2024 10:41

@PanagiotisKanavos, в нашей базе данных используется float, в некоторых числах нет десятичной дроби. Я извлек этот пример данных из базы данных и округлил их так, чтобы они представляли только 2 десятичных знака (потому что это требование, а не потому, что мне так нравится). Значения плавающих данных поступают от датчика температуры.

jdistro07 20.06.2024 10:46

Давайте продолжим обсуждение в чате.

jdistro07 20.06.2024 10:48

«наша база данных использует float» Это редко бывает правильным выбором типа данных. Числа по основанию 2 и 10 плохо сочетаются, когда вам нужно хранить нецелые числа. Для приведенного выше примера данных float явно неправильный выбор; decimal(3,2) кажется гораздо более подходящим.

Thom A 20.06.2024 10:54

Но меня беспокоит группировка данных при агрегации на 5-минутном интервале. Это не результат усреднения чисел. Давайте сосредоточимся на временных метках.

jdistro07 20.06.2024 10:56

Столбец GMT+8 (corrected 5 minutes) в таблицах показывает, как MSSQL сгруппировал данные с 5-минутными интервалами. Я обновил вопрос совсем недавно.

jdistro07 20.06.2024 10:58

@ jdistro07, каковы ожидаемые результаты? Вы еще этого не опубликовали. Агрегации сокращают количество строк, пока вы публикуете исходные данные, плюс столбец, который совсем не помогает. Должен ли 2024-06-19 23:45:52.940 отображаться как 2024-06-19 23:45:00 или 2024-06-19 23:50:00? Это то, что помогает, наряду с фактическими средними показателями, которые вы ожидаете.

Panagiotis Kanavos 20.06.2024 12:01

@PanagiotisKanavos, вы можете увидеть таблицу в What I want, это ожидаемый результат. Как вы можете видеть в столбце GMT+8 (corrected 5 minutes) этой таблицы, я усредняю ​​данные за последние 5 минут. Потому что первые данные технически уже прошли 5 минут, как и 52 секунды назад.

jdistro07 20.06.2024 12:08
07:45:00 AM не имеет отношения к 2024-06-19 23:47:23.203. Каковы ожидаемые результаты агрегирования, включая фактические временные метки? Должно быть всего 4 ряда. Речь идет не о прошлом и настоящем, а о том, хотите ли вы отображать начало или конец 5-минутного интервала. У вас уже есть 2 действительных ответа, подтверждающих это. Средние значения неверны? Если нет, то это просто вопрос отображения начала или конца.
Panagiotis Kanavos 20.06.2024 12:15
2024-06-19 23:45:52.940 и 2024-06-19 23:47:23.203 должны быть усреднены до 23:50:00, учитывая, что вы не хотите смещать время до UTC+08. Я только что увидел, что последние три группы данных в моем разделе What I want неверны. Позвольте мне это исправить
jdistro07 20.06.2024 12:21

Готово, я исправил раздел What I Want, а @evilmandarine уже предоставил мне нужный результат. Я сейчас тестирую его на больших данных.

jdistro07 20.06.2024 12:24

Нет, ты этого не сделал. Вместо 4 строк результатов имеется 10 исходных строк со средними значениями и метками времени. В любом случае оба ответа содержат любую комбинацию, которую вы захотите. Отображение начала или конца интервала. в формате UTC, в определенном часовом поясе по имени или по смещению. Если вы не объясните, что не так с этими результатами, этот вопрос будет закрыт, поскольку вы все еще не предоставили ожидаемые результаты.

Panagiotis Kanavos 20.06.2024 12:28

Это потому что я убрал ошибку. Я прошу прощения, если вы разочарованы тем, что я задал вопрос, который не имеет для вас смысла. В любом случае, спасибо за помощь, несмотря ни на что.

jdistro07 20.06.2024 12:41
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
18
96
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

После исправления типов во избежание ошибок округления и устранения неоднозначности запрос можно свести к следующему:

;with x as (
    select * ,
    DATETIMEOFFSETFROMPARTS(
        datepart(year,timestamp), 
        datepart(month,timestamp),
        datepart(day,timestamp),
        datepart(hour,timestamp),
        5* (datepart(minute,timestamp)/5),
        0,0,0,0,0)
    as TS
from @MyTableVar
)
SELECT
    T1 = ROUND(AVG([TE-01]),1),
    T2 = ROUND(AVG([TE-02]),1),
    T3 = ROUND(AVG([TE-03]),1),
    T4 = ROUND(AVG([TE-04]),1),
    T5 = ROUND(AVG([TE-05]),1),
    T6 = ROUND(AVG([TE-06]),1),
    T7 = ROUND(AVG([TE-07]),1),
    [TS] 
FROM x
WHERE 
    [timestamp] between DATEADD(HOUR,@hours,SYSDATETIMEOFFSET()) and SYSDATETIMEOFFSET()
GROUP BY TS
ORDER BY TS

Чтобы отобразить конец интервала вместо начала в определенном часовом поясе с учетом правил летнего времени, нам нужно всего лишь изменить выражение TS, например:

   dateadd(MINUTE,5,[TS]) at time zone 'Singapore Standard Time' as TS

float подвержен ошибкам округления и datetime имеет точность примерно 3 мс. datetime тоже понятия не имеет о смещении или часовом поясе. Использование datetimeoffset(3) устраняет ошибки округления и двусмысленность.

Вопрос заключается в использовании обычного трюка для усечения времени до интервала. То же самое можно сделать в SQL Server 2022 с помощью функции DATETRUNC, но ни одна из них здесь не помогает.

Однако здесь действительно необходимо сократить интервалы до 5 минут. Это можно сделать, выделив части timestamp и используя их для создания нового значения, урезав всего лишь минуты.

Запуск этого скрипта:

DECLARE @MyTableVar TABLE (
    [TE-01] numeric(8,1),
    [TE-02] numeric(8,1),
    [TE-03] numeric(8,1),
    [TE-04] numeric(8,1),
    [TE-05] numeric(8,1),
    [TE-06] numeric(8,1),
    [TE-07] numeric(8,1),
    [timestamp] datetimeoffset(3)
)

INSERT INTO @MyTableVar ([TE-01], [TE-02], [TE-03], [TE-04], [TE-05], [TE-06], [TE-07], [timestamp])
VALUES
(25.1, 25.7, 24.9, 26.2, 25.9, 0, 0, '2024-06-19 23:45:52.940'),
(1,    0.6, -0.6, 0, 30.9, 0, 0, '2024-06-19 23:47:23.203'),
(25.1, 25.7, 25, 26.3, 25.9, 0, 0, '2024-06-19 23:51:05.867'),
(25.1, 25.7, 25, 26.3, 25.9, 0, 0, '2024-06-19 23:51:18.927'),
(0.8,  0.5, -0.4, 0.8, 32.2, 0, 0, '2024-06-19 23:52:35.317'),
(25.1, 25.7, 25, 26.3, 26, 0, 0, '2024-06-19 23:56:30.233'),
(1.2,  0.7, 0.2, 0.9, 31.8, 0, 0, '2024-06-19 23:57:47.440'),
(0.8,  0.4, -0.5, 0.6, 32.5, 0, 0, '2024-06-20 00:05:09.667'),
(25.2, 25.7, 25.1, 26.4, 26, 0, 0, '2024-06-20 00:05:11.447'),
(0.8,  0.4, -0.5, 0.7, 32.6, 0, 0, '2024-06-20 00:05:22.670');


;with x as (
    select * ,
    DATETIMEOFFSETFROMPARTS(
        datepart(year,timestamp),
        datepart(month,timestamp),
        datepart(day,timestamp),
        datepart(hour,timestamp),
        5*(datepart(minute,timestamp)/5),
        0,0,0,0,0)
    as ts
from @MyTableVar
)
SELECT
    T1 = ROUND(AVG([TE-01]),1),
    T2 = ROUND(AVG([TE-02]),1),
    T3 = ROUND(AVG([TE-03]),1),
    T4 = ROUND(AVG([TE-04]),1),
    T5 = ROUND(AVG([TE-05]),1),
    T6 = ROUND(AVG([TE-06]),1),
    T7 = ROUND(AVG([TE-07]),1),
    [TS] 
FROM x
WHERE 
    [timestamp] between dateadd(minute,-6000,SYSDATETIMEOFFSET()) and SYSDATETIMEOFFSET()
GROUP BY ts
ORDER BY ts

Производит:

T1  T2  T3  T4  T5  T6  T7  TS
13.100000   13.200000   12.200000   13.100000   28.400000   0.000000    0.000000    2024-06-19 23:45:00 +00:00
17.000000   17.300000   16.500000   17.800000   28.000000   0.000000    0.000000    2024-06-19 23:50:00 +00:00
13.200000   13.200000   12.600000   13.600000   28.900000   0.000000    0.000000    2024-06-19 23:55:00 +00:00
8.900000    8.800000    8.000000    9.200000    30.400000   0.000000    0.000000    2024-06-20 00:05:00 +00:00

Чтобы результаты выглядели лучше, мы можем использовать decimal(8,1) вместо округления:

SELECT
    T1 = cast(AVG([TE-01]) as decimal(8,1)),
    T2 = cast(AVG([TE-02]) as decimal(8,1)),
    T3 = cast(AVG([TE-03]) as decimal(8,1)),
    T4 = cast(AVG([TE-04]) as decimal(8,1)),
    T5 = cast(AVG([TE-05]) as decimal(8,1)),
    T6 = cast(AVG([TE-06]) as decimal(8,1)),
    T7 = cast(AVG([TE-07]) as decimal(8,1)),
    [TS] 
FROM x
...

Возврат

T1  T2  T3  T4  T5  T6  T7  TS
13.1    13.2    12.2    13.1    28.4    0.0 0.0 2024-06-19 23:45:00 +00:00
17.0    17.3    16.5    17.8    28.0    0.0 0.0 2024-06-19 23:50:00 +00:00
13.2    13.2    12.6    13.6    28.9    0.0 0.0 2024-06-19 23:55:00 +00:00
 8.9     8.8     8.0     9.2    30.4    0.0 0.0 2024-06-20 00:05:00 +00:00

Отобразить конец интервала

Если мы хотим отобразить данные к концу интервала, все, что нам действительно нужно сделать, это добавить 5 минут к TS:

SELECT
    T1 = cast(AVG([TE-01]) as decimal(8,1)),
    T2 = cast(AVG([TE-02]) as decimal(8,1)),
    T3 = cast(AVG([TE-03]) as decimal(8,1)),
    T4 = cast(AVG([TE-04]) as decimal(8,1)),
    T5 = cast(AVG([TE-05]) as decimal(8,1)),
    T6 = cast(AVG([TE-06]) as decimal(8,1)),
    T7 = cast(AVG([TE-07]) as decimal(8,1)),
    DATEADD(MINUTE,5,[TS]) as TS
FROM x

который производит

T1  T2  T3  T4  T5  T6  T7  TS
13.1    13.2    12.2    13.1    28.4    0.0 0.0 2024-06-19 23:50:00 +00:00
17.0    17.3    16.5    17.8    28.0    0.0 0.0 2024-06-19 23:55:00 +00:00
13.2    13.2    12.6    13.6    28.9    0.0 0.0 2024-06-20 00:00:00 +00:00
8.9      8.8     8.0     9.2    30.4    0.0 0.0 2024-06-20 00:10:00 +00:00

Перевести в другой часовой пояс

Наконец, чтобы преобразовать время в другой часовой пояс, самый безопасный способ — использовать AT TIME ZONE с именем часового пояса вместо смещения. На имена часовых поясов не влияют изменения летнего времени, например:

SELECT
    ...
    dateadd(MINUTE,5,[TS]) at time zone 'W. Australia Standard Time' as TS
FROM x

Производит

T1  T2  T3  T4  T5  T6  T7  TS
13.1    13.2    12.2    13.1    28.4    0.0 0.0 2024-06-20 07:50:00 +08:00
17.0    17.3    16.5    17.8    28.0    0.0 0.0 2024-06-20 07:55:00 +08:00
13.2    13.2    12.6    13.6    28.9    0.0 0.0 2024-06-20 08:00:00 +08:00
8.9      8.8     8.0     9.2    30.4    0.0 0.0 2024-06-20 08:10:00 +08:00

Конвертировать по смещению

Использование SWITCHOFFSET рискованно. Правильное смещение зависит от фактической сохраненной даты, а не от текущей системной даты. Если тот же запрос выполняется в декабре, смещение не будет таким же.

Если и только если мы уверены, что даты не пересекают границы летнего времени, мы можем использовать

SELECT
    ...
    SWITCHOFFSET(dateadd(MINUTE,5,[TS]),'+08:00') as TS
FROM x

Я посмотрел на результат. Но похоже, что созданный вами сценарий не учитывает секунды. Например, данные в 2024-06-19 23:45:52.940 находятся на 5-минутной отметке, но в вашем сценарии кажется, что либо 23:45:00, либо 23:50:00 данные уже опережают 5-минутную отметку на 52 секунды. Оно уже должно быть усреднено на отметке 23:50:00.

jdistro07 20.06.2024 11:34

И откуда у вас данные по отметке 23:45:00? Потому что, насколько я вручную проверил группировку агрегации, результирующие данные должны начинаться с 23:50:00, а не с 23:45:00.

jdistro07 20.06.2024 11:37

@jdistro07 Я получил результаты по твоим данным. Вы никогда не публиковали то, что на самом деле ожидаете от своего запроса, поэтому нет возможности проверить, что вы хотите. doesn't consider the seconds округление до 5 минут исключает секунды. should start at 23:50:00, not 23:45:00 затем добавьте 5 минут. Это не имеет значения. Округление вверх аналогично усечению и добавлению интервала. on either 23:45:00 or 23:50:00 нет, такое значение только одно и оно включено 23:45. Среднее значение 25.1 + 1 равно 13.05, 13.1, если округлить до 1 цифры.

Panagiotis Kanavos 20.06.2024 11:45
Ответ принят как подходящий

Ответ Панайотиса гораздо более полный, но, поскольку я потратил некоторое время на попытки, я опубликую это решение здесь. Сосредоточившись только на группировке, это тоже работает:

declare @mindate datetime = (select min(timestamp) from @MyTableVar)

SELECT
    T1 = ROUND(AVG([TE-01]), 1),
    T2 = ROUND(AVG([TE-02]), 1),
    T3 = ROUND(AVG([TE-03]), 1),
    T4 = ROUND(AVG([TE-04]), 1),
    T5 = ROUND(AVG([TE-05]), 1),
    T6 = ROUND(AVG([TE-06]), 1),
    T7 = ROUND(AVG([TE-07]), 1),
    -1 * datediff(minute, timestamp, @mindate)/5 as 'idx',
    dateadd(minute, (datediff(minute, 0, timestamp) / 5) * 5 + 5, 0) as 'date group',
    TODATETIMEOFFSET(dateadd(minute, (datediff(minute, 0, timestamp) / 5) * 5 + 5, 0) + '08:00:00', '+08:00') as 'date group gmt+8'
FROM @MyTableVar

GROUP BY 
  -1 * datediff(minute, timestamp, @mindate)/5,
  dateadd(minute, (datediff(minute, 0, timestamp) / 5) * 5 + 5, 0)
ORDER BY
  -1 * datediff(minute, timestamp, @mindate)/5

По сути, округляйте временную метку до следующей границы 5 минут и назначайте своего рода идентификатор группы (idx), а затем группируйте по этому идентификатору. Получите временную метку и преобразуйте ее в GMT+8. Результат тот же, что и выше.

T1     T2   T3      T4      T5      T6  T7  idx date group                  date group gmt+8
13.1 13.2   12.1    13.1    28.4    0   0   0   2024-06-19 23:50:00.000     2024-06-20 07:50:00.000 +08:00
17   17.3   16.5    17.8    28      0   0   1   2024-06-19 23:55:00.000     2024-06-20 07:55:00.000 +08:00
13.2 13.2   12.6    13.6    28.9    0   0   2   2024-06-19 00:00:00.000     2024-06-20 08:00:00.000 +08:00
8.9   8.8   8        9.2    30.4    0   0   4   2024-06-20 00:10:00.000     2024-06-20 08:10:00.000 +08:00

Спасибо за публикацию вашего решения. Но как я проверял. Группировка во время агрегирования этого запроса и ответа @Panagiotis в среднем не превышает 5 минут. По сути, он делает то же самое, что и мой сценарий, но лучше. Если вы посмотрите на первые данные, необработанная временная метка равна 2024-06-19 23:45:52.940, она уже опережает 5-минутный отсчет на 52 секунды. а это значит, что оно уже должно быть усреднено на отметке 23:50:00. Потому что мы вычисляем данные за последние 5 минут.

jdistro07 20.06.2024 11:50

Нет проблем, самый простой способ — просто добавить к запросу 5 минут, чтобы «округлить» до более высокой границы. См. редактирование.

evilmandarine 20.06.2024 11:53

@ jdistro07 jdistro07 то, что вы утверждаете, неверно. Оба ответа в среднем с интервалом в 5 минут. Хотите ли вы отображать начало или конец интервала, это вопрос добавления продолжительности интервала в предложение SELECT. Сам интервал не меняется

Panagiotis Kanavos 20.06.2024 11:56

Просто альтернативное решение с использованием деления миллисекунд:

SELECT  T1 = ROUND(AVG([TE-01]), 1),
    T2 = ROUND(AVG([TE-02]), 1),
    T3 = ROUND(AVG([TE-03]), 1),
    T4 = ROUND(AVG([TE-04]), 1),
    T5 = ROUND(AVG([TE-05]), 1),
    T6 = ROUND(AVG([TE-06]), 1),
    T7 = ROUND(AVG([TE-07]), 1)
,   dateadd(ms,  5 * 60 * 1000 * ceiling(datediff(ms,utc, [timeStamp (UTC+0 raw timestamp)] ) / (1000. * 60 * 5)), utc) AS datetimeNew
FROM
(
    VALUES  (25.1, 25.7, 24.9, 26.2, 25.9, 0, 0, N'2024-06-19 23:45:52.940', '')
    ,   (1, 0.6, -0.6, 0, 30.9, 0, 0, N'2024-06-19 23:47:23.203', N'07:50:00 AM')
    ,   (25.1, 25.7, 25, 26.3, 25.9, 0, 0, N'2024-06-19 23:51:05.867', '')
    ,   (25.1, 25.7, 25, 26.3, 25.9, 0, 0, N'2024-06-19 23:51:18.927', '')
    ,   (0.8, 0.5, -0.4, 0.8, 32.2, 0, 0, N'2024-06-19 23:52:35.317', N'07:55:00 AM')
    ,   (25.1, 25.7, 25, 26.3, 26, 0, 0, N'2024-06-19 23:56:30.233', '')
    ,   (1.2, 0.7, 0.2, 0.9, 31.8, 0, 0, N'2024-06-19 23:57:47.440', N'08:00:00 AM')
    ,   (0.8, 0.4, -0.5, 0.6, 32.5, 0, 0, N'2024-06-20 00:05:09.667', '')
    ,   (25.2, 25.7, 25.1, 26.4, 26, 0, 0, N'2024-06-20 00:05:11.447', '')
    ,   (0.8, 0.4, -0.5, 0.7, 32.6, 0, 0, N'2024-06-20 00:05:22.670', N'08:10:00 AM')
) t ([TE-01],[TE-02],[TE-03],[TE-04],[TE-05],[TE-06],[TE-07],[timeStamp (UTC+0 raw timestamp)],[GMT+8 (corrected 5 minutes)])
CROSS apply (
        SELECT  cast(cast([timeStamp (UTC+0 raw timestamp)] AS date) AS datetime) AS utc
    ) u
GROUP BY dateadd(ms,  5 * 60 * 1000 * ceiling(datediff(ms,utc, [timeStamp (UTC+0 raw timestamp)] ) / (1000. * 60 * 5)), utc)

Я беру разницу между датой временной метки и датой-временем с интервалом в 5 минут, беру ее верхний предел и возвращаю ее к исходной временной метке. Это даст вам ближайший 5-минутный интервал.

Если вы хотите включить точное второе время (23:50:00) в скобку 23:55:00, вы можете добавить 3 мс к дате перед выполнением вычислений.

Я не включил информацию о зонировании, но информация о дате зоны почти всегда неверна, поэтому не стесняйтесь добавлять ее самостоятельно, если она вам действительно нужна.

Другие вопросы по теме