sql优化之面向集合编程

最近重新翻阅oracle资料，有提到在编写sql时需要面向集合思考，这是每一个SQL高手必备技巧。面向集合思考是相对面向过程而言，意在寻找整个结果集的特征，而不是只专注实现逻辑过程，从而写出高效、简洁的代码。先上两个课本上例子，供大家参考(伪代码)

任职工龄筛选

有一张员工表，记录了员工在各个职位的任职时间，求出在各个岗位任职时间相同的职工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高级编程