数据分析系列 18/32 | MySQL中的数据分析函数实战

作者: 数据之魅 | 来源:发表于2020-05-01 22:56 被阅读0次

数据分析系列 18/32 | MySQL中的数据分析函数实战
python实战总结 - 草稿
python实战总结
数据分析课程笔记
【python】AQI处理分析
MySQL实战目录
项目实战-实战Docker到Kubernetes技术系列视频教程
SQL分析函数，看这一篇就够了
《数据分析与挖掘实战》笔记之-航空公司客户价值分析（1）
大数据分析，首席分析师，金融数据分析，项目实战视频网盘下载

image

数据分析系列 17/32 | MySQL中的数据分析函数，在前面我们对MySQL中的数据分析函数有了了解，今天我们就来看一下数据分析函数的实际应用。

image

PART

01

** 基础数据准备**

假设我们有一张会员订单表，表里包含了如下字段：订单id，订单金额，会员id，订单创建时间，订单状态等。

CREATE TABLE dm_order_2018(
  id varchar(100) DEFAULT NULL COMMENT '订单id',
  order_money float DEFAULT NULL COMMENT '订单金额',
  member_id int(11) DEFAULT NULL COMMENT '会员id',
  create_time timestamp DEFAULT NULL COMMENT '创建时间',
  status int(11) DEFAULT NULL COMMENT '订单状态|0：取消，1：待支付，2:付款成功'
);

我们往表里插入一些订单明细数据：

INSERT INTO dm_order_2018 VALUES ('A001', '100.15', '1', '2018-01-01 13:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A023', '100.15', '1', '2018-01-01 15:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A002', '100', '2', '2018-04-01 14:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A003', '12.1', '3', '2018-01-01 13:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A004', '200.15', '4', '2018-01-01 13:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A005', '1200.15', '5', '2018-01-01 13:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A006', '0.15', '11', '2018-01-01 17:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A007', '1215.1', '31', '2018-01-01 10:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A008', '100.75', '15', '2018-01-01 14:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A009', '100.15', '8', '2018-01-01 15:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A010', '20.15', '9', '2018-01-01 16:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A011', '110.15', '21', '2018-01-01 14:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A012', '1.15', '13', '2018-01-01 13:03:12', '2');
INSERT INTO dm_order_2018 VALUES ('A013', '20.15', '14', '2018-02-01 13:53:12', '2');
INSERT INTO dm_order_2018 VALUES ('A014', '30.15', '15', '2018-03-01 13:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A015', '40.5', '13', '2018-01-01 13:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A016', '65', '1', '2017-01-01 13:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A017', '78.15', '1', '2017-03-01 09:23:12', '1');
INSERT INTO dm_order_2018 VALUES ('A018', '88.15', '1', '2017-11-01 20:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A019', '99', '12', '2018-02-11 13:23:12', '1');
INSERT INTO dm_order_2018 VALUES ('A020', '10.3', '13', '2018-04-01 13:23:12', '2');
INSERT INTO dm_order_2018 VALUES ('A021', '600', '5', '2018-11-01 08:58:31', '2');
INSERT INTO dm_order_2018 VALUES ('A022', '500', '31', '2018-11-11 08:59:02', '2');

查看一下我们插入的数据：

select * FROM dm_order_2018;
-- 查询结果如下：
A005  1200.15  5  2018-01-01 13:23:12  2
A006  0.15  11  2018-01-01 17:23:12  2
A007  1215.1  31  2018-01-01 10:23:12  2
A008  100.75  15  2018-01-01 14:23:12  2
A009  100.15  8  2018-01-01 15:23:12  2
A010  20.15  9  2018-01-01 16:23:12  2
A011  110.15  21  2018-01-01 14:23:12  2

下面我们来分别看看各数据分析函数的用法。
PART

02
分析函数实践

首先我们举几个简单的例子：

（1）按用户id分组，消费金额进行降序排序

select * 
FROM 
( select id, order_money, member_id, create_time, 
      row_number() over(partition by member_id ORDER BY order_money desc) as row_num
  from dm_order_2018
) t

查询结果如下：

（2）滑动窗口示例

select * 
FROM 
( select id, order_money, member_id, create_time, 
      avg(order_money) over w as avg_num,
      sum(order_money) over w as sum_num
  from dm_order_2018
window w as (partition by member_id ORDER BY create_time DESC rows BETWEEN 1 preceding AND 1 following)
) t;

查询结果如下：

从结果可以看出，id为A001订单属于边界值，没有前一行，因此平均订单金额为(100.15+100.15)/2=100.15；
第二行id为A001的订单前后都有订单，所以平均订单金额为（100.15+100.15+88.15/3=96.15；
以此类推就可以得到一个基于滑动窗口的动态平均订单值。此例中，窗口函数用到了传统的聚合函数avg()，用来计算动态的平均值。

下面就按照功能来分别举例说明：
1、序号函数：row_number() / rank() / dense_rank()

-- 查询排序小于等于3的订单。
select * 
FROM 
( select id, order_money, member_id, create_time, 
      row_number() over(partition by member_id ORDER BY order_money desc) as row_num
  from dm_order_2018
) t where row_num <= 3;

-- 多种排序方式：
select * 
FROM 
( select id, order_money, member_id, create_time, 
      row_number() over(partition by member_id ORDER BY order_money desc) as row_num1,
      rank() over(partition by member_id ORDER BY order_money desc) as row_num2,
      dense_rank() over(partition by member_id ORDER BY order_money desc) as row_num3
  from dm_order_2018
) t;

查询排序小于等于3的订单：

image

多种排序方式：

image

即rank（）会产生序号相同的记录，同时可能产生序号间隙；而dense_rank（）也会产生序号相同的记录，但不会产生序号间隙。
2、分布函数：percent_rank() / cume_dist()

-- 查看数据的分位数
select * 
FROM 
( select id, order_money, member_id, create_time, 
      row_number() over w as row_num,
      percent_rank() over w as percent
  from dm_order_2018
  window w as (partition by member_id ORDER BY create_time DESC)
) t;
-- 大于等于当前订单金额的订单比例有多少
select * 
FROM 
( select id, order_money, member_id, create_time, 
      row_number() over w as row_num,
      cume_dist() over w as percent
  from dm_order_2018
  window w as (partition by member_id ORDER BY create_time DESC)
) t;

查看数据的分位数：

image

大于等于当前订单金额的订单比例有多少：

image

3、前后函数：****lag() / lead()

-- 查询上一个订单距离当前订单的时间间隔。
select id, order_money, member_id, create_time, last_time, datediff(create_time, last_time) as diff
FROM 
( select id, order_money, member_id, create_time, 
      row_number() over w as row_num,
      lag(create_time, 1) over w as last_time
  from dm_order_2018
  window w as (partition by member_id ORDER BY create_time ASC)
) t;

image

可以看到，没有匹配到的数值默认以null填充。

4、头尾函数：****ﬁrst_val() / last_val()

-- 查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。
select *
FROM 
( select id, order_money, member_id, create_time, 
      first_value(order_money) over w as first_money,
      last_value(order_money) over w as last_money
  from dm_order_2018
  window w as (partition by member_id ORDER BY create_time ASC)
) t;

查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。