Grouping statistics query (grouping by month and hour)

Create a data table IP address, access time, and access count. If you insert a record every time you access the database, the AccessCount is not required. You can use count for queries. This puts a lot of pressure on the database when the traffic is large.

Create a data table IP address, access time, and access count. If you insert a record every time you access the database, the AccessCount is not required. You can use count for queries. This puts a lot of pressure on the database when the traffic is large.

You can set the AccessCount field to accumulate access from the same IP address in a specific time range as required. The Code is as follows:
Create table Counter
(
CounterID int identity (1, 1) not null,
IP varchar (20 ),
AccessDateTime datetime,
AccessCount int
)

This table is only used for demonstration, so only the most basic fields are provided.
Insert several records into the table
Insert into Counter
Select '2014. 0.0.1 ', getdate (), 1 union all
Select '2014. 0.0.2 ', getdate (), 1 union all
Select '2014. 0.0.3 ', getdate (), 1

1. query by year, in the unit of month
Generally, a simple grouping can be done.
The Code is as follows:
Select
Convert (varchar (7), AccessDateTime, 120) as Date,
Sum (AccessCount) as [Count]
From
Counter
Group
Convert (varchar (7), AccessDateTime, 120)

The month that is not recorded after grouping like this will not be displayed, as shown below:
The Code is as follows:
Declare @ Year int
Set @ Year = 2009
Select
M as [Date],
Sum (
Case when datepart (month, AccessDateTime) = m
Then AccessCount else 0 end
) As [Count]
From
Counter c,
(
Select 1 m
Union all select 2
Union all select 3
Union all select 4
Union all select 5
Union all select 6
Union all select 7
Union all select 8
Union all select 9
Union all select 10
Union all select 11
Union all select 12
) Aa
Where
@ Year = year (AccessDateTime)
Group
M

The query result is as follows:

2. query by day, in hours. This is similar to the above Code. The Code is as follows:
The Code is as follows:
Declare @ DateTime datetime
Set @ DateTime = getdate ()
Select
Right (100 + a, 2) + ': 00->' + right (100 + B, 2) + ': 00' as DateSpan,
Sum (
Case when datepart (hour, AccessDateTime)> =
And datepart (hour, AccessDateTime)Then AccessCount else 0 end
) As [Count]
From Counter c,
(Select 0 a, 1 B
Union all select 1, 2
Union all select 2, 3
Union all select 3, 4
Union all select 4, 5
Union all select 5, 6
Union all select 6, 7
Union all select 7,8
Union all select 8, 9
Union all select 9, 10
Union all select 10, 11
Union all select 11,12
Union all select 12,13
Union all select 13, 14
Union all select 14,15
Union all select 15,16
Union all select 16, 17
Union all select 17,18
Union all select 18, 19
Union all select 19,20
Union all select 20, 21
Union all select 21,22
Union all select 22, 23
Union all select 23,24
) Aa
Where datediff (day, @ DateTime, AccessDateTime) = 0
Group by right (100 + a, 2) + ': 00->' + right (100 + B, 2) + ': 00'

The query result is as follows: