sql - 如何通过 HireDate 和 TerminationDate 在公司中获得最多的员工？

Question

我是新手。所以我的 PostgreSQL 查询需要帮助。我有一个名为 Employee 的表，如下所示:

ID | FirstName | LastName | HireDate   | TerminationDate | Salary
---+-----------+----------+------------+-----------------+-------
1  | Peter     | Jones    | 2010-06-20 | 2016-01-01      | 1000
2  | Eva       | Johnson  | 2010-02-12 | NULL            | 2000
3  | Ryan      | Kent     | 2012-03-14 | 2014-01-01      | 2000
4  | Jack      | River    | 2013-09-10 | NULL            | 3000
5  | Thomas    | Lex      | 2013-09-10 | NULL            | 2000
6  | Gerald    | Smith    | 2015-04-10 | 2015-05-01      | 4000

我如何获取员工的所有信息，我必须添加另一列显示公司在服务期间雇用的最大员 worker 数，以及获得该最大员 worker 数的日期？

表格应该是这样的

ID | FirstName | LastName | HireDate   | TerminationDate | Salary | MaxEmp | DateAcq
---+-----------+----------+------------+-----------------+--------+--------+-----------
1  | Peter     | Jones    | 2010-06-20 | 2016-01-01      | 1000   | 5      | 2015-04-10
2  | Eva       | Johnson  | 2010-02-12 | NULL            | 2000   | 5      | 2015-04-10
3  | Ryan      | Kent     | 2012-03-14 | 2014-01-01      | 2000   | 5      | 2013-09-10
4  | Jack      | River    | 2013-09-10 | NULL            | 3000   | 5      | 2015-04-10
5  | Thomas    | Lex      | 2013-09-10 | NULL            | 2000   | 5      | 2015-04-10
6  | Gerald    | Smith    | 2015-04-10 | 2015-05-01      | 4000   | 5      | 2015-04-10

其中MaxEmp为公司在其任职期间雇用的最大人数，DateAcq为获得最大人数的日期，TerminationDate中的NULL记录表明该员工仍在公司工作。

Answer 1

此查询显示就业随时间的变化:

select sum(count) over (order by dateaq) as maxemp, dateaq
from (
    select hiredate as dateaq, 1 as count
    from employee
    union all
    select terminationdate, -1
    from employee
    where terminationdate is not null
    ) s;

 maxemp |   dateaq   
--------+------------
      1 | 2010-02-12
      2 | 2010-06-20
      3 | 2012-03-14
      5 | 2013-09-10
      5 | 2013-09-10
      4 | 2014-01-01
      5 | 2015-04-10
      4 | 2015-05-01
      3 | 2016-01-01
(9 rows)

如您所见，最大雇佣人数为 5 人，因为 Ryan 和 Gerald 没有同时被雇佣。

将这些结果与表格结合起来，并为每位员工选择最大值:

select distinct on (id) *
from employee e
join (
    select sum(count) over (order by dateaq) as maxemp, dateaq
    from (
        select hiredate as dateaq, 1 as count
        from employee
        union all
        select terminationdate, -1
        from employee
        where terminationdate is not null
        ) s
    ) s on dateaq between hiredate and coalesce(terminationdate, 'infinity')
order by id, maxemp desc, dateaq desc;

 id | firstname | lastname |  hiredate  | terminationdate | salary | maxemp |   dateaq   
----+-----------+----------+------------+-----------------+--------+--------+------------
  1 | Peter     | Jones    | 2010-06-20 | 2016-01-01      |   1000 |      5 | 2015-04-10
  2 | Eva       | Johnson  | 2010-02-12 |                 |   2000 |      5 | 2015-04-10
  3 | Ryan      | Kent     | 2012-03-14 | 2014-01-01      |   2000 |      5 | 2013-09-10
  4 | Jack      | River    | 2013-09-10 |                 |   3000 |      5 | 2015-04-10
  5 | Thomas    | Lex      | 2013-09-10 |                 |   2000 |      5 | 2015-04-10
  6 | Gerald    | Smith    | 2015-04-10 | 2015-05-01      |   4000 |      5 | 2015-04-10
(6 rows)