`

SQL实现分组统计查询(按月、小时分组)

阅读更多

转自:http://xuzhaoshancm.blog.163.com/blog/static/51118891200981454846536/

首先创建数据表IP地址,访问时间和访问次数。如果每访问一次就插入一条记录,那么AccessCount可以不要,查询时使用count就可以了,这样当访问量很大的时候会对数据库造成很大压力。设置AccessCount字段可以根据需求在特定的时间范围内如果是相同IP访问就在AccessCount上累加。

Create table Counter
(
    CounterID int identity(1,1) not null,
    IP varchar(20),
    AccessDateTime datetime,
    AccessCount int
)

该表在这儿只是演示使用,所以只提供了最基本的字段


现在往表中插入几条记录

insert into Counter
select '127.0.0.1',getdate(),1 union all
select '127.0.0.2',getdate(),1 union all
select '127.0.0.3',getdate(),1

 

1 根据年来查询,以月为时间单位

通常情况下一个简单的分组就能搞定

select 
    convert(varchar(7),AccessDateTime,120) as Date,
    sum(AccessCount) as [Count]
from
    Counter 
group by 
    convert(varchar(7),AccessDateTime,120)

像这样分组后没有记录的月份不会显示,如下:

 

这当然不是我们想要的,所以得换一种思路来实现,如下: 

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 by   
    m

 

查询结果如下:

 

2 根据天来查询,以小时为单位。这个和上面的类似,代码如下: 

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)> =a 

                  and datepart(hour,AccessDateTime) <b 
        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 '


 

查询结果如下图:

 

 

分享到:
评论

相关推荐

    SQLserver 实现分组统计查询(按月、小时分组)

    这两种方法都利用了SQL的分组函数(如`SUM`)和条件语句(如`CASE`)来实现分组统计。这样的查询方式能够帮助我们快速获取按月或按小时的访问统计信息,即使在某些时间段没有数据的情况下也能正确展示。 请注意,...

    Oracle分组统计

    例如,我们可以使用以下 SQL 语句来实现分组统计: ```sql SELECT a, b, c, d, SUM(e) FROM dept GROUP BY GROUPING SETS ((a, b), (c, d)); ``` 这条语句将对 dept 表中的数据进行分组统计,首先按照 a 和 b 列...

    mysql 统计一天24小时数据默认补0SQL

    这里使用 `HOUR` 函数来获取 `login_time` 的小时部分,并用 `GROUP BY hours` 来分组统计数据。 ```sql SELECT HOUR(login_time) AS hours, SUM(user_id) AS count FROM tb_log_login WHERE DATE_FORMAT(login...

    SQL SERVER 分组求和sql语句

    分组后分组合计以及总计SQL语句(稍微整理了一下)MYSQL每隔10分钟进行分组统计的实现方法mysql使用GROUP BY分组实现取前N条记录的方法详解MySQL中的分组查询与连接查询语句sql server如何利用开窗函数over()进行...

    sql中时间以5分钟半个小时任意间隔分组的实现方法

    本文主要探讨如何在SQL中以5分钟或半个小时为间隔对时间数据进行分组,以达到统计特定时间段内数据的目的。我们将介绍两种不同的实现方法,一种是通过自定义函数,另一种则是利用时间戳和算术运算。 方法1:自定义...

    24小时SQL教程

    《24小时SQL教程》是一本专为初学者设计的SQL Server 2005学习指南,涵盖了SQL语言的基础到进阶知识。在24个精心编排的章节中,读者将逐步掌握如何使用SQL进行数据管理和分析。下面将详细阐述这个教程中涉及的主要...

    MYSQL每隔10分钟进行分组统计的实现方法

    基本思路是利用MySQL的日期时间函数处理时间戳字段,提取出我们需要进行分组的时间单元(如小时、分钟等),然后在查询语句中使用GROUP BY进行分组统计。 一个常用的函数是`UNIX_TIMESTAMP`,它将日期时间类型转换...

    sqlserver/mysql按天、按小时、按分钟统计连续时间段数据【推荐】

    - **按月**:类似地,用`DateAdd(MONTH, number, start_date)`和`DateDiff(MONTH, start_date, end_date)`获取指定月份范围内的连续月份。 - **按天**:通过`DateAdd(DAY, number, start_date)`和`DateDiff(DAY, ...

    sql时间查询语句学习

    在这里,`DATEPART`函数被用来提取日期的月份部分,而`GROUP BY`子句则按照月份对结果进行分组,从而实现按月统计。 #### 按小时、天统计特定时间段的数据 同样,我们也可以按小时或天来进行更精细的统计数据。...

    SQL工具查寻WINCC归档数据.rar

    4. **高级查询技巧**:除了基础查询,还可以利用SQL的聚合函数(如SUM, AVG, MAX, MIN)进行统计分析,使用JOIN操作合并多表数据,或者使用子查询和临时表进行复杂的数据处理。例如,我们可以计算过去一小时的平均...

    非常完美Java实现年、月、日、周访问量统计

    8. **数据聚合**:对于更复杂的需求,比如按小时或按用户分组统计,可能需要使用数据聚合框架,如Apache Commons Math的统计模块,或者更强大的Apache Spark或Hadoop。 9. **异常处理**:在处理文件或数据库操作时...

    审计模式下常用的SQL查询语句示例.doc

    ### 审计模式下的SQL查询语句解析 ...无论是简单的单条件查询还是复杂的分组统计,SQL都提供了强大的工具来帮助审计人员高效完成任务。熟练掌握这些查询技巧对于提高审计工作的质量和效率至关重要。

    sql两日速成

    为了实现更复杂的数据操作,SQL还提供了子查询、联结(JOIN)、连接(UNION)、事务控制(如COMMIT和ROLLBACK)、以及用于数据安全的权限管理命令等高级功能。事务控制命令确保了一系列操作的原子性,保证数据的一致...

    laravel实现按月或天或小时统计mysql数据的方法

    在本文中,我们将深入探讨如何使用 Laravel 框架实现按月、按天或按小时统计 MySQL 数据的方法。Laravel 是一个流行的 PHP 开发框架,它提供了丰富的工具和功能,使得处理数据库操作变得简单易行。 首先,我们需要...

    MYSQL 统计每小时数据,统计一天24小时数据默认补0

    最后,通过`GROUP BY tm.currentHour`按小时进行分组统计。 完整的SQL查询如下: ``` SELECT tm.currentHour, COUNT(gf.id) AS distributionNo FROM ( -- 获取 24 小时 SELECT @xi := (CASE WHEN @xi + 1 &gt; 9 ...

    数据库大作业程序_sql_数据库_

    此外,可能还需要实现一些高级功能,如数据聚合、分组、联接查询,甚至使用视图和存储过程简化复杂查询。 在开发过程中,测试和调试也是必不可少的步骤,确保SQL语句的正确性和效率。同时,良好的文档记录将有助于...

    1 小时 SQL 极速入门(一).pdf

    ### 1 小时 SQL 极速入门(一) #### SQL简介及CRUD操作 本文档旨在通过简短的学习过程帮助读者快速掌握SQL的基础知识及其在企业级开发中的应用。SQL(Structured Query Language,结构化查询语言)是一种专门用于...

    自己整理的sql笔试题

    SQL笔试题通常考察应聘者...总结来看,本次整理的SQL笔试题覆盖了基础查询语句、连接查询、聚合函数与分组、以及查询性能考量等多方面的知识点。熟练掌握这些知识点对于应聘者来说至关重要,是成功通过SQL笔试的关键。

    学习SQL的看过来!!!24小时自学SQL(第四版)2008最新版.

    《24小时自学SQL(第四版)2008最新版》是一本全面且深入的SQL学习指南,专为SQL初学者以及有一定基础的学习者设计。SQL,全称Structured Query Language,即结构化查询语言,是数据库管理的核心工具,用于处理关系型...

Global site tag (gtag.js) - Google Analytics