从零搭建开发脚手架 MySql获取最近一天、一周、一月数据进行统计分析

骑猪看日落 2022-09-06 13:28 194阅读 0赞

# MySql获取最近一天、一周、一月数据进行统计分析 #

> ☎️ ☎️ ☎️ **已开源基于SpringBoot+Mybatisplus+Layui+SnakerFlow前后端分离轻量级工作流引擎的脚手架项目** [easy-admin][]

## 前言 ##

在我的开源项目[EasyAdmin][easy-admin]中，外网地址：http://101.132.189.23/admin/login.html ，想在首页做个统计展示，显示最近一周的访问量折线图以及最近一天访问IP前10名。截图如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70 1][]

这是一个共性问题，总结整理记录下。

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

*  DATE - 格式 YYYY-MM-DD
 *  DATETIME - 格式: YYYY-MM-DD HH:MM:SS
 *  TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
 *  YEAR - 格式 YYYY 或 YY

## 内置函数介绍 ##

### 当前时间 ###

#### now()/curdate()/curtime() ####

`now()`：返回当前的日期和时间

`curdate()`：返回当前的日期

`curtime()`：返回当前的时间

示例：

SELECT NOW(),CURDATE(),CURTIME()

结果类似：

<table> 
 <thead> 
  <tr> 
   <th align="left">NOW()</th> 
   <th align="left">CURDATE()</th> 
   <th align="left">CURTIME()</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">2008-12-29 16:25:46</td> 
   <td align="left">2008-12-29</td> 
   <td align="left">16:25:46</td> 
  </tr> 
 </tbody> 
</table>

> 也可用于建表默认时间`OrderDate datetime NOT NULL DEFAULT NOW(),`

### 处理 ###

#### to\_day() ####

`to_day()`：返回从年份0开始的天数。

示例

SELECT TO_DAYS(‘1997-10-07’);

结果：729669

就是从0年开始 到1997年10月7号之间的天数

#### date() ####

`date()`：返回日期或日期/时间表达式的日期部分。用法`DATE(date)`,*date* 参数是合法的日期表达式。

示例：

<table> 
 <thead> 
  <tr> 
   <th align="left">OrderId</th> 
   <th align="left">ProductName</th> 
   <th align="left">OrderDate</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">‘Computer’</td> 
   <td align="left">2008-12-29 16:25:46.635</td> 
  </tr> 
 </tbody> 
</table>

SELECT ProductName, DATE(OrderDate) AS OrderDate

结果：

<table> 
 <thead> 
  <tr> 
   <th align="left">ProductName</th> 
   <th align="left">OrderDate</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">‘Computer’</td> 
   <td align="left">2008-12-29</td> 
  </tr> 
 </tbody> 
</table>

#### quarter() ####

`quarter()`：返回日期的季度，QUARTER(*date*)，date：必须项。从中提取季度的日期或日期时间

示例

SELECT QUARTER("2017-06-15"); 结果：2

QUARTER()函数返回给定日期值（1到4之间的数字）的一年中的季度。

*  1月至3月返回1
 *  4月至6月返回2
 *  7月至9月返回3
 *  10月至12月返回4

#### extract() ####

`extract()`：返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

**语法**

EXTRACT(unit FROM date)

*date* 参数是合法的日期表达式。*unit* 参数可以是下列的值

**SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR等**

示例

**OrderDate：2008-12-29 16:25:46.635**

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
    	   EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
           EXTRACT(DAY FROM OrderDate) AS OrderDay

<table> 
 <thead> 
  <tr> 
   <th align="left">OrderYear</th> 
   <th align="left">OrderMonth</th> 
   <th align="left">OrderDay</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">2008</td> 
   <td align="left">12</td> 
   <td align="left">29</td> 
  </tr> 
 </tbody> 
</table>

#### date\_add()/date\_sub() ####

`date_add()`：给日期添加指定的时间间隔

`date_sub()`：从日期减去指定的时间间隔

**语法**

DATE_ADD(date,INTERVAL expr type)

*date* 参数是合法的日期表达式。*expr* 参数是您希望添加的时间间隔。

type 参数可以是下列值：

**SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR等**

示例

**OrderDate：2008-12-29 16:25:46.635**

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate 加2天

结果：

<table> 
 <thead> 
  <tr> 
   <th align="left">OrderId</th> 
   <th align="left">OrderPayDate</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">2008-12-31 16:25:46.635</td> 
  </tr> 
 </tbody> 
</table>

#### datediff() ####

`datediff()`：返回两个日期之间的天数

DATEDIFF(date1,date2)

*date1* 和 *date2* 参数是合法的日期或日期/时间表达式。

\*\*注释：\*\*只有值的日期部分参与计算。

示例

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate 结果为：1
    SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate 结果为: -1

#### period\_diff() ####

`period_diff()`：返回两个时期之间的差异,结果以月份计算,

PERIOD_DIFF(period1, period2)

参数格式为：YYMM或YYYYMM

示例

SELECT PERIOD_DIFF('201710', '201703'); 结果为：7

### 格式化 ###

#### date\_format() ####

`date_format()`：用不同的格式显示日期/时间

用法

DATE_FORMAT(date,format)

*date* 参数是合法的日期。*format* 规定日期/时间的输出格式。

示例

DATE_FORMAT(NOW(),'%Y-%m-%d') 2021-08-20
    DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') Dec 29 2008 11:45 PM
    DATE_FORMAT(NOW(),'%m-%d-%Y') 12-29-2008
    DATE_FORMAT(NOW(),'%d %b %y') 29 Dec 08
    DATE_FORMAT(NOW(),'%d %b %Y %T:%f') 29 Dec 2008 16:25:46.635

**可以使用的格式有：**

<table> 
 <thead> 
  <tr> 
   <th align="left">格式</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">%a</td> 
   <td align="left">缩写星期名</td> 
  </tr> 
  <tr> 
   <td align="left">%b</td> 
   <td align="left">缩写月名</td> 
  </tr> 
  <tr> 
   <td align="left">%c</td> 
   <td align="left">月，数值</td> 
  </tr> 
  <tr> 
   <td align="left">%D</td> 
   <td align="left">带有英文前缀的月中的天</td> 
  </tr> 
  <tr> 
   <td align="left">%d</td> 
   <td align="left">月的天，数值(00-31)</td> 
  </tr> 
  <tr> 
   <td align="left">%e</td> 
   <td align="left">月的天，数值(0-31)</td> 
  </tr> 
  <tr> 
   <td align="left">%f</td> 
   <td align="left">微秒</td> 
  </tr> 
  <tr> 
   <td align="left">%H</td> 
   <td align="left">小时 (00-23)</td> 
  </tr> 
  <tr> 
   <td align="left">%h</td> 
   <td align="left">小时 (01-12)</td> 
  </tr> 
  <tr> 
   <td align="left">%I</td> 
   <td align="left">小时 (01-12)</td> 
  </tr> 
  <tr> 
   <td align="left">%i</td> 
   <td align="left">分钟，数值(00-59)</td> 
  </tr> 
  <tr> 
   <td align="left">%j</td> 
   <td align="left">年的天 (001-366)</td> 
  </tr> 
  <tr> 
   <td align="left">%k</td> 
   <td align="left">小时 (0-23)</td> 
  </tr> 
  <tr> 
   <td align="left">%l</td> 
   <td align="left">小时 (1-12)</td> 
  </tr> 
  <tr> 
   <td align="left">%M</td> 
   <td align="left">月名</td> 
  </tr> 
  <tr> 
   <td align="left">%m</td> 
   <td align="left">月，数值(00-12)</td> 
  </tr> 
  <tr> 
   <td align="left">%p</td> 
   <td align="left">AM 或 PM</td> 
  </tr> 
  <tr> 
   <td align="left">%r</td> 
   <td align="left">时间，12-小时（hh:mm:ss AM 或 PM）</td> 
  </tr> 
  <tr> 
   <td align="left">%S</td> 
   <td align="left">秒(00-59)</td> 
  </tr> 
  <tr> 
   <td align="left">%s</td> 
   <td align="left">秒(00-59)</td> 
  </tr> 
  <tr> 
   <td align="left">%T</td> 
   <td align="left">时间, 24-小时 (hh:mm:ss)</td> 
  </tr> 
  <tr> 
   <td align="left">%U</td> 
   <td align="left">周 (00-53) 星期日是一周的第一天</td> 
  </tr> 
  <tr> 
   <td align="left">%u</td> 
   <td align="left">周 (00-53) 星期一是一周的第一天</td> 
  </tr> 
  <tr> 
   <td align="left">%V</td> 
   <td align="left">周 (01-53) 星期日是一周的第一天，与 %X 使用</td> 
  </tr> 
  <tr> 
   <td align="left">%v</td> 
   <td align="left">周 (01-53) 星期一是一周的第一天，与 %x 使用</td> 
  </tr> 
  <tr> 
   <td align="left">%W</td> 
   <td align="left">星期名</td> 
  </tr> 
  <tr> 
   <td align="left">%w</td> 
   <td align="left">周的天 （0=星期日, 6=星期六）</td> 
  </tr> 
  <tr> 
   <td align="left">%X</td> 
   <td align="left">年，其中的星期日是周的第一天，4 位，与 %V 使用</td> 
  </tr> 
  <tr> 
   <td align="left">%x</td> 
   <td align="left">年，其中的星期一是周的第一天，4 位，与 %v 使用</td> 
  </tr> 
  <tr> 
   <td align="left">%Y</td> 
   <td align="left">年，4 位</td> 
  </tr> 
  <tr> 
   <td align="left">%y</td> 
   <td align="left">年，2 位</td> 
  </tr> 
 </tbody> 
</table>

*  以上整理自：https://www.w3school.com.cn/sql/sql\_dates.asp

## 常用记录 ##

### 查询今天数据 ###

select * from 表名
    	where to_days(时间字段名) = to_days(now());

### 查询昨天数据 ###

SELECT * FROM 表名 
    	WHERE to_days(now()) - to_days( 时间字段名) <= 1

### 查询最近7天数据 ###

SELECT * FROM 表名 
    	where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

### 查询本周的数据 ###

select * from 表名
    	WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

### 查询上周的数据 ###

select * from 表名 
    	WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

### 查询本月数据 ###

SELECT * FROM 表名 
    	WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

### 查询上一月的数据 ###

SELECT * FROM 表名 
    	WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) = 1

### 查询本季度数据 ###

select * from 表名
    	where QUARTER(create_date)=QUARTER(now());

### 查询上季度数据 ###

select * from 表名
    	where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

### 查询本年数据 ###

select * from 表名 
    	where YEAR(create_date)=YEAR(NOW());

### 查询上年数据 ###

select * from 表名
    	where year(create_date)=year(date_sub(now(),interval 1 year));

[easy-admin]: https://gitee.com/lakernote/easy-admin
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70]: /images/20220829/b0666b652f4e46618691f4c235030fa3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70 1]: /images/20220829/904a8d56acd34e8882ad071d9805cd0e.png