在工作中,我们经常会遇到循环计算的需求。例如,计算一日留存率、二日留存率、……、N日留存率。如果N值不大,我们虽然可以通过合并多次查询结果来解决,但代码可读性会差很多,而且运行速度也不快。
在《数据分析手册:SQL必修课内容,会计算用户留存就算合格了》一文中我们就已经抛出来这个问题了,今天我们就利用存储过程来解决这一类问题。
什么是存储过程
存储过程是实现经过编译并存储在数据库中的一段SQL语句集合。它的思想很简单,从名字中就可以看出来,它的主要功能就是进行数据的存储。与普通增删改查语句不同的是,它通过增加变量、控制等方法来丰富了进行存储的条件。
简单来说,当我们要插入10条数据时,要复制insert语句十次,但如果加入存储过程的循环语句,一次就够了。具有Java、Python编程基础的人会很容易理解这一点。作为一种方法,就必要具有优点和缺点的。
(1)优点
- 存储过程在创造时就进行编译,以后每次执行都不需再重新编译,从而提高执行速度;
- 可重复使用,简化数据库开发人员工作;
- 安全性高,可设定用户的使用权。
(2)缺点
- 往往定制化于特定的数据库上,当切换到其它数据库系统时,需重写原有的存储过程;
- 性能调校与撰写受限于数据库系统。
案例分析
本文仍是选用阿里天池中的淘宝用户行为数据集进行介绍。
(1)数据集说明
该数据集记录了2019-11-28至2019-12-03期间的行为数据,数据集中具有如下10个字段,每个字段均有注释。
![fa95e63a963cee6ee7924faddc81e01f.png](https://i-blog.csdnimg.cn/blog_migrate/665a4916680c851f41304596fabf833d.jpeg)
其中,behavior为用户的线上行为,有四个选项:浏览、收藏、加购物车、购买;time_stamp为时间戳格式,表明每种行为的记录时间。我们的任务仍然是得到如下的结果表。
![8eec5a6d1d5247c7267c77ba49dbe8ef.png](https://i-blog.csdnimg.cn/blog_migrate/2a25c3d04c4d4541001cb785f4e8252a.jpeg)
(2)解决思路
- 创建如下的中间表;
![d17fe9908d1776443ca2ec7690c23996.png](https://i-blog.csdnimg.cn/blog_migrate/685b10284392843c3e54fa50d20be5f0.jpeg)
- 写出计算间隔天数为N的留存查询语句,并利用存储过程对间隔天数进行循环,每次循环结果写入中间表中;
- 对中间表进行行转列,得到结果表。
代码呈现
接下来看代码
(1)创建中间表
create table retention(id int auto_increment,date varchar(20) default null,new_users varchar(20)default null,nday_after int default null,retention_number int default null,primary key(id)); --建立中间表
(2)编写存储过程
truncate retention;drop procedure if exists cal_retention;delimiter $$create procedure cal_retention ( in nday int) beginset @i=1;while @i<=nday doinsert into retention(date,new_users,nday_after,retention_number)select bh1.date ,count(distinct bh1.user_id) as new_users,@i as nday_after,count(distinct bh2.user_id) as retention_numberfrom (select *from(select *,from_unixtime(time_stamp) as datetime,from_unixtime(time_stamp,'%y-%m-%d') as date,from_unixtime(time_stamp,'%h:%i:%s') as time,row_number() over(partition by user_id order by time_stamp asc) as rnfrom behaviorwhere 1=1)t1where rn=1) bh1 left join behavior bh2 on bh1.user_id=bh2.user_id and datediff(from_unixtime(bh2.time_stamp,'%y-%m-%d'),bh1.date)=@iwhere 1=1group by bh1.date;set @i=@i+1;end while;end $$delimiter;call cal_retention(2)
执行得到中间表。
![71d8ddb566b9c6938aea9149821ec923.png](https://i-blog.csdnimg.cn/blog_migrate/4badeff3ec8601714f2410c966c09bc7.jpeg)
(3)行转列得到结果表
select date as 日期,min(new_users) as 新增用户数,sum(case when nday_after=1 then retention_number else 0 end) as 一日留存数,sum(case when nday_after=2 then retention_number else 0 end) as 二日留存数from retentionwhere 1=1group by date
执行得到结果表。
![3a92592ce1e8f534bb245fd521051e02.png](https://i-blog.csdnimg.cn/blog_migrate/5828d89e06d1f6bd971717f7e1227275.jpeg)
结语
虽然实际中数据分析师往往会借助Python或R语言来解决类似的问题,但掌握存储过程原理、知道存储过程用途,对于数据分析师在和数据库开发工程师打交道也是非常有帮助的!