计算一列中某个值的个数

忆之独秀 https://blog.csdn.net/lavorange/article/details/25004181这两天在参加阿里大数据竞赛进入第二赛季要用到不少的SQL语句现在才发现当时的数据库是白学了今天就根据具体的需求来整理一下。可以把需求整理成为一道简单的题目题目如下给定一个表t_alibaba_datauser_id brand_id type visit_datetime10944750 13451 0 060410944750 13451 2 060410944750 13451 2 072310944750 13451 0 080110944750 13451 0 051510944750 13451 0 070310944750 13451 0 062910944750 13451 0 081410944750 21110 0 042210944750 1131 0 0713… … … …其中user_id代表用户brand_id代表品牌type代表操作的类型0-点击1-购买2-收藏3-购物车visit_datetime即用户对品牌行为的时间(0415-0515,0516-0615,0616-0715,0716-0815四个月)。假设一共有10000条交易记录。现在有这样的需求即我现在需要对用户user_id对某一个品牌brand_id进行评分grade可以理解为用户对品牌的喜爱程度评分grade规则为用户对品牌不同类型type0,1,2,3的操作总数然后给这些总数乘以相应的权值grade w0*count(type0) w1*count(type1) w2*count(type2)w3*count(type3)。当然考虑到时间的因素可以对靠后时间的点击操作提高权重相对距离较远的点击操作降低权重那么w0*count(type0)可以改写成w0*( 1*count(type0 and 0716visit_datatime0815) 0.8*count(type0 and 0616visit_datatime0715) 0.6*count(type0 and 0516visit_datetime0615) 0.4*count(type0 and 0415visit_datetime0515)).现在就是要创建一个表user_brand_gradeuser_id,brand_id,grade按照grade降序排列以便找到前1000个user_id,brand_id对。Step1首先不考虑时间的因素首先要找到用户user_id对于某个品牌brand_id某种操作type0/1/2/3的总次数。SELECT user_id , brand_id , count(type0) as type0 ,count(type1) as type1 , count(type2) as type2 ,count(type3) as type3 FROM t_alibaba_data t where visit_datetime 415 visit_datetime 715 group by user_id,brand_id;结果当我这么写完之后我发现type0type1type2type3的值都是一样的。这非常不合理才知道这么写是有问题的。SQL语句count()的用法http://www.w3school.com.cn/sql/sql_func_count.asp可见count只能计算某一列的总数目或者某一列符合一个条件的总数目但不能计算某列符合多个条件的相应数目。Step2那么修改count成为sum加上type的条件可以做出相应的选择。SELECT user_id , brand_id , sum(type0) as type0, sum(type1) as type1, sum(type2) as type2, sum(type3) as type3 FROM t_alibaba_data t where visit_datetime 515 visit_datetime 815 group by user_id ,brand_id;结果SQL语句sum()的用法http://www.w3school.com.cn/sql/sql_func_sum.aspsum中是可以加表达式的因此可以加上type的相应值。Step3此时不禁要想如果当type0的时候要根据visit_datetime选择不同的值怎么办在此要用到case进行条件的选择。那么就可以重写Step2的SQL语句。SELECT user_id , brand_id , sum(case when type0 then 1 else 0 end) as type0, sum(case when type1 then 1 else 0 end) as type1, sum(case when type2 then 1 else 0 end) as type2, sum(case when type3 then 1 else 0 end) as type3 FROM t_alibaba_data t group by user_id , brand_id ;结果应该和上图相同Step4用case增加时间上的判断select * ,(type0type1type2type3) as grade from ( SELECT user_id , brand_id , sum(case when type0 visit_datetime 801 visit_datetime815 then 10 when type0 visit_datetime 716 visit_datetime731 then 8 when type0 visit_datetime 701 visit_datetime715 then 6 when type0 visit_datetime 616 visit_datetime630 then 4 when type0 visit_datetime 601 visit_datetime615 then 2 when type0 visit_datetime 516 visit_datetime531 then 1 else 0 end) as type0, sum(case when type1 then 1 else 0 end) as type1, sum(case when type2 then 2 else 0 end) as type2, sum(case when type3 then 3 else 0 end) as type3 FROM t_alibaba_data t group by user_id , brand_id order by type0 desc )a order by grade desc limit 1000;然后得到结果那么就可以找到符合要求的user_id,brand_id对了即最活跃的用户品牌对。