CODEWITHSUNDEEP
sqlsql-server-2005selectgroup-by
Jesper
Jesper

Reputation: 999

SQL Server query, most frequent in hour

I have a table where I insert page loads. The interesting fields are entrytime and country.

How do I query, so that I can get the most frequent country by hour. The result set should look like this (Hour, Country):

... and so on.

I was getting started with something like

select DatePart(hour, entrytime) AS h, country from rpageload group by DatePart(hour, entrytime), country

but I think I am going in the wrong direction. ORDER BY and COUNT(*) needs to fit in somewhere :)

-- EDITED / ADDED --

I have found out that this sort-of gives me the right results. But I obviously also get many result sets, and not just one, like I'd like.

DECLARE @count INT
SET @count = 0
WHILE (@count < 24)
BEGIN
    SELECT TOP 1 @count AS hr, COUNT(*) AS nbr, country FROM rpageload WHERE DATEPART(hour, entrytime) = @count GROUP BY country ORDER BY nbr DESC
    SET @count = (@count + 1)
END

Just to clarify: The day or date doesn't matter. The purpose is to show which country is most frequent in the different hours of the day

Upvotes: 1

Views: 426

Answers (1)

Chris Bednarski
Chris Bednarski

Reputation: 3424

You're almost there!

SELECT DatePart(hour, entrytime) AS h, country
FROM rpageload 
GROUP BY DatePart(hour, entrytime), country
ORDER BY COUNT(*) DESC

However, this will combine entries from every single day per hour So between 1-2pm yesterday will be counted together with 1-2pm today

If this is undesired, you need to filter out other days in a WHERE clause. Alternatively, group by days on top of the hours

Edit:
Actually, I didn't read the question properly.

SELECT DatePart(hour, entrytime) AS h, country
FROM rpageload rp1
GROUP BY DatePart(hour, entrytime), country
HAVING COUNT(*) = 
 (SELECT MAX(COUNT(*))
  FROM rpageload rp2
  WHERE DatePart(rp2.hour, rp2.entrytime) = DatePart(rp1.hour, rp1.entrytime)
  GROUP BY DatePart(hour, entrytime), country
 )
ORDER BY h

What should happen when two or more countries have the same count and it's the maximum for that hour?

Alternatively,

WITH RPL1 AS
(
  SELECT DatePart(hour, entrytime) AS h, country, COUNT(*) AS cnt
  FROM rpageload rp1
  GROUP BY DatePart(hour, entrytime), country
),
RPL2 AS
(
  SELECT h, MAX(cnt) as maxcnt
  FROM RPL1
  GROUP BY h
)
SELECT RPL1.h, country
FROM RPL1
JOIN RPL2 ON RPL1.h = RPL2.h AND RPL1.cnt = RPL2.maxcnt

The last two queries will return all rows matching the same maximum frequency. SO, the same hour may appear more than once in the resultset.

If you want to filter those out, look at ROW_NUMBER

WITH RPL1 AS
(
  SELECT DatePart(hour, entrytime) AS h, country, COUNT(*) AS cnt
  FROM rpageload rp1
  GROUP BY DatePart(hour, entrytime), country
),
RPL2 AS
(
  SELECT h, MAX(cnt) as maxcnt
  FROM RPL1
  GROUP BY h
),
DUPES AS
(
  SELECT RPL1.h, country, cnt, ROW_NUMBER() OVER(PARTITION BY RPL1.h ORDER BY country) AS rn
  FROM RPL1
  JOIN RPL2 ON RPL1.h = RPL2.h AND RPL1.cnt = RPL2.maxcnt
)
SELECT h, country, cnt
FROM DUPES
WHERE rn = 1

Upvotes: 2

Related Questions