mysql按照1分钟,5分钟,1小时为分组统计数据,控制横坐标的显示方式_mysql 每5分钟一组统计_这名字可真靓的博客

业务需求需要页面横坐标以5分钟一个点,显示选定区间段的数据,如果时间跨度大于一天,就按照一小时一个点进行统计.

首先通过查询找到了可以通过mysql实现5分钟分组的数据查询.
## 按5分钟分组查数据
SELECT count(id),DATE_FORMAT(FLOOR(DATE_FORMAT(c_time, '%Y%m%d%H%i%s')/500)*500,'%Y-%m-%d %H:%i') as time FROM tableName GROUP BY time
## 按照1小时分组查询数据
SELECT count(id),DATE_FORMAT(FLOOR(DATE_FORMAT(c_time, '%Y%m%d%H%i%s')/500)*500,'%Y-%m-%d %H') as time FROM tableName GROUP BY time
## 按照天分组查询数据
SELECT count(id),DATE_FORMAT(FLOOR(DATE_FORMAT(c_time, '%Y%m%d%H%i%s')/500)*500,'%Y-%m-%d ') as time FROM tableName GROUP BY time
处理时间段不连续的问题:
 
 但是发现了新的问题,由于mysql是根据时间字段c_time进行统计,时间段可能发生不连续的情况.例如2:05 下一个点可能是2:15.为了能够在前端正确的展示,需要在后端进行处理.先上代码(但请先看代码解释). 
     * @param startTime
     * @param endTime
     * @param timeLimit  时间粒度     1M 代表一分钟,1H 代表一小时,1D 代表一天.
     * @return  连续的时间集合
public static List<String> splitTimeList(String startTime, String endTime, String timeLimit) {
        String[] str = new String[]{"yyyy/MM/dd HH:mm"};
        List times = null;
        String defPattern = "yyyy/MM/dd HH:mm:ss";
        SimpleDateFormat sdf = new SimpleDateFormat(defPattern);
        int dayOfYear = Calendar.MINUTE;
        try {
            int num = 1;
            if (timeLimit.indexOf("H") != -1) {
                str[0] = "yyyy/MM/dd HH";
                dayOfYear = Calendar.HOUR_OF_DAY;
            if (timeLimit.indexOf("D") != -1) {
                str[0] = "yyyy/MM/dd";
                dayOfYear = Calendar.DAY_OF_YEAR;
            if (timeLimit.equals("5M")) {
                num = 5;
            if (timeLimit.equals("1M")) {
                num = 1;
            long l = Long.parseLong(startTime);
            long e = Long.parseLong(endTime);
            startTime = sdf.format(new Date(l));
            endTime = sdf.format(new Date(e));
            if ("5M".equals(timeLimit) || "1M".equals(timeLimit)) {
                // 2021/01/28 07:20:40-->0 获取个位分钟数(20中的0)
                String substring = startTime.substring(startTime.length() - 4, startTime.length() - 3);
                int i = Integer.parseInt(substring);
                if (i < 5) {
                    i = 0;
                } else {
                    i = 5;
                // 处理开始时间的分钟位,要么是0 要么是5
                //  2021/01/28 07:2+0+:40
                startTime = startTime.substring(0, startTime.length() - 4) + i + startTime.substring(startTime.length() - 3, startTime.length());
            times = getTimes(startTime, endTime, dayOfYear, num, str);
        } catch (Exception e) {
            e.printStackTrace();
        return times;
     * 获取时间段
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param pattern   格式化模型
     * @return List 时间list
     * @throws Exception
public static List getTimes(String startTime, String endTime, int type, int num, String... pattern) throws Exception {
        String defPattern = "yyyy/MM/dd HH:mm:ss";
        if (pattern != null && pattern.length > 0) {
            defPattern = pattern[0];
        SimpleDateFormat sdf = new SimpleDateFormat(defPattern);
        List listKey = new ArrayList();
        Calendar startCalendar = Calendar.getInstance();
        Date end = sdf.parse(endTime);
        Date start = sdf.parse(startTime);
        startCalendar.setTime(start);
        while (startCalendar.getTime().getTime() <= end.getTime()) {
            listKey.add(sdf.format(startCalendar.getTime()));
            // 以num为基数增加时间 例如 5min  1小时等
            startCalendar.add(type, num);
            if (startCalendar.getTime().getTime() - end.getTime() > 0) {
                String format = sdf.format(new Date(startCalendar.getTime().getTime()));
        return listKey;
代码解释:
 
 timeLimit:分隔时间的维度,1M 代表一分钟,1H 代表一小时,1D 代表一天. 
 如果认真看一定很容易懂,思路有点绕的是第36行,如同timeLimit为5M或者1M,通过截取字符串去获取分钟的个位数,为了之后以5分钟为间隔操作时间.num即对应的间隔. 
 第45行,以分钟为维度的话,处理开始时间的分钟的个位数,取整为5或者0,例如2021/01/31 10:12:40 变化成2021/01/31 10:10:40 
 第二个方法核心步骤就是第24行,通过Calendar类进行时间间隔的累加,返回List.你也可以用java8中特有的时间进行操作. 
 相信通过以上方法,应该能够解决你当下的问题,起码能够应付客户的需求了,但是总感觉还有更简便更巧妙的方法,如果有请在评论区里面留言,我们一起讨论,谢谢.
                    业务需求需要页面横坐标以5分钟一个点,显示选定区间段的数据,如果时间跨度大于一天,就按照一小时一个点进行统计.首先通过查询找到了可以通过mysql实现5分钟分组的数据查询.## 按5分钟分组查数据SELECT count(id),DATE_FORMAT(FLOOR(DATE_FORMAT(c_time, '%Y%m%d%H%i%s')/500)*500,'%Y-%m-%d %H:%i') as time FROM tableName GROUP BY time## 按照1小时分组查询数据SELEC
				mysql不同时间粒度下的分组统计
我们在做项目或者数据分析时，经常遇到这样的需求：统计不同时间粒度下的数据分布情况，例如，每一天中每个小时网站的访问量，某路口每半个小时通过的车辆数量等。对于此类的问题，一个sql简单的查询就能实现，故特此记录下，方便以后使用。
在MySQL中，我的表为：track 数据结构如下所示：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RrtyupaQ-1636080436464)(images/20181105163738204.png)]
获取每个用户每天的总数 - 用户每天在5分钟重复的次数；
1、查出用户第一次请求时间，计算从这次时间开始的5分钟内，有多少次请求；
2、获取5分钟后，第一次最小请求时间，计算从这次时间开始的5分钟内，有多少次请求，并累加；
3、循环，直到没有最小时间；
4、用总次数 - 重
				--无秒值
SELECT DATE_FORMAT(FLOOR(DATE_FORMAT('2019-09-12 09:09:08', '%Y%m%d%H%i%s')/500)*500,'%Y-%m-%d %H:%i');
SELECT DATE_FORMAT(FLOOR(DATE_FORMAT('2019-09-12 09:11:08', '%Y%m%d%H%i%s')/500)*500,'%Y-...
				在Mysql数据库中按照不同时间粒度进行分组统计
最近遇到的需求，在Mysql数据库中按照不同时间粒度进行分组统计，返回的数据用做画echarts图使用。下面介绍以：秒、分钟、小时、天、周（本周，上周，最近7天）、月、年进行分组统计，仅以此做个人笔记和分享，粒度不够的评论后补。
现在开始介绍，以下是的我用来做演示的部分测试数据
我们以统计未恢复和已恢复的告警状态为例，那首先说明一下告警状态值的含义：
‘01’,‘02’,‘03’,‘04’ 表示未恢复状态
‘10’, ‘11’, ‘12’, ‘20