Каждый час я загружаю данные ЧАСЫ для 6 разных групп (DG). Как получить максимальное значение (использование) для каждой группы за каждый день? Кажется, я не могу собрать его по дате.
Пример данных (показаны 2 часа загрузки, но для каждого дня будет 24)
DG Usage UploadDateTime
--------- --------- ----------
1 100 2012-08-30 10:00:03.000
2 500 2012-08-30 10:00:03.000
3 400 2012-08-30 10:00:03.000
4 550 2012-08-30 10:00:03.000
5 220 2012-08-30 10:00:03.000
6 110 2012-08-30 10:00:03.000
1 200 2012-08-30 11:00:03.000
2 600 2012-08-30 11:00:03.000
3 450 2012-08-30 11:00:03.000
4 200 2012-08-30 11:00:03.000
5 240 2012-08-30 11:00:03.000
6 50 2012-08-30 11:00:03.000
Предполагаемый вывод: (Это только один день, для которого потребуется блок для каждого дня доступных данных)
DG Usage UploadDateTime
--------- --------- ----------
1 200 2012-08-30 11:00:03.000
2 600 2012-08-30 11:00:03.000
3 450 2012-08-30 11:00:03.000
4 550 2012-08-30 10:00:03.000
5 240 2012-08-30 11:00:03.000
6 110 2012-08-30 10:00:03.000
РЕДАКТИРОВАТЬ ** Я обновил поля, чтобы не отражать зарезервированные слова.
Всего 3 ответа
Если вы хотите, чтобы время после даты было таким же, как в «предполагаемом выводе», вы можете использовать номер строки:
SELECT [group],
usage,
[datetime]
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY [group], CAST([datetime] AS DATE) ORDER BY usage DESC) AS Row_Num
FROM #Usage
) UsageRow
WHERE Row_Num = 1;
Во-первых, вам нужно будет преобразовать столбец date в дату только с помощью CONVERT(date, [UploadDateTime]) . Я предполагаю, что у вашего фактического столбца есть имя, которое не является datetime и вы хотите, чтобы SUM для каждой группы для каждого дня было тем, что показывают результаты выборки.
Затем с помощью SUM и GROUP BY вы можете сделать что-то вроде:
SELECT
DG,
SUM(Usage) AS [UseForDay],
CONVERT(date, [UploadDateTime]) AS [UploadDateTime]
FROM
MyTable
GROUP BY
DG,
CONVERT(date, [UploadDateTime])
Я уверен, что это то, что вы хотите (я добавил немного данных для другой даты)
use tempdb
GO
DROP TABLE IF EXISTS #DATA
CREATE TABLE #DATA (
DG INTEGER
, Usage INTEGER
, UploadDateTime DATETIME2(3)
)
INSERT INTO #DATA (
DG, Usage, UploadDateTime
)
SELECT 1, 100, -08-30 10:00:03.000' UNION ALL
SELECT 2, 500, -08-30 10:00:03.000' UNION ALL
SELECT 3, 400, -08-30 10:00:03.000' UNION ALL
SELECT 4, 550, -08-30 10:00:03.000' UNION ALL
SELECT 5, 220, -08-30 10:00:03.000' UNION ALL
SELECT 6, 110, -08-30 10:00:03.000' UNION ALL
SELECT 1, 200, -08-30 11:00:03.000' UNION ALL
SELECT 2, 600, -08-30 11:00:03.000' UNION ALL
SELECT 3, 450, -08-30 11:00:03.000' UNION ALL
SELECT 4, 200, -08-30 11:00:03.000' UNION ALL
SELECT 5, 240, -08-30 11:00:03.000' UNION ALL
SELECT 6, 50 , -08-30 11:00:03.000' UNION ALL
SELECT 6, 51 , -08-31 10:00:03.000' UNION ALL
SELECT 6, 52 , -08-31 11:00:03.000'
SELECT * FROM #DATA
-- what is the max usage for each DG by date
SELECT DG, CAST(UploadDateTime AS DATE) AS UploadDate, MAX(Usage)
FROM #DATA
GROUP BY DG, CAST(UploadDateTime AS DATE)
-- using the max usage for each DG by date to join back to the orginal data
-- to get the datetime that relates to the max value
; WITH MAXES AS (
SELECT DG, CAST(UploadDateTime AS DATE) AS UploadDate, MAX(Usage) AS MaxUsage
FROM #DATA
GROUP BY DG, CAST(UploadDateTime AS DATE)
)
SELECT #DATA.*
FROM #DATA
INNER JOIN MAXES
ON #DATA.DG = MAXES.DG
AND CAST(#DATA.UploadDateTime AS DATE) = MAXES.UploadDate
AND #DATA.Usage = MaxUsage
ORDER BY UploadDate ASC, DG ASC