MySQL中使用SQL统计App的日活,周活,月活,年活

你的名字 2023-01-21 09:22 14阅读 0赞

### MySQL中使用SQL统计App的日活,周活,月活,年活 ###

*  1.MySQL中使用SQL统计App的日活,周活,月活,年活
 *   *  1.1 统计App当天的日活
     *   *  1.1.1 第一种方法 使用DATE\_FORMAT 函数
         *  1.1.2 第二种方法 使用TO\_DAYS 函数
         *  1.1.3 第三种方法 使用DATE\_SUB 函数
     *  1.2 统计App 周活(包括今天)
     *  1.3 统计App 月活(包括今天)
     *  1.4 统计App 年活(包括今天)
 *  2. 附录
 *   *  2.1 附录一 MySQL DATE\_FORMAT() 函数
     *   *  2.1.1 定义和用法
         *  2.1.2 语法
         *  2.1.3 实例
     *  2.3. 附录二 MYSQL DATE\_SUB 函数
 *  3.参考文献

# 1.MySQL中使用SQL统计App的日活,周活,月活,年活 #

大家好,我是技术宅星云,今天这篇博文我们来总结下MySQL中使用SQL统计App的日活,周活,月活,年活。

下面的表格列出了 MySQL 中最重要的内建日期函数：

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_now.asp" rel="nofollow">NOW()</a></td> 
   <td align="left">返回当前的日期和时间</td> 
  </tr> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_curdate.asp" rel="nofollow">CURDATE()</a></td> 
   <td align="left">返回当前的日期</td> 
  </tr> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_curtime.asp" rel="nofollow">CURTIME()</a></td> 
   <td align="left">返回当前的时间</td> 
  </tr> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_date.asp" rel="nofollow">DATE()</a></td> 
   <td align="left">提取日期或日期/时间表达式的日期部分</td> 
  </tr> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_extract.asp" rel="nofollow">EXTRACT()</a></td> 
   <td align="left">返回日期/时间按的单独部分</td> 
  </tr> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_date_add.asp" rel="nofollow">DATE_ADD()</a></td> 
   <td align="left">给日期添加指定的时间间隔</td> 
  </tr> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_date_sub.asp" rel="nofollow">DATE_SUB()</a></td> 
   <td align="left">从日期减去指定的时间间隔</td> 
  </tr> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_datediff_mysql.asp" rel="nofollow">DATEDIFF()</a></td> 
   <td align="left">返回两个日期之间的天数</td> 
  </tr> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_date_format.asp" rel="nofollow">DATE_FORMAT()</a></td> 
   <td align="left">用不同的格式显示日期/时间</td> 
  </tr> 
 </tbody> 
</table>

现在假设我们有一张用户表 t\_user\_info ,这张表中有一个叫做last\_open\_app\_date 的日期字段.

## 1.1 统计App当天的日活 ##

统计当前的日活一共有三种方法：

### 1.1.1 第一种方法 使用DATE\_FORMAT 函数 ###

第一种方法 使用DATE\_FORMAT 函数统计当天的日活

SELECT
    	COUNT( 1 ) 
    FROM
    	t_user_info 
    WHERE
    	DATE_FORMAT( last_open_app_date, '%Y-%m-%d' )= DATE_FORMAT( NOW(), '%Y-%m-%d' ) 
    	AND last_open_app_date IS NOT NULL;

### 1.1.2 第二种方法 使用TO\_DAYS 函数 ###

第二种方法 使用TO\_DAYS 函数统计当天的日活

SELECT
    	COUNT( 1 ) 
    FROM
    	t_user_info 
    WHERE
    	TO_DAYS( last_open_app_date ) = TO_DAYS(NOW()) 
    	AND last_open_app_date IS NOT NULL;

### 1.1.3 第三种方法 使用DATE\_SUB 函数 ###

第三种方法 使用DATE\_SUB 函数统计当天的日活

SELECT
    	COUNT( 1 ) 
    FROM
    	t_user_info 
    WHERE
    DATE(last_open_app_date) >= DATE_SUB(CURDATE(),INTERVAL 0 DAY)
    	AND last_open_app_date IS NOT NULL;

## 1.2 统计App 周活(包括今天) ##

使用DATE\_SUB 函数统计当天的周活

SELECT
    	COUNT( 1 ) 
    FROM
    	t_user_info 
    WHERE
    DATE(last_open_app_date) >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
    	AND last_open_app_date IS NOT NULL;

## 1.3 统计App 月活(包括今天) ##

使用DATE\_SUB 函数统计当天的月活

SELECT
    	COUNT( 1 ) 
    FROM
    	t_user_info 
    WHERE
    DATE(last_open_app_date) >= DATE_SUB(CURDATE(),INTERVAL 30 DAY)
    	AND last_open_app_date IS NOT NULL;

## 1.4 统计App 年活(包括今天) ##

使用DATE\_SUB 函数统计当天的年活

SELECT
    	COUNT( 1 ) 
    FROM
    	t_user_info 
    WHERE
    DATE(last_open_app_date) >= DATE_SUB(CURDATE(),INTERVAL 365 DAY)
    	AND last_open_app_date IS NOT NULL;

# 2. 附录 #

## 2.1 附录一 MySQL DATE\_FORMAT() 函数 ##

### 2.1.1 定义和用法 ###

DATE\_FORMAT() 函数用于以不同的格式显示日期/时间数据。

### 2.1.2 语法 ###

DATE_FORMAT(date,format)

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

可以使用的格式有：

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

### 2.1.3 实例 ###

下面的脚本使用 DATE\_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
    DATE_FORMAT(NOW(),'%m-%d-%Y')
    DATE_FORMAT(NOW(),'%d %b %y')
    DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

结果类似：

Dec 29 2008 11:45 PM
    12-29-2008
    29 Dec 08
    29 Dec 2008 16:25:46.635

## 2.3. 附录二 MYSQL DATE\_SUB 函数 ##

*  **定义和用法**

DATE\_SUB() 函数从日期减去指定的时间间隔。

*  **语法**

DATE\_SUB(date,INTERVAL expr type)

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

type 参数可以是下列值：

<table> 
 <thead> 
  <tr> 
   <th align="left">Type 值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">MICROSECOND</td> 
  </tr> 
  <tr> 
   <td align="left">SECOND</td> 
  </tr> 
  <tr> 
   <td align="left">MINUTE</td> 
  </tr> 
  <tr> 
   <td align="left">HOUR</td> 
  </tr> 
  <tr> 
   <td align="left">DAY</td> 
  </tr> 
  <tr> 
   <td align="left">WEEK</td> 
  </tr> 
  <tr> 
   <td align="left">MONTH</td> 
  </tr> 
  <tr> 
   <td align="left">QUARTER</td> 
  </tr> 
  <tr> 
   <td align="left">YEAR</td> 
  </tr> 
  <tr> 
   <td align="left">SECOND_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td align="left">MINUTE_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td align="left">MINUTE_SECOND</td> 
  </tr> 
  <tr> 
   <td align="left">HOUR_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td align="left">HOUR_SECOND</td> 
  </tr> 
  <tr> 
   <td align="left">HOUR_MINUTE</td> 
  </tr> 
  <tr> 
   <td align="left">DAY_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td align="left">DAY_SECOND</td> 
  </tr> 
  <tr> 
   <td align="left">DAY_MINUTE</td> 
  </tr> 
  <tr> 
   <td align="left">DAY_HOUR</td> 
  </tr> 
  <tr> 
   <td align="left">YEAR_MONTH</td> 
  </tr> 
 </tbody> 
</table>

假设我们有如下的表：

<table> 
 <thead> 
  <tr> 
   <th>订单Id</th> 
   <th>产品名称</th> 
   <th>订单日期</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>OrderId</td> 
   <td>ProductName</td> 
   <td>OrderDate</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>‘Computer’</td> 
   <td>2008-12-29 16:25:46.635</td> 
  </tr> 
 </tbody> 
</table>

现在，我们希望从 `OrderDate` 减去 2 天。

我们使用下面的 SELECT 语句：

SELECT
    	OrderId,
    	DATE_SUB( OrderDate, INTERVAL 2 DAY ) AS OrderPayDate 
    FROM
    	Orders

# 3.参考文献 #

>  *  [SQL教程][SQL]
>  *  [SQL Date 函数][SQL Date]

[SQL]: https://www.w3school.com.cn/sql/index.asp
[SQL Date]: https://www.w3school.com.cn/sql/sql_dates.asp