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.上周首单日期