SQL:统计过去一周有活动的用户 [英] SQL: Count Users with Activity in the Past Week

问题描述

我正在尝试计算在所有日期的 30 天之内的 7 天或 10 天内至少进行过两次会话的用户数量.

我的数据如下:

Date UserID SessionID1/1/2013 Bob1234 12/1/2013 Bob1234 22/2/2013 Bob1234 32/3/2013 Cal5678 4

这将导致下表(仅显示选择的日期)

Date CountActiveUsers1/1/2013 11/15/2013 02/2/2013 12/3/2013 2

真实数据集具有连续数据范围内所有日期的值，结果表应为每个日期都有一个条目.

SessionID 是唯一的，UserID 总是指同一个人.

到目前为止，我有两个查询可以做一些接近的事情.第一个返回用户过去一周的会话数:

SELECT Count(d.[会话ID]) 作为 SessionPastWeek,m.[用户ID],m.[日期]FROM [Cosmos].[dbo].[Sessions_tbl] 作为 m内连接 [Cosmos].[dbo].[Sessions_tbl] 作为 d在 m.[UserID2] = d.[UserID] AND--Between 由于某种原因在这里不起作用d.[日期] <= m.[日期] ANDd. [日期] >DATEADD(d,-7,m.[日期])按 m.[UserID] 分组,m.[日期]

另一个来自以下链接，它计算给定日期的活跃用户数活动用户 SQL 查询

我使用的是 SQL Server 2012

我无法将两者结合起来.

编辑澄清:我需要的查询可能不会有任何 getdate() 或类似的，因为我需要知道有多少用户符合 1 月 1 日、今天和所有日期之间的活跃"条件.

感谢您的帮助！

解决方案

我认为你只需要添加一个 HAVING 子句:

HAVING COUNT(d.[SessionID]) >= 2

在 10 in 30 查询中，只需将 DATEADD() 更改为 30 天，并将 HAVING 子句更改为 >= 10.

 SELECT COUNT(d.[SessionID]) AS SessionPastPeriod, m.[用户ID], m.[日期]FROM Sessions_tbl AS mINNER JOIN Sessions_tbl as dON m.UserID = d.UserIDAND d.[日期] <= m.[日期]和 d.[日期] >DATEADD(d,-7,m.[日期])按 m.UserID 分组, m.[日期]有 COUNT(d.[SessionID]) >= 2

我希望这会有所帮助.

I am trying to count the number of users who have had at least two sessions within 7 days of OR ten in 30 days of all dates.

My data is as follow:

Date        UserID  SessionID 
1/1/2013    Bob1234    1
2/1/2013    Bob1234    2
2/2/2013    Bob1234    3
2/3/2013    Cal5678    4

Which would result in the following table (only select dates shown)

Date    CountActiveUsers
1/1/2013    1
1/15/2013   0
2/2/2013    1
2/3/2013    2

The real data set has values for all dates in a continuous data range and the results table should have an entry for every date.

SessionIDs are unique and a UserID always refers to the same person.

So far I have two queries that do something close-ish. The first returns the count of sessions in the past week by user:

SELECT Count(
d.[SessionID]
) As SessionPastWeek
      ,m.[UserID]
      ,m.[Date]
  FROM [Cosmos].[dbo].[Sessions_tbl] as m
  Inner Join [Cosmos].[dbo].[Sessions_tbl] as d 
on m.[UserID2] = d.[UserID] AND 
--Between does not work here for some reason
d.[Date] <= m.[Date] AND
d.[Date] > DATEADD(d,-7,m.[date])

  Group By m.[UserID]
      ,m.[Date]

The other is from the following link which count the number of active users in a given date Active Users SQL query

I am in SQL Server 2012

I am having trouble combining the two.

Edit for clarification: the query I need likely won't have any getdate() or similar as I need to know how many users fit the 'active' criteria on Jan 1, today, and all the dates inbetween.

Thanks for any help!

解决方案

I think you just need to add a HAVING clause:

HAVING COUNT(d.[SessionID]) >= 2

On your 10 in 30 query, just change your DATEADD() to have 30 days, and change the HAVING clause to be >= 10.

    SELECT COUNT(d.[SessionID]) AS SessionPastPeriod
        , m.[UserID]
        , m.[Date]
    FROM Sessions_tbl AS m
        INNER JOIN Sessions_tbl as d
            ON m.UserID = d.UserID
            AND d.[Date] <= m.[Date]
            AND d.[Date] > DATEADD(d,-7,m.[Date])
    GROUP BY m.UserID
        , m.[Date]
    HAVING COUNT(d.[SessionID]) >= 2

I hope this helps.

这篇关于SQL:统计过去一周有活动的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！