CODEWITHSUNDEEP
sqlsql-serverselectcount
Firyn
Firyn

Reputation: 312

Count of Distinct Value Within a Time Range

I have a table like this

Time   |  user_id
12100  |    23
12100  |    23
12100  |    22
12100  |    19
12100  |    20
...
12160  |    273
12160  |    223
12160  |    1223
...

Time is in seconds. I'm looking for a way to get the count of unique user_id within a minute and have the result table looks like this

Minute  |  Count
  1     |  36
  2     |  100
...

Assuming it starts from 12100, so minute 1 is from 12100 ~ 12159, minute 2 is from 12160 to 12219.

Help is much appreciated.

Update: (This is what I have tried, but seems to include duplicates in count)

SELECT ROW_NUMBER() OVER (ORDER BY [Time]) AS [Row]
        , [Time]
        , COUNT(DISTINCT([user_id])) as [Count]
    INTO [table].[dbo].[temp]
  FROM [db].[dbo].[table]
  GROUP BY [Time]

  SELECT t.[Minute], SUM(t.[Count]) AS [Count]
    FROM
    (SELECT (ROW_NUMBER() OVER (ORDER BY [Row]) + 59)/60 AS [Minute]
            , SUM([Count]) AS [Count]
        FROM [db].[dbo].[temp]
        GROUP BY [Row]) AS t
    GROUP BY t.[Minute]
    ORDER BY t.[Minute]

DROP TABLE [db].[dbo].[temp]

Upvotes: 1

Views: 1145

Answers (1)

Bohemian
Bohemian

Reputation: 424983

Create an expression that turns the time into your target minute numbers and group by that:

select
    ((Time - 12000) / 60) + 1 as Minute,
    count(distinct user_id) as Count
from ... 
group by ((Time - 12000) / 60) + 1

Upvotes: 3

Related Questions