我如何编写一些SQL来返回显示每小时发生的行数?

I need to produce a SQL report showing the number of times a particular event happened in each hourly period during the day. My table has a date/time column on it containing the time the event occurred.

我需要生成一个SQL报告，显示一天中每小时发生一次特定事件的次数。我的表上有一个日期/时间列，其中包含事件发生的时间。

How do I do a count of the number of rows that fall within each each hourly period during the day?

如何计算每天每小时内的行数?

So I need to see output like this...

所以我需要看到这样的输出。

10:00 - 11:00 12 times

10:00 - 11:00 12次。

11.00 - 12:00 53 times

11.00 - 12:00 53次

12:00 - 13:00 5 times etc

12:00 - 13:00 5次等。

I'm guessing it would be a Group By, but how do you group by each hour? Thanks in advance.

我猜是一组一组一组，但是你怎么按小时分组呢?提前谢谢。

2 个解决方案

#1

SELECT DATEPART(hh, DateTimeColumn), COUNT(*)
FROM
    TableName
GROUP BY
    DATEPART(hh, DateTimeColumn)
ORDER BY
    DATEPART(hh, DateTimeColumn)

#2

Seans solution will only work with 24 hours worth of data as datepart dd only returns 0-23.

Seans解决方案将只使用24小时的数据，因为datepart dd只返回0-23。

If you need to process more than that, you'll need to add in the day too.

如果你需要处理更多，你也需要增加一天。

Something like:

喜欢的东西:

SELECT CAST(DateTimeColumn AS INT) [day],DATEPART(hh, DateTimeColumn), COUNT(*)
FROM
    TableName
GROUP BY
    CAST(DateTimeColumn AS INT),
    DATEPART(hh, DateTimeColumn)
ORDER BY
    CAST(DateTimeColumn AS INT),
    DATEPART(hh, DateTimeColumn

#1

SELECT DATEPART(hh, DateTimeColumn), COUNT(*)
FROM
    TableName
GROUP BY
    DATEPART(hh, DateTimeColumn)
ORDER BY
    DATEPART(hh, DateTimeColumn)

#2