kettle监控销售人员当月每天任务完成率_20161107周一
1、上面是目标表，其中激活客户数为当月每天之前30天未下单的客户
2、写SQL
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.订单日期)=1,金额,NULL)) AS a1,SUM(IF(DAY(a1.订单日期)=2,金额,NULL)) AS a2,SUM(IF(DAY(a1.订单日期)=3,金额,NULL)) AS a3, SUM(IF(DAY(a1.订单日期)=4,金额,NULL)) AS a4,SUM(IF(DAY(a1.订单日期)=5,金额,NULL)) AS a5,SUM(IF(DAY(a1.订单日期)=6,金额,NULL)) AS a6, SUM(IF(DAY(a1.订单日期)=7,金额,NULL)) AS a7,SUM(IF(DAY(a1.订单日期)=8,金额,NULL)) AS a8,SUM(IF(DAY(a1.订单日期)=9,金额,NULL)) AS a9, SUM(IF(DAY(a1.订单日期)=10,金额,NULL)) AS a10,SUM(IF(DAY(a1.订单日期)=11,金额,NULL)) AS a11,SUM(IF(DAY(a1.订单日期)=12,金额,NULL)) AS a12, SUM(IF(DAY(a1.订单日期)=13,金额,NULL)) AS a13,SUM(IF(DAY(a1.订单日期)=14,金额,NULL)) AS a14,SUM(IF(DAY(a1.订单日期)=15,金额,NULL)) AS a15, SUM(IF(DAY(a1.订单日期)=16,金额,NULL)) AS a16,SUM(IF(DAY(a1.订单日期)=17,金额,NULL)) AS a17,SUM(IF(DAY(a1.订单日期)=18,金额,NULL)) AS a18, SUM(IF(DAY(a1.订单日期)=19,金额,NULL)) AS a19,SUM(IF(DAY(a1.订单日期)=20,金额,NULL)) AS a20,SUM(IF(DAY(a1.订单日期)=21,金额,NULL)) AS a21, SUM(IF(DAY(a1.订单日期)=22,金额,NULL)) AS a22,SUM(IF(DAY(a1.订单日期)=23,金额,NULL)) AS a23,SUM(IF(DAY(a1.订单日期)=24,金额,NULL)) AS a24, SUM(IF(DAY(a1.订单日期)=25,金额,NULL)) AS a25,SUM(IF(DAY(a1.订单日期)=26,金额,NULL)) AS a26,SUM(IF(DAY(a1.订单日期)=27,金额,NULL)) AS a27, SUM(IF(DAY(a1.订单日期)=28,金额,NULL)) AS a28,SUM(IF(DAY(a1.订单日期)=29,金额,NULL)) AS a29,SUM(IF(DAY(a1.订单日期)=30,金额,NULL)) AS a30, SUM(IF(DAY(a1.订单日期)=31,金额,NULL)) AS a31 FROM `a003_order` AS a1 WHERE a1.销售员 IS NOT NULL AND a1.城市="北京" AND DATE_FORMAT(a1.订单日期,"%Y%m")=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.激活情况="新增",1,NULL))AS 当月首单数,SUM(IF(b5.激活情况="重激活",1,NULL)) AS 当月激活数,#首单数 SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=1,1,NULL)) AS b1,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=2,1,NULL)) AS b2,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=3,1,NULL)) AS b3, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=4,1,NULL)) AS b4,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=5,1,NULL)) AS b5,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=6,1,NULL)) AS b6, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=7,1,NULL)) AS b7,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=8,1,NULL)) AS b8,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=9,1,NULL)) AS b9, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=10,1,NULL)) AS b10,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=11,1,NULL)) AS b11,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=12,1,NULL)) AS b12, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=13,1,NULL)) AS b13,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=14,1,NULL)) AS b14,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=15,1,NULL)) AS b15, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=16,1,NULL)) AS b16,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=17,1,NULL)) AS b17,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=18,1,NULL)) AS b18, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=19,1,NULL)) AS b19,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=20,1,NULL)) AS b20,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=21,1,NULL)) AS b21, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=22,1,NULL)) AS b22,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=23,1,NULL)) AS b23,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=24,1,NULL)) AS b24, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=25,1,NULL)) AS b25,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=26,1,NULL)) AS b26,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=27,1,NULL)) AS b27, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=28,1,NULL)) AS b28,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=29,1,NULL)) AS b29,SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=30,1,NULL)) AS b30, SUM(IF(b5.激活情况="新增" AND DAY(b5.当月首单日期)=31,1,NULL)) AS b31, #SUM(IF(b5.激活情况="重激活",1,NULL)) AS 当月激活数,#激活数 SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=1,1,NULL)) AS c1,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=2,1,NULL)) AS c2,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=3,1,NULL)) AS c3, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=4,1,NULL)) AS c4,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=5,1,NULL)) AS c5,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=6,1,NULL)) AS c6, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=7,1,NULL)) AS c7,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=8,1,NULL)) AS c8,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=9,1,NULL)) AS c9, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=10,1,NULL)) AS c10,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=11,1,NULL)) AS c11,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=12,1,NULL)) AS c12, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=13,1,NULL)) AS c13,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=14,1,NULL)) AS c14,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=15,1,NULL)) AS c15, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=16,1,NULL)) AS c16,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=17,1,NULL)) AS c17,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=18,1,NULL)) AS c18, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=19,1,NULL)) AS c19,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=20,1,NULL)) AS c20,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=21,1,NULL)) AS c21, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=22,1,NULL)) AS c22,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=23,1,NULL)) AS c23,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=24,1,NULL)) AS c24, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=25,1,NULL)) AS c25,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=26,1,NULL)) AS c26,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=27,1,NULL)) AS c27, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=28,1,NULL)) AS c28,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=29,1,NULL)) AS c29,SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=30,1,NULL)) AS c30, SUM(IF(b5.激活情况="重激活" AND DAY(b5.当月首单日期)=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.订单日期)<=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.订单日期)<=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 `a003_order` AS b1 WHERE b1.城市="北京" AND DATE_FORMAT(b1.订单日期,"%Y%m")=DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY),"%Y%m") AND b1.订单日期<CURRENT_DATE AND b1.金额>0 GROUP BY b1.用户ID,DATE(b1.订单日期) ) AS b2 GROUP BY b2.用户ID ) AS b3 LEFT JOIN `a003_order` AS b4 ON b4.用户ID=b3.用户ID #where b3.用户ID=22200 GROUP BY b3.用户ID ) AS b5 WHERE b5.销售员 IS NOT NULL GROUP BY b5.销售员 ) AS b ON a.销售员=b.销售员 LEFT JOIN (#05表销售确认额 SELECT c1.销售员,SUM(c1.销售额) AS 当月销售确认额 FROM `a005_account` AS c1 WHERE c1.销售员 IS NOT NULL AND c1.城市="北京" AND DATE_FORMAT(c1.应收日,"%Y%m")=DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY),"%Y%m") AND c1.应收日<CURRENT_DATE GROUP BY c1.销售员 ) AS c ON a.销售员=c.销售员 ORDER BY a.当月订单额 DESC
3、做excel模板
将上面SQL数据导入excel中 设置好格式表头 删除数据 还是用到SUMif函数 把所有销售员当月每天的这两个指标都用公式计算出来
4、保存excel模板 文件名设置成英文名 * _style.xlsx 这样结尾最好
5、设置kettle转换
设置好数据库连接服务器 表输入里选择数据库连接 表输出选择excel表输出 调用第4步excel模板文件* _style.xlsx
6、执行转换检测生成的数据和预设的格式是否相同 如果相同进行第7步即可 不相同再调整excel模板
7、设置发邮件作业 收件人地址 发件人地址 用户名 密码 服务器端口等设置好
