统计记录

最新推荐文章于 2024-10-08 15:18:03 发布

揽峰moc

最新推荐文章于 2024-10-08 15:18:03 发布

阅读量90

点赞数

分类专栏： Mysql数据库文章标签：移动开发

本文链接：https://blog.csdn.net/big1989wmf/article/details/84808233

版权

Mysql数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏




--统计1：领用和使用消费券情况
select c.* from(
	select '已领取' as '消费券', count(a.id) as '数量'  from t1 a
	union all
	select '已领取并使用' as '消费券', count(b.id) as '数量'  from t1 b  where b.status=1
)c

--时段统计
select '2016-07-02 00:00:00' as '开始时间','2016-07-03 23:59:59' as '结束时间',t.* from(
select  '甲' as '绑定对象',  count(a.id) as '数量'  from  t1 a where LOWER(a.account_type)='jd' and a.enable_flag=1 and a.created_date between '2016-07-02 00:00:00' and '2016-07-03 23:59:59' 
union
select '乙' as '绑定对象',  count(b.id) as '数量'  from  t1 b where LOWER(b.account_type)='alipay' and b.enable_flag=1 and b.created_date between '2016-07-02 00:00:00' and '2016-07-03 23:59:59' 
union
select '丙' as '绑定对象',  count(c.id) as '数量'  from  t1 c where LOWER(c.account_type)='taobao' and c.enable_flag=1 and c.created_date between '2016-07-02 00:00:00' and '2016-07-03 23:59:59' 
) t

--统计2：绑定电商情况
select t.* from(
select '甲' as '绑定对象',  count(a.id) as '数量'  from  t1 a where LOWER(a.account_type)='jd' and a.enable_flag=1
union
select '乙' as '绑定对象',  count(b.id) as '数量'  from  t1 b where LOWER(b.account_type)='alipay' and b.enable_flag=1
union
select '丙' as '绑定对象',  count(c.id) as '数量'  from  t1 c where LOWER(c.account_type)='taobao' and c.enable_flag=1
) t
--统计2：根据用户类型统计电商绑定情况

select t.* from(
	select 'Android用户' as '用户类型','甲' as '绑定对象',  count(a.id) as '数量' from t1 a
	inner join t2 b
	on a.customer_id=b.id
	inner join t3 c
	on b.app_id=c.id
	where LOWER(a.account_type)='jd' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=0 and length(IFNULL(c.device_id,0))<20 
union all
	select 'Android用户' as '用户类型','乙' as '绑定对象',  count(a.id) as '数量' from t1 a
	inner join t2 b
	on a.customer_id=b.id
	inner join t3 c
	on b.app_id=c.id
	where LOWER(a.account_type)='alipay' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=0 and length(IFNULL(c.device_id,0))<20 

union all
	select 'Android用户' as '用户类型','丙' as '绑定对象',  count(a.id) as '数量' from t1 a
	inner join t2 b
	on a.customer_id=b.id
	inner join t3 c
	on b.app_id=c.id
	where LOWER(a.account_type)='taobao' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=0 and length(IFNULL(c.device_id,0))<20 

union all
  select 'IOS用户' as '用户类型','甲' as '绑定对象',  count(a.id) as '数量' from t1 a
	inner join t2 b
	on a.customer_id=b.id
	inner join t3 c
	on b.app_id=c.id
	where LOWER(a.account_type)='jd' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=20 

union all
	select 'IOS用户' as '用户类型','乙' as '绑定对象',  count(a.id) as '数量' from t1 a
	inner join t2 b
	on a.customer_id=b.id
	inner join t3 c
	on b.app_id=c.id
	where LOWER(a.account_type)='alipay' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=20 

union all
	select 'IOS用户' as '用户类型','丙' as '绑定对象',  count(a.id) as '数量' from t1 a
	inner join t2 b
	on a.customer_id=b.id
	inner join t3 c
	on b.app_id=c.id
	where LOWER(a.account_type)='taobao' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=20 

) t


--区分android和ios用户
//ios
select  count(id) from t3 
where length(IFNULL(device_id,0))>=20                                        --132367
//android
select  count(id) from t3 
where length(IFNULL(device_id,0))>=1 and length(IFNULL(device_id,0))<20      --167454