MySQL上周新增激活用户在上周下单情况_20161107周一
上周新增激活用户在上周下单情况
1、上周激活用户明细
#上周激活用户明细 SELECT a.城市,a.用户ID,a.用户名称,b.用户地址,b.联系电话,a.订单日期,c.年周,c.上周一,a.订单ID,a.产品ID,a.销售员,a.金额 FROM ( SELECT a1.城市,a1.用户ID,a1.用户名称,DATE(a1.订单日期) AS 订单日期,a1.订单ID,a1.产品ID,a1.销售员,SUM(a1.金额) AS 金额 FROM `a003_order` AS a1 WHERE a1.城市="北京" AND a1.金额>0 AND a1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND a1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) GROUP BY a1.订单ID,a1.产品ID ORDER BY a1.订单日期,a1.订单ID ) AS a LEFT JOIN `a001_resterant` AS b ON a.用户ID=b.用户ID INNER JOIN (#上周重激活新增用户明细 取交集 SELECT c6.* FROM ( SELECT c5.城市,c5.用户ID,c5.上周首单日期,c5.年周,c5.上周一 ,c5.上周首单日前30天之前金额,c5.上周首单日前30天金额,c5.上周首单日金额, CASE WHEN c5.上周首单日期>=c5.上周一 AND c5.上周首单日期< c5.本周一 THEN CONCAT("上周",c5.激活情况) ELSE NULL END AS 激活情况,c5.本周一 FROM (#取上周首单 并添加用户重激活新增属性 SELECT c3.城市,c3.用户ID,c3.订单日期 AS 上周首单日期,c3.年周, SUM(IF(DATE(c4.订单日期)<=DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND c4.金额>0,c4.金额,NULL)) AS 上周首单日前30天之前金额, SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,c4.金额,NULL)) AS 上周首单日前30天金额, c3.订单额 AS 上周首单日金额, CASE WHEN SUM(IF(DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,c4.金额,NULL)) IS NULL THEN "新增" WHEN SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,金额,NULL)) IS NOT NULL THEN "留存" WHEN SUM(IF(DATE(c4.订单日期)<=DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND c4.金额>0,金额,NULL)) IS NOT NULL AND SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,金额 ,NULL)) IS NULL THEN "重激活" ELSE NULL END AS 激活情况,c3.上周一,c3.本周一 FROM ( SELECT c2.城市,c2.用户ID,c2.订单日期,c2.年周,c2.订单额,c2.本周一,c2.上周一#取出当月首单订单日期 首单销售 首单额 以这个日期往前推30天判断激活留存情况 FROM (#上周每一天 SELECT c1.城市,c1.用户ID,DATE(c1.订单日期) AS 订单日期,DATE_FORMAT(c1.订单日期,"%x%v") AS 年周,SUM(c1.金额) AS 订单额,DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AS 上周一,DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) AS 本周一 #本月下单用户每天明细 FROM `a003_order` AS c1 WHERE c1.城市="北京" AND c1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND c1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) AND c1.金额>0 GROUP BY c1.用户ID,DATE(c1.订单日期) ) AS c2 GROUP BY c2.用户ID ) AS c3 LEFT JOIN `a003_order` AS c4 ON c4.用户ID=c3.用户ID #where c3.用户ID=22200 GROUP BY c3.用户ID ) AS c5 WHERE c5.激活情况<>"留存" ) AS c6 WHERE c6.激活情况 IS NOT NULL ) AS c ON c.用户ID=a.用户ID
2、上周汇总
SELECT a.城市,a.用户ID,a.用户名称,d.用户地址,d.联系电话,c.上周首单日期 AS 上周激活新增日期,c.年周,c.上周一,a.上周下单天次,a.上周订单数,b.上周产品数,a.上周金额,c.激活情况,c.本周一 FROM (#上周下单天次 订单数 金额 SELECT a3.城市,a3.用户ID,a3.用户名称,COUNT(a3.订单日期) AS 上周下单天次,SUM(a3.订单数) AS 上周订单数,SUM(a3.金额2) AS 上周金额 FROM ( SELECT a2.城市,a2.用户ID,a2.用户名称,a2.订单日期,COUNT(a2.订单ID) AS 订单数,SUM(a2.金额1) AS 金额2 FROM ( SELECT a1.城市,a1.用户ID,a1.用户名称,DATE(订单日期) AS 订单日期,a1.订单ID,SUM(金额) AS 金额1 FROM `a003_order` AS a1 WHERE a1.城市="北京" AND a1.金额>0 AND a1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND a1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) GROUP BY a1.订单ID ) AS a2 GROUP BY a2.用户ID,a2.订单日期 ) AS a3 GROUP BY a3.用户ID ) AS a LEFT JOIN (#上周产品数 SELECT b2.城市,b2.用户ID,COUNT(b2.产品ID) AS 上周产品数 FROM ( SELECT b1.城市,b1.用户ID,b1.产品ID,SUM(金额) AS 金额 FROM `a003_order` AS b1 WHERE b1.城市="北京" AND b1.金额>0 AND b1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND b1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) GROUP BY b1.用户ID,b1.产品ID ) AS b2 GROUP BY b2.用户ID ) AS b ON a.用户ID=b.用户ID INNER JOIN (#上周重激活新增用户明细 取交集 SELECT c6.* FROM ( SELECT c5.城市,c5.用户ID,c5.上周首单日期,c5.年周,c5.上周一 ,c5.上周首单日前30天之前金额,c5.上周首单日前30天金额,c5.上周首单日金额, CASE WHEN c5.上周首单日期>=c5.上周一 AND c5.上周首单日期< c5.本周一 THEN CONCAT("上周",c5.激活情况) ELSE NULL END AS 激活情况,c5.本周一 FROM (#取上周首单 并添加用户重激活新增属性 SELECT c3.城市,c3.用户ID,c3.订单日期 AS 上周首单日期,c3.年周, SUM(IF(DATE(c4.订单日期)<=DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND c4.金额>0,c4.金额,NULL)) AS 上周首单日前30天之前金额, SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,c4.金额,NULL)) AS 上周首单日前30天金额, c3.订单额 AS 上周首单日金额, CASE WHEN SUM(IF(DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,c4.金额,NULL)) IS NULL THEN "新增" WHEN SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,金额,NULL)) IS NOT NULL THEN "留存" WHEN SUM(IF(DATE(c4.订单日期)<=DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND c4.金额>0,金额,NULL)) IS NOT NULL AND SUM(IF(DATE(c4.订单日期)>DATE_ADD(c3.订单日期,INTERVAL -30 DAY) AND DATE(c4.订单日期)<c3.订单日期 AND c4.金额>0,金额 ,NULL)) IS NULL THEN "重激活" ELSE NULL END AS 激活情况,c3.上周一,c3.本周一 FROM ( SELECT c2.城市,c2.用户ID,c2.订单日期,c2.年周,c2.订单额,c2.本周一,c2.上周一#取出当月首单订单日期 首单销售 首单额 以这个日期往前推30天判断激活留存情况 FROM (#上周每一天 SELECT c1.城市,c1.用户ID,DATE(c1.订单日期) AS 订单日期,DATE_FORMAT(c1.订单日期,"%x%v") AS 年周,SUM(c1.金额) AS 订单额,DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AS 上周一,DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) AS 本周一 #本月下单用户每天明细 FROM `a003_order` AS c1 WHERE c1.城市="北京" AND c1.订单日期>=DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AND c1.订单日期<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)) AND c1.金额>0 GROUP BY c1.用户ID,DATE(c1.订单日期) ) AS c2 GROUP BY c2.用户ID ) AS c3 LEFT JOIN `a003_order` AS c4 ON c4.用户ID=c3.用户ID #where c3.用户ID=22200 GROUP BY c3.用户ID ) AS c5 WHERE c5.激活情况<>"留存" ) AS c6 WHERE c6.激活情况 IS NOT NULL ) AS c ON c.用户ID=a.用户ID LEFT JOIN `a001_resterant` AS d ON d.用户ID=a.用户ID ORDER BY a.用户ID,c.上周首单日期
