ClickHouse 26.8 引入管道式 SQL 语法

SQL 查询的编写顺序并不总是与我们的思考顺序一致。我们可能首先选择表、过滤行、聚合，最后对结果排序，但传统 SQL 是先描述查询将返回的列。

自 ClickHouse 22.12 起，我们可以将 FROM 子句放在 SELECT 之前。ClickHouse 26.8 通过使用新的 |> 运算符进一步推进了管道化 SQL，该运算符允许我们将查询写成一系列转换。

在本文中，我们将比较传统 SQL、FROM-优先，以及使用UK 房产价格数据集的管道查询，然后使用管道构建原本需要嵌套子查询或 CTE 的查询。

传统 SQL 查询

让我们从一个查询开始，该查询查找自 2024 年以来伦敦各区中位房价最高的区域：

1SELECT district, count() AS sales, round(median(price)) AS median_price2FROM uk_price_paid3WHERE (town ='LONDON') AND (date>='2024-01-01')4GROUPBY district5ORDERBY median_price DESC6LIMIT 10;

1┌─district───────────────┬─sales─┬─median_price─┐2│ KENSINGTON AND CHELSEA │  2694 │      1125000 │3│ RICHMOND UPON THAMES   │   708 │       960000 │4│ CITY OF WESTMINSTER    │  3830 │       900000 │5│ CITY OF LONDON         │   367 │       842000 │6│ HARROW                 │     2 │       806750 │7│ HOUNSLOW               │   660 │       780000 │8│ CAMDEN                 │  3200 │       760000 │9│ HAMMERSMITH AND FULHAM │  3312 │       730000 │10│ ISLINGTON              │  3316 │       650000 │11│ WANDSWORTH             │  7155 │       625000 │12└────────────────────────┴───────┴──────────────┘

将FROM放在前面

ClickHouse SQL 的一个鲜为人知的特性是我们可以将FROM子句放在SELECT之前，因此以下也是有效的查询：

1FROM uk_price_paid2SELECT district, count() AS sales, round(median(price)) AS median_price3WHERE (town ='LONDON') AND (date>='2024-01-01')4GROUPBY district5ORDERBY median_price DESC6LIMIT 10;

将数据源放在第一位可以使查询更易于可视化。我们首先确定正在处理的数据，然后描述列、过滤、聚合、排序和限制。除了移动FROM之外，这仍然是一个传统的 SQL 查询。

构建管道

ClickHouse 26.8 通过管道 SQL 进一步推动了这种自顶向下的风格。|> 运算符将一个阶段的结果传递给下一个阶段，使转换顺序明确：读取表、过滤行、聚合、排序结果，最后应用限制。

以下是相同的查询写成管道的形式：

1FROM uk_price_paid2|>WHERE (town ='LONDON') AND (date>='2024-01-01')3|> AGGREGATE count() AS sales, round(median(price)) AS median_price4GROUPBY district5|>ORDERBY median_price DESC6|> LIMIT 10;

传统版本、FROM-优先版本和管道版本都返回相同的结果。

虽然我们也可以增量地简化普通 SQL，但每个|>提供了一个明确的检查点，在此处前面的管道是一个完整的查询。这使得逐步构建和检查转换变得特别方便。

ClickHouse 如何翻译管道？

管道查询在执行前被转换为标准 SQL。我们可以为查询添加前缀EXPLAIN SYNTAX（我在写这篇博客时才知道这个功能！）来查看从管道生成的传统 SQL：

1EXPLAIN SYNTAX2FROM uk_price_paid3|>WHERE (town ='LONDON') AND (date>='2024-01-01')4|> AGGREGATE count() AS sales, round(median(price)) AS median_price5GROUPBY district6|>ORDERBY median_price DESC7|> LIMIT 108FORMAT LineAsString;

1SELECT*FROM (2SELECT*FROM (3SELECT district, count() AS sales, round(median(price)) AS median_price4FROM (5SELECT*FROM (6SELECT*7FROM uk_price_paid8            )9WHEREand(equals(town, 'LONDON'), greaterOrEquals(date, '2024-01-01'))10        )11GROUPBY district12    )13ORDERBY median_price DESC14)15LIMIT 10;

尽管转换后的查询包含多个嵌套的SELECT语句，但 ClickHouse 不会物化每个中间结果，它会在执行前优化整个查询。

使用 EXTEND 添加列

EXTEND添加计算列，同时保留管道中已有的所有列。

1FROM uk_price_paid2|>WHERE town ='LONDON'ANDdate>='2024-01-01'3|> EXTEND round(price /1000000, 2) AS price_millions4|>SELECTdate, district, price, price_millions5|>ORDERBY price DESC6|> LIMIT 3;

1┌───────date─┬─district──────┬─────price─┬─price_millions─┐2│ 2024-03-20 │ TOWER HAMLETS │ 164300000 │          164.3 │3│ 2024-03-20 │ TOWER HAMLETS │ 164300000 │          164.3 │4│ 2024-03-20 │ TOWER HAMLETS │ 161890000 │         161.89 │5└────────────┴───────────────┴───────────┴────────────────┘

在此示例中，使用EXTEND等价于SELECT *, round(price / 1000000, 2) AS price_millions。下面是此查询的EXPLAIN SYNTAX输出：

1SELECT*FROM (2SELECTdate, district, price, price_millions3FROM (4SELECT*, round(divide(price, 1000000), 2) AS price_millions5FROM (6SELECT*7FROM (8SELECT*9FROM uk_price_paid10              )11WHEREand(equals(town, 'LONDON'), greaterOrEquals(date, '2024-01-01'))12          )13      )14ORDERBY price DESC15  )16  LIMIT 3;

重用中间结果

你还可以使用管道语法扩展现有的聚合。例如，以下查询计算按县分组的每个区域的中位房价：

1SELECT county, district, median(price) AS district_median2FROM uk_price_paid3WHEREdate>='2024-01-01'4GROUPBY county, district

district_median别名成为可以在下一个管道阶段使用的列。这使我们能够再次聚合它，而无需自己编写嵌套子查询或 CTE。别名成为一个我们可以在下一个管道阶段使用的列。这让我们能够再次聚合它，而无需自己编写嵌套子查询或CTE。

因此，我们可以扩展查询以计算每个县的这些区域级中位数的平均值，然后返回值最高的十个县：

1SELECT county, district, median(price) AS district_median2FROM uk_price_paid3WHEREdate>='2024-01-01'4GROUPBY county, district5|> AGGREGATE round(avg(district_median)) AS average_district_median6GROUPBY county7|>ORDERBY average_district_median DESC8|> LIMIT 10;

1┌─county─────────────────┬─average_district_median─┐2│ GREATER LONDON         │                  558476 │3│ WINDSOR AND MAIDENHEAD │                  520000 │4│ SURREY                 │                  497455 │5│ WOKINGHAM              │                  478000 │6│ HERTFORDSHIRE          │                  452250 │7│ BUCKINGHAMSHIRE        │                  443870 │8│ ISLES OF SCILLY        │                  430000 │9│ BRIGHTON AND HOVE      │                  405000 │10│ OXFORDSHIRE            │                  400451 │11│ BRACKNELL FOREST       │                  400000 │12└────────────────────────┴─────────────────────────┘

阶段顺序很重要

需要记住的是，管道阶段的顺序很重要。如果我们移动LIMIT 10在第二次聚合之前，它限制了中间结果仅为十个区域中位数。然后，县平均值仅根据这十行计算，而不是根据所有区域计算：

1SELECT county, district, median(price) AS district_median2FROM uk_price_paid3WHEREdate>='2024-01-01'4GROUPBY county, district5|> LIMIT 106|> AGGREGATE round(avg(district_median)) AS average_district_median7GROUPBY county8|>ORDERBY average_district_median DESC;

1┌─county──────────────────────────────┬─average_district_median─┐2│ BUCKINGHAMSHIRE                     │                  443000 │3│ BRIGHTON AND HOVE                   │                  405000 │4│ BRACKNELL FOREST                    │                  400000 │5│ BATH AND NORTH EAST SOMERSET        │                  390000 │6│ BEDFORD                             │                  330000 │7│ BOURNEMOUTH, CHRISTCHURCH AND POOLE │                  325000 │8│ BRIDGEND                            │                  205000 │9│ BLACKBURN WITH DARWEN               │                  150000 │10│ BLAENAU GWENT                       │                  126250 │11│ BLACKPOOL                           │                  125000 │12└─────────────────────────────────────┴─────────────────────────┘

注意

由于在第一个ORDER BY之前没有LIMIT，因此选择的十个区域行不是确定性的，因此确切结果可能会有所不同。

在其他语句中使用管道

管道不仅限于以SELECT开头的查询，它们可以用于 ClickHouse 期望的任何SELECT查询，包括子查询、INSERT ... SELECT语句和视图。

在以下示例中，我们使用管道作为视图背后的查询：

1CREATEVIEW million_pound_london_sales AS2FROM uk_price_paid3|>WHERE town ='LONDON'AND price >=10000004|>SELECTdate, price, district, postcode1, postcode2;

然后我们可以使用传统 SQL 语法查询该视图：

1SELECT*2FROM million_pound_london_sales3ORDERBY price DESC4LIMIT 3;

1┌───────date─┬─────price─┬─district────────────┬─postcode1─┬─postcode2─┐2│ 2017-07-31 │ 594300000 │ CITY OF WESTMINSTER │ W1U       │ 8EW       │3│ 2018-02-08 │ 569200000 │ CITY OF WESTMINSTER │ W1J       │ 7BT       │4│ 2019-11-20 │ 542540820 │ CAMDEN              │ NW5       │ 2HB       │5└────────────┴───────────┴─────────────────────┴───────────┴───────────┘

我们还可以使用管道作为 INSERT 的来源。例如，假设我们有下表存储昂贵的伦敦房产：

1CREATE TABLE expensive_london_sales2  (3dateDate,4      price UInt32,5      district LowCardinality(String)6  )7  ENGINE = MergeTree8ORDERBY (district, date);

传统的 INSERT ... SELECT 查询将选中的列放在源表之前：

1INSERT INTO expensive_london_sales (date, price, district)2SELECTdate, price, district3FROM uk_price_paid4WHERE town ='LONDON'AND price >=1000000;

使用管道语法，我们可以按处理顺序编写相同的操作：选择目标、读取源、过滤其行，并选择要插入的列：

1INSERT INTO expensive_london_sales (date, price, district)2FROM uk_price_paid3|>WHERE town ='LONDON'AND price >=10000004|>SELECTdate, price, district;

结论

管道 SQL 为我们提供了另一种表达 ClickHouse 查询的方式。它不会取代传统的 SQL，但将查询写成一系列转换可以使多阶段查询更易于构建和理解。

请告诉我们你的想法，以及你是否能够使用它来简化你的任何查询。