高级教程
期望从本教程中获得什么？
在本教程中，您将创建一个表并插入大型数据集（纽约出租车数据的两百万行）。然后，您将在数据集上运行查询，包括如何创建字典并使用它来执行JOIN的示例。
本教程假设您可以访问运行中的ClickHouse服务。如果没有，请查看快速入门。
1. 创建一个新表
纽约市出租车数据包含数百万次出租车乘车的详细信息，例如上车和下车时间和地点、费用、小费金额、过路费、付款方式等。让我们创建一个表来存储这些数据...
连接到SQL控制台
SQL控制台如果您需要SQL客户端连接，您的ClickHouse Cloud服务有一个关联的基于Web的SQL控制台；展开下面的连接到SQL控制台以获取详细信息。
连接到SQL控制台
从您的ClickHouse Cloud服务列表中，选择要使用的服务，然后单击连接。 从这里您可以打开SQL控制台：
如果您使用自管理的ClickHouse，可以在https://hostname:8443/play上连接到SQL控制台（请与您的ClickHouse管理员核实详细信息）。
在
default数据库中创建以下trips表：CREATE TABLE trips
(
`trip_id` UInt32,
`vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
`pickup_date` Date,
`pickup_datetime` DateTime,
`dropoff_date` Date,
`dropoff_datetime` DateTime,
`store_and_fwd_flag` UInt8,
`rate_code_id` UInt8,
`pickup_longitude` Float64,
`pickup_latitude` Float64,
`dropoff_longitude` Float64,
`dropoff_latitude` Float64,
`passenger_count` UInt8,
`trip_distance` Float64,
`fare_amount` Float32,
`extra` Float32,
`mta_tax` Float32,
`tip_amount` Float32,
`tolls_amount` Float32,
`ehail_fee` Float32,
`improvement_surcharge` Float32,
`total_amount` Float32,
`payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
`trip_type` UInt8,
`pickup` FixedString(25),
`dropoff` FixedString(25),
`cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
`pickup_nyct2010_gid` Int8,
`pickup_ctlabel` Float32,
`pickup_borocode` Int8,
`pickup_ct2010` String,
`pickup_boroct2010` String,
`pickup_cdeligibil` String,
`pickup_ntacode` FixedString(4),
`pickup_ntaname` String,
`pickup_puma` UInt16,
`dropoff_nyct2010_gid` UInt8,
`dropoff_ctlabel` Float32,
`dropoff_borocode` UInt8,
`dropoff_ct2010` String,
`dropoff_boroct2010` String,
`dropoff_cdeligibil` String,
`dropoff_ntacode` FixedString(4),
`dropoff_ntaname` String,
`dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;
2. 插入数据集
现在您已经创建了一个表，让我们从那里加载纽约出租车数据。它在S3中的CSV文件中，您可以从那里加载数据。
以下命令从S3中的两个不同文件
trips_1.tsv.gz和trips_2.tsv.gz中向您的trips表中插入~2,000,000行：INSERT INTO trips
SELECT * FROM s3(
'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
'TabSeparatedWithNames', "
`trip_id` UInt32,
`vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
`pickup_date` Date,
`pickup_datetime` DateTime,
`dropoff_date` Date,
`dropoff_datetime` DateTime,
`store_and_fwd_flag` UInt8,
`rate_code_id` UInt8,
`pickup_longitude` Float64,
`pickup_latitude` Float64,
`dropoff_longitude` Float64,
`dropoff_latitude` Float64,
`passenger_count` UInt8,
`trip_distance` Float64,
`fare_amount` Float32,
`extra` Float32,
`mta_tax` Float32,
`tip_amount` Float32,
`tolls_amount` Float32,
`ehail_fee` Float32,
`improvement_surcharge` Float32,
`total_amount` Float32,
`payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
`trip_type` UInt8,
`pickup` FixedString(25),
`dropoff` FixedString(25),
`cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
`pickup_nyct2010_gid` Int8,
`pickup_ctlabel` Float32,
`pickup_borocode` Int8,
`pickup_ct2010` String,
`pickup_boroct2010` String,
`pickup_cdeligibil` String,
`pickup_ntacode` FixedString(4),
`pickup_ntaname` String,
`pickup_puma` UInt16,
`dropoff_nyct2010_gid` UInt8,
`dropoff_ctlabel` Float32,
`dropoff_borocode` UInt8,
`dropoff_ct2010` String,
`dropoff_boroct2010` String,
`dropoff_cdeligibil` String,
`dropoff_ntacode` FixedString(4),
`dropoff_ntaname` String,
`dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0等待
INSERT完成 - 可能需要一段时间才能下载150 MB的数据。:::note
`s3`函数聪明地知道如何解压数据，`TabSeparatedWithNames`格式告诉ClickHouse数据是以制表符分隔的，并且还要跳过每个文件的标题行。
:::插入完成后，验证它是否成功：
SELECT count() FROM trips您应该看到大约2M行（确切地说是1,999,657行）。
note注意ClickHouse需要处理的行数很少，用来确定计数的时间有多快？您可以在0.001秒内获得计数，只处理了6行。（6恰好是
trips表当前拥有的part的数量，part知道它们有多少行。）如果运行需要访问每一行的查询，您会注意到需要处理的行数要多得多，但运行时间仍然非常快：
SELECT DISTINCT(pickup_ntaname) FROM trips此查询必须处理2M行并返回190个值，但请注意它在大约1秒内完成。
pickup_ntaname列表示纽约市出租车乘车的起始地点的名称。
3. 分析数据
让我们运行一些查询来分析200万行的数据...
我们将从一些简单的计算开始，例如计算平均小费金额：
SELECT round(avg(tip_amount), 2) FROM trips响应是：
┌─round(avg(tip_amount), 2)─┐
│ 1.68 │
└───────────────────────────┘此查询根据乘客数量计算平均费用：
SELECT
passenger_count,
ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_countpassenger_count范围从0到9：┌─passenger_count─┬─average_total_amount─┐
│ 0 │ 22.69 │
│ 1 │ 15.97 │
│ 2 │ 17.15 │
│ 3 │ 16.76 │
│ 4 │ 17.33 │
│ 5 │ 16.35 │
│ 6 │ 16.04 │
│ 7 │ 59.8 │
│ 8 │ 36.41 │
│ 9 │ 9.81 │
└─────────────────┴──────────────────────┘以下是一个计算每个街区每天乘车次数的查询：
SELECT
pickup_date,
pickup_ntaname,
SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC结果如下：
┌─pickup_date─┬─pickup_ntaname───────────────────────────────────────────┬─number_of_trips─┐
│ 2015-07-01 │ Brooklyn Heights-Cobble Hill │ 13 │
│ 2015-07-01 │ Old Astoria │ 5 │
│ 2015-07-01 │ Flushing │ 1 │
│ 2015-07-01 │ Yorkville │ 378 │
│ 2015-07-01 │ Gramercy │ 344 │
│ 2015-07-01 │ Fordham South │ 2 │
│ 2015-07-01 │ SoHo-TriBeCa-Civic Center-Little Italy │ 621 │
│ 2015-07-01 │ Park Slope-Gowanus │ 29 │
│ 2015-07-01 │ Bushwick South │ 5 │此查询计算行程的长度，并根据该值对结果进行分组：
SELECT
avg(tip_amount) AS avg_tip,
avg(fare_amount) AS avg_fare,
avg(passenger_count) AS avg_passenger,
count() AS count,
truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
FROM trips
WHERE trip_minutes > 0
GROUP BY trip_minutes
ORDER BY trip_minutes DESC结果如下：
┌──────────────avg_tip─┬───────────avg_fare─┬──────avg_passenger─┬──count─┬─trip_minutes─┐
│ 1.9600000381469727 │ 8 │ 1 │ 1 │ 27511 │
│ 0 │ 12 │ 2 │ 1 │ 27500 │
│ 0.542166673981895 │ 19.716666666666665 │ 1.9166666666666667 │ 60 │ 1439 │
│ 0.902499997522682 │ 11.270625001192093 │ 1.95625 │ 160 │ 1438 │
│ 0.9715789457909146 │ 13.646616541353383 │ 2.0526315789473686 │ 133 │ 1437 │
│ 0.9682692398245518 │ 14.134615384615385 │ 2.076923076923077 │ 104 │ 1436 │
│ 1.1022105210705808 │ 13.778947368421052 │ 2.042105263157895 │ 95 │ 1435 │
4. 创建字典
如果您是ClickHouse的新手，了解字典的工作原理非常重要。简单地说，字典是存储在内存中的键值对的映射。字典的详细信息和所有选项在教程的末尾链接。
让我们看看如何在您的ClickHouse服务中创建与表相关联的字典。表和因此字典将基于一个包含265行的CSV文件，每个行对应纽约市的一个街区。这些街区映射到纽约市的五个行政区（纽约市有5个行政区：布朗克斯、布鲁克林、曼哈顿、皇后区和斯塔滕岛），并且此文件还将纽瓦克机场（EWR）视为一个行政区。
这是CSV文件的一部分（为了清晰起见，显示为表）。文件中的
LocationID列映射到您的trips表中的pickup_nyct2010_gid和dropoff_nyct2010_gid列：LocationID Borough Zone service_zone 1 EWR Newark Airport EWR 2 Queens Jamaica Bay Boro Zone 3 Bronx Allerton/Pelham Gardens Boro Zone 4 Manhattan Alphabet City Yellow Zone 5 Staten Island Arden Heights Boro Zone 文件的URL是
https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv。运行以下SQL，它创建一个名为taxi_zone_dictionary的字典，并从S3中的CSV文件中填充字典：验证它是否有效-您应该获得265行（每个街区一行）：
SELECT * FROM taxi_zone_dictionary使用
dictGet函数（或其变体）从字典中检索值。您传递字典的名称，您想要的值以及键（在我们的示例中是taxi_zone_dictionary的LocationID列）。使用
dictHas函数查看字典中是否存在键。例如，以下查询返回1（在ClickHouse中为“true”）：SELECT dictHas('taxi_zone_dictionary', 132)以下查询返回0，因为4567不是字典中
LocationID的值：SELECT dictHas('taxi_zone_dictionary', 4567)在查询中使用
dictGet函数检索字典中的街区名称。例如：SELECT
count(1) AS total,
dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
FROM trips
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY borough_name
ORDER BY total DESC此查询对每个街区的出租车乘车次数进行求和，这些出租车乘车次数要么在LaGuardia机场，要么在JFK机场结束。结果如下，注意有很多乘车次数，其中下车街区是未知的：
┌─total─┬─borough_name──┐
│ 23683 │ Unknown │
│ 7053 │ Manhattan │
│ 6828 │ Brooklyn │
│ 4458 │ Queens │
│ 2670 │ Bronx │
│ 554 │ Staten Island │
│ 53 │ EWR │
└───────┴───────────────┘
7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)
5. 执行连接
让我们编写一些查询，将taxi_zone_dictionary与您的trips表连接起来。
我们可以从一个简单的JOIN开始，它的作用类似于上面的机场查询：
SELECT
count(1) AS total,
Borough
FROM trips
JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY Borough
ORDER BY total DESC响应看起来很熟悉：
┌─total─┬─Borough───────┐
│ 7053 │ Manhattan │
│ 6828 │ Brooklyn │
│ 4458 │ Queens │
│ 2670 │ Bronx │
│ 554 │ Staten Island │
│ 53 │ EWR │
└───────┴───────────────┘
6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)note请注意，上面的
JOIN查询的输出与之前使用dictGetOrDefault的查询相同（除了不包括Unknown值）。在幕后，ClickHouse实际上正在为taxi_zone_dictionary字典调用dictGet函数，但是JOIN语法对于SQL开发人员来说更为熟悉。在ClickHouse中，我们不经常使用
SELECT *-您应该只检索实际需要的列！但是很难找到一个需要很长时间的查询，因此这个查询故意选择了每一列并返回了每一行（除了默认情况下响应中内置的10000行最大值），并且还对每一行与字典进行了右连接：SELECT *
FROM trips
JOIN taxi_zone_dictionary
ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
WHERE tip_amount > 0
ORDER BY tip_amount DESC
LIMIT 1000note请注意，这个查询返回了1000行，但是它只处理了1000行。这是因为ClickHouse默认情况下只返回1000行，但是它仍然处理了200万行。
祝贺！
做得好-您已经完成了教程，希望您对如何使用ClickHouse有了更好的理解。以下是下一步该做什么的一些建议：
- 阅读ClickHouse中主键的工作原理-这个知识将使您在成为ClickHouse专家的道路上走得更远
- 集成外部数据源如文件、Kafka、PostgreSQL、数据管道或许多其他数据源
- 连接您喜欢的UI/BI工具到ClickHouse
- 查看SQL参考并浏览各种函数。ClickHouse有一个令人惊叹的函数集，用于转换、处理和分析数据
- 了解更多关于字典的知识