#sql #sql-server
#sql #sql-сервер
Вопрос:
У меня есть измеритель мощности, который регистрирует энергопотребление с интервалом в 5 минут. Ежедневное потребление энергии накапливается.
Имя столбца энергопотребления ‘Wh_1_R’
Когда я пытаюсь выполнить группировку по дате с максимальным минимальным значением, данные между 11:55 вечера и 00:00 утра на следующий день будут потеряны.
Любой другой способ включить данные за 5 минут в расчет за тот же день?
Мой текущий запрос:
select
CONVERT(varchar, Datetime, 111) As Date,
'KMN3FLK_11' As DPM,
'R3' As Line,
'Machine' As Category,
Max(Wh_1_R) - Min(Wh_1_R) As Active_P,
Max(Varh_1_R) - Min(Varh_1_R) As Reactive_P,
Max(P_Total) As Max_Power,
Min(P_Total) as Min_Power,
Max(Q_Total) As Max_QPower,
Min(Q_Total) as Min_QPower
From [dbo].[KMN3FLK_11]
Where Wh_1_R <> 0
group by CONVERT(varchar, Datetime, 111)
Комментарии:
1. Каков ожидаемый результат?
2. Ожидаемые результаты соответствуют моему запросу выше, текущий столбец my Active_P указывает Max(Wh_1_R) — Min (Wh_1_R), в то время как в этом результате будут опущены данные с 11:55 вечера до 00:00 утра. Я также хотел бы включить их. Пожалуйста, сообщите
3. Ну, почему вы группируете по преобразованию в varchar, просто группируете его по дате и времени?
4. Я хочу знать ежедневное потребление.. преобразование заключается в преобразовании даты и времени в дату. Иначе, если я сгруппируюсь по дате и времени, я не смогу получить ежедневное значение
5. @JenasTan, чтобы было понятно — вы хотите включить строки со значением времени
23:55:00как часть вычисления для двух разных дней?
Ответ №1:
Я понятия не имею, какое отношение ваши данные выборки имеют к вопросу — учитывая, что вопрос касается одного столбца, а данные выборки имеют миллионы (и их также трудно читать).
Но решение вашей проблемы состоит в том, чтобы получать данные о потреблении в начале каждого дня и использовать оконные функции. Учитывая, что значения являются кумулятивными, агрегирование с min() работами:
select convert(date, datetime) as dte,
min(Wh_1_R) as first_consumption,
(lead(min(Wh_1_R)) over (order by min(datetime)) -
min(Wh_1_R)
) as daily_consumption
from [dbo].[KMN3FLK_11]
group by convert(date, datetime)
order by min(datetime);
Комментарии:
1. Спасибо, Гордон, я попробовал запрос ur, я получил фактическое значение дня, одна из проблем заключается в том, что я получу нулевое значение для потребления сегодня, поскольку данные tmr не были зарегистрированы. В любом случае, чтобы решить эту проблему? Пожалуйста, сообщите.
2. @JenasTan . , , Если в данный день нет журналов, то дата не будет отображаться в наборе результатов. Я не понимаю вашего комментария.
Ответ №2:
Если я вас правильно понял, вы можете попытаться «удвоить» строки в течение этого 5-минутного интервала с помощью APPLY оператора. Следующий упрощенный пример демонстрирует это распределение:
Таблица:
SELECT *
INTO KMN3FLK_11
FROM (VALUES
(CONVERT(datetime, '2021-01-13T00:00:00'), 141111),
(CONVERT(datetime, '2021-01-12T23:55:00'), 141121),
(CONVERT(datetime, '2021-01-12T00:00:00'), 141131),
(CONVERT(datetime, '2021-01-11T23:55:00'), 141141)
) v ([Datetime], Wh_1_R)
Инструкция:
SELECT
CONVERT(date, a.[Datetime]) As [Datetime],
'KMN3FLK_11' As DPM,
'R3' As Line,
'Machine' As Category,
MAX(k.Wh_1_R) - MIN(k.Wh_1_R) AS Active_P
FROM KMN3FLK_11 k
CROSS APPLY (VALUES
(k.[Datetime]),
(
CASE
WHEN DATEDIFF(day, k.[Datetime], DATEADD(minute, 5, k.[Datetime])) = 1
THEN DATEADD(minute, 5, k.[Datetime])
END
)
) a ([Datetime])
WHERE (a.[Datetime] IS NOT NULL) AND (k.Wh_1_R <> 0)
GROUP BY CONVERT(date, a.[Datetime])
Результат:
Datetime DPM Line Category Active_P
--------------------------------------------
2021-01-11 KMN3FLK_11 R3 Machine 0
2021-01-12 KMN3FLK_11 R3 Machine 20
2021-01-13 KMN3FLK_11 R3 Machine 10