常用的函数-MySQL

1.截取日期中的年月日

SELECT DATE_FORMAT('2022-09-20 18:24:20', '%Y%m%d') 
FROM dual;

2.计算某个日期之后几天或者之前几天

SET @dt = 	NOW();
SELECT @dt FROM DUAL;
SELECT DATE_ADD(@dt, INTERVAL 3 DAY) FROM DUAL;  # 当前日期+3天
 
SELECT @dt FROM DUAL;
SELECT DATE_ADD(@dt, INTERVAL -1 DAY) FROM DUAL; # 当前日期-1天

当前日期+3

当前日期-1

笔试题

题目： 表字段：pay_date（日期）、user_id（用户id）、user_type（用户类型）、amount（消费金额）。

表名：table_name

• 问题一：每天每类用户的总消费金额；
• 问题二：每天每类用户的消费增长率（与3天前（pay_date - 3）的消费金额做对比）；问题二需要返回的列：pay_date，user_type，amount，amount_new(3天前金额)，amount_rate（增长率）。

分析:

• 问题一：考察分组函数(Group By)的使用
• 问题二：考察等值连接(内连接)的使用，以及日期+天数需要使用到的函数，以及根据日期提取日期中的年、月、日的函数的使用。

问题一比较简单，问题二也不难，但是稍微有些麻烦。

具体考点如下：

• 内连接

内连接(我们可以称为是等值连接)，当表a 内连接 表b 时，需要使用 ON 表名连接的条件，不符合该条件的表a和表b中的其他记录都不显示，只显示表a和表b中满足 ON 后面的条件的记录。具体语句如下：

select 字段1, 字段2
from 表a 
inner join 表b
on a.字段1 = b.字段2;

内连接的inner咱们统一不省略，以上是内连接的使用方法，这是标准的SQL语法形式。以下是一种不标准的书写形式，比较简洁，如下：

select *
from  表a,
      表b
where a.字段1 = b.字段2;

这种方法不是标准的SQL，虽然简洁，不是不建议写了，两者得到的答案是一样的。

建表

首先，我们根据题意先建立 t_user 表，代码如下：

CREATE TABLE `t_user` (
	`order_id` int(10) AUTO_INCREMENT NOT NULL COMMENT '订单ID',
  `pay_date` datetime NOT NULL COMMENT '支付日期',
  `user_id`  int(10) COMMENT '用户id',
  `user_type` int(10) COMMENT '用户类型',
	`amount`    int COMMENT '消费金额',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
DROP table `t_user`;

插入测试数据

然后，我们插入测试数据：

insert into `t_user`(`pay_date`, `user_id`, `user_type`, `amount`) values
('2022-09-01 18:25:26', 10001, 01, 800),
('2022-09-01 15:20:26', 10002, 02, 200),
('2022-09-02 14:20:10', 10001, 01, 2000),
('2022-09-03 12:10:02', 10003, 03, 100),
('2022-09-02 20:10:18', 10001, 01, 500),
('2022-09-04 18:25:26', 10001, 01, 800),
('2022-09-05 15:20:26', 10002, 02, 200),
('2022-09-06 14:20:10', 10005, 01, 2000),
('2022-09-07 12:10:02', 10003, 03, 100),
('2022-09-08 20:10:18', 10004, 01, 500);

测试数据查询结果如下：

t_user表

问题一

MySQL代码如下：

SELECT
	DATE_FORMAT(pay_date, '%Y%m%d') AS pay_date,
	user_type,
	sum(amount) all_amount
	FROM
		`t_user`
	GROUP BY
        DATE_FORMAT(pay_date, '%Y%m%d'),
	user_type;

根据 日期年月日(DATE_FORMAT(pay_date,'%Y%m%d')) 和 user_type进行分组查询聚合即可。

结果如下：

问题一得到的答案

问题二

WITH
tt
AS(
	WITH t AS (
		SELECT
			DATE_FORMAT(pay_date, '%Y%m%d') AS pay_date,
			user_type,
			sum(amount) all_amount
		FROM
			`t_user`
		GROUP BY
			DATE_FORMAT(pay_date, '%Y%m%d'),
			user_type
	) SELECT
			t1.pay_date curr_pay_date,
			t2.pay_date before_pay_date,
			t2.user_type,
			t1.all_amount curr_amt,
			t2.all_amount before_amt
	FROM t AS t1 
	INNER JOIN t AS t2
	ON DATE_FORMAT(DATE_ADD(t1.pay_date, INTERVAL - 3 DAY), '%Y%m%d') = t2.pay_date
)
SELECT 
			curr_pay_date,
			before_pay_date,
			user_type,
			curr_amt,
			before_amt,
			ROUND((curr_amt-before_amt)/before_amt, 2) gorw_rate 
FROM tt;

第二问需要使用到第一问的结果，第一问的结果如下：

问题二要求我们求解：每天每类用户的消费增长率（与3天前（pay_date - 3）的消费金额做对比）；

分析这个题目：

• 我们根据第一问已经求出每天每类用户的信息，我们把它建立为临时表；
• 要求解3天前的 pay_date-3 （可以使用把问题一的表格数据和本表进行连接，连接条件是pay_date-3 和 pay_date）；

自身进行连接

需要掌握两个函数：

• DATE_ADD(t1.pay_date, INTERVAL - 3 DAY) ：得到的答案形如：20220901
• DATE_FORMAT(pay_date, '%Y%m%d') ： 得到的答案形如：2022-09-01

以上完成之后该问题就迎刃而解了。

如何使用Python语言解决上述题目？

• 问题一：每天每类用户的总消费金额；
• 问题二：每天每类用户的消费增长率（与3天前（pay_date - 3）的消费金额做对比）；问题二需要返回的列：pay_date，user_type，amount，amount_new(3天前金额)，amount_rate（增长率）。

对于这道题目，我们使用Python语言该怎么处理哪？

问题一比较好写，使用pandas直接操作即可：

import numpy as np
import pandas as pd
 
data = pd.DataFrame([['20220101', '10001', '01', 3000.],
                     ['20220101', '10002', '02', 500.],
                     ['20220102', '10002', '02', 200.],
                     ['20220102', '10002', '02', 100.]], columns=['pay_date', 'user_id', 'user_type', 'amount'])
 
print(data)
res = data.groupby(by=['pay_date', 'user_type']).sum()
print(res)

对于问题二：

遇到的问题就是日期移位使用那个方法？经过查找资料我们使用如下方法：

print(pd.to_datetime('20220103', format='%Y%m%d') + pd.Timedelta(days=-3))   # 移位日期

执行结果

SQL里的分组和连接的功能，分别对应于pandas中的groupby()和merge()函数，具体如下：

import numpy as np
import pandas as pd
 
data = pd.DataFrame([[pd.to_datetime('20220101'), '10001', '01', 3000.],
                     [pd.to_datetime('20220101'), '10002', '02', 500.],
                     [pd.to_datetime('20220102'), '10002', '02', 200.],
                     [pd.to_datetime('20220102'), '10002', '02', 100.],
                     [pd.to_datetime('20220103'), '10002', '02', 400.],
                     [pd.to_datetime('20220104'), '10002', '02', 200.]], columns=['pay_date', 'user_id', 'user_type', 'amount'])
 
print(data)
res = data.groupby(by=['pay_date', 'user_type']).sum()   # 使用groupby进行分组
print(res)
 
# 使用merge进行2个表的连接
print(data.merge(data, left_on=data['pay_date']+pd.Timedelta(days=-3), right_on='pay_date'))

表成功进行了连接

数据分析有哪些方法

1、对比分析法 ：常用于对纵向的、横向的、最为突出的、计划与实际的等各种相关数据的。例如：今年与去年同期工资收入的增长情况、3月CPI环比增长情况等。

2、 趋势分析法：常用于在一段时间周期内，通过分析数据运行的变化趋势(上升或下降)，为未来的发展方向提供帮助。例如：用电量的季节性波动、股市的涨跌趋势等。

3、 相关分析法：常用于分析两个或多个变量之间的性质以及相关程度。例如：气温与用电量的相关性、运动量大小与体重的相关性等。

4、 回归分析法：常用于分析一个或多个自变量的变化对一个特定因变量的影响程度，从而确定其关系。例如：气温、用电设备、用电时长等因素对用电量数值大小的影响程度、工资收入的高低对生活消费支出大小的影响程度等。

5、 描述性分析法：常用于对一组数据样本的各种特征进行分析，以便于描述样本的各种及其所代表的总体的特征。例如：本月日平均用电量、上海市工资收入中位数等。

6、结构分析法 ：常用于分析数据总体的内部特征、性质和变化规律等。例如：各部分用电量占总用电的比重、生活消费支出构成情况等。

总结

整体来说，笔试题第一问相对简单，第二问也不是太难，中间使用到的部分MySQL的函数要进行单独记忆，毕竟考试是不让百度的，不忘初心，砥砺前行。

【每日一记，防止忘记】