sqlite - 一天中各组的前 3 名 - SQL Lite
问题描述
我目前正在学习如何使用 SQL Lite，并希望按小时对最受欢迎的 3 个取货地点进行排序。我有数百万行数据，其中感兴趣的列是 lpep_pickup_datetime（取件时间）和 POLocationID（取件位置）。
我想按小时排名前 3 位最受欢迎的接送地点。
以下是数据示例：
+----------------------+--------------+-----------------+
| lpep_pickup_datetime | PULocationID | passenger_count |
+----------------------+--------------+-----------------+
| 1/1/2017 0:01 | 42 | 1 |
| 1/1/2017 0:03 | 75 | 1 |
| 1/1/2017 0:04 | 82 | 5 |
| 1/1/2017 0:01 | 255 | 1 |
| 1/1/2017 0:00 | 166 | 1 |
| 1/1/2017 0:00 | 179 | 1 |
| 1/1/2017 0:02 | 74 | 1 |
| 1/1/2017 0:15 | 112 | 1 |
| 1/1/2017 0:06 | 36 | 1 |
| 1/1/2017 0:14 | 127 | 5 |
| 1/1/2017 0:01 | 41 | 1 |
| 1/1/2017 0:31 | 97 | 1 |
| 1/1/2017 0:01 | 255 | 5 |
| 1/1/2017 0:00 | 70 | 1 |
| 1/1/2017 0:03 | 255 | 1 |
| 1/1/2017 0:03 | 82 | 1 |
| 1/1/2017 0:00 | 36 | 1 |
| 1/1/2017 0:01 | 7 | 1 |
+----------------------+--------------+-----------------+
在 SQLLiteStudio 3.2.1 上尝试这个 - 我可能只需要使用完整的 MySQL 套件就可以使用正确的功能吗？
SELECT
PULocationID, count(PULocationID)
FROM GreenCabs2017
GROUP BY PULocationID
ORDER BY count(PULocationID) DESC
LIMIT 3
我尝试过的查询只返回整个数据集中的前 3 个取货地点，而不是按一天中的小时 - 我如何能够按一天中的小时分组？StackExchange 上的其他解决方案参考了 date_time 和 date_format 函数，当我在 SQL Lite 上尝试它们时不会执行 - 什么是可以在 SQL Lite 上工作的查询？
理想情况下会有如下内容：
+-------------+--------------+-----------------+
| Time of Day | PULocationID | PULocationCount |
+-------------+--------------+-----------------+
| 0:00 | 74 | 677 |
| 0:00 | 65 | 333 |
| 0:00 | 55 | 220 |
+-------------+--------------+-----------------+
这将是从午夜到凌晨 1:00 的前 3 个取货地点的输出。这个时间范围必须适用于所有日期，即 1/1 到 1/31，而不仅仅是像我提供的示例那样的 1/1。
更新：将时间戳的格式更改为 YYYY-MM-DD HH:MM:SS 格式，因此我现在可以使用 datetime 函数。
能够运行我认为可能使我更接近我正在寻找的查询的查询：
SELECT lpep_pickup_datetime, PULocationID, count(PULocationID)
FROM GreenCabs2017
WHERE STRFTIME('%Y', lpep_pickup_datetime) = '2017' AND
STRFTIME('%H', lpep_pickup_datetime) <= '01' AND
STRFTIME('%H', lpep_pickup_datetime) >= '00'
GROUP BY PULocationID
ORDER BY count(PULocationID) DESC
LIMIT 3
这给出了一个输出
+----------------------+--------------+---------------------+
| lpep_pickup_datetime | PULocationID | count(PULocationID) |
+----------------------+--------------+---------------------+
| 1/31/2017 1:13 | 255 | 7845 |
| 1/31/2017 1:04 | 7 | 4596 |
| 1/31/2017 1:07 | 82 | 3892 |
+----------------------+--------------+---------------------+
但是 lpep_pickup_datetime 列仍然表明这将在凌晨 1:00 到凌晨 2:00 之间，而不是在凌晨 12:00 到凌晨 1:00 之间？删除查询中的“=”符号不会返回任何结果。而且我不想在一天中的每个小时都这样做 - 有没有办法通过一个查询按小时输出？
解决方案
您的数据使用的时间戳字符串格式m/d/YYYY H:MM, 不是很好。它不能被 sqlite日期和时间函数使用，不能被有意义地排序以进行排序，而且通常在 sqlite 中很难使用。请记住，sqlite没有专用的日期或时间类型，只有字符串或数字，因此您使用的格式必须遵守这些类型的规则。因此，您的第一步是通过任何方式修复这些时间戳。以下假设您将它们更改为YYYY-mm-dd HH:MM字符串，如2017-01-01 00:01，或其他兼容格式。它还假设您使用的是相当新的 sqlite 版本，因为它使用了 3.25 中添加的窗口函数。
（编辑：您似乎正在使用来自此处的纽约出租车数据，它已经具有格式良好的时间戳，并且适合轻松导入 sqlite。这使得修复变得微不足道。）
鉴于这一切，这个查询：
WITH ranked AS
(SELECT hour, PULocationID, pickups
, row_number() OVER (PARTITION BY hour ORDER BY pickups DESC) AS rn
FROM (SELECT strftime('%H:00', lpep_pickup_datetime) AS hour
, PULocationID
, count(*) AS pickups
FROM GreenCabs2017
GROUP BY strftime('%H:00', lpep_pickup_datetime), PULocationID))
SELECT * FROM ranked
WHERE rn <= 3
ORDER BY hour, rn
将提供 2017 年 1 月的纽约市绿色出租车数据
hour PULocationID pickups rn
---------- ------------ ---------- ----------
00:00 255 4224 1
00:00 7 2518 2
00:00 82 2135 3
01:00 255 3621 1
01:00 7 2078 2
01:00 256 1870 3
02:00 255 3261 1
02:00 256 1798 2
02:00 7 1676 3
03:00 255 2854 1
03:00 256 1589 2
03:00 7 1475 3
等等。
基本上，它计算每个位置每小时出现的次数，并且对于每个小时，根据按该编号排序的每个位置分配一个行号。然后在最终的外部选择中只返回每小时的前三行。您也可以使用rank()ordense_rank()代替row_number()，如果出现平局，它可能会每小时返回超过 3 行，但在这些情况下也更准确地反映了最受欢迎的位置。
（这个查询受益于在 group by 表达式上有一个索引：
CREATE INDEX greencabs2017_idx_hour_loc ON GreenCabs2017(strftime('%H:00', lpep_pickup_datetime), PULocationID);
)
通过 sqlite3 shell 创建的测试表：
sqlite> .mode csv
sqlite> .import '|curl -s https://s3.amazonaws.com/nyctlc/trip+data/green_tripdata_2017-01.csv | sed 2d' GreenCabs2017