sql - 在 SQL 中获得频繁使用日

Question

我有一个表，其中包含每个 user_id 的旅行日期。我day of week从日期派生了一个列。

桌子

+------+----------------+-----------+
| user | Date of Travel | DayofWeek |
+------+----------------+-----------+
|    1 | 2019-07-22     | Monday    |
|    1 | 2019-07-22     | Monday    |
|    1 | 2019-07-29     | Monday    |
|    1 | 2019-08-05     | Monday    |
|    1 | 2019-08-06     | Tuesday   |
|    2 | 2019-07-24     | Wednesday |
|    2 | 2019-07-24     | Wednesday |
|    2 | 2019-07-31     | Wednesday |
|    2 | 2019-08-07     | Wednesday |
|    2 | 2019-08-06     | Tuesday   |
+------+----------------+-----------+

对于每个用户，我想要他最频繁的旅行日。所需的输出如下

+------+---------------------+
| user | Frequent_Travel_Day |
+------+---------------------+
|    1 | Monday              |
|    2 | Wednesday           |
+------+---------------------+

任何帮助将不胜感激

标签： sqlhivehiveql

Answer 1

你可以使用：

SELECT TOP 1 WITH TIES [user], DayOfWeek AS Frequent_Travel_Day
FROM tab
GROUP BY [user], DayOfWeek
ORDER BY ROW_NUMBER() OVER(PARTITION BY [user] ORDER BY COUNT(*) DESC);

db<>小提琴演示

输出：

user    DayOfWeek
1       Monday
2       Wednesday