我想知道每月的活躍用戶數，我使用 SQL Server 2017。
我有一個 AuditLog 表，例如：
- UserID: int
- DateTime: datetime
- AuditType: int
UserID DateTime AuditType
------------------------------
1 2022-01-01 1
1 2022-01-15 4
1 2022-02-20 3
2 2022-01-10 8
2 2022-03-10 1
3 2022-03-20 1
如果某人在給定月份至少有一個條目，則他/她被視為活躍。我希望得到如下結果：
Date Count
2022-01 2
2022-02 1
2022-03 2
uj5u.com熱心網友回復：
我認為您可以將Count 函式Month(datetime)中的函式結合起來GROUP BYSELECT COUNT(UserID)
uj5u.com熱心網友回復：
SELECT (CAST(YEAR(C.DATE)AS CHAR(4)) '-' CAST(MONTH(C.DATE)AS CHAR(2)))YEAR_MONTH,COUNT(C.USER_ID)CNTT
FROM AUDITLOG AS C
GROUP BY (CAST(YEAR(C.DATE)AS CHAR(4)) '-' CAST(MONTH(C.DATE)AS CHAR(2)))
ORDER BY (CAST(YEAR(C.DATE)AS CHAR(4)) '-' CAST(MONTH(C.DATE)AS CHAR(2)));
uj5u.com熱心網友回復：
這是解決方案，
Select [Date],count(1) as Count From (
select Cast(cast(d.DateTime as date) as varchar(7)) as [Date],UserId
from AuditLog d
Group by Cast(cast(d.DateTime as date) as varchar(7)),UserId
) as q1 Group by [Date]
Order by 1
希望，它會奏效。
uj5u.com熱心網友回復：
GROUP DATE (Year and Month) 組合或分開計算不同的 userId
SELECT CONVERT(VARCHAR(7), [DateTime], 126)[Date], COUNT(DISTINCT UserID)[Count]
FROM AuditLog
GROUP BY CONVERT(VARCHAR(7), [DateTime], 126)
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/466596.html