Максимальное использование на каждый день для каждой группы

Каждый час я загружаю данные ЧАСЫ для 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

РЕДАКТИРОВАТЬ ** Я обновил поля, чтобы не отражать зарезервированные слова.

sql-server tsql max

Всего 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