ClickHouse Newsletter 2022 年 8 月刊：航空公司也许并没有那么糟糕

您是通过点击电子邮件中的“在浏览器中查看”链接找到此页面的吗？那太不幸了。您只需点击链接即可找到十二月刊。

在 ClickHouse，我们非常高兴线下聚会终于回归了。诚然，有时它们会与 38 摄氏度（100 华氏度）的高温重合——就像我们上个月在伦敦的聚会一样。但是，嘿，这就是为什么它们在晚上举行，而且还有披萨。如果可以，请加入附近的 ClickHouse 聚会小组！

我们还推出了一个全新的网页！它比旧网页更漂亮，快来看看：https://clickhouse.ac.cn/

继续阅读以了解我们的新基准，让我们向您介绍 ClickHouse 22.7 的新功能，了解航空公司是否真的像他们说的那样糟糕，并为您的阅读清单添加一些夏季素材。

顺便说一下，如果您正在我们的网站上阅读本文，您知道您也可以在收件箱中收到每月的时事通讯电子邮件吗？在此处注册 注册。

即将举行的活动

ClickHouse v22.8 发布网络研讨会

• 时间？ 8 月 18 日星期四上午 9 点（太平洋标准时间）/ 晚上 6 点（欧洲中部夏令时间）
• 如何加入？ 在此处注册。

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

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

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

阅读角

我们一直在阅读

1. DeepL 与 ClickHouse 的旅程 DeepL 引入了 ClickHouse、Kafka 和 Metabase，以转向世界最佳机器翻译器的数据驱动开发。

2. NANO Corp：从实验到生产，Supercolumn 之旅 阅读 NANO Corp 在对所有其他替代方案感到失望后偶然发现 ClickHouse，并因其非常适合他们实时监控千兆位网络的用例而留下的故事。

3. 快速、功能丰富且可变：ClickHouse 为 Darwinium 的安全和欺诈分析用例提供支持 数字风险平台 Darwinium 选择 ClickHouse 用于高速率和低延迟地写入数千列的宽表。

4. 面向数据爱好者的 ClickHouse 和开源现代数据堆栈 区块链分析提供商 Luabase 因其速度而选择了 ClickHouse，并介绍了包括透视数据在内的一些简洁查询。

5. 新的 ClickHouse 采用者： 欢迎开源功能标记和实验平台 GrowthBook。也让自己被添加进来吧！

感谢您的阅读。我们下个月再见！

ClickHouse 团队