狂魔型代码改造记

功能需求
   统计员工指定月：在职员工数，入职员工数，离职员工数，及前个月的员工数。

原有设计
   ■bj_worker：员工表        .
   ■bj_worker_change：员工入职离职日志表，每次入职离职都会添加一条记录，通过状态区分入离职
    ●entry_flag:    入职标志
    ●dimission_flag:离职标志
    ●change_date:入职或离职日

“狂魔型”代码实现（假设统计月份为2016-10） 

   SELECT 
  (
    (SELECT 
      COUNT(id) 
    FROM
      bj_worker_change t 
    WHERE t.change_date <= '20161031' 
      AND t.entry_flag = 1 
      AND t.worker_type = b.worker_type 
      AND t.company_id = b.company_id) - 
    (SELECT 
      COUNT(id) 
    FROM
      bj_worker_change t 
    WHERE t.change_date <= '20161031' 
      AND t.dimission_flag = 1 
      AND t.worker_type = b.worker_type 
      AND t.company_id = b.company_id)
  ) AS current_month_num,<=当前月在职人数
  (SELECT 
    COUNT(id) 
  FROM
    bj_worker_change t 
  WHERE t.change_date <= '20161031' 
    AND t.change_date >= '20161001' 
    AND t.entry_flag = 1 
    AND t.worker_type = b.worker_type 
    AND t.company_id = b.company_id) AS current_add_num,<=本月入职人数
  (SELECT 
    COUNT(id) 
  FROM
    bj_worker_change t 
  WHERE t.change_date <= '20161031' 
    AND t.change_date >= '20161001' 
    AND t.dimission_flag = 1
    AND t.worker_type = b.worker_type 
    AND t.company_id = b.company_id) AS current_js_num,<=本月离职人数
  (
    (SELECT 
      COUNT(id) 
    FROM
      bj_worker_change t 
    WHERE t.change_date <= '20160931' 
      AND t.entry_flag = 1 
      AND t.worker_type = b.worker_type 
      AND t.company_id = b.company_id) - 
    (SELECT 
      COUNT(id) 
    FROM
      bj_worker_change t 
    WHERE t.change_date <= '20160931' 
      AND t.dimission_flag = 1 
      AND t.worker_type = b.worker_type 
      AND t.company_id = b.company_id)
  ) AS last_month_num <=上月员工人数
FROM
  bj_worker_change b 

问题分析

  症状分析

  (1)SQL过于复杂：开发工作量大，容易出错误，后续维护困难；
  (2)执行效率过慢：SQL复杂，数据库压力大，执行效率也很慢；

  症结所在

     表结构设计不合理：bj_worker_change是操作流水表，人员入职离职的操作都对应一条记录，入职离职操作引发业务主体（bj_worker）状态变化，即bj_worker的历史工作状态信息，但原设计中缺失了这张bj_worker历史工作状态表，在获职员工的历史工作状态时就需要实时分析，造成模块代码的复杂及效率的低下。

解决之道
添加bj_worker_duty表，用于记录用户历史在离职日期，结构如下
  bj_worker_duty
   ●id
   ●worker_id
   ●entry_date：       入职日期
   ●departure_date：离职日期（默认为99999999，当添加离职时更改）
   程序需要做相应的改造：
   (1)根据bj_woker_change记录，使用程序或存储过程生成bj_worker_change的初始化记录；
   (2)在插入bj_worker_change这张表时，同时维护bj_worker_duty表的记录。
 
  SQL改造如下

     
 SELECT 
    SUM(CASE WHEN d.`entry_date`<='201610' AND d.`departure_date` >=  '201610'   
        THEN 1 ELSE 0 END) AS current_month_num,
    SUM(CASE WHEN d.`entry_date`>='20161000' AND d.`entry_date` <=  '20161099' 
        THEN 1 ELSE 0 END) AS current_add_num,
    SUM(CASE WHEN d.`departure_date`>='20161000' AND d.`departure_date` <=  '20161099' 
        THEN 1 ELSE 0 END) AS current_sj_num,
    SUM(CASE WHEN d.`entry_date`<='201609' AND d.`departure_date` >=  '201609' 
        THEN 1 ELSE 0 END) AS last_month_num
     FROM bj_worker_duty d;