Сумма значений каждого элемента по часам от даты до даты в SQL
Я много искал, но не мог найти ответ. Может кто подскажет: У меня есть таблица, содержащая следующие столбцы и данные (просто пример):
+-------+------------------+----+--------+--------+
| Type | InsertDate | ID | Value1 | Value2 |
+-------+------------------+----+--------+--------+
| Data | 2019-04-29 14:30 | 01 | 2 | 1 |
| Info | 2019-04-29 14:30 | 01 | 3 | 3 |
| Data | 2019-04-29 14:42 | 01 | 5 | 5 |
| Data | 2019-04-29 14:42 | 02 | 8 | 5 |
| Info | 2019-04-28 09:30 | 02 | 1 | 7 |
| Data | 2019-04-28 12:30 | 02 | 8 | 0 |
| Data | 2019-04-28 12:10 | 02 | 3 | 1 |
+-------+------------------+----+--------+--------+
Теперь я хочу добиться суммирования Value1 ежечасно для каждого идентификатора, где тип — это данные в настроенное время. т.е. с 28.04 по 29.04 я хотел бы иметь вывод вроде:
+----+------------------+------------------+
| ID | 12:00 28-04-2019 | 14:00 29-04-2019 |
+----+------------------+------------------+
| 01 | 0 | 7 |
| 02 | 11 | 8 |
+----+------------------+------------------+
Я нашел много примеров того, как ежечасно суммировать Value1 для одного идентификатора, но мне нужно иметь вывод для нескольких идентификаторов.
Сейчас у меня есть запрос ниже:
DECLARE @cols NVARCHAR(20000)
SELECT
ID,
DATEADD(hour, DATEDIFF(hour, '00:00:00', InsertDate), '00:00:00') as TruncatedToHour,
sum(Value1) as data
INTO t3
FROM table1
WHERE Type = 'Data' AND InsertDate > '2019-04-28 00:00'
GROUP BY
ID,
DATEADD(hour, DATEDIFF(hour, '00:00:00', InsertDate), '00:00:00')
SELECT @cols = LIST(DISTINCT cast(TruncatedToHour as nvarchar(1000)), ', ') FROM t3
Этот запрос возвращает все необходимые данные (но не в правильном формате) и динамически создает имена столбцов для необходимой структуры таблицы (@cols). Теперь мне нужно создать необходимую структуру таблицы и заполнить ее нужными данными. Кто-нибудь может помочь?
@SeanLange.. 11 верно. потому что тип 5-го ряда Info.
@ Пугал, ты прав.
@SeanLange Я сделал первую часть, сгруппировав все по идентификатору и дате (я отредактировал вопрос с моим запросом). Теперь я борюсь со второй частью - динамической таблицей. Не могли бы вы дать мне еще несколько советов, как это сделать в Sybase?
@SeanLange Я нашел решение самостоятельно и опубликовал его как ответ.
Ответы 1
Хорошо, я найду решение, которое работает для меня. Я определенно не эксперт по Sybase, но я хочу поделиться этим решением с сообществом. Может быть, это поможет кому-то или какой-то специалист улучшит его лучше.
Мое решение (я добавил комментарии, чтобы объяснить, что делает код):
DECLARE @delimiter char(1), @quotechar char(1), @pattern varchar(500), @piece varchar(500), @pos int, @datefrom TEXT
DECLARE @cols TEXT
DECLARE @query TEXT
SET @datefrom = '2019-04-26 00:00' --//---> Define the 'date from' here
SET @quotechar = CHAR (39)
SET @delimiter = ','
--//Group everything by id and date and store it to temp table #t3
SELECT
ID,
DATEADD(minute, -DATEPART(minute, InsertDate), InsertDate) as TruncatedToHour,
sum(Value1) as data
INTO #t3
FROM table1
WHERE Type = 'Data' AND InsertDate >= @datefrom
GROUP BY
ID,
DATEADD(minute, -DATEPART(minute, InsertDate), InsertDate)
--//Select distinct the dates and hours from #t3 to create columns for the result table
--//and return them as delimited list
SELECT @cols = LIST(DISTINCT cast(TruncatedToHour as TEXT), ',') FROM #t3
--//Prepare the query which will create the result table
SET @pattern = '%' + @delimiter + '%'
SET @pos = patindex(@pattern , @cols)
SELECT @query = 'SELECT ID, max (case when TruncatedToHour = ' + @quotechar
--//Loop through all column names from @cols and add them to the query
WHILE @pos <> 0
BEGIN
SET @piece = LEFT(@cols, @pos - 1)
--// In @piece we have now a single column name
SELECT @query = @query + @piece + @quotechar + ' then data else null end) ' +@quotechar +@piece + @quotechar + ', max (case when TruncatedToHour = ' + @quotechar
SET @cols = stuff(@cols, 1, @pos, '')
SET @pos = patindex(@pattern , @cols)
END
SELECT @query = @query + ' INTO #t4 FROM #t3 GROUP BY ID ORDER BY ID'
SELECT @query = REPLACE(@query, ', max (case when TruncatedToHour = ' + @quotechar + ' ', ' ')
--//Execute the @query and collect the data from #t4
EXECUTE(@query)
SELECT * FROM #t4 ORDER BY ID
Не уверен, какова логика для значений времени в ваших столбцах, и я думаю, что у вас должно быть 12, а не 11 для идентификатора 02. Сначала вы просто группируете все по идентификатору и дате. Затем динамическая перекрестная таблица или динамическая сводная таблица для получения столбцов.