狂魔型代码改造记

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

原有设计
  

• bj_worker：员工表        .
•    bj_worker_change：员工入职离职日志表，每次入职离职都会添加一条记录，通过状态区分入离职
  [list]
   
• entry_flag:    入职标志
   
• dimission_flag:离职标志
   
• change_date:入职或离职日
 

[/list]

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

<pre name="code" class="sql">
   SELECT
  (
    (SELECT
      COUNT(id)
    FROM
      bj_worker_change t
    WHERE t.change_date &lt;= '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 &lt;= '20161031'
      AND t.dimission_flag = 1
      AND t.worker_type = b.worker_type
      AND t.company_id = b.company_id)
  ) AS current_month_num,&lt;=当前月在职人数
  (SELECT
    COUNT(id)
  FROM
    bj_worker_change t
  WHERE t.change_date &lt;= '20161031'
    AND t.change_date &gt;= '20161001'
    AND t.entry_flag = 1
    AND t.worker_type = b.worker_type
    AND t.company_id = b.company_id) AS current_add_num,&lt;=本月入职人数
  (SELECT
    COUNT(id)
  FROM
    bj_worker_change t
  WHERE t.change_date &lt;= '20161031'
    AND t.change_date &gt;= '20161001'
    AND t.dimission_flag = 1
    AND t.worker_type = b.worker_type
    AND t.company_id = b.company_id) AS current_js_num,&lt;=本月离职人数
  (
    (SELECT
      COUNT(id)
    FROM
      bj_worker_change t
    WHERE t.change_date &lt;= '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 &lt;= '20160931'
      AND t.dimission_flag = 1
      AND t.worker_type = b.worker_type
      AND t.company_id = b.company_id)
  ) AS last_month_num &lt;=上月员工人数
FROM
  bj_worker_change b
</pre>

问题分析
  症状分析
  (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，当添加离职时更改）

    
  SQL改造如下
<pre name="code" class="sql">     SELECT
         SUM(CASE WHEN d.`entry_date`&lt;='201610' AND d.`departure_date` &gt;=  '201610' THEN 1 ELSE 0 END) AS current_month_num,
         SUM(CASE WHEN d.`entry_date`&gt;='20161000' AND d.`entry_date` &lt;=  '20161099' THEN 1 ELSE 0 END) AS current_add_num,
         SUM(CASE WHEN d.`departure_date`&gt;='20161000' AND d.`departure_date` &lt;=  '20161099' THEN 1 ELSE 0 END) AS current_sj_num,
         SUM(CASE WHEN d.`entry_date`&lt;='201609' AND d.`departure_date` &gt;=  '201609' THEN 1 ELSE 0 END) AS last_month_num
     FROM bj_worker_duty d; </pre>