Mysql查询语句技巧之一

1.    计算一个表中某个字段值在表中的占比

场景：

table: frequency

field:adv_id, user_id，dis_imp_pv, imp_pv,freq, uv

计算客户id为25的访客中竞价id去重浏览次数在10次以上的用户数占比

select a.adv_id,sum(a.pv) asTOTAL,avg(b.total_pv),

sum(a.pv)/avg(b.total_pv) as freq

from frequency a,

(select adv_id,sum(pv) as total_pv

from frequency b where adv_id=25

group by adv_id) b

where a.adv_id=b.adv_id

and a.advr_id=25 and cast(a.imp_pv as int)>= 11

group by a.adv__id;

 

相当于在原表中增加了一列新的总数列

 

可以优化，使用case when 语句

SELECT

sum(case when imp_pv > 3 then pv else 0end)/sum(pv)

from frequency WHERE advr_id=25;

简单整洁并且高效