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

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天或近几天

-- 方式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 30 DAY)

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 30 DAY

GROUP BY recordTime

ORDER BY recordTime;

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

6.按季度分组

CONCAT(DATE_FORMAT(ar.request_start_time, '%Y'), ' Q', QUARTER(ar.request_start_time)) as recordTime

7.按年分组

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

时间从来不语，却回答了所有问题。岁月从来不言，却见证了所有真心。

参考阅读

 您阅读本篇文章共花了：