本文字数6323估计阅读时间16分钟作者Mark Needham编者按本文译自 ClickHouse 原博客链接点文末-查看原文。 原文围绕「管道式 SQL 让查询书写顺序与数据转换思路对齐」展开。从 FROM 前置到管道运算符ClickHouse 在语法层面持续降低复杂查询的表达成本。这一改动对习惯链式数据处理的用户较为友好实际收益仍取决于优化器对管道结构的处理能力。SQL 查询的书写顺序并不总和我们的思考顺序一致。我们可能先选表、筛选行、聚合最后排序但传统 SQL 开篇却要先描述查询会返回哪些列。自 ClickHouse 22.12 起我们已经可以把 FROM 子句放在 SELECT 前面。ClickHouse 26.8 更进一步引入管道式 SQL新增|运算符让我们把查询写成一系列转换。下面先用 UK property prices dataset 对比传统查询、FROM前置查询和管道式查询再用管道构建原本需要嵌套子查询或 CTE 的查询。传统 SQL 查询先从一个查询开始找出 2024 年以来房产中位价最高的伦敦区SELECT district, count() AS sales, round(median(price)) AS median_price FROM uk_price_paid WHERE (town LONDON) AND (date 2024-01-01) GROUP BY district ORDER BY median_price DESC LIMIT 10;┌─district───────────────┬─sales─┬─median_price─┐ │ KENSINGTON AND CHELSEA │ 2694 │ 1125000 │ │ RICHMOND UPON THAMES │ 708 │ 960000 │ │ CITY OF WESTMINSTER │ 3830 │ 900000 │ │ CITY OF LONDON │ 367 │ 842000 │ │ HARROW │ 2 │ 806750 │ │ HOUNSLOW │ 660 │ 780000 │ │ CAMDEN │ 3200 │ 760000 │ │ HAMMERSMITH AND FULHAM │ 3312 │ 730000 │ │ ISLINGTON │ 3316 │ 650000 │ │ WANDSWORTH │ 7155 │ 625000 │ └────────────────────────┴───────┴──────────────┘把 FROM 前置ClickHouse SQL 有一个不太为人知的特性可以把FROM子句放在SELECT前面所以下面这个查询也合法FROM uk_price_paid SELECT district, count() AS sales, round(median(price)) AS median_price WHERE (town LONDON) AND (date 2024-01-01) GROUP BY district ORDER BY median_price DESC LIMIT 10;把数据源放在前面查询更容易在脑中形成画面。我们先确定要处理的数据再描述列、过滤、聚合、排序和限制。除了FROM的位置变了这仍然是一个传统 SQL 查询。构建管道ClickHouse 26.8 通过管道式 SQL 把这种自顶向下的风格再推进一步。|运算符把一个阶段的结果传给下一个阶段让转换顺序变得明确读取表、筛选行、聚合、排序结果最后应用限制。同一个查询用管道写出来是这样FROM uk_price_paid | WHERE (town LONDON) AND (date 2024-01-01) | AGGREGATE count() AS sales, round(median(price)) AS median_price GROUP BY district | ORDER BY median_price DESC | LIMIT 10;传统写法、FROM前置写法和管道写法返回的结果完全相同。虽然我们也可以逐步简化普通 SQL但每个|都提供了一个明确的检查点前面的管道本身就是一个完整查询。这样一步一步构建和检查转换过程就特别方便。ClickHouse 如何转换管道管道查询在执行前会被转换成标准 SQL。我们可以在查询前加上EXPLAIN SYNTAX我写这篇博客时才知道有这东西查看管道生成的传统 SQLEXPLAIN SYNTAX FROM uk_price_paid | WHERE (town LONDON) AND (date 2024-01-01) | AGGREGATE count() AS sales, round(median(price)) AS median_price GROUP BY district | ORDER BY median_price DESC | LIMIT 10 FORMAT LineAsString;SELECT * FROM ( SELECT * FROM ( SELECT district, count() AS sales, round(median(price)) AS median_price FROM ( SELECT * FROM ( SELECT * FROM uk_price_paid ) WHERE and(equals(town, LONDON), greaterOrEquals(date, 2024-01-01)) ) GROUP BY district ) ORDER BY median_price DESC ) LIMIT 10;转换后的查询虽然包含多个嵌套的SELECT语句但 ClickHouse 不会物化每个中间结果而是在执行前优化整个查询。用 EXTEND 添加列EXTEND会添加计算列同时保留管道中已有的所有列。FROM uk_price_paid | WHERE town LONDON AND date 2024-01-01 | EXTEND round(price / 1000000, 2) AS price_millions | SELECT date, district, price, price_millions | ORDER BY price DESC | LIMIT 3;┌───────date─┬─district──────┬─────price─┬─price_millions─┐ │ 2024-03-20 │ TOWER HAMLETS │ 164300000 │ 164.3 │ │ 2024-03-20 │ TOWER HAMLETS │ 164300000 │ 164.3 │ │ 2024-03-20 │ TOWER HAMLETS │ 161890000 │ 161.89 │ └────────────┴───────────────┴───────────┴────────────────┘在这个例子中使用EXTEND等价于SELECT *, round(price / 1000000, 2) AS price_millions。该查询的EXPLAIN SYNTAX输出如下SELECT * FROM ( SELECT date, district, price, price_millions FROM ( SELECT *, round(divide(price, 1000000), 2) AS price_millions FROM ( SELECT * FROM ( SELECT * FROM uk_price_paid ) WHERE and(equals(town, LONDON), greaterOrEquals(date, 2024-01-01)) ) ) ORDER BY price DESC ) LIMIT 3;复用中间结果也可以用管道语法扩展现有聚合。例如下面的查询按郡分组计算每个区的房产价格中位数SELECT county, district, median(price) AS district_median FROM uk_price_paid WHERE date 2024-01-01 GROUP BY county, districtdistrict_median别名成为一列可以在下一个管道阶段使用。这样我们就能再次聚合它而无需自己编写嵌套子查询或 CTE。因此我们可以扩展查询计算每个郡的这些区级中位数的平均值然后返回平均值最高的十个郡SELECT county, district, median(price) AS district_median FROM uk_price_paid WHERE date 2024-01-01 GROUP BY county, district | AGGREGATE round(avg(district_median)) AS average_district_median GROUP BY county | ORDER BY average_district_median DESC | LIMIT 10;┌─county─────────────────┬─average_district_median─┐ │ GREATER LONDON │ 558476 │ │ WINDSOR AND MAIDENHEAD │ 520000 │ │ SURREY │ 497455 │ │ WOKINGHAM │ 478000 │ │ HERTFORDSHIRE │ 452250 │ │ BUCKINGHAMSHIRE │ 443870 │ │ ISLES OF SCILLY │ 430000 │ │ BRIGHTON AND HOVE │ 405000 │ │ OXFORDSHIRE │ 400451 │ │ BRACKNELL FOREST │ 400000 │ └────────────────────────┴─────────────────────────┘阶段顺序很重要需要记住管道阶段的顺序很重要。如果把LIMIT 10移到第二次聚合之前它会把中间结果限制为十个区的中位数。这样郡平均值就只根据这十行计算而不是根据每个区计算SELECT county, district, median(price) AS district_median FROM uk_price_paid WHERE date 2024-01-01 GROUP BY county, district | LIMIT 10 | AGGREGATE round(avg(district_median)) AS average_district_median GROUP BY county | ORDER BY average_district_median DESC;┌─county──────────────────────────────┬─average_district_median─┐ │ BUCKINGHAMSHIRE │ 443000 │ │ BRIGHTON AND HOVE │ 405000 │ │ BRACKNELL FOREST │ 400000 │ │ BATH AND NORTH EAST SOMERSET │ 390000 │ │ BEDFORD │ 330000 │ │ BOURNEMOUTH, CHRISTCHURCH AND POOLE │ 325000 │ │ BRIDGEND │ 205000 │ │ BLACKBURN WITH DARWEN │ 150000 │ │ BLAENAU GWENT │ 126250 │ │ BLACKPOOL │ 125000 │ └─────────────────────────────────────┴─────────────────────────┘注意由于第一个 LIMIT 之前没有 ORDER BY选出的十个区行并不确定因此精确结果可能变化。在其他语句中使用管道管道并不限于以SELECT开头的查询它们可以用在 ClickHouse 期望SELECT查询的任何地方包括子查询、INSERT ... SELECT语句和视图。在下面的例子中我们用管道作为视图背后的查询CREATE VIEW million_pound_london_sales AS FROM uk_price_paid | WHERE town LONDON AND price 1000000 | SELECT date, price, district, postcode1, postcode2;然后就可以用传统 SQL 语法查询该视图SELECT * FROM million_pound_london_sales ORDER BY price DESC LIMIT 3;┌───────date─┬─────price─┬─district────────────┬─postcode1─┬─postcode2─┐ │ 2017-07-31 │ 594300000 │ CITY OF WESTMINSTER │ W1U │ 8EW │ │ 2018-02-08 │ 569200000 │ CITY OF WESTMINSTER │ W1J │ 7BT │ │ 2019-11-20 │ 542540820 │ CAMDEN │ NW5 │ 2HB │ └────────────┴───────────┴─────────────────────┴───────────┴───────────┘我们也可以把管道用作INSERT的源。例如假设有如下表用于存储伦敦的高价房产CREATE TABLE expensive_london_sales ( date Date, price UInt32, district LowCardinality(String) ) ENGINE MergeTree ORDER BY (district, date);传统的INSERT ... SELECT查询会把要选择的列放在源表之前INSERT INTO expensive_london_sales (date, price, district) SELECT date, price, district FROM uk_price_paid WHERE town LONDON AND price 1000000;使用管道语法我们可以按处理顺序编写同样的操作选择目标读取源过滤行然后选择要插入的列INSERT INTO expensive_london_sales (date, price, district) FROM uk_price_paid | WHERE town LONDON AND price 1000000 | SELECT date, price, district;总结Pipelined SQL 为我们提供了另一种表达 ClickHouse 查询的方式。它不会取代传统 SQL但把查询写成一系列转换步骤能让多阶段查询更容易编写和理解。欢迎告诉我们你的看法以及你是否已经用它简化了自己的查询。关于我们ClickHouseclickhouse.com) 是面向 AI 时代打造的高性能实时分析数据库能够以极致性能处理海量数据分析任务。凭借高并发、低延迟和云原生架构ClickHouse 广泛应用于可观测性、数据仓库、实时分析及 AI 数据基础设施等场景。我们致力于帮助企业在公有云平台上构建安全、弹性且高性价比的实时分析与 AI 数据平台加速释放数据价值推动智能化创新与数字化转型。目前Trip.com、DiDi、Meta、Sony、Netflix、Deutsche Bank、Sierra、Cloudflare 等全球领先企业均在使用 ClickHouse 支撑其关键业务和数据分析平台。