每年年底统计会员人数

Question

我有一个表，其中包含以下字段:user_id,join_date,leave_date。注意:有些年份没有加入者或离开者，如果它们仍然是成员，则leave_date为NULL。

我试图检索从2010年到今天的每个日历年结束时的成员总数。

在最近的一年里，我一直试图用下面的代码来写这个:

SELECT COUNT(user_id) 
FROM memberships 
WHERE join_date < '2021-12-31' AND leave_date is null

Answer 1

With Years 
As
(
Select min(year(join_date)) as [Year]
From members
Union All
Select Y.[Year] + 1
From Years Y
Where Y.[Year] + 1 <= Year(GetDate()) 
)
Select [Year], 
COUNT(*) As [End Year Member Count]
From Years
Left Join members On [Year] Between Year(members.join_date) 
And (Year(IsNull(leave_date, getdate() + 365)) - 1)
Group By [Year]

Answer 2

是的，像下面这样的查询呢

; with date_cte as 
(
select cast('2010-12-31' as date) as lastdate, cast('2010-01-01' as date) as prev_lastdate
union all
select dateadd(YY,1,lastdate) as lastdate, dateadd(YY,1,prev_lastdate) as prev_lastdate
from date_cte where  dateadd(YY,1,lastdate) <getutcdate()
)

select 
lastdate,count(user_id) as count_members
from memberships 
right join date_cte
on  join_date  <= lastdate 
and (leave_date is null or leave_date >lastdate)
group by lastdate

我对这个数据运行了测试

drop table if exists memberships ;
create table memberships (user_id int , join_date date, leave_date date) ;
insert into memberships values
(1,'2009-01-01','2015-09-08'),
(2,'2009-01-01','2016-09-08'),
(3,'2012-01-01','2013-09-08'),
(4,'2013-01-01','2013-07-08'),
(5,'2017-01-01',NULL);
select * from memberships

结果:

lastdate    count_members
2010-12-31  2
2011-12-31  2
2012-12-31  3
2013-12-31  2
2014-12-31  2
2015-12-31  1
2016-12-31  0
2017-12-31  1
2018-12-31  1
2019-12-31  1
2020-12-31  1