ClickHouse 八月 2022 年通讯：航空公司也许没有那么糟糕

Christoph Wurm

2022 年 8 月 11 日

您是通过电子邮件中的“在浏览器中查看”链接找到此页面的吗？好吧，这很遗憾。您可以点击链接直接找到12 月通讯。

在 ClickHouse，我们非常高兴线下聚会终于回归了。的确，有时它们会恰逢 38 摄氏度（100 华氏度）的高温——就像我们上个月在伦敦举办的聚会一样。但是，这就是为什么它们在晚上举行，而且还有披萨的原因。如果您方便的话，请加入附近的ClickHouse 聚会小组！

我们还有一个新的网页！它比旧版网页更漂亮，请查看：https://clickhouse.ac.cn/

继续阅读，了解我们新的基准测试，让我们让您了解 ClickHouse 22.7 的新功能，了解航空公司是否真的像人们说的那样糟糕，并为您的阅读清单添加一些夏季读物。

顺便说一句，如果您是在我们的网站上阅读本文，您是否知道您也可以在收件箱中接收每个月的通讯电子邮件？请在此处注册。

即将举行的活动

ClickHouse v22.8 版本发布网络研讨会

何时？8 月 18 日星期四上午 9 点 PST / 下午 6 点 CEST
如何参加？请在此处注册。

ClickBench - 分析型数据库基准测试 您知道 ClickHouse 对性能非常重视吗？在我们的变更日志中，每个版本都有一个完整的“性能改进”部分，而且通常是一个非常长的部分。这就是为什么如此多家公司将 ClickHouse 用于需要对大量数据进行快速查询的用例。这也是为什么我们的 T 恤背面印有此文本的原因

12 rows in set.
Elapsed: 0.920 sec.
Processed: 107.43 billion rows.
214.86 GB (116.80 billion rows/s, 233.61 GB/s)

为了持续跟踪 ClickHouse 的性能并将其与其他系统进行比较，我们推出了 ClickBench，这是我们的分析型数据库基准测试。数据集基于真实的网络分析工作负载，ClickHouse 通常用于此类工作负载。许多其他用例具有类似的数据和类似的查询，例如网络流量、结构化日志和事件数据。

请此处了解背景和方法，并在https://benchmark.clickhouse.com/查看当前结果。

顺便说一句，您是否知道解析 JSON 的最快方法不是解析 JSON？

ClickHouse v22.7

像往常一样，我们每月的版本都充满了新功能

位置参数现在默认启用，因此您可以使用 SELECT ... ORDER BY 1, 2。但正如我们自己发现的那样，旧习惯很难改！
弃用 Ordinary 只有长期用户才知道这一点：Ordinary 数据库引擎和旧的 *MergeTree 语法现已弃用。如果您有任何在无数次升级中幸存下来的旧数据库，现在是时候更改它们了。
窗口函数中的表达式 现已得到支持。
不是一个，而是两个新的联接算法 - direct 和 full_sorting_merge，试试看！更多内容即将推出。
MongoDB 表函数 用于从 ClickHouse 查询 MongoDB。更好的是，从 MongoDB 读取数据并将其写入 ClickHouse 以获得更好的查询性能！尤其是在 ClickHouse 中，聚合应该会快得多。
附加过滤器 用于在从表中读取数据 (additional_table_filters) 或在结果集中 (additional_result_filter) 时透明地应用过滤条件。
简单图表 内置的 Play UI（例如 https://127.0.0.1:8123/play）现在可以绘制一些基本图表。
Go 的 HTTP 支持 官方的 clickhouse-go 驱动程序现在支持 HTTP。
ClickHouse + Superset 我们有一个新的 Superset 连接器，clickhouse-connect。请尝试一下并报告任何问题。

请查看版本发布网络研讨会的幻灯片、录制，并请升级（除非您想停留在 LTS 版本上）。

本月查询：航空公司也许没有那么糟糕

人们普遍认为航空公司很糟糕，而且只会变得更糟。从行李到机上水的各种额外费用，每年座位空间都越来越小——以及航班时刻表安排得太紧，导致延误不断。前两个肯定是事实，但第三个呢？我们可以用 ClickHouse 来调查！

ClickHouse 文档中的一个示例数据集是OnTime。它包含超过 2 亿条美国商业航班记录，可追溯到 1987 年。每条记录都包含有关航班的信息，包括航班日期和时间、出发和到达机场、航班号等等。对我们来说重要的是，它包含了一些字段，显示航班与计划时间的延迟情况（如果有）。

我们想检查一下航班时刻表是否安排得太紧，导致许多飞机陷入延误，并且延误在一天中会越来越严重。为此，我们需要隔离单个飞机。幸运的是，数据集包含尾号，该尾号可以唯一标识一架特定的飞机。让我们尝试一些查询来了解数据

-- Total number of unique aircraft in the dataset
SELECT count(DISTINCT Tail_Number) FROM ontime

-- Number of unique tail numbers by year
SELECT Year, count(DISTINCT Tail_Number) count FROM ontime GROUP BY Year
ORDER BY count DESC

第一个查询显示，数据集中有 18,531 个不同的尾号。请注意，实际飞机的总数会有所不同——飞机可以并且确实会更改尾号，例如，在更换承运商或注册国家时。这也可能是第二个查询显示 2002 年的唯一尾号历史最高值为 7,768 的原因，远高于 2019 年的第二高值 5,892。2002 年对航空业来说是特别糟糕的一年，许多飞机可能更换了所有者。

第二个查询还显示，在 1995 年之前，数据集不包含任何尾号。因此，我们应该将查询限制在包含尾号的行上。我们可能会认为 WHERE Tail_Number != '' 可以工作，但它不能

SELECT DISTINCT Tail_Number FROM ontime WHERE Tail_Number != '' ORDER BY Tail_Number
-- Top results: #N/A, '144DA, (null), -N037M

SELECT DISTINCT Tail_Number FROM ontime WHERE Tail_Number != '' ORDER BY Tail_Number DESC
-- Top results: �NKNO�, XXXXXX, Unknow, UNKNOW

许多值看起来不像有效的尾号！与其过滤掉我们不需要的内容，不如只过滤掉我们想要的内容，即有效的尾号。幸运的是，尾号具有统一的结构：表示注册国家的字母、一些数字以及更多字母，通常表示航空公司。末尾的字母是可选的。因此，我们可以像这样选择仅包含有效尾号的行

SELECT DISTINCT Tail_Number FROM ontime WHERE match(Tail_Number,
'^[A-Z]+[0-9]+[A-Z0-9]*$') ORDER BY Tail_Number
-- Top results: A367NW, D942DN, M67153, etc. - all valid

我们在迄今为止运行的简单查询在使用新数据集学习其内容和限制时非常重要——没有哪个数据集是完美的，越早发现问题越好。

在您继续阅读最后一步之前，请查看sequenceMatch函数的文档此处。这是一个巧妙的函数，允许您检查有序的行集是否包含与一系列条件匹配的行。它通常用于识别用户行为，例如，您可以使用它来识别所有在线商店的用户，他们首先搜索特定单词或短语，然后继续查看产品详情页，将其放入购物车并购买。您可以检查主序列的子序列以查看哪个步骤的流失率最大。

sequenceMatch是目前 ClickHouse 内置的 1261 个函数之一 (SELECT count() FROM system.functions)。有些是众所周知的 SQL 函数（count、sum、avg 等），但许多是 ClickHouse 特有的，我敢打赌您并不了解所有这些函数！我花了六个多月才注意到sequenceMatch。有时浏览一下函数列表，您很有可能会学到新东西。

回到我们的数据集。我们可以使用sequenceMatch来检查飞机延误在一天中是如何演变的——它们是否趋于恶化或改善？在第一个查询中，我们检查有多少航班延误超过 60 分钟随后又延误超过 90 分钟

WITH (
	DepDelayMinutes > 60 AS delay,
	DepDelayMinutes > 90 AS longer_delay
)
SELECT sum(c) FROM (
	SELECT count() c FROM ontime
	WHERE match(Tail_Number, '^[A-Z]+[0-9]+[A-Z0-9]*$')
	GROUP BY FlightDate, Tail_Number
	HAVING sequenceMatch('(?1)(?2)')(toDateTime(DepTime), delay, longer_delay)
)

结果：6,884,070。延误时间缩短到不到 30 分钟的情况呢？

WITH (
   DepDelayMinutes > 60 AS delay,
   DepDelayMinutes < 30 AS shorter_delay
)
SELECT sum(c) from (
   SELECT count() c FROM ontime
   WHERE match(Tail_Number, '^[A-Z]+[0-9]+[A-Z0-9]*$')
   GROUP BY FlightDate, Tail_Number
   HAVING sequenceMatch('(?1)(?2)')(toDateTime(DepTime), delay, shorter_delay)
)

此处的结果：7,264,056。看来确实有很多飞机在一天中赶上了他们的行程。也许航空业并非我们想象的那么糟糕。当然，近年来延误情况有所恶化，如今的平均延误时间大约是 90 年代的两倍。

 SELECT Year, avg(DepDelayMinutes) FROM ontime GROUP BY Year ORDER BY Year DESC

今天就到这里吧。下次您在机场被困时，启动 ClickHouse 并开始探索吧！

阅读角

我们一直在阅读的内容

DeepL 与 ClickHouse 的旅程 DeepL 引入了 ClickHouse、Kafka 和 Metabase，以转向数据驱动的世界最佳机器翻译器开发。
NANO Corp：从实验到生产，通往 Supercolumn 的旅程阅读 NANO Corp 如何在对所有其他替代方案感到沮丧后偶然发现 ClickHouse，并因其完美契合其实时监控千兆位网络用例而坚持使用它。
快速、功能丰富且可变：ClickHouse 为 Darwinium 的安全和欺诈分析用例提供支持数字风险平台 Darwinium 选择 ClickHouse 以实现高吞吐量和低延迟写入包含数千列的宽表。
ClickHouse 和开源现代数据栈区块链分析提供商 Luabase 选择 ClickHouse 因为它速度快，并介绍了一些简洁的查询，包括数据透视。
新的 ClickHouse 用户：欢迎开源功能标记和实验平台GrowthBook。也请将自己添加进来！