mysql 卖家数据 查询模板 放在博客里 方便使用

流失用户

select COUNT(DISTINCT o.s_user_id) from TP_ORDER o where 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.s_user_id NOT IN (SELECT oo.s_user_id FROM TP_ORDER oo where oo.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') ) 

无下单用户

select  * from TP_SALER s 

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 = 0)

cc on s.user_id = cc.user_id 

where s.user_id not in 

(select o.s_user_id from TP_ORDER o where 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.`status` = 1 AND s.grade = 0  

 

下单低于20单

select * from TP_SALER s  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 = 0)

cc on s.user_id = cc.user_id

where s.user_id in (

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

where 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) GROUP BY o.s_user_id ) ss where ss.sum<20 )

活跃商家名单

select * from (select DISTINCT o.s_user_id,s.display_name,s.sale_name,s.category,s.createdTime,s.address,cc.display_name as namea,count(o.order_id) as sum from TP_ORDER o INNER JOIN TP_SALER s 

on s.user_id = o.s_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 = 0) 

cc on s.user_id = cc.user_id 

where 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) GROUP BY o.s_user_id ORDER BY sum desc) 

ss where ss.sum>19  

流失商家名单

 select DISTINCT o.s_user_id,s.display_name,s.sale_name,s.category,s.createdTime,cc.display_name as namea,s.address from TP_ORDER o 

INNER JOIN TP_SALER s on o.s_user_id = s.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 = 0)

cc on s.user_id = cc.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 (0,6,7,8) 

and o.s_user_id NOT in (

select o.s_user_id from TP_ORDER o

where 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 count(DISTINCT o.s_user_id) from TP_ORDER o 

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

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) and 

s.createdTime 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') 

按品类的活跃商家数

select o.s_user_id, count(o.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

 where pd.fcategory_id in (1) and

 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) GROUP BY o.s_user_id  ORDER BY summ desc