MySQL 常用函数

1 SUBDATE() 函数

1.1 语法

SUBDATE() 函数用于从日期中减去时间, 语法如下:

SUBDATE(date,  INTERVAL expression unit)
1

• date: 要从中减去时间的日期
• expression: 要减去的值
• unit: 要减去的时间单位

1.2 SQL 样例

以下是使用 SUBDATE() 函数的示例:

# 查找 2021-09-15 之前的 7 天的数据  
SELECT SUBDATE('2021-09-15', INTERVAL 7 DAY);  
# 简单写法  
SELECT SUBDATE('2021-09-15', 7);  
1
2
3
4

这将返回 2021 年 9 月 15 日之前的 7 天的日期, 即 2021-09-08

2 ADDDATE() 函数

2.1 语法

ADDDATE(date,   INTERVAL expr unit)
1

• date 参数是合法的日期表达式
• expr 参数是您希望添加的时间间隔
• type 要增加的时间单位

2.2 SQL 样例

在 MySQL 中获取给定日期的后一天日期可以使用 DATE_ADD() 函数可以像这样使用 DATE_ADD() 函数来获取一个日期的后一天:

-- 查找 2021-09-15 的下一天  
SELECT DATE_ADD('2021-09-15',INTERVAL 1 DAY);  
1
2

以上 SQL 语句将返回 2021-09-16, 因为它将’2021-09-15’ 的日期加上一个 INTERVAL 1 DAY(1 天的时间间隔)

还可以使用 DATE_ADD() 函数和 INTERVAL 关键字来添加其他时间间隔, 例如:

-- 查找 2021-09-15 的下一月  
SELECT DATE_ADD('2021-09-15',INTERVAL 1 MONTH);  
1
2

这将返回 2021-10-15, 因为它将’2021-09-15’ 的日期加上一个 INTERVAL 1 MONTH(1 个月的时间间隔)

3 DATEDIFF()

3.1 语法

DATEDIFF(date1,  date2)
1

date1 和 date2 是两个日期 (格式为 YYYY-MM-DD) , 用于计算它们之间的天数. 返回值是一个整数, 表示两个日期之间的天数差

• 如果 date1 的值大于 date2 的值, 则 DATEDIFF() 函数将返回一个正整数
• 如果 date2 的值大于 date1 的值, 则返回一个负整数

3.2 SQL 样例

假设我们有一个 customers 表, 其中包含以下列:

CREATE TABLE customers (  
    id INT NOT NULL,  
    name VARCHAR(50)  NOT NULL,  
    registration_date DATE NOT NULL,  
    last_purchase_date DATE  
);  
1
2
3
4
5
6

我们可以使用以下 SQL 语句在 customers 表中计算每个客户的注册天数和最后一次购买天数之间的天数差:

SELECT id, name, DATEDIFF(NOW(), registration_date) AS registration_days, DATEDIFF(NOW(),  last_purchase_date) AS days_since_last_purchase  
FROM customers;  
1
2

在上面的例子中, 我们使用 DATEDIFF() 函数计算当前日期 (使用 NOW() 函数) 与 registration_date 和 last_purchase_date 列中的日期之间的天数差. 使用 AS 关键字为新计算的列创建了 registration_days 和 days_since_last_purchase 列别名
在运行上面的查询后, 将返回一个结果集, 该结果集包含每个客户的 ID, 姓名, 注册天数和最后一次购买以来的天数

4 SUM() OVER()

4.1 语法

SUM(<column>)  OVER (PARTITION BY <partition_column> ORDER BY <order_column>)
1

• 是需要计算总和的列名
• 是根据哪个列进行分区的列名
• 是按照哪个列进行排序的列名

SUM() OVER() 函数用于对窗口或分组中的行执行聚合函数运算, 并生成与原始表相同的结果集, 但是计算结果不会针对行进行累加, 而是针对所有行执行计算

4.2 MySQL 样例

假设我们有一个 sales 表, 其中包含以下列:

CREATE TABLE sales (  
    id INT NOT NULL comment 'id',  
    region VARCHAR(20)  NOT NULL comment '地区',  
    year INT NOT NULL comment '年份',  
    amount DECIMAL(10,  2)  NOT NULL comment '销售额'  
);  
1
2
3
4
5
6

我们可以使用以下 SQL 语句在 sales 表中计算每个地区和每年的销售总额:

SELECT id, region, year, amount, SUM(amount) OVER (PARTITION BY region, year) AS total_amount  
FROM sales;
1
2

在上面的例子中, 我们使用了 SUM() OVER() 函数对每个地区和每年的 amount 列执行聚合运算, 并将结果存储在名为 total_amount 的新列中, 使用 PARTITION BY 关键字指定要使用哪些列进行分组计算

在运行上面的查询后, 将返回一个结果集, 该结果集包含与 sales 表相同的列, 但是添加了一个名为 total_amount 的新列, 其中包含每个地区和每年的总销售额

5 AVG()

5.1 语法

AVG(column_name)  
AVG(condition)  
1
2

• column_name 统计的字段
• condition 是一个条件表达式, 用于判断值是否满足平均值计算的条件
  MySQL 中的 AVG() 函数用于计算某个列的平均值. 它可以应用于数值和时间列, 忽略其他类型的列. AVG() 函数返回的是一个浮点数
  condition 是一个条件表达式, 用于判断值是否满足平均值计算的条件. 如果满足条件, 则将对这些值计算平均值；如果不满足条件, 则计算结果不包括这些值

5.2 MySQL 样例

假设我们有一个 activity 表, 每一行是一个游戏玩家的记录, 他在某一天使用某个设备注销之前登录并玩了很多游戏 (可能是 0)

CREATE TABLE activity (  
    id INT NOT NULL comment 'id',  
    games_played INT NOT NULL comment '',  
    event_date date NOT NULL comment '日期'  
);  
1
2
3
4
5

测试数据

+-----------+------------+--------------+  
| id        | event_date | games_played |  
+-----------+------------+--------------+  
| 1         | 2016-03-01 | 5            |  
| 1         | 2016-03-02 | 6            |  
| 2         | 2017-06-25 | 1            |  
| 3         | 2016-03-02 | 0            |  
| 3         | 2018-07-03 | 5            |  
+-----------+------------+--------------+  
1
2
3
4
5
6
7
8
9

我们可以使用以下 SQL 语句在 activity 表中, 计算所有游戏玩家的登录游戏数量的平均值

SELECT AVG(games_played) AS average_games_played FROM activity;  
1

查询结果

+----------------------+  
| average_games_played |  
+----------------------+  
|       3.4000         |  
1
2
3
4

如果需要计算玩家登录后, 有玩游戏 (games_played > 0) 的玩家占比

SELECT AVG(games_played > 0) AS average_paly FROM activity;  
1

表中总共 5 条数据, 其中有 1 条数据不满足条件, 登录后有玩游戏的占比是 0.8

+--------------+  
| average_paly |  
+--------------+  
|    0.8000    |  
1
2
3
4