代码之家 › 专栏 › 技术社区 › Bagzli

使用SQL计算12个月的定期用户

sql-server-2012 sql-server sql

Bagzli · 技术社区 · 7 年前

我现在这样做的方式是:查询我的表,获取所有用户ID和活动时间戳,并将它们返回到我的c代码中。然后用一堆循环和LINQ计算值(这个问题的代码太多了,我不想用c来解决这个问题,我认为没有必要这样做)。

现在,这需要一些时间来运行,我相信一定有更好的方法来使用SQL实现这一点。我已经搜索过了,但还没有找到任何可以让您根据循环条件计数的SQL函数。

MyUsersTable :

UserId | Timestamp
1      | '2018-12-23 00:00:00.000'
1      | '2018-11-23 00:00:00.000'
1      | '2018-10-23 00:00:00.000'

编辑:我确实想过使用 SUM(CASE WHEN month = 1 and month = 2 and month = 3) 但这似乎也不是一个很好的解决方案。

过去12个月内每月至少活跃一次的用户总数。

3 回复 | 直到 7 年前

Gordon Linoff 7 年前

如果您需要2018年每月登录的用户:

select ut.userid
from MyUsersTable ut
where timestamp >= '2018-01-01' and timestamp < '2019-01-01'
group by ut.userid
having count(distinct month(timestamp)) = 12;

Mureinik 7 年前

SELECT   userid
FROM     mytable
WHERE    YEAR(timestamp) = 2018
GROUP BY userid
HAVING   COUNT(DISTINCT MONTH(timestamp)) = 12

Shawn 7 年前

/* These are your input values */
DECLARE @searchDate date = '2018-12-15' ;
DECLARE @monthsToSearch int = 12 ;

 /* First day of search month */    
DECLARE @EndDate   date = DATEADD(month, DATEDIFF(month, 0, @searchDate), 0) ;
 /* First day of month to search from */
DECLARE @StartDate date = DATEADD(month, -@monthsToSearch, @EndDate) ;

SELECT userID --, @StartDate AS startDate, @EndDate AS endDate
FROM (
    SELECT userID,  ( (YEAR(userLoginDT)*100)+MONTH(userLoginDT) ) AS datePoint /* YYYYMM */
    FROM t1
    WHERE userLoginDT >= @StartDate
        AND userLoginDT < @EndDate
) s1
GROUP BY userID
HAVING count(distinct(datePoint)) = @monthsToSearch
;

见 db<&燃气轮机;不停摆弄 here 我举个例子。

@EndDate 基本上是以您声明的日期为准,并计算您当前搜索的月份的第一天。您将搜索此日期之前的任何日期。

@StartDate 从你的 @结束日期 计算要搜索的月数。

(YEAR(userLoginDT)*100)+MONTH(userLoginDT) GROUP BY 以获得您正在搜索的月份的明确计数。这一部分可以通过日历表来加速。

那你就用 HAVING 选择您需要的不同记录数 @monthsToSearch .

注: 这里的很多人都可以证明,在处理日期计算和大量搜索数据时,我非常喜欢使用日历表。这样做可能会使查询速度加快一点。