我有以下模式:
name | hire_date | last_date | active
n1 | January 20, 2018, 12:00 AM | | true
n2 | October 1, 2018, 12:00 AM | February 18, 2020, 12:00 AM | false
n3 | October 15, 2018, 12:00 AM | | true
n4 | January 12 2019, 12:00 AM | | true
n5 | February 12, 2019, 12:00 AM | | true
n6 | December 12, 2019, 12:00 AM | January 15, 2020, 12:00 AM | false
n7 | February 17, 2020, 12:00 AM | | true
我需要提取active
员工总数的每月计数。
我正在尝试将表本身连接起来,并获得行数,但不确定如何进行:
select
-- ot1.name, ot2.name, ot1.hire_date, ot2.hire_date, ot1.last_date, ot2.last_date,
date_trunc('month', ot1.hire_date) AS hire_dates, count(ot1.hire_date)
from ops_teammates ot1
join ops_teammates ot2 ON ot1.hire_date >= ot2.hire_date
group by hire_dates
order by ot1.hire_date, ot2.hire_date;
我怎样才能得到像一样的计数
Jan 2018, 1
Oct 2018, 3
Jan 2019, 4
Feb 2019, 5
Dec 2019, 6
Jan 2020, 5
Feb 2020, 5
您可以使用union all
和聚合::
select month_start, sum(cnt),
sum(sum(cnt)) over (order by month_start) as running_cnt
from (
select 1 cnt, date_trunc('month', hire_date) month_start from ops_teammates
union all
select -1, date_trunc('month', last_date) + interval '1 month' from ops_teammates where last_date is not null
)
group by month_start
order by month_start