狂魔型代码改造记

[b][size=x-large]功能需求[/size][/b]
统计员工指定月：在职员工数，入职员工数，离职员工数，及前个月的员工数。

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

[b][size=x-large]“狂魔型”代码实现（假设统计月份为2016-10）
[/size][/b]

   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 

[b][size=x-large]问题分析[/size][/b]

[b][size=large]症状分析[/size][/b]

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

[b][size=large]症结所在[/size][/b]

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

[b][size=x-large]解决之道[/size][/b]
[b][size=large]添加bj_worker_duty表，用于记录用户历史在离职日期，结构如下[/size][/b]
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表的记录。

[b][size=large]SQL改造如下[/size][/b]

     
 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;