@@ -1,58 +0,0 @@ Mysql按日、周、月进行分组统计 | 凤凰涅槃进阶之路

Mysql按日、周、月进行分组统计

Abel sun2022年12月24日
约 905 字大约 3 分钟

Mysql按日、周、月进行分组统计

1. 背景

我们在用Mysql抽取数据时候,经常需要按照天、周、月等不同的粒度对数据进行分组统计。而我们的时间可能是“2017/12/5 0:0:0”这种准确的时间。所以在进行分组之前我们需要对时间进行一下处理。

2. DATE_FORMAT

DATE_FORMAT是MySQL内置的一个函数,作用是以不同的格式显示日期/时间数据。具体的语法如下:

DATE_FORMAT(date,format),其中

date:合法的日期。format:规定日期/时间的输出格式,其中format可使用的格式见文末链接。

2.1 示例

下面我们通过具体例子来看如何通过DATE_FORMAT进行分组统计:

下表两列分别代表产品买出的准确时间(精确到秒),和买出的产品类型。

start_timeproduct_no
2017/12/1 00:00:112A
2017/12/3 07:51:113C
2017/12/3 07:59:253C
2017/12/5 15:40:456C

现在我们需要对每天,每周,每月各个产品的销量进行统计,

1)按天统计:

select DATE_FORMAT(start_time,'**%Y%m%d**') days,count(product_no) count from test group by days; 

2)按周统计:

select DATE_FORMAT(start_time,'**%Y%u**') weeks,count(product_no) count from test group by weeks; 

3)按月统计:

select DATE_FORMAT(start_time,'**%Y%m**') months,count(product_no) count from test group bymonths; 

2.2 面临问题

如果有一天没有数据,则改直接直接为空白

需求注:按照时间段来查询显示该时间段内每一天的数据量,如果某一天没有数据,显示数据量为0.

image-20210126170727871

3. 优化方案

查询日期表,外连接数据表来显示 网络上查询到生成日期表的方法有几种

  1. 需要另建一张日期表,直接从此表查询(比较麻烦)
  2. 使用笛卡尔积生成时间(推荐)

3.1 生成连续日期表

select date_add('2020-04-01',interval @i:=@i+1 day) as date 
from (
select 1 
union all select 1 
union all select 1
union all select 1 
union all select 1 
union all select 1 
union all select 1 
union all select 1) as tmp,
 (select @i:= -1) t

结果如下

image-20210126171101910

3.2 关联查询显示

select count(u.id) as count,s.date from 
(select date_add('2020-04-01',interval @i:=@i+1 day) as date 
from (
select 1 
union all select 1 
union all select 1
union all select 1 
union all select 1 
union all select 1 
union all select 1 
union all select 1) as tmp,
 (select @i:= -1) t
) s left join user u on s.date = date_format(u.createtime,'%Y-%m-%d')
GROUP BY s.date

3.3 如何控制生成多少个日期

现在还有一个问题,如何控制生成多少个日期,也就是union all select 1 的个数,

  1. 使用存储过程(耦合性过高,不易维护,暂不考虑)
  2. mybatis 循环拼接(√)

选择了java代码计算日期差,使用mybatis的 foreach 标签实现sql拼接,全部代码如下

  • service

     @Override
        public Object queryByDate(String[] date) {
            String date1 = date[0];
            String date2 = date[1];
            int num = calcBetweenDate(date1, date2);
            String[] countArr = new String[num];
            return userMapper.queryByDate(date1,countArr);
        }
        
        public int calcBetweenDate(String start, String end) {  
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");  
            Date startDate = null;  
            Date endDate = null;  
            try {  
                startDate = df.parse(start);  
                endDate = df.parse(end);  
            } catch (Exception e) {  
                System.out.println("日期转换出错");  
            }  
            int count = (int) ((endDate.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000));  
            return count;  
        }  
    
  • mapper

    List<Map<String,Object>> queryByDate(@Param("date")String date, @Param("countArr")String[] countArr);
    
  • xml

    	<select id="queryByDate" resultType="java.util.HashMap">
    	   select count(u.id) as count,s.date from 
    		(select date_add('2020-04-01',interval @i:=@i+1 day) as date 
    		from (
    		select 1 
    	   	<foreach item="index" collection="countArr">
    		  union all select 1 
    		</foreach>
    		) as tmp,
    		 (select @i:= -1) t
    		) s left join user u on s.date = date_format(u.createtime,'%Y-%m-%d')
    		GROUP BY s.date
    	</select>
    
    

参考文章

mysql查询一个时间段每天数据数量,没有显示为0open in new window

评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.9.1