注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

小木舟的博客

笔墨生活随想,记录似水年华。

 
 
 

日志

 
 

case when使用案例  

2014-06-29 20:35:44|  分类: 数据分析 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
有这样一张数据库表t_paylist,存储用户的消费记录,如下图所示:

case when语句的使用案例 - 小木舟 - 小木舟的博客

 每行记录表示用户的一次购物记录,shop表示用户发生购物行为的商店,paynum表示消费的金额,单位为元,paytime表示购物的时间,以值为20140406212233的paytime为例,其表示2014年4月6日21时22分33秒。
现有一个需要使用SQL来完成的数据统计需求:需要统计每个用户每个月在以下消费区间[0, 20]、(20, 40] 、(40, +∞)的总消费金额和总消费笔数,结果类似下表所示:

case when语句的使用案例 - 小木舟 - 小木舟的博客
 
这个统计需求看起来挺复杂,不生成几个中间表折腾下貌似不能解决,但其实使用case when语句可以简单快速地解决,实现的SQL语句如下所示:

select username, fmonth, 
  sum(a1) as a1, sum(case when a1 > 0 then 1 else 0 end) as c1,
  sum(a2) as a2, sum(case when a2 > 0 then 1 else 0 end) as c2,
  sum(a3) as a3, sum(case when a3 > 0 then 1 else 0 end) as c3
from
(
select username, fmonth, 
  case when paynum <= 20 then paynum else 0 end as a1,
  case when paynum > 20  and paynum <= 40 then paynum else 0 end as a2,
  case when paynum > 40 then paynum else 0 end as a3
from
(
select username, paynum, substr(paytime,1,6) fmonth
from t_paylist
)
)
group by username, fmonth
逐步讲解上述的SQL语句。首先,由于统计的时间单位为月份,因此将每行记录的时间截取到月份,下面的SQL语句实现这个功能:

select username, paynum, substr(paytime,1,6) fmonth
from t_paylist

substr函数的下标是从1算起的,substr(paytime,1,6)表示从第一位开始截取,长度为6。
接着,使用case when语句来对用户的消费金额进行分区。使用case when进行判断,如case when paynum <= 20 then paynum else 0 end as a1,当消费金额paynum小于20时,返回paynum的实际值,否则返回0,并将其作为新的字段a1,a1就是表示每个用户在消费区间[0, 20]的消费金额。具体的实现如下面的SQL语句所示:

select username, fmonth, 
case when paynum <= 20 then paynum else 0 end as a1,
case when paynum > 20  and paynum <= 40 then paynum else 0 end as a2,
case when paynum > 40 then paynum else 0 end as a3
from
(
select username, paynum, substr(paytime,1,6) fmonth
from t_paylist
)

最后,因为是按每个用户每个月进行统计的,所以需要按照用户和月份做分组的聚合计算。在做聚合运算的时候,还需要对各个消费区间的总消费笔数进行计算,此时需要再次用到case when语句。判断用户的消费金额是否在某个区间,如果是,则返回1,表示1次消费记录,如果否,则返回0,外层再嵌套一个sum聚合函数做聚合运算,并将结果作为一个新的字段表示用户在某个消费区间的总消费笔数,如sum(case when a1 > 0 then 1 else 0 end) as c1,该语句就是计算用户在区间[0, 20]的总消费笔数c1。而计算用户在每个消费区间的总消费金额则要简单得多,只需要使用sum聚合函数对a1、a2和a3进行聚合运算即可。最终的SQL查询语句如最开始给出的SQL语句所示。
此外,case when语句还可以同时判断多个字段,如

case when username = ‘kaka’ and shop = ‘amazon’ then ‘yes’

诸如此类。
  评论这张
 
阅读(342)| 评论(0)
推荐

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017