1 Вопрос: Суммируйте значения каждого элемента по часам от даты к дате в SQL

вопрос создан в Thu, May 2, 2019 12:00 AM

Я много искал, но не смог найти ответ. Может быть, кто-то может дать мне несколько советов: У меня есть таблица, которая содержит следующие столбцы и данные (только пример):

+-------+------------------+----+--------+--------+
| 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 ежечасно для каждого идентификатора, где Type - это Data в настроенное время. То есть с 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). Теперь мне нужно создать необходимую структуру таблицы и заполнить ее правильными данными. Кто-нибудь может помочь?

    
2
  1. Не уверен, какова логика для значений времени в ваших столбцах, и я думаю, у вас должно быть 12, а не 11 для идентификатора 02. Сначала вы просто группируете все по идентификатору и дате , Затем динамическая кросс-таблица или динамический круг для получения столбцов.
    2019-05-02 15: 26: 28Z
  2. @ SeanLange .. 11 верно. потому что тип 5-й строки - Info.
    2019-05-02 15: 32: 43Z
  3. @ Правильно, Пугал.
    2019-05-02 15: 45: 25Z
  4. @ SeanLange Я сделал первую часть, сгруппировав все по идентификатору и дате (я отредактировал вопрос с помощью моего запроса). Сейчас я борюсь со второй частью - динамическим столом. Можете ли вы дать мне еще несколько советов, как это сделать в Sybase?
    2019-05-06 11: 22: 50Z
  5. @ SeanLange Я нашел решение самостоятельно и опубликовал его как ответ.
    2019-05-07 07: 58: 57Z
1 ответ                              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
    
1
2019-05-07 11: 02: 59Z
источник размещен Вот