任职工龄筛选
有一张员工表,记录了员工在各个职位的任职时间,求出在各个岗位任职时间相同的职工staff 表结构 staff_id,job_id,start_dt,end_dt(员工id,职位id,开始时间,结束时间)
面向过程
既然员工各个岗位时间都一样,那么员工找不到第其它岗位和该岗位不时长一样,这个简单,用个not exists循环就可以
select distinct staff_id
from staff t
where not exists(select 1
from staff t1
where (t1.end_dt - t1.start_dt) <> (t.end_dt - t.start_dt)
and t.job_id <> t1.job_id
)
这样写用到了not exists,看起来好像还不错...
面向集合员工每个岗位时间都一样,那么最大时间和最小时间都是一样
select staff_id
from staff
group by staff_id
having max(end_dt - start_dt) = min(end_dt - start_dt)
这样是不是简单很多,执行计划比面向过程简单很多
信用卡消费平均时长
现有一张用户消费信用卡记录表,求用户平均消费间隔consumer_log表结构user_id,consume_time(用户id,消费时间)
面向过程当前消费时间减去上次消费时间,即为此次消费的间隔,先求出每次的间隔,然后取平均值,此处可用分析函数求上次消费时间
select user_id
, avg(consume_time - last_time) avg_time
from (select user_id
, consume_time
, lag(consume_time,1,consume_time) over(partition by user_id order by consume_time) last_time --上次时间
from consumer_log)t
此处还用了分析函数求上次的日期,按照处理过程来思路清晰,逻辑合理
面向集合
实际上只需要求 最后一次消费时间 - 最先一次消费时间,然后除以消费记录数即可
select user_id,
(max(consume_time) - min(consume_time))/count(1) avg_time
from consumer_log
group by user_id
这样简单很多
看完上面两个例子,不禁感慨: 一顿操作猛如虎,一看结果二百五,换一个角度,过程就不一样。接下来,分享下我遇到的一个例子
新增用户
简化一下:如果昨天用户没有登录,今天有登录,那么认为改用户为新增用户用户登录记录user_log,表结构 user_id,time(用户id,登录时间),求新增用户
面向过程
笔者第一反应,用今天登录用户和昨天登录用户进行进行比较,今天有而昨天没有的用户即是新增用户。记录表有重复数据,所以先要进行去重
with info as
(
select user_id
, to_char(time,'yyyymmdd') dt
from user_log
where to_char(time,'yyyymmdd') in ('20180314','20180313')
group by to_char(time,'yyyymmdd')
, user_log
)
select t.user_id
from info t
left join info t1
on t.user_id = t1.user_id
and t1.dt = '20180313'
where t.dt = '20180314'
and t1.user_id is null
面向集合
后经过讨论,发现集合数据一个特点,新增用户满足最小值为今天日期,优化代码如下
select user_id
from user_log
where to_char(time,'yyyymmdd') in ('20180314','20180313')
group by user_id
having min(to_char(time,'yyyymmdd')) = '20180313'
优化后简单明了,执行效率更高。实际工作中在hive中进行计算,数据量几十亿条,优化后较优化前缩短时间40%左右
当然,不是所有的sql都可以通过面向集合思维找出特点进行优化,但是我们应该有一颗追求写出高质量代码的心
参考书籍 <oracle> SQL高级编程