大数据量mysql

1.来自百度渠道的购买成功和失败率是多少?

思路：先找出百度渠道的总数（Channel_id=2000），成功的数，就可以分别算出成功率和失败率。

SELECT total,(count*100/total)||"%" ,(100-(count*100/total))||"%" FROM     
 (SELECT count(*) as count FROM 
  (SELECT DISTINCT User_id FROM UserActions WHERE Channel_id=2000 AND Action_id=4005)) t1
LEFT JOIN 
 (SELECT count(*) as total  FROM 
  (SELECT DISTINCT User_id FROM UserActions  WHERE Channel_id=2000)) t2

容易遗漏的是：DISTINCT User_id。目的是将顾客去重。

技巧：如果两个表t1，t2都只含有一条数据，那么将他们直接联表就能组装成一个新的表，无需ON t1.xx=t2.xx

计算成功率：

(count*100/total)||"%"  ---其实就是把百分比的数字和百分号连接

2.各个渠道占比（要%）

SELECT t1.Channel_id,((t1.count*100)/t2.total) || "%" FROM
 (SELECT Channel_id, count(*) as count 
  FROM UserActions 
  WHERE Action_id in (4001) 
  GROUP BY Channel_id) as t1 
LEFT JOIN
 (SELECT Channel_id, count(*) as total 
  FROM UserActions 
  WHERE Action_id in (4001)) as t2  

思路：把各个渠道计算出来，再把总数计算出来，用LEFT JOIN将数据连接起来。在连接后的新的表上计算占比。

注意：1）4001是查看，用它来表示引流的量

           2）直接得到渠道代号就可，不需要连接Channels表得到渠道名称

3.哪个品牌最火，哪个最差（要名字和数量）：

select Brand_name,max(count) as count from 
(select Brand_id,Brand_name,count(*) as count 
 from UserActions t1 left join   ----t1是UserActions的别名，可以不加as
 (select * from Product left join Brands 
  on Product.Brand_id = Brands.Brand_id) as t2 
 on t1.Product_id=t2.Product_id 
 group by Brand_id 
 having Brand_id is not null) as bc

4.用户行为漏斗数据 

SELECT t1.Action_id, Action_name,count FROM 
 (select count(*) as count, Action_id 
  FROM UserActions 
  WEHRE Action_id in (4001,4003,4004,4005) 
  GROUP BY Action_id) t1 
LEFT JOIN 
ActionTypes t2 on t1.Action_id = t2.Action_id 

所谓漏斗就是从点击到购买的漏斗，不包括4002收藏