SQL Server 查询，最频繁的时间

Question

我有一个表，用于插入页面加载。有趣的字段是进入时间和国家/地区。

我如何查询，以便我可以按小时获取最频繁的国家/地区。结果集应如下所示（时间，国家/地区）：

• 01 - 美国
• 02 - 新加坡
• 03 - 印度
• 04 - 丹麦

... 等等。

我开始做类似的事情

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

，但我认为我走错了方向。 ORDER BY 和 COUNT(*) 需要适合某个地方:)

- 编辑/添加 -

我发现这种方式给了我正确的结果。但显然我也得到了很多结果集，而不仅仅是一个，就像我想要的那样。

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

只是为了澄清：日期或日期并不重要。目的是显示一天中不同时间哪个国家最常出现

Answer 1

你快到了！

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

但是，这将合并每天每小时的条目
因此，昨天下午 1-2 点之间将与今天下午 1-2 点一起计算。

如果不希望出现这种情况，则需要在 WHERE 子句中过滤掉其他日期。
或者，在小时之上按天分组

编辑：
事实上，我没有正确阅读问题。

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

当两个或多个国家/地区的计数相同且为该小时的最大值时，会发生什么情况？

或者，

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

最后两个查询将返回与相同最大频率匹配的所有行。因此，同一小时可能会在结果集中出现多次。

如果您想过滤掉这些内容，请查看 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