mysql统计（1）- 常见的时间范围和时间维度统计

ゝ一世哀愁。 2023-10-15 20:49 34阅读 0赞

[**mysql常用的日期和时间格式化选项**][mysql]

request\_start\_time在mysql中的数据类型是datetime。

## 一、时间范围 ##

示例的所有sql都增加了时间分组，如果不需要，删除recordTime相关统计即可。

### 1.统计今日 ###

select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d %H') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where DATE(ar.request_start_time) = CURDATE() 
    GROUP BY recordTime
    ORDER BY recordTime;

### 2.统计近1个小时或近几个小时 ###

-- 方式1
    select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d %H:%i') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
    GROUP BY recordTime
    ORDER BY recordTime;
    
    -- 方式2
    select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d %H:%i') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time  >=  NOW() - INTERVAL 1 HOUR
    GROUP BY recordTime
    ORDER BY recordTime;
    
    -- 需要统计近24小时  修改INTERVAL 后面的数值即可   
    -- 根据自己的需求修改时间格式 一小时可以使用分钟展示，
    -- 24小时则可以使用小时方式展示：DATE_FORMAT(ar.request_start_time, '%Y-%m-%d %H')

### 3.统计近1天或近几天 ###

### 4.统计近一周或近几周 ###

-- 方式1
    select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time  >= DATE_SUB(NOW(), INTERVAL 1 week)
    GROUP BY recordTime
    ORDER BY recordTime;
    
    -- 方式2
    select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time  >= NOW() - INTERVAL 1 week
    GROUP BY recordTime
    ORDER BY recordTime;
    -- 方式3 可以使用第三点统计  天数设为7即可

### 5.统计本月 ###

-- 方式1
    select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where MONTH(ar.request_start_time) = MONTH(CURDATE()) 
    AND YEAR(ar.request_start_time) = YEAR(CURDATE())
    GROUP BY recordTime
    ORDER BY recordTime;
    
    -- 方式2
    select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time >= DATE_FORMAT(NOW() ,'%Y-%m-01')
    GROUP BY recordTime
    ORDER BY recordTime;

### 6.统计近一个月或近几个月、近一个季度 ###

-- 方式1
    select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time  >= DATE_SUB(NOW(), INTERVAL 1 month)
    GROUP BY recordTime
    ORDER BY recordTime;
    
    -- 方式2
    select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time  >= NOW() - INTERVAL 1 month
    GROUP BY recordTime
    ORDER BY recordTime;
    
    -- 方式3 可以使用第三点统计  天数设为30即可
    
    -- 多个月分可以考虑按月显示：DATE_FORMAT(ar.request_start_time, '%Y-%m')
    
    -- 近一个季度设置为3个月即可（不准确），使用时间过滤配合即可

### 7.统计本季度 ###

select DATE_FORMAT(ar.request_start_time, '%Y-%m-%d') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where QUARTER(ar.request_start_time) = QUARTER(CURDATE()) 
    AND YEAR(ar.request_start_time) = YEAR(CURDATE())
    GROUP BY recordTime
    ORDER BY recordTime;

### 8.统计近1年或近几年 ###

-- 方式1
    select DATE_FORMAT(ar.request_start_time, '%Y-%m') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time  >= DATE_SUB(NOW(), INTERVAL 1 year)
    GROUP BY recordTime
    ORDER BY recordTime;
    
    -- 方式2
    select DATE_FORMAT(ar.request_start_time, '%Y-%m') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where ar.request_start_time  >= NOW() - INTERVAL 1 YEAR
    GROUP BY recordTime
    ORDER BY recordTime;

### 9.统计本年 ###

-- 方式1
    select DATE_FORMAT(ar.request_start_time, '%Y-%m') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where YEAR(ar.request_start_time) = YEAR(NOW())
    GROUP BY recordTime
    ORDER BY recordTime;
    
    -- 方式2
    select DATE_FORMAT(ar.request_start_time, '%Y-%m') as recordTime,
           COUNT(ar.id) as total
    from biz_api_record ar where 
    ar.request_start_time >= DATE_FORMAT(NOW(), '%Y-01-01')
    GROUP BY recordTime
    ORDER BY recordTime;

## 二、时间维度 ##

sql语句就参考上面的了，时间的维度统计基本都是按查询的时间格式分组，这里讲的是recordTime的时间格式，

### 1.按分钟分组 ###

DATE_FORMAT(ar.request_start_time, '%Y-%m-%d %H:%i') as recordTime

### 2.按小时分组 ###

DATE_FORMAT(ar.request_start_time, '%Y-%m-%d %H') as recordTime

### 3.按天分组 ###

DATE_FORMAT(ar.request_start_time, '%Y-%m-%d') as recordTime

### 4.按周分组 ###

DATE_FORMAT(ar.request_start_time, '%X %V') as recordTime

### 5.按月分组 ###

DATE_FORMAT(ar.request_start_time, '%Y-%m') as recordTime