SQL Server Group by Hourly + Display

egdjgwm8 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(121)

I have a database which looks like that:

ID  gesamtzaehler   gutzaehler  timestamp
1   7119529 7007905 2023-03-01 14:09:15.043
2   7119553 7007929 2023-03-01 14:10:15.013
3   7119577 7007953 2023-03-01 14:11:15.043
4   7119601 7007977 2023-03-01 14:12:15.030
5   7119625 7008001 2023-03-01 14:13:15.047
6   7119649 7008025 2023-03-01 14:14:15.010
7   7119673 7008049 2023-03-01 14:15:15.010
8   7119697 7008073 2023-03-01 14:16:15.027
9   7119721 7008097 2023-03-01 14:17:15.027
10  7119731 7008107 2023-03-01 14:18:15.043
11  7119731 7008107 2023-03-01 14:19:15.057
12  7119731 7008107 2023-03-01 14:20:15.040
13  7119731 7008107 2023-03-01 14:21:15.010

and want to get the hourly greatest difference between Gesamtzaehler and Gutzaehler. Everything works fine like that:

SELECT        MAX(gesamtzaehler - gutzaehler) - MIN(gesamtzaehler - gutzaehler) AS Ausschuss, CAST(DATEPART(Hour, timestamp) AS varchar) + ':00' AS Hour
FROM            dbo.Zaehler
WHERE        (timestamp >= DATEADD(hour, - 24, GETDATE()))
GROUP BY DATEPART(hh, timestamp)

The Answer of the Statement is this:

Ausschuss Hour
12  0:00
13  23:00
6   15:00
14  3:00
3   6:00
11  21:00
63  7:00
4   1:00
14  18:00
19  4:00
25  19:00
7   5:00
0   22:00
5   16:00
15  2:00
29  17:00
4   20:00
2   14:00

And here is my problem. For my visualization I need the Hour column to be sorted. As for example a timestamp can be sorted. If it is 7 ' O-Clock the sorting mechanism should bring the result (like order by timestamp 8-24-7)... it will in this example always sort from 0 - 24.

Thank you for your help.

sql-server

来源：https://stackoverflow.com/questions/75612234/group-by-hourly-display

2条答案

按热度按时间

suzh9iv81#

If we take a slightly modified version of your example data (it's really helpful if you provide this as reproducible DDL/DML when asking questions):

DECLARE @Table TABLE (ID BIGINT, gesamtzaehler BIGINT, gutzaehler BIGINT, timestamp DATETIME);
INSERT INTO @Table (ID, gesamtzaehler, gutzaehler, timestamp) VALUES
(1 , 7119529, 7007534, '2023-03-01 17:09:15.043'),(2 , 7119553, 7007543, '2023-03-01 17:10:15.013'),(3 , 7119577, 7007954, '2023-03-01 17:11:15.043'),(4 , 7119881, 7007977, '2023-03-01 17:12:15.030'),
(5 , 7119625, 7008225, '2023-03-01 18:13:15.047'),(6 , 7119649, 7008555, '2023-03-01 18:14:15.010'),(7 , 7119673, 7008045, '2023-03-01 18:15:15.010'),(8 , 7119657, 7008073, '2023-03-01 18:16:15.027'),
(9 , 7119721, 7008254, '2023-03-01 18:17:15.027'),(10, 7119731, 7008444, '2023-03-01 19:18:15.043'),(11, 7119731, 7008107, '2023-03-01 19:19:15.057'),(12, 7119531, 7008107, '2023-03-01 19:20:15.040'),
(13, 7119731, 7008987, '2023-03-01 20:21:15.010'),(14, 7119529, 7007653, '2023-03-02 07:09:15.043'),(15, 7119553, 7007978, '2023-03-02 07:10:15.013'),(16, 7119567, 7007953, '2023-03-02 07:11:15.043'),
(17, 7119601, 7007321, '2023-03-02 07:12:15.030'),(18, 7119625, 7008444, '2023-03-02 08:13:15.047'),(19, 7119649, 7008099, '2023-03-02 08:14:15.010'),(70, 7119573, 7008049, '2023-03-02 09:15:15.010'),
(21, 7119697, 7008277, '2023-03-02 09:16:15.027'),(22, 7119721, 7008044, '2023-03-02 09:17:15.027'),(23, 7119731, 7008124, '2023-03-02 10:18:15.043'),(24, 7119761, 7008107, '2023-03-02 10:19:15.057'),
(25, 7119731, 7008107, '2023-03-02 12:20:15.040'),(26, 7119222, 7008333, '2023-03-02 12:21:15.010');

Then we can do something like this:

;WITH Hours AS (
SELECT DATEADD(MILLISECOND,-DATEPART(MILLISECOND,GETDATE()),DATEADD(SECOND,-DATEPART(SECOND,GETDATE()),DATEADD(MINUTE,-DATEPART(MINUTE,GETDATE()),DATEADD(hour, - 24, GETDATE())))) AS DateTime
UNION ALL
SELECT DATEADD(HOUR,1,DateTime)
  FROM Hours
 WHERE DateTime < GETDATE()
)

SELECT h.DateTime, CAST(h.DateTime AS TIME) AS Time, a.Ausschuss
  FROM Hours h
    LEFT OUTER JOIN (
                     SELECT DATEADD(HOUR,DATEPART(HOUR,timestamp),CAST(CAST(timestamp AS DATE) AS DATETIME)) AS DateTime, 
                            MAX(gesamtzaehler - gutzaehler) - MIN(gesamtzaehler - gutzaehler) AS Ausschuss
                       FROM @Table
                      GROUP BY DATEADD(HOUR,DATEPART(HOUR,timestamp),CAST(CAST(timestamp AS DATE) AS DATETIME))
                    ) a
      ON h.DateTime = a.DateTime
 ORDER BY h.DateTime

DateTime	Time	Ausschuss
2023-03-01 16:00:00.000	16:00:00.0000000	NULL
2023-03-01 17:00:00.000	17:00:00.0000000	387
2023-03-01 18:00:00.000	18:00:00.0000000	534
2023-03-01 19:00:00.000	19:00:00.0000000	337
2023-03-01 20:00:00.000	20:00:00.0000000	0
2023-03-01 21:00:00.000	21:00:00.0000000	NULL
2023-03-01 22:00:00.000	22:00:00.0000000	NULL
2023-03-01 23:00:00.000	23:00:00.0000000	NULL
2023-03-02 00:00:00.000	00:00:00.0000000	NULL
2023-03-02 01:00:00.000	01:00:00.0000000	NULL
2023-03-02 02:00:00.000	02:00:00.0000000	NULL
2023-03-02 03:00:00.000	03:00:00.0000000	NULL
2023-03-02 04:00:00.000	04:00:00.0000000	NULL
2023-03-02 05:00:00.000	05:00:00.0000000	NULL
2023-03-02 06:00:00.000	06:00:00.0000000	NULL
2023-03-02 07:00:00.000	07:00:00.0000000	705
2023-03-02 08:00:00.000	08:00:00.0000000	369
2023-03-02 09:00:00.000	09:00:00.0000000	257
2023-03-02 10:00:00.000	10:00:00.0000000	47
2023-03-02 11:00:00.000	11:00:00.0000000	NULL
2023-03-02 12:00:00.000	12:00:00.0000000	735
2023-03-02 13:00:00.000	13:00:00.0000000	NULL
2023-03-02 14:00:00.000	14:00:00.0000000	NULL
2023-03-02 15:00:00.000	15:00:00.0000000	NULL
2023-03-02 16:00:00.000	16:00:00.0000000	NULL
2023-03-02 17:00:00.000	17:00:00.0000000	NULL

What are we doing here? First, we build out a CTE to give us all the hours from our start time (24 hours ago) until now. This let's us have a row for each hour, even though it may not be present in the data. Then we LEFT OUTER JOIN to our aggregated data to get the values for that hour. In the sub query, we're aggregating the data up in a similar way to your example, but we're preserving the data type of DATETIME.

赞(0）回复(0）举报 2023-03-07

tvokkenx2#

if you are using SQL-Server 2022, you can directly order by datetrunc() function.

If you are using an older version of SQL-Server, you can use the following code( dateadd() and datediff() ) to truncate timestamps to hour and then order by truncated timestamps.

dateadd(HOUR, datediff(HOUR,0,timestamp),0)

Here is the quey:

SELECT 
    MAX(gesamtzaehler - gutzaehler) - MIN(gesamtzaehler - gutzaehler) AS Ausschuss, 
    CAST(DATEPART(Hour, dateadd(HOUR, datediff(HOUR,0,timestamp),0)) AS varchar) + ':00' AS Hour
FROM  Zaehler
WHERE (timestamp >= DATEADD(hour, - 24, GETDATE()))
GROUP BY dateadd(HOUR, datediff(HOUR,0,timestamp),0)
ORDER BY dateadd(HOUR, datediff(HOUR,0,timestamp),0)

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Group by Hourly + Display

2条答案

相关问题

热门标签

最新问答