如何使用日期范围计算一个月内的活跃用户

Question

我需要根据用户类型计算给定月份和年份的活动用户。我有每个用户的日期范围，包括开始日期和结束日期。具有开放结束日期的用户是活动用户，必须从时间开始到当前日期(年-月(进行计数。

USER_ID
3673246		1813148内部		836682		1107520		11833644

我相信这个示例查询正是您想要做的。但是，我不同意您的预期结果。例如，在2022年1月，您的"；"内部"；，但是，即使手工将用户相加，我也看到了3个在1月份活跃的用户。自己测试一下：

--Create table variable to hold test data.
DECLARE @testData TABLE (
[user_id] int
, user_type nvarchar(50)
, [start_date] datetime
, end_date datetime
);
--Load up some test data.
INSERT INTO @testData ([user_id], user_type, [start_date], end_date)
VALUES
(3673246,'INTERNAL','1/1/2022','12/31/2999')
,(1813148,'INTERNAL','1/1/2022','12/31/2999')
,(407508,'INTERNAL','1/1/2022','3/1/2022')
,(836682,'EXTERNAL','2/1/2022','12/31/2999')
,(1107520,'EXTERNAL','2/1/2022','6/1/2022')
,(2289472,'INTERNAL','2/1/2022','12/31/2999')
,(1833644,'EXTERNAL','3/1/2022','12/31/2999')
;
--First CTE "x" is used to create a sequence of 10 numbers.
WITH x as (
SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as x(a)
)
--Second CTE "y" creates a sequence of 1000 numbers.
, y as (
SELECT ROW_NUMBER() OVER(ORDER BY hundreds.a, tens.a, ones.a) as row_num
FROM x as ones, x as tens, x as hundreds
)
--Third CTE "months" creates a sequence of months going back in time from today.
--To go farther back than 1000 months, modify the "y" CTE to have a "thousands" (or more) table(s).
, months as (
SELECT
YEAR(DATEADD(month, -1 * y.row_num, GETDATE())) as [year]
, MONTH(DATEADD(month, -1 * y.row_num, GETDATE())) as [month]
, CAST(YEAR(DATEADD(month, -1 * y.row_num, GETDATE())) as nvarchar(6))
+ RIGHT('00' + CAST(MONTH(DATEADD(month, -1 * y.row_num, GETDATE())) as nvarchar(6)),2) as YEAR_MONTH
FROM y
)   
SELECT
m.YEAR_MONTH
, utype.user_type
, COUNT(td.[user_id]) as active_user_count
FROM months as m
--This join makes sure there is a 0 qty record for every month even
--if there are no active users in the month for this user_type.
JOIN (SELECT DISTINCT user_type FROM @testData) as utype
ON 1=1
--This joins our test data to the 2 tables above.
--We make sure the month in m is after the users' start dates
--and before the users' end dates.
LEFT OUTER JOIN @testData as td
ON td.user_type = utype.user_type
AND (
YEAR(td.[start_date]) <= m.[year]
AND MONTH(td.[start_date]) <= m.[month]
)
AND (
YEAR(td.end_date) >= m.[year]
AND MONTH(td.end_date) >= m.[month]
)
WHERE m.[year] = 2022 --Limiting results to just 2022
GROUP BY m.YEAR_MONTH
, utype.user_type
ORDER BY m.YEAR_MONTH, utype.user_type

经过编辑以更好地处理日期。