查询数据

当您加载数据集后，就可以开始构建一些查询来发现数据告诉您什么。在本节中，您将学习如何编写查询来回答以下问题

每天发生多少次乘车？

此数据集包含 2016 年 1 月的乘车数据。要找出每天发生多少次乘车，您可以使用 SELECT 语句。在本例中，您想要计算每天的总乘车次数，并按日期将其显示在列表中。

查找每天发生多少次乘车

连接到包含 NYC 出租车数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询选择 2016 年 1 月第一周的所有乘车记录，并返回每天的乘车次数计数


SELECT date_trunc('day', pickup_datetime) as day,
COUNT(*) FROM rides
WHERE pickup_datetime < '2016-01-08'
GROUP BY day
ORDER BY day;

查询结果如下所示


day         | count
---------------------+--------
 2016-01-01 00:00:00 | 345037
 2016-01-02 00:00:00 | 312831
 2016-01-03 00:00:00 | 302878
 2016-01-04 00:00:00 | 316171
 2016-01-05 00:00:00 | 343251
 2016-01-06 00:00:00 | 348516
 2016-01-07 00:00:00 | 364894

平均票价是多少？

您可以在 SELECT 查询中包含一个函数，以确定每位乘客支付的平均票价。

查找平均票价金额

连接到包含 NYC 出租车数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询选择 2016 年 1 月第一周的所有乘车记录，并返回每天支付的平均票价


SELECT date_trunc('day', pickup_datetime)
AS day, avg(fare_amount)
FROM rides
WHERE pickup_datetime < '2016-01-08'
GROUP BY day
ORDER BY day;

查询结果如下所示


day         |         avg
---------------------+---------------------
 2016-01-01 00:00:00 | 12.8569325028909943
 2016-01-02 00:00:00 | 12.4344713599355563
 2016-01-03 00:00:00 | 13.0615900461571986
 2016-01-04 00:00:00 | 12.2072927308323660
 2016-01-05 00:00:00 | 12.0018670885154013
 2016-01-06 00:00:00 | 12.0002329017893009
 2016-01-07 00:00:00 | 12.1234180337303436

每种费率类型的乘车次数是多少？

纽约市的出租车对不同类型的行程使用不同的费率类型。例如，往返机场的行程从市内任何地点起价均为统一费率。本节将向您展示如何构建一个查询，显示每种不同票价类型的行程数量。它还使用 JOIN 语句以更易于理解的方式呈现数据。

查找每种票价类型的乘车次数

连接到包含 NYC 出租车数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询选择 2016 年 1 月第一周的所有乘车记录，并返回每种费率代码的总行程次数


SELECT rate_code, COUNT(vendor_id) AS num_trips
FROM rides
WHERE pickup_datetime < '2016-01-08'
GROUP BY rate_code
ORDER BY rate_code;

查询结果如下所示


rate_code | num_trips
-----------+-----------
         1 |   2266401
         2 |     54832
         3 |      4126
         4 |       967
         5 |      7193
         6 |        17
        99 |        42

此输出是正确的，但不太容易阅读，因为您可能不知道不同的费率代码意味着什么。但是，数据集中的 rates 表包含每个代码的人类可读描述。您可以在查询中使用 JOIN 语句连接 rides 和 rates 表，并在结果中呈现来自两者的信息。

显示每种票价类型的乘车次数

连接到包含 NYC 出租车数据集的 Timescale 数据库。

在 psql 提示符下，复制此查询以选择 2016 年 1 月第一周的所有乘车记录，连接 rides 和 rates 表，并返回每种费率代码的总行程次数，以及费率代码的描述


SELECT rates.description, COUNT(vendor_id) AS num_trips
FROM rides
JOIN rates ON rides.rate_code = rates.rate_code
WHERE pickup_datetime < '2016-01-08'
GROUP BY rates.description
ORDER BY LOWER(rates.description);

查询结果如下所示


description      | num_trips
-----------------------+-----------
 group ride            |        17
 JFK                   |     54832
 Nassau or Westchester |       967
 negotiated fare       |      7193
 Newark                |      4126
 standard rate         |   2266401

往返机场的行程是哪种类型的

数据集中有两个主要的机场：约翰·肯尼迪机场（JFK），费率代码为 2；纽瓦克机场（EWR），费率代码为 3。

有关往返这两个机场的行程信息对于城市规划以及纽约市旅游局等组织非常有用。

本节将向您展示如何构建一个查询，返回仅往返新主机场的行程信息。

查找往返机场的行程类型

连接到包含 NYC 出租车数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询选择 2016 年 1 月第一周往返 JFK 和纽瓦克机场的所有乘车记录，并返回往返该机场的行程次数、平均行程时长、平均行程费用和平均乘客人数


SELECT rates.description,
    COUNT(vendor_id) AS num_trips,
    AVG(dropoff_datetime - pickup_datetime) AS avg_trip_duration,
    AVG(total_amount) AS avg_total,
    AVG(passenger_count) AS avg_passengers
FROM rides
JOIN rates ON rides.rate_code = rates.rate_code
WHERE rides.rate_code IN (2,3) AND pickup_datetime < '2016-01-08'
GROUP BY rates.description
ORDER BY rates.description;

查询结果如下所示


description | num_trips | avg_trip_duration |      avg_total      |   avg_passengers
-------------+-----------+-------------------+---------------------+--------------------
 JFK         |     54832 | 00:46:44.614222   | 63.7791311642836300 | 1.8062080536912752
 Newark      |      4126 | 00:34:45.575618   | 84.3841783809985458 | 1.8979641299079011

2016 年元旦发生了多少次乘车？

纽约市以时代广场的新年降球庆祝活动而闻名。成千上万的人聚集在一起迎接新年，然后前往城市各处：去他们最喜欢的酒吧，与朋友聚餐，或返回家中。本节将向您展示如何构建一个查询，返回 2016 年 1 月 1 日每 30 分钟间隔的出租车行程次数。

在 PostgreSQL 中，按 30 分钟的时间间隔分割数据并不容易。为此，您需要使用 TRUNC 函数来计算行程开始时间的分钟数除以 30 的商，然后截断结果以取该商的底。当您得到该结果时，您可以将截断的商乘以 30。

在您的 Timescale 数据库中，您可以使用 time_bucket 函数将数据分割成时间间隔。

查找 2016 年元旦发生了多少次乘车

连接到包含 NYC 出租车数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询选择 2016 年 1 月 1 日的所有乘车记录，并返回每 30 分钟间隔的乘车次数计数


SELECT time_bucket('30 minute', pickup_datetime) AS thirty_min, count(*)
FROM rides
WHERE pickup_datetime < '2016-01-02 00:00'
GROUP BY thirty_min
ORDER BY thirty_min;

查询结果开始如下所示


thirty_min      | count
---------------------+-------
 2016-01-01 00:00:00 | 10920
 2016-01-01 00:30:00 | 14350
 2016-01-01 01:00:00 | 14660
 2016-01-01 01:30:00 | 13851
 2016-01-01 02:00:00 | 13260
 2016-01-01 02:30:00 | 12230
 2016-01-01 03:00:00 | 11362