YTD 每月唯一用户数

Question

我需要计算YTD每月的唯一用户数。我目前正在为此编写逻辑。我找到了不需要计算唯一值的解决方案，但在这种情况下，它使事情变得更加复杂。 因此，例如，如果我有 4 个用户 A，B，C，D，他们显示如下：

Date          User id
============
2019/01/06    A
-------------
2019/01/25    B
-------------
2019/02/05    C
-------------
2019/02/05    A
------------
2019/03/05    C
-------------
2019/04/05    D
-------------

我的输出应该是这样的(例如，对于 3 月，从 1 月到 3 月底显示的唯一用户数(

MONTH   user count
=================
January  2
-----------------
February 3
-----------------
March    3
-----------------
April    4
------------------

有没有一些简单的解决方案？

多谢

Answer 1

看看这个：

DECLARE  @Table TABLE(date date , UserID varchar(250))
INSERT INTO @table values
('2019/01/06', 'A'),
('2019/01/25', 'B'),
('2019/02/05', 'C'),
('2019/02/05', 'A'),
('2019/03/05', 'C'),
('2019/04/05', 'D')

SELECT DISTINCT
DATENAME(MONTH, Date) [Date], --This function returns a character string representing the specified datepart of the specified date.
Count(UserID) [UserIDSum] -- it counts the amount of UserIds
FROM @Table GROUP BY Date

日期名称的函数源

数据输出：

Date     | UserIDSum
--------------------
April    | 1
February | 2
January  | 1
March    | 1

至少这适用于MSSSQL。我希望它对你有所帮助。

Answer 2

您可以在出现的第一个月计算用户：

select year(min_date), month(min_date),
count(*) as starts_in_month,
sum(count(*)) over (order by min(min_date)
from (select user_id, min(date) as min_date
from t
group by user_id
) u
group by year(min_date), month(min_date)
order by year(min_date), month(min_date);

请注意，日期函数因数据库而异。 您应该能够使用这样的结构提取年份和月份。

Answer 3

请尝试以下查询，

create table #tempt(tdate datetime, userid varchar(20));
insert into #tempt (tdate, userid) values('2019-02-06','A');
insert into #tempt (tdate, userid) values('2019-01-06','A');
insert into #tempt (tdate, userid) values('2019-01-06','B');
select * from #tempt
select  FORMAT(tdate, 'MMMM')as Monthname, count(userid)as usercount from #tempt 
group by tdate

Answer 4

你可以试试这个。

declare @Table table (tdate datetime, userid varchar(20));
insert into @Table (tdate, userid) values('2019-01-06','A');
insert into @Table (tdate, userid) values('2019-02-06','D');
insert into @Table (tdate, userid) values('2019-02-06','C');
insert into @Table (tdate, userid) values('2019-02-06','A');
insert into @Table (tdate, userid) values('2019-03-06','B');
insert into @Table (tdate, userid) values('2019-04-06','D');
insert into @Table (tdate, userid) values('2019-05-06','E');
; with cte as (
select tdate, userid, year(tdate) as yr , month(tdate) as mon from @Table
) 
, ct as 
(
select distinct c.yr, c.mon, ct.userid as cc from cte as c 
cross join cte as ct where c.tdate>=ct.tdate
group by c.yr, c.mon, ct.userid
)
select distinct yr, mon, count(cc) from ct group by yr, mon