Question

2984. 找到每座城市的高峰通话时间

English Version

题目描述

表： Calls

+--------------+----------+
| Column Name  | Type   |
+--------------+----------+
| caller_id  | int    |
| recipient_id | int    |
| call_time  | datetime |
| city     | varchar  |
+--------------+----------+
(caller_id, recipient_id, call_time) 是该表的主键(具有唯一值的列)。
每一行包含 caller id, recipient id, call time,和 city。

编写一个查询，找到每个 city 的 高峰 通话 时间。如果 多个时间 有 相同 数量的通话，则所有这些时间都将被视为该特定城市的 高峰时间。

按照 高峰时间 和_ _city_ _按 降序 排序返回结果表。

结果格式如下例所示。

 

示例 1:

输入：
Calls table:
+-----------+--------------+---------------------+----------+
| caller_id | recipient_id | call_time       | city   |
+-----------+--------------+---------------------+----------+
| 8     | 4      | 2021-08-24 22:46:07 | Houston  |
| 4     | 8      | 2021-08-24 22:57:13 | Houston  |  
| 5     | 1      | 2021-08-11 21:28:44 | Houston  |  
| 8     | 3      | 2021-08-17 22:04:15 | Houston  |
| 11    | 3      | 2021-08-17 13:07:00 | New York |
| 8     | 11       | 2021-08-17 14:22:22 | New York |
+-----------+--------------+---------------------+----------+
输出：
+----------+-------------------+-----------------+
| city   | peak_calling_hour | number_of_calls |
+----------+-------------------+-----------------+
| Houston  | 22        | 3         |
| New York | 14        | 1         |
| New York | 13        | 1         |
+----------+-------------------+-----------------+
解释：
对于 Houston：
  - 高峰时间是 22:00，总共记录了 3 次通话。 
对于 New York：
  - 3:00 和 14:00 都有相同数量的通话，因此这两个时间都被视为高峰时间。
输出表按照高峰时间和城市按降序排序。

解法

方法一

# Write your MySQL query statement below
WITH
  T AS (
    SELECT
      *,
      RANK() OVER (
        PARTITION BY city
        ORDER BY cnt DESC
      ) AS rk
    FROM
      (
        SELECT
          city,
          HOUR(call_time) AS h,
          COUNT(1) AS cnt
        FROM Calls
        GROUP BY 1, 2
      ) AS t
  )
SELECT city, h AS peak_calling_hour, cnt AS number_of_calls
FROM T
WHERE rk = 1
ORDER BY 2 DESC, 1 DESC;