2016年11月7日周一：Kettle系统监测销售团队每日任务完成情况分析

SELECT a.销售员,c.当月销售确认额,a.当月订单额,b.当月首单数,b.当月激活数,
a1,b.b1,b.c1,a2,b.b2,b.c2,a3,b.b3,b.c3,a4,b.b4,b.c4,a5,b.b5,b.c5,a6,b.b6,b.c6,a7,b.b7,b.c7,a8,b.b8,b.c8,a9,b.b9,b.c9,a10,b.b10,b.c10,a11,b.b11,b.c11,a12,b.b12,b.c12,a13,b.b13,b.c13,a14,b.b14,b.c14,a15,b.b15,b.c15,
a16,b.b16,b.c16,a17,b.b17,b.c17,a18,b.b18,b.c18,a19,b.b19,b.c19,a20,b.b20,b.c20,a21,b.b21,b.c21,a22,b.b22,b.c22,a23,b.b23,b.c23,a24,b.b24,b.c24,a25,b.b25,b.c25,a26,b.b26,b.c26,a27,b.b27,b.c27,a28,b.b28,b.c28,
a29,b.b29,b.c29,a30,b.b30,b.c30,a31,b.b31,b.c31
FROM (SELECT a1.销售员,SUM(a1.金额) AS 当月订单额,#当月订单额及每天订单额SUM(IF(DAY(a1.订单日期)&＃61;1,金额,NULL)) AS a1,SUM(IF(DAY(a1.订单日期)&＃61;2,金额,NULL)) AS a2,SUM(IF(DAY(a1.订单日期)&＃61;3,金额,NULL)) AS a3,SUM(IF(DAY(a1.订单日期)&＃61;4,金额,NULL)) AS a4,SUM(IF(DAY(a1.订单日期)&＃61;5,金额,NULL)) AS a5,SUM(IF(DAY(a1.订单日期)&＃61;6,金额,NULL)) AS a6,SUM(IF(DAY(a1.订单日期)&＃61;7,金额,NULL)) AS a7,SUM(IF(DAY(a1.订单日期)&＃61;8,金额,NULL)) AS a8,SUM(IF(DAY(a1.订单日期)&＃61;9,金额,NULL)) AS a9,SUM(IF(DAY(a1.订单日期)&＃61;10,金额,NULL)) AS a10,SUM(IF(DAY(a1.订单日期)&＃61;11,金额,NULL)) AS a11,SUM(IF(DAY(a1.订单日期)&＃61;12,金额,NULL)) AS a12,SUM(IF(DAY(a1.订单日期)&＃61;13,金额,NULL)) AS a13,SUM(IF(DAY(a1.订单日期)&＃61;14,金额,NULL)) AS a14,SUM(IF(DAY(a1.订单日期)&＃61;15,金额,NULL)) AS a15,SUM(IF(DAY(a1.订单日期)&＃61;16,金额,NULL)) AS a16,SUM(IF(DAY(a1.订单日期)&＃61;17,金额,NULL)) AS a17,SUM(IF(DAY(a1.订单日期)&＃61;18,金额,NULL)) AS a18,SUM(IF(DAY(a1.订单日期)&＃61;19,金额,NULL)) AS a19,SUM(IF(DAY(a1.订单日期)&＃61;20,金额,NULL)) AS a20,SUM(IF(DAY(a1.订单日期)&＃61;21,金额,NULL)) AS a21,SUM(IF(DAY(a1.订单日期)&＃61;22,金额,NULL)) AS a22,SUM(IF(DAY(a1.订单日期)&＃61;23,金额,NULL)) AS a23,SUM(IF(DAY(a1.订单日期)&＃61;24,金额,NULL)) AS a24,SUM(IF(DAY(a1.订单日期)&＃61;25,金额,NULL)) AS a25,SUM(IF(DAY(a1.订单日期)&＃61;26,金额,NULL)) AS a26,SUM(IF(DAY(a1.订单日期)&＃61;27,金额,NULL)) AS a27,SUM(IF(DAY(a1.订单日期)&＃61;28,金额,NULL)) AS a28,SUM(IF(DAY(a1.订单日期)&＃61;29,金额,NULL)) AS a29,SUM(IF(DAY(a1.订单日期)&＃61;30,金额,NULL)) AS a30,SUM(IF(DAY(a1.订单日期)&＃61;31,金额,NULL)) AS a31FROM &＃96;a003_order&＃96; AS a1WHERE a1.销售员 IS NOT NULL AND a1.城市&＃61;"北京" AND DATE_FORMAT(a1.订单日期,"%Y%m")&＃61;DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY),"%Y%m") AND a1.订单日期<CURRENT_DATE GROUP BY a1.销售员
) AS a
LEFT JOIN (SELECT b5.销售员,SUM(IF(b5.激活情况&＃61;"新增",1,NULL))AS 当月首单数,SUM(IF(b5.激活情况&＃61;"重激活",1,NULL)) AS 当月激活数,#首单数SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;1,1,NULL)) AS b1,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;2,1,NULL)) AS b2,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;3,1,NULL)) AS b3,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;4,1,NULL)) AS b4,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;5,1,NULL)) AS b5,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;6,1,NULL)) AS b6,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;7,1,NULL)) AS b7,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;8,1,NULL)) AS b8,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;9,1,NULL)) AS b9,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;10,1,NULL)) AS b10,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;11,1,NULL)) AS b11,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;12,1,NULL)) AS b12,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;13,1,NULL)) AS b13,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;14,1,NULL)) AS b14,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;15,1,NULL)) AS b15,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;16,1,NULL)) AS b16,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;17,1,NULL)) AS b17,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;18,1,NULL)) AS b18,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;19,1,NULL)) AS b19,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;20,1,NULL)) AS b20,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;21,1,NULL)) AS b21,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;22,1,NULL)) AS b22,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;23,1,NULL)) AS b23,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;24,1,NULL)) AS b24,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;25,1,NULL)) AS b25,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;26,1,NULL)) AS b26,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;27,1,NULL)) AS b27,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;28,1,NULL)) AS b28,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;29,1,NULL)) AS b29,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;30,1,NULL)) AS b30,SUM(IF(b5.激活情况&＃61;"新增" AND DAY(b5.当月首单日期)&＃61;31,1,NULL)) AS b31,#SUM(IF(b5.激活情况&＃61;"重激活",1,NULL)) AS 当月激活数,#激活数SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;1,1,NULL)) AS c1,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;2,1,NULL)) AS c2,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;3,1,NULL)) AS c3,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;4,1,NULL)) AS c4,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;5,1,NULL)) AS c5,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;6,1,NULL)) AS c6,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;7,1,NULL)) AS c7,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;8,1,NULL)) AS c8,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;9,1,NULL)) AS c9,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;10,1,NULL)) AS c10,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;11,1,NULL)) AS c11,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;12,1,NULL)) AS c12,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;13,1,NULL)) AS c13,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;14,1,NULL)) AS c14,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;15,1,NULL)) AS c15,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;16,1,NULL)) AS c16,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;17,1,NULL)) AS c17,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;18,1,NULL)) AS c18,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;19,1,NULL)) AS c19,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;20,1,NULL)) AS c20,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;21,1,NULL)) AS c21,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;22,1,NULL)) AS c22,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;23,1,NULL)) AS c23,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;24,1,NULL)) AS c24,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;25,1,NULL)) AS c25,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;26,1,NULL)) AS c26,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;27,1,NULL)) AS c27,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;28,1,NULL)) AS c28,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;29,1,NULL)) AS c29,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;30,1,NULL)) AS c30,SUM(IF(b5.激活情况&＃61;"重激活" AND DAY(b5.当月首单日期)&＃61;31,1,NULL)) AS c31 FROM (SELECT b3.用户ID,b3.销售员,b3.订单日期 AS 当月首单日期,SUM(IF(DATE(b4.订单日期)<b3.订单日期 AND b4.金额>0,b4.金额,NULL)) AS 当月首单日以前总金额,SUM(IF(DATE(b4.订单日期)<&＃61;DATE_ADD(b3.订单日期,INTERVAL -30 DAY) AND b4.金额>0,b4.金额,NULL)) AS 当月首单日前30天之前金额,SUM(IF(DATE(b4.订单日期)>DATE_ADD(b3.订单日期,INTERVAL -30 DAY) AND DATE(b4.订单日期)<b3.订单日期 AND b4.金额>0,b4.金额,NULL)) AS 当月首单日前30天金额,b3.订单额 AS 当月首单日金额,CASE WHEN SUM(IF(DATE(b4.订单日期)<b3.订单日期 AND b4.金额>0,b4.金额,NULL)) IS NULL THEN "新增"WHEN SUM(IF(DATE(b4.订单日期)>DATE_ADD(b3.订单日期,INTERVAL -30 DAY) AND DATE(b4.订单日期)<b3.订单日期 AND b4.金额>0,金额,NULL)) IS NOT NULL THEN "留存"WHEN SUM(IF(DATE(b4.订单日期)<&＃61;DATE_ADD(b3.订单日期,INTERVAL -30 DAY) AND b4.金额>0,金额,NULL)) IS NOT NULL AND SUM(IF(DATE(b4.订单日期)>DATE_ADD(b3.订单日期,INTERVAL -30 DAY) AND DATE(b4.订单日期)<b3.订单日期 AND b4.金额>0,金额 ,NULL)) IS NULL THEN "重激活"ELSE NULL END AS 激活情况FROM (SELECT b2.用户ID,b2.订单日期,b2.销售员 AS 销售员,b2.订单额#取出当月首单订单日期 首单销售 首单额 以这个日期往前推30天判断激活留存情况 FROM ( SELECT b1.用户ID,DATE(b1.订单日期) AS 订单日期,b1.销售员,SUM(金额) AS 订单额 #当月下单用户每天明细FROM &＃96;a003_order&＃96; AS b1WHERE b1.城市&＃61;"北京" AND DATE_FORMAT(b1.订单日期,"%Y%m")&＃61;DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY),"%Y%m") AND b1.订单日期<CURRENT_DATE AND b1.金额>0GROUP BY b1.用户ID,DATE(b1.订单日期)) AS b2GROUP BY b2.用户ID) AS b3LEFT JOIN &＃96;a003_order&＃96; AS b4 ON b4.用户ID&＃61;b3.用户ID#where b3.用户ID&＃61;22200GROUP BY b3.用户ID) AS b5WHERE b5.销售员 IS NOT NULLGROUP BY b5.销售员
) AS b ON a.销售员&＃61;b.销售员
LEFT JOIN (#05表销售确认额SELECT c1.销售员,SUM(c1.销售额) AS 当月销售确认额FROM &＃96;a005_account&＃96; AS c1WHERE c1.销售员 IS NOT NULL AND c1.城市&＃61;"北京" AND DATE_FORMAT(c1.应收日,"%Y%m")&＃61;DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY),"%Y%m") AND c1.应收日<CURRENT_DATEGROUP BY c1.销售员
) AS c ON a.销售员&＃61;c.销售员
ORDER BY a.当月订单额 DESC