活动类数据查询模板

活跃用户名单

select o.b_user_id,b.address,b.display_name,b.phone,count(o.b_user_id) as c from TP_ORDER o INNER JOIN TP_BUYER b on 

o.b_user_id = b.user_id where o.order_dt between str_to_date(' 2015-07-15','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-07','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) and GROUP BY o.b_user_id ORDER BY c desc limit 50

新用户名单

select o.b_user_id,b.address,b.display_name,b.phone,count(o.b_user_id) as c from TP_ORDER o INNER JOIN TP_BUYER b on 

o.b_user_id = b.user_id where b.createTime between str_to_date(' 2015-07-15','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-07','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) and GROUP BY o.b_user_id ORDER BY c desc limit 50

新注册用户

select count(*) from TP_BUYER b

where b.createdTime between str_to_date(' 2015-08-05','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-07','%Y-%m-%d %H:%i:%s') 

新增下单用户数

select count(DISTINCT o.b_user_id) from TP_ORDER o INNER JOIN TP_BUYER b on o.b_user_id = b.user_id

where o.order_dt between

str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-11','%Y-%m-%d %H:%i:%s')  and

b.createdTime between str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-11','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8)

 下单数

select count(*) from TP_ORDER o

where o.order_dt between

str_to_date(' 2015-08-05','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-07','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8)

品类数据

select COUNT(DISTINCT o.order_id) as sum  from TP_ORDER o 

where

o.order_id in (select d.order_id as order_id from TP_ORDER_PROD d INNER JOIN TP_PROD pd on d.prod_id = pd.id

where pd.fcategory_id in (1) ) and 

 o.order_dt between str_to_date(' 2015-08-05','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-07','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8)

品类区分新注册用户

select COUNT(DISTINCT o.order_id) as sum  from TP_ORDER o  INNER JOIN TP_BUYER b on o.b_user_id = b.user_id

where

o.order_id in (select d.order_id as order_id from TP_ORDER_PROD d INNER JOIN TP_PROD pd on d.prod_id = pd.id

where pd.fcategory_id in (1989,5,6) ) and

 o.order_dt between str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8)  and

b.createdTime between str_to_date(' 2015-07-26','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') 

品类订单金额数据

select SUM(d.prod_num*d.prod_price)  from TP_ORDER_PROD d INNER JOIN TP_ORDER o on d.order_id = o.order_id 

INNER JOIN TP_PROD pd on d.prod_id = pd.id 

 where pd.fcategory_id in (1) and

 o.order_dt between str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8)

当周活跃用户数

select count(*) from (select DISTINCT o.b_user_id,count(o.order_id) as sum from TP_ORDER o 

where o.order_dt between

str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s')  and `status` not in (0,6,7,8) GROUP BY o.b_user_id ) ss where ss.sum>1

上周活跃在本周继续活跃用户数量

select count(DISTINCT oo.b_user_id) from TP_ORDER oo  

where oo.b_user_id in (

select ss.b_user_id from (select DISTINCT o.b_user_id,count(o.order_id) as sum from TP_ORDER o 

where o.order_dt between

str_to_date(' 2015-07-26','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) GROUP BY o.b_user_id ) ss where ss.sum>1 )

and oo.order_dt between

str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s')  and oo.`status` not in (0,6,7,8)

上周新用户在上周未下单 但是本周下单数量

select count(DISTINCT oo.b_user_id) from TP_ORDER oo INNER JOIN TP_BUYER b on oo.b_user_id = b.user_id 

where oo.b_user_id not in (

select o.b_user_id from TP_ORDER o where o.order_dt between

str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8)

) 

and oo.order_dt between

str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-16','%Y-%m-%d %H:%i:%s')  and oo.`status` not in (0,6,7,8) and 

b.createdTime between str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s')

上周活跃本周无下单的用户数

select count(DISTINCT oo.user_id) from TP_BUYER oo  

where oo.user_id in (

select ss.b_user_id from (select DISTINCT o.b_user_id,count(o.order_id) as sum from TP_ORDER o

where o.order_dt between

str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) GROUP BY o.b_user_id ) ss where ss.sum>1 )

and oo.user_id not in (

select o.b_user_id from TP_ORDER o where o.order_dt between

str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-16','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8)

)

肉类下单用户名单

select o.b_user_id,COUNT(DISTINCT o.order_id) as sum,b.display_name,b.phone,o.recevier_name,o.address 

from TP_ORDER o INNER JOIN TP_BUYER b on o.b_user_id = b.user_id  

where

o.order_id in (select d.order_id as order_id from TP_ORDER_PROD d INNER JOIN TP_PROD pd on d.prod_id = pd.id

where pd.fcategory_id in (2) ) and

 o.order_dt between str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) group by o.b_user_id ORDER BY sum desc

在线支付用户名单

select o.b_user_id,COUNT(DISTINCT o.order_id) as sum,b.display_name,b.phone,o.recevier_name,o.address 

from TP_ORDER o INNER JOIN TP_BUYER b on o.b_user_id = b.user_id  

where

 o.order_dt between str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) 

and o.pay_type = 0

group by o.b_user_id ORDER BY sum desc

新注册用户名单

select b.user_id,b.display_name,b.phone,b.shop_name,b.createdTime,cc.display_name  

from TP_BUYER b 

LEFT JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 ) cc on b.user_id = cc.user_id

where 

 b.createdTime between str_to_date(' 2015-08-02','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-09','%Y-%m-%d %H:%i:%s')

销售补贴金额

select cc.marketer_id,cc.display_name,o.order_id,o.order_dt,o.b_user_id,b.display_name as aname,

o.mny,o.pay_type,o.coupon_mny 

  from TP_ORDER o 

INNER JOIN TP_BUYER b on o.b_user_id = b.user_id 

INNER JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1) cc on o.b_user_id = cc.user_id 

where

 o.order_dt between str_to_date(' 2015-08-05','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-11','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) 

销售按品类补贴金额

select cc.marketer_id,cc.display_name,sum(o.mny),sum(o.coupon_mny) 

  from TP_ORDER o 

INNER JOIN TP_BUYER b on o.b_user_id = b.user_id 

INNER JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1) cc on o.b_user_id = cc.user_id 

where o.order_id in (select d.order_id as order_id from TP_ORDER_PROD d INNER JOIN TP_PROD pd on d.prod_id = pd.id

where pd.fcategory_id in (1989,5,6) ) and  

 o.order_dt between str_to_date(' 2015-08-05','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-11','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) group by cc.marketer_id order by cc.marketer_id 

流失用户名单

select * from TP_BUYER oo  

where oo.user_id in (

select ss.b_user_id from (select DISTINCT o.b_user_id,count(o.order_id) as sum from TP_ORDER o

where o.order_dt between

str_to_date(' 2015-07-01','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-01','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) GROUP BY o.b_user_id ) ss where ss.sum>4 )

and oo.user_id NOT in (

select o.b_user_id from TP_ORDER o

where o.order_dt between

str_to_date(' 2015-08-01','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-13','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) )

买家订单名单

select o.order_id,b.user_id,cc.display_name,o.recevier_name,b.display_name,b.phone,o.address,o.order_dt   

from TP_ORDER o INNER JOIN TP_BUYER b on o.b_user_id = b.user_id 

INNER JOIN TP_BUY_ADDR d on b.user_id = d.user_id 

LEFT JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 ) cc on b.user_id = cc.user_id

where

 o.order_dt between str_to_date(' 2015-08-01','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-13','%Y-%m-%d %H:%i:%s') and o.status not in (0,6,7,8)

买家订单

SELECT o.b_user_id,sum(o.mny),count(o.order_id),b.display_name,b.phone,MAX(o.order_dt),ad.address,o.s_user_id    

FROM TP_ORDER o INNER JOIN TP_BUYER b on o.b_user_id = b.user_id 

INNER JOIN TP_BUY_ADDR ad on o.b_user_id = ad.user_id 

where o.s_user_id = 219  

and o.order_dt between

str_to_date(' 2015-07-01','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-14','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) GROUP BY o.b_user_id

SELECT o.b_user_id,sum(o.mny),count(o.order_id),b.display_name,b.phone,MAX(o.order_dt),ad.address,o.s_user_id    

FROM TP_ORDER o INNER JOIN TP_BUYER b on o.b_user_id = b.user_id 

INNER JOIN TP_SALER s on s.user_id = o.s_user_id 

INNER JOIN TP_BUY_ADDR ad on o.b_user_id = ad.user_id

where o.b_user_id in ( 10311, 10337, 10362, 10368, 10378, 10386, 10388,

10418, 10425, 10439, 10443, 10357, 10302, 10300, 10299, 10290, 10284, 10268, 10245, 10194, 10172, 10135

)

and o.order_dt between

str_to_date(' 2015-07-01','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-15','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) GROUP BY o.b_user_id   

order by o.b_user_id desc

select * from TP_BUYER oo  INNER JOIN TP_BUY_ADDR ad on ad.user_id = oo.user_id 

INNER JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1 and c.marketer_id = 7) cc 

on oo.user_id = cc.user_id 

where oo.user_id in (

select ss.b_user_id from (select DISTINCT o.b_user_id,count(o.order_id) as sum from TP_ORDER o

where o.order_dt between

str_to_date(' 2015-06-01','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-15','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) GROUP BY o.b_user_id ) ss where ss.sum>0 ) 

生鲜类下单

select count(DISTINCT p.order_id) from TP_ORDER_PROD p INNER JOIN TP_PROD d on p.prod_id = d.id INNER JOIN TP_ORDER o on o.order_id = p.order_id 

INNER JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1 and c.marketer_id = 37) cc

on o.b_user_id = cc.user_id 

where d.fcategory_id in (1,2) and 

o.order_dt between

str_to_date(' 2015-08-01','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-15','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) 

下单大于5次的某个班客户数据

select * from TP_BUYER oo  INNER JOIN TP_BUY_ADDR ad on ad.user_id = oo.user_id

INNER JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1 and c.marketer_id 

in (3,4,48,130,144,155,160,163) ) cc

on oo.user_id = cc.user_id

where oo.user_id in (

select ss.b_user_id from (select DISTINCT o.b_user_id,count(o.order_id) as sum from TP_ORDER o

where o.order_dt between

str_to_date(' 2015-07-01','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-19','%Y-%m-%d %H:%i:%s')  and o.`status` not in (0,6,7,8) GROUP BY o.b_user_id ) ss where ss.sum>4 ) 

GROUP BY oo.user_id

下给某个卖家在某段时间以后无下单的用户

select DISTINCT o.b_user_id,MAX(o.order_dt),b.display_name,b.address,b.phone,cc.display_name,ad.address from TP_ORDER o 

INNER JOIN TP_BUYER b on o.b_user_id = b.user_id 

LEFT JOIN TP_BUY_ADDR ad on o.b_user_id = ad.user_id 

LEFT JOIN  (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1  ) cc

on o.b_user_id = cc.user_id 

where s_user_id in (167,179,279,469,245,493,255,37,40,41,42,44,125,36,158,227,687,126,48,728,32,33,34,35,38,43,45,39) 

AND b_user_id NOT IN (SELECT oo.b_user_id FROM TP_ORDER oo where oo.order_dt between

str_to_date(' 2015-07-15','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-21','%Y-%m-%d %H:%i:%s') ) GROUP BY o.b_user_id

卖家接单数

select o.s_user_id,s.display_name,count(o.order_id) from TP_ORDER o INNER JOIN TP_SALER s on o.s_user_id = s.user_id 

where o.order_dt between

str_to_date(' 2015-08-16','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-23','%Y-%m-%d %H:%i:%s') and o.`status` not in (6,7,8) GROUP BY o.s_user_id 

select o.s_user_id,s.display_name,count(o.order_id),s.category from TP_ORDER o INNER JOIN TP_SALER s on o.s_user_id = s.user_id 

where o.s_user_id in (select user_id from TP_SALE_CID c where c.cid in (3,4,5,6,1988,1989)) and 

o.order_dt between

str_to_date(' 2015-08-16','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-23','%Y-%m-%d %H:%i:%s') and o.`status` not in (6,7,8) GROUP BY o.s_user_id 

按品类查卖家

select sum(d.prod_num*d.prod_price)  from TP_ORDER_PROD d INNER JOIN TP_ORDER o on d.order_id = o.order_id

INNER JOIN TP_PROD pd on d.prod_id = pd.id

 where pd.fcategory_id in (1) and

 o.order_dt between str_to_date(' 2015-08-23','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-30','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8)

区分新增

select o.s_user_id,count(d.order_id) AS summ from TP_ORDER_PROD d INNER JOIN TP_ORDER o on d.order_id = o.order_id 

INNER JOIN TP_PROD pd on d.prod_id = pd.id 

INNER JOIN TP_SALER s on s.user_id = o.s_user_id 

 where pd.fcategory_id in (1989,5,6) and

 o.order_dt between str_to_date(' 2015-08-23','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-30','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) 

and s.createdTime between

str_to_date(' 2015-08-23','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-30','%Y-%m-%d %H:%i:%s') 

GROUP BY o.s_user_id ORDER BY summ desc

select DISTINCT s.user_id  from TP_ORDER_PROD d INNER JOIN TP_ORDER o on d.order_id = o.order_id

INNER JOIN TP_PROD pd on d.prod_id = pd.id 

INNER JOIN TP_SALER s on o.s_user_id = s.user_id 

 where pd.fcategory_id in (1989,5,6) and

 o.order_dt between str_to_date(' 2015-08-16','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-23','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) 

and o.s_user_id not in (select oo.s_user_id from TP_ORDER oo where oo.order_dt 

between str_to_date(' 2015-08-23','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-08-30','%Y-%m-%d %H:%i:%s'))

卖家有交易的数量

select count(DISTINCT o.s_user_id) from TP_ORDER o WHERE  o.order_dt between str_to_date(' 2015-08-30','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-09-06','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8)