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、设置发邮件作业 收件人地址 发件人地址 用户名 密码 服务器端口等设置好