在工作中，我们经常会遇到循环计算的需求。例如，计算一日留存率、二日留存率、……、N日留存率。如果N值不大，我们虽然可以通过合并多次查询结果来解决，但代码可读性会差很多，而且运行速度也不快。

在《数据分析手册：SQL必修课内容，会计算用户留存就算合格了》一文中我们就已经抛出来这个问题了，今天我们就利用存储过程来解决这一类问题。

什么是存储过程

存储过程是实现经过编译并存储在数据库中的一段SQL语句集合。它的思想很简单，从名字中就可以看出来，它的主要功能就是进行数据的存储。与普通增删改查语句不同的是，它通过增加变量、控制等方法来丰富了进行存储的条件。

简单来说，当我们要插入10条数据时，要复制insert语句十次，但如果加入存储过程的循环语句，一次就够了。具有Java、Python编程基础的人会很容易理解这一点。作为一种方法，就必要具有优点和缺点的。

(1)优点

  • 存储过程在创造时就进行编译，以后每次执行都不需再重新编译，从而提高执行速度；
  • 可重复使用，简化数据库开发人员工作；
  • 安全性高，可设定用户的使用权。

(2)缺点

  • 往往定制化于特定的数据库上，当切换到其它数据库系统时，需重写原有的存储过程；
  • 性能调校与撰写受限于数据库系统。

案例分析

本文仍是选用阿里天池中的淘宝用户行为数据集进行介绍。

(1)数据集说明

该数据集记录了2019-11-28至2019-12-03期间的行为数据，数据集中具有如下10个字段，每个字段均有注释。




sql server 从存过过程获取结果集_plsql如何执行存储过程


其中，behavior为用户的线上行为，有四个选项：浏览、收藏、加购物车、购买；time_stamp为时间戳格式，表明每种行为的记录时间。我们的任务仍然是得到如下的结果表。


sql server 从存过过程获取结果集_oracle存储过程如何查询多个sql_02


(2)解决思路

  • 创建如下的中间表；


sql server 从存过过程获取结果集_oracle存储过程如何查询多个sql_03


  • 写出计算间隔天数为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)

执行得到中间表。


sql server 从存过过程获取结果集_oracle存储过程如何查询多个sql_04


(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

执行得到结果表。


sql server 从存过过程获取结果集_oracle存储过程如何查询多个sql_05


结语

虽然实际中数据分析师往往会借助Python或R语言来解决类似的问题，但掌握存储过程原理、知道存储过程用途，对于数据分析师在和数据库开发工程师打交道也是非常有帮助的！