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 中最重要的内建日期函数：

函数描述
NOW()返回当前的日期和时间
CURDATE()返回当前的日期
CURTIME()返回当前的时间
DATE()提取日期或日期/时间表达式的日期部分
EXTRACT()返回日期/时间按的单独部分
DATE_ADD()给日期添加指定的时间间隔
DATE_SUB()从日期减去指定的时间间隔
DATEDIFF()返回两个日期之间的天数
DATE_FORMAT()用不同的格式显示日期/时间

现在假设我们有一张用户表 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;

有道友说不理解这个怎么回事，明明不是范围判断为何可以实现这个功能呢？
首先需要知道DATE_SUB(CURDATE(),INTERVAL 0 DAY)是获取距离今天间隔天数的日期
范围是年月日，不包括时分秒。
DATE_SUB() 函数从日期减去指定的时间间隔，具体用法见：https://www.runoob/sql/func-date-sub.html
INTERVAL 是间隔函数，INTERVAL 0 DAY 表示距离当前时间减去0天，也就是今天。
INTERVAL 1 DAY 表示距离当前时间减去1天，即距离当前时间间隔1天，包括今天和昨天。
参考：https://wwwblogs/kenshinobiy/p/4920730.html>
为了更易于理解，我们来一个示例，我们可以看到下面sql获取到的日期是2022年7月26日，也就是今天的日期，
那么我们按照文中获取到的就是最后一次打开app的时间大于2022-07-26 00:00:00，也就是今天所有打开记录> 如果我们修改下，那么获取到日期就是2022-07-25
DATE(last_open_app_date) >= DATE_SUB(CURDATE(),INTERVAL 1 DAY) 就变成了最后一次打开app的时间大于2022-07-25 00:00:00 获取到的就是最近两天的app打开记录。
依此类推，可以好好理解下。

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 规定日期/时间的输出格式。

可以使用的格式有：

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

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 参数可以是下列值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

假设我们有如下的表：

订单Id产品名称订单日期
OrderIdProductNameOrderDate
1‘Computer’2008-12-29 16:25:46.635

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

我们使用下面的 SELECT 语句：

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

3.参考文献

  • SQL教程
  • SQL Date 函数

更多推荐

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