数据分析系列 17/32 | MySQL中的数据分析函数,在前面我们对MySQL中的数据分析函数有了了解,今天我们就来看一下数据分析函数的实际应用。
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的订单:
多种排序方式:
即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 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;
可以看到,没有匹配到的数值默认以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中的窗口函数的实际操作,这对分析查询很有帮助,希望对大家有帮助。谢谢支持
欢迎关注微信公众号,访问更多精彩:数据之魅。
如需转载,请联系授权,谢谢合作。