MySQL基础——按月/周/日分组统计数据

ゝ一纸荒年。 2022-12-17 05:57 376阅读 0赞

# MySQL基础——按月/周/日分组统计数据 #

在使用MySQL进行查询时，很多时候，我们需要按时间段进行统计，例如每周，每月的数据；这里我们需要使用到MySQL的关键词：`DATE_FORMAT`

## 1. 语法 ##

select DATE_FORMAT(upload_time,'%Y%u') weeks,count(SN_ID) count from table_sn group by weeks;  -- 按照周分组
    select DATE_FORMAT(upload_time,'%Y%m%d') days,count(SN_ID) count from table_sn group by days;  -- 按照日分组 
    select DATE_FORMAT(upload_time,'%Y%m') months,count(SN_ID) count from table_sn group by months;  -- 按照月分组

解释：  
![在这里插入图片描述][20201022104036303.png_pic_center]

## 2. 时间格式化修饰符 ##

上述的分组格式中，使用特定的修饰符来格式化时间字符串，所用到的修饰符，如下表所示：

<table> 
 <thead> 
  <tr> 
   <th>修饰符</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%M</td> 
   <td>月名字(January……December)</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td>星期名字(Sunday……Saturday)</td> 
  </tr> 
  <tr> 
   <td>%D</td> 
   <td>有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）</td> 
  </tr> 
  <tr> 
   <td>%Y</td> 
   <td>年, 数字, 4 位</td> 
  </tr> 
  <tr> 
   <td>%y</td> 
   <td>年, 数字, 2 位</td> 
  </tr> 
  <tr> 
   <td>%a</td> 
   <td>缩写的星期名字(Sun……Sat)</td> 
  </tr> 
  <tr> 
   <td>%d</td> 
   <td>月份中的天数, 数字(00……31)</td> 
  </tr> 
  <tr> 
   <td>%e</td> 
   <td>月份中的天数, 数字(0……31)</td> 
  </tr> 
  <tr> 
   <td>%m</td> 
   <td>月, 数字(01……12)</td> 
  </tr> 
  <tr> 
   <td>%c</td> 
   <td>月, 数字(1……12)</td> 
  </tr> 
  <tr> 
   <td>%b</td> 
   <td>缩写的月份名字(Jan……Dec)</td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td>一年中的天数(001……366)</td> 
  </tr> 
  <tr> 
   <td>%H</td> 
   <td>小时(00……23)</td> 
  </tr> 
  <tr> 
   <td>%k</td> 
   <td>小时(0……23)</td> 
  </tr> 
  <tr> 
   <td>%h</td> 
   <td>小时(01……12)</td> 
  </tr> 
  <tr> 
   <td>%I</td> 
   <td>小时(01……12)</td> 
  </tr> 
  <tr> 
   <td>%l</td> 
   <td>小时(1……12)</td> 
  </tr> 
  <tr> 
   <td>%i</td> 
   <td>分钟, 数字(00……59)</td> 
  </tr> 
  <tr> 
   <td>%r</td> 
   <td>时间,12 小时(hh:mm:ss [AP]M)</td> 
  </tr> 
  <tr> 
   <td>%T</td> 
   <td>时间,24 小时(hh:mm:ss)</td> 
  </tr> 
  <tr> 
   <td>%S</td> 
   <td>秒(00……59)</td> 
  </tr> 
  <tr> 
   <td>%s</td> 
   <td>秒(00……59)</td> 
  </tr> 
  <tr> 
   <td>%p</td> 
   <td>AM或PM</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td>一个星期中的天数(0=Sunday ……6=Saturday ）</td> 
  </tr> 
  <tr> 
   <td>%U</td> 
   <td>星期(0……52), 这里星期天是星期的第一天</td> 
  </tr> 
  <tr> 
   <td>%u</td> 
   <td>星期(0……52), 这里星期一是星期的第一天</td> 
  </tr> 
  <tr> 
   <td>%%</td> 
   <td>一个文字“%”</td> 
  </tr> 
 </tbody> 
</table>

## 3. 实例 ##

> 统计表中每月的SN个数；

*  表table\_sn的结构如下表：

<table> 
 <thead> 
  <tr> 
   <th>NO</th> 
   <th>PO_No</th> 
   <th>SN_ID</th> 
   <th>Upload_time</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>PO_1234543</td> 
   <td>1F5478563251</td> 
   <td>2020-07-13 13:08:33</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>PO_1234544</td> 
   <td>1F5478563252</td> 
   <td>2020-07-13 14:01:30</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>…</td> 
   <td>…</td> 
   <td>…</td> 
  </tr> 
 </tbody> 
</table>

*  执行语句：

select DATE_FORMAT(upload_time,'%Y%m') months,count(SN_ID) count from table_sn group by months;

*  输出结果：

<table> 
 <thead> 
  <tr> 
   <th>months</th> 
   <th>count</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>202007</td> 
   <td>1638280</td> 
  </tr> 
  <tr> 
   <td>202009</td> 
   <td>257889</td> 
  </tr> 
  <tr> 
   <td>202010</td> 
   <td>95496</td> 
  </tr> 
 </tbody> 
</table>

[20201022104036303.png_pic_center]: /images/20221123/f6c329adb2c84d2d94dd2aa8e158a5a7.png