工作中一些统计数据的SQL

1，

SELECT  
	date_sub(curdate(),interval 1 day) 日期,
	(select count(1) from pintuan.group_order  where has_pay='yes' and date(gmt_create)=date_sub(curdate(),interval 1 day)) as 订单数,
	count(distinct(cookie)) AS 总UV,
    count(distinct(IF(t.page_name='group',cookie,'')))-1 AS 首页UV,
	count(distinct(IF(t.page_name='groupDetail',cookie,'')))-1 AS 详情页UV  ,
	count(distinct(IF(t.page_name='groupOrderStatus',cookie,'')))-1 AS 拼团结果页UV  ,
	count(distinct(IF(t.page_name='real-groupbalance',cookie,'')))-1 AS 结算页UV
from oversea.log_page t where date(gmt_create)=date_sub(curdate(),interval 1 day)

2，

SELECT  
	date(gmt_create) date,
	count(distinct(cookie)) AS 总UV,
    count(distinct(IF(t.page_name='group',cookie,'')))-1 AS 首页UV,
	count(distinct(IF(t.page_name='groupDetail',cookie,'')))-1 AS 详情页UV  ,
	count(distinct(IF(t.page_name='groupOrderStatus',cookie,'')))-1 AS 拼团结果页UV  ,
	count(distinct(IF(t.page_name='real-groupbalance',cookie,'')))-1 AS 结算页UV
from oversea.log_page t where id>13000000
group by date order by date desc

3，

SELECT date(gmt_create)d, count(1), sum(cast(real_price as DECIMAL(9,2))) as price FROM group_order a
where gmt_create>'2016-03-07' and gmt_create<'2016-03-11' and has_pay='yes' and real_price>0.01
and exists (select 1 from pintuan.group_instance b where b.status=1 and b.id=a.group_instance_id)
group by d

4，

select 	left(t.introduction,16) 标题,t.package_id,t.amount 拼团价,
(SELECT require_count FROM pintuan.group_product_package where  id=t.package_id) 每个团最低人数,
(SELECT count(1) FROM pintuan.group_instance where   date(gmt_modified)='2016-03-03' and package_id=t.package_id) 拼团数量,
(SELECT count(1) FROM pintuan.group_instance where  status=1 and date(gmt_modified)='2016-03-03' and package_id=t.package_id) 拼团成功数量,
t.d 付款订单数量,
(SELECT sum(real_count) FROM pintuan.group_instance where  status=1 and date(gmt_modified)='2016-03-03' and package_id=t.package_id) 拼团成功订单总数
from
(	 
	 select introduction,amount,count(1) d,package_id,status
	 from pintuan.group_order 
	 where has_pay='yes' and date(gmt_create)='2016-03-03' group by package_id
) as t		 
order by 付款订单数量 desc	

5，

SELECT  
   SUM(CASE t.num WHEN 1  THEN 1 ELSE 0 END) AS 购买1单数量num1,
   SUM(CASE t.num WHEN 2  THEN 1 ELSE 0 END) AS 购买2单_num2,
   SUM(CASE t.num WHEN 3  THEN 1 ELSE 0 END) AS num3,
   SUM(CASE t.num WHEN 4  THEN 1 ELSE 0 END) AS num4,  
   SUM(CASE t.num WHEN 5  THEN 1 ELSE 0 END) AS num5,
   SUM(CASE t.num WHEN 6  THEN 1 ELSE 0 END) AS num6,
   SUM(CASE t.num WHEN 7  THEN 1 ELSE 0 END) AS num7,
   SUM(CASE t.num WHEN 8 THEN 1 ELSE 0 END) AS num8,
   SUM(CASE t.num WHEN 9  THEN 1 ELSE 0 END) AS num9,
   SUM(CASE t.num WHEN 10  THEN 1 ELSE 0 END) AS num10
   from
(SELECT user_id,count(1)num FROM group_order a where   has_pay='yes' and exists (SELECT 1 FROM pintuan.group_instance  b where  status=1 and b.id=a.group_instance_id ) group by user_id)as t

6，

SELECT  
   SUM(CASE t.acq_channel WHEN 1  THEN 1 ELSE 0 END) AS PC,
   SUM(CASE t.acq_channel WHEN 2  THEN 1 ELSE 0 END) AS APP,
   SUM(CASE t.acq_channel WHEN 3  THEN 1 ELSE 0 END) AS  WECHAT
FROM mobile.mobile_send_point_record t