我有一个数据库，其中包含一年中不同旅程的历史数据。我需要知道一天中旅行次数的分布（使用Java）。例如：
00:00-01:00: 1, 01:00-02:00: 0, ..., 23:00-00:00: 4
注意：输出应为JSON格式。
我对解决此任务的最佳方法（步骤）感兴趣。目前，我打算通过以下方式解决该问题：
1) Create List<Hashtable<String,Integer>> (Hashtable has 24 keys, each corresponding to one hour interval over the day).
2) Connect to DB and run SQL query in order to download all trips.
3) Run through ResultSet and add 1 to the corresponding day and time slot in List<Hashtable<String,Integer>>
4) Close connection with DB
5) Create Hashtable<String,Integer> with 24 keys, each corresponding to one hour interval over the day.
6) Run over List<Hashtable<String,Integer>>. Calculate an average number of trips per each hourly interval over all days and save results in Hashtable<String,Integer>.
7) Convert Hashtable<String,Integer> to JSON as follows:
样本JSON输出：
{"00:00-01:00": 1, "01:00-02:00": 0, ..., "23:00-00:00": 4}
也许我可以将SQL与AVERAGE一起执行相同的任务？
最佳答案
SQL聚合查询对此非常有用。
该查询为您提供去年数据库中所代表的天数。
SELECT COUNT(DISTINCT(DATE(triptime)))
FROM trip_hour
WHERE YEAR(triptime) = YEAR(NOW()-1)
这样可以为您提供每个小时时段的旅行次数。
SELECT HOUR(a.triptime) AS trip_hour,
COUNT(*) AS trip_count
FROM trip
WHERE YEAR(triptime) = YEAR(NOW()-1)
GROUP BY HOUR(triptime)
ORDER BY HOUR(triptime)
最后，结合上述两个查询，得出每个小时时段每天的平均出行次数。
SELECT HOUR(a.triptime) AS trip_hour,
COUNT(*) AS trip_count,
COUNT(*) / (SELECT COUNT(DISTINCT(DATE(triptime)))
FROM trip_hour
WHERE YEAR(triptime) = YEAR(NOW()-1)) AS trip_avg
FROM trip
WHERE YEAR(triptime) = YEAR(NOW()-1)
GROUP BY HOUR(triptime)
ORDER BY HOUR(triptime)
这样一来，您就可以下载24行结果集，而不用花大量的时间旅行。
可以采用
WHERE，GROUP BY和ORDER BY子句中的所有复杂性。但这应该可以帮助您入门。