sql - Count distinct values in an SQL table for each hour in a day

Question

I have a table that looks like this:

ID    TimeStamp    CarNumber
1    2018\12\03 14:05:32    433
2    2018\12\03 14:13:52    420
3    2018\12\03 14:55:14    433
4    2018\12\03 15:12:03    420
5    2018\12\03 16:15:55    570

I have to get the number of distinct car numbers for each hour of the day, so what I do now is run a command for each hour separately:

SELECT DISTINCT CarNumber
FROM Cars
WHERE TimeStamp BETWEEN '2018\12\03 14:00:00' AND '2018\12\03 15:00:00'

Is there a way to build a table that will do that for each hour of the day automatically? Expected output:

Hour    NumberOfCars    CarNumbers
0       0               0
...
14      2               433, 420
15      1               420
16      1               570
...

标签： sqlsql-server-2008

Answer 1

You can aggregate by date and hour:

select cast(timestamp as date) as thedate,
       datepart(hour, timestamp) as thehour,
       count(distinct CarNumber) as num_cars
from cars
group by cast(timestamp as date), datepart(hour, timestamp)
order by thedate, thehour;

You can add a where clause if you want the results for only one date. If you want the results aggregated over multiple days, remove thedate from the logic:

select datepart(hour, timestamp) as thehour,
       count(distinct CarNumber) as num_cars
from cars
group by datepart(hour, timestamp)
order by thehour;