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

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

 

PART01

  基础数据准备

 

假设我们有一张会员订单表，表里包含了如下字段：订单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  2A006  0.15  11  2018-01-01 17:23:12  2A007  1215.1  31  2018-01-01 10:23:12  2A008  100.75  15  2018-01-01 14:23:12  2A009  100.15  8  2018-01-01 15:23:12  2A010  20.15  9  2018-01-01 16:23:12  2A011  110.15  21  2018-01-01 14:23:12  2

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

 

PART02

  分析函数实践

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

（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_2018window 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的订单：

多种排序方式：

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

查看数据的分位数：

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

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

-- 查询上一个订单距离当前订单的时间间隔。select id, order_money, member_id, create_time, last_time, datediff(create_time, last_time) as diffFROM ( 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;

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

4、头尾函数：first_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;

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

 

今天分享下MySQL中的窗口函数的实际操作，这对分析查询很有帮助，希望对大家有帮助。谢谢支持

欢迎关注微信公众号，访问更多精彩：数据之魅。

如需转载，请联系授权，谢谢合作。