postgresql - 查询按月查找员 worker 数

Question

我想查找日期范围内每个月的员 worker 数 - 2012 年 1 月到 2013 年 1 月。

示例历史:

• 我们在 2012 年之前雇用了 500 名员工
• 2012 年 1 月 - 雇用了 ​​30 名员工
• 2 月 - 20 名员工离职-3 月 - 没有任何变化

我想要这样的结果，

Jan 2012   530
Feb 2012   510
March 2012 550
...

在 employees 表中我有列:

id
employee_name
date_started
date_terminated
employee_status_type /* true for current employees, false otherwise */

Answer 1

create table employees (id int, date_started date, date_terminated date);

insert into employees values
  (1,'2012-01-01','2012-07-01'),
  (2,'2012-11-01',NULL),
  (3,'2010-01-01','2012-02-10');

with

time_space as (
  select
    gs::date as bom,
    (gs + interval '1 month' - interval '1 day')::date as eom
  from
     generate_series('2012-01-01'::date,'2013-01-01'::date, '1 month') gs

)

select
  ts.bom,
  coalesce(x.employees,0) as employees
from
  time_space ts
  left join (
    select
      bom,
      count(*) as employees
    from
      time_space ts
      join employees e on (coalesce(e.date_terminated,'3000-01-01'::date) >= ts.bom and e.date_started <= ts.eom)
    group by
      bom) x using (bom)

SQLFiddle