SQL how to sum events in one hour between two date

问题:

I am developing a report with C# and SQL server 2005, I have to only show how many hit we got in each hour. the table is very huge. output should look like this:

Row# |     Date    |  Time  | Hit Count
-----------------------------
1    | 07/05/2012  |  8:00  |  3
2    | 07/05/2012  |  9:00  |  4
3    | 07/05/2012  |  10:00 |  0
4    | 07/05/2012  |  11:00 |  5

My table is look like this:

"HitTime":

07/05/2012 08:02:24
07/05/2012 08:12:21
07/05/2012 08:23:00
07/05/2012 09:01:00
07/05/2012 09:08:14
07/05/2012 09:12:31
07/05/2012 09:22:27

..etc As you see in HitTime field I only have date and time, I need to show at same date, from for example 8:00 till 8:59 how many hit did I get, and it should be for all day, from very first second that day starts till very end second of the day.

回答1:

DECLARE @current_date DATETIME

SET @current_date = '2012-05-07';

WITH    hours (hr) AS
        (
        SELECT  0
        UNION ALL
        SELECT  hr + 1
        FROM    hours
        WHERE   hr < 23
        )
SELECT  ROW_NUMBER() OVER (ORDER BY hr) AS rn,
        @current_date AS [date],
        CONVERT(VARCHAR(5), DATEADD(hour, h.hr, @current_date), 108) AS [time],
        COUNT(hs.hittime) AS hitcount
FROM    hours h
LEFT JOIN
        hits hs
ON      hs.hittime >= DATEADD(hour, h.hr, @current_date)
        AND hs.hittime < DATEADD(hour, h.hr + 1, @current_date)
GROUP BY
        hr

回答2:

How about this?

;WITH aggregation(hit_date, hit_time)
AS
(
SELECT DATEADD(dd, DATEDIFF(dd, 0, hittime), 0)
     , DATEPART(hour, hittime)
FROM test
)
SELECT ROW_NUMBER() OVER (ORDER BY hit_date, hit_time) AS rn
     , CONVERT(VARCHAR,hit_date,101) as [date]
     , CAST(hit_time AS VARCHAR) + ':00' as [time]
     , COUNT(*) as hit_count
FROM aggregation
  GROUP BY hit_date
         , hit_time

回答3:

WITH hit_count AS(
select CONVERT(VARCHAR,hit_time,101)as [date], substring(convert(varchar,hit_time,108), 0, 4)+'00' as [time] from hit
)
select date,[time], count(*) as hit from hit_count group by [time],[date]

And if You want AM/PM then :

WITH hit_count AS(
select CONVERT(VARCHAR,hit_time,101)as [date], (substring(convert(varchar,hit_time,100), 12, 4)+'00'+substring(convert(varchar,hit_time,100),18,2)) as [time] from hit
)
select date,[time], count(*) as hit from hit_count group by [time],[date]
GO