ReaderMX.
ClickHouseInc

别只盯着性能优化了：2025 年 ClickHouse 被低估的新函数盘点

CMark Needham约 10 分钟
图片

本文字数：10706；估计阅读时间：27 分钟

作者：Mark Needham

Image

原标题：2025 年你可能忽略的新函数

我和同事 Tom Schreiber 会在每次 ClickHouse 版本发布之后撰写一篇博客文章，重点介绍该版本中的重要变化，例如新增的数据湖目录或 join 性能方面的改进。

不过，在每个版本中，往往还会包含一些我们没有机会详细介绍的新函数。在这篇博客文章中，我将挑选一些 2025 年引入、但你可能没有注意到的新函数进行介绍。

Image

2025 年一共引入了多少个函数？

首先，你是否知道，可以通过运行下面的查询来统计 2025 年一共新增了多少个函数：

select count() FROM system.functions WHERE introduced_in LIKE '25%';
┌─count()─┐│     119 │└─────────┘

我们也可以进一步统计每个版本分别引入了多少个函数。为此，我们需要借用一个用于对语义版本号进行排序的用户自定义函数：

CREATE FUNCTION sortableSemVer AS version ->   arrayMap(    x -> toUInt32OrZero(x),     splitByChar('.', extract(version, '(d+(.d+)+)'))  );

接下来，我们就可以编写如下查询：

SELECT introduced_in, count()FROM system.functionsWHERE introduced_in LIKE '25%'GROUP BY ALLORDER BY sortableSemVer(introduced_in);
┌─introduced_in─┬─count()─┐│ 25.1          │       2 ││ 25.2          │       2 ││ 25.3          │       3 ││ 25.4          │       7 ││ 25.5          │      18 ││ 25.6          │      17 ││ 25.7          │      25 ││ 25.8          │      18 ││ 25.9          │       8 ││ 25.10         │      10 ││ 25.11         │       7 ││ 25.12         │       2 │└───────────────┴─────────┘

如果想要获取函数的完整列表，可以运行下面的查询：

SELECT    name,    introduced_in AS version,    if(length(description) > 80,       substring(description, 1, 80) || '...',       description) AS descriptionFROM system.functionsWHERE introduced_in LIKE '25%'ORDER BY sortableSemVer(introduced_in);

为了便于展示，这里的结果仅保留了前十行：

Row 1:──────name:        variantElementversion:     25.2description:Extracts a column with specified type from a `Variant` column.Row 2:──────name:        numericIndexedVectorPointwiseMultiplyversion:     25.7description:Performs pointwise multiplication between a numericIndexedVector and either ano...Row 3:──────name:        __patchPartitionIDversion:     25.5description:Internal function. Receives the name of a part and a hash of patch part's colum...Row 4:──────name:        readWKBPolygonversion:     25.5description:                Parses a Well-Known Binary (WKB) representation of a Polygon ge...Row 5:──────name:        initialQueryStartTimeversion:     25.4description:Returns the start time of the initial current query.`initialQueryStartTime` re...

下面让我们来看看其中一些新函数！

Image

mapContainsValueLike 

mapContainsValueLike 是在 ClickHouse 25.5 中引入的，用于检查某个 map 中是否存在一个值，该值可以通过 LIKE 运算符匹配指定的模式。

例如，假设我们有下面这样一个查询，它会返回公司名称以及对应的用例详情：

SELECT        'Netflix' AS company,        map('use_case', 'streaming analytics', 'scale', '5 petabytes daily') AS details    UNION ALL    SELECT        'Tesla',        map('use_case', 'observability platform', 'scale', 'quadrillion rows', 'feature', 'vector search')    UNION ALL    SELECT        'Anthropic',        map('use_case', 'AI observability', 'scale', 'billions of events')    UNION ALL    SELECT        'Uber',        map('use_case', 'ride analytics', 'scale', 'petabyte scale')FORMAT Vertical;
Row 1:──────company: Netflixdetails: {'use_case':'streaming analytics','scale':'5 petabytes daily'}Row 2:──────company: Tesladetails: {'use_case':'observability platform','scale':'quadrillion rows','feature':'vector search'}Row 3:──────company: Anthropicdetails: {'use_case':'AI observability','scale':'billions of events'}Row 4:──────company: Uberdetails: {'use_case':'ride analytics','scale':'petabyte scale'}

接着，我们可以编写如下查询，用来检查 map 中是否有任意值包含 obser、petabyte 或 vector 这些关键词：

WITH useCases AS (    SELECT        'Netflix' AS company,        map('use_case', 'streaming analytics', 'scale', '5 petabytes daily') AS details    UNION ALL    SELECT        'Tesla',        map('use_case', 'observability platform', 'scale', 'quadrillion rows', 'feature', 'vector search')    UNION ALL    SELECT        'Anthropic',        map('use_case', 'AI observability', 'scale', 'billions of events')    UNION ALL    SELECT        'Uber',        map('use_case', 'ride analytics', 'scale', 'petabyte scale'))SELECT    company,    details,    mapContainsValueLike(details, '%observ%') AS is_observability,    mapContainsValueLike(details, '%petabyte%') AS petabyte_scale,    mapContainsValueLike(details, '%vector%') AS has_vector_searchFROM useCasesFORMAT Vertical;

执行后，我们会得到如下结果：

Row 1:──────company:           Netflixdetails:           {'use_case':'streaming analytics','scale':'5 petabytes daily'}is_observability:  0petabyte_scale:    1has_vector_search: 0Row 2:──────company:           Tesladetails:           {'use_case':'observability platform','scale':'quadrillion rows','feature':'vector search'}is_observability:  1petabyte_scale:    0has_vector_search: 1Row 3:──────company:           Anthropicdetails:           {'use_case':'AI observability','scale':'billions of events'}is_observability:  1petabyte_scale:    0has_vector_search: 0Row 4:──────company:           Uberdetails:           {'use_case':'ride analytics','scale':'petabyte scale'}is_observability:  0petabyte_scale:    1has_vector_search: 0

Image

perimeterCartesian 

perimeterCartesian 是在 ClickHouse 25.10 中新增的函数，用于在笛卡尔 (平面) 坐标系中计算指定 Geometry 对象的周长。

下面我们来看一个示例，演示它在计算正方形周长时的效果：

SELECT perimeterCartesian(readWKT('POLYGON((0 0,1 0,1 1,0 1,0 0))'));
┌─perimeterCar⋯ 1,0 0))'))─┐│                        4 │└──────────────────────────┘

此外，还有 perimeterSpherical，它用于计算位于球面上的 Geometry 对象的周长。因此，如果我们想要计算环绕伦敦的 M25 高速公路的周长，就可以使用 perimeterSpherical：

WITH    readWKT('POLYGON((0.13870239257812503 51.2968127854147, 0.16342163085937503 51.37403072457134, 0.212860107421875 51.41516045575089, 0.27053833007812506 51.483627853536014, 0.27328491210937506 51.54686881000932, 0.25405883789062506 51.633894901713354, 0.13870239257812503 51.67308742846449, 0.08102416992187501 51.695224736990404, -0.023345947265625003 51.68500886266592, -0.12222290039062501 51.69352225137908, -0.29525756835937506 51.71224607096211, -0.37490844726562506 51.71905281158759, -0.44631958007812506 51.68330599278565, -0.49850463867187506 51.64412230646439, -0.5259704589843751 51.55028473901506, -0.5039978027343751 51.51440469156115, -0.5369567871093751 51.44255973575031, -0.5177307128906251 51.37403072457134, -0.41061401367187506 51.30883300776494, -0.29525756835937506 51.30539897974217, -0.15243530273437503 51.272762896039936, 0.04531860351562501 51.272762896039936, 0.13870239257812503 51.2968127854147))') AS m25,    perimeterSpherical(m25) AS per_radSELECT    per_rad,    per_rad * 6371000 AS per_meters,    per_rad * 6371 AS per_km;

该函数返回的是单位球面上的弧度长度，因此需要再乘以地球半径，才能得到以米或千米为单位的实际长度：

┌──────────────per_rad─┬─────────per_meters─┬─────────────per_km─┐│ 0.027954722202348813 │ 178099.53515116428 │ 178.09953515116428 │└──────────────────────┴────────────────────┴────────────────────┘

事实上，这条高速公路的直径约为 188 km，因此计算结果已经相当接近了 —— 那点误差就归咎于我画的多边形不够精确吧。

Image

HMAC

HMAC (Hash-based Message Authentication Code) 是一种加密构造，用于同时验证消息的完整性与真实性。ClickHouse 25.12 新增了这一函数。

下面我们通过为单词 ‘ClickHouse’ 生成签名，来看一下它的使用方式。返回结果采用十六进制格式，因此我们会使用 hex 函数将其转换为字符串形式：

SELECT hex(HMAC('sha256', 'ClickHouse', 'mySecretKey'))
┌─hex(HMAC('sha256', 'ClickHouse', 'mySecretKey'))─────────────────┐│ 5A79F3AA2874164CFD9811F9D1DBCEBE428C9BC52A7F57303EC6BAFCD6C9377B │└──────────────────────────────────────────────────────────────────┘

消息 ‘ClickHouse’ 及其对应的签名随后可以发送给另一方。接收方可以使用自己持有的密钥副本计算 HMAC，并将计算结果与收到的签名进行比对，从而验证消息的真实性。

Image

argAndMin and argAndMax 

ClickHouse 25.11 引入了 argAndMax 和 argAndMin 函数。下面我们通过英国房产价格数据集来了解这两个函数的用法。

假设我们想要找出 2025 年成交价格最高的一套房产，可以编写如下查询：

SELECT max(price) FROM uk_price_paid WHERE toYear(date) = 2025;
┌─max(price)─┐│  127700000 │ -- 127.70 million└────────────┘

如果我们还想知道这个最高价格对应的是哪个城镇，该怎么做呢？这时可以使用 argMax 函数：

SELECT argMax(town, price) FROM uk_price_paid WHERE toYear(date) = 2025;
┌─argMax(town, price)─┐│ PURFLEET-ON-THAMES  │└─────────────────────┘

argAndMax 函数则可以一次性返回城镇名称以及对应的最高成交价格：

SELECT argAndMax(town, price) FROM uk_price_paid WHERE toYear(date) = 2025;
┌─argAndMax(town, price)───────────┐│ ('PURFLEET-ON-THAMES',127700000) │└──────────────────────────────────┘

同样地，我们也可以使用 argAndMin 来获取城镇以及对应的最低成交价格：

SELECT argAndMin(town, price) FROM uk_price_paid WHERE toYear(date) = 2025;
┌─argAndMin(town, price)─┐│ ('CAMBRIDGE',100)      │└────────────────────────┘

这个结果看起来并不合理，因为在 2025 年以 100 英镑成交一套房产的可能性非常低。

Image

sparseGrams

sparseGrams 是在 ClickHouse 25.5 中引入的函数，用于查找给定字符串中所有长度至少为 n 的子字符串。这些子字符串需要满足一个条件：其边界处的 (n-1)-gram 的哈希值必须严格大于该子字符串内部任意 (n-1)-gram 的哈希值。该函数使用 CRC32 作为哈希函数。

下面来看一个示例，看看它是如何工作的：

SELECT sparseGrams('ClickHouse') FORMAT Vertical;
Row 1:──────sparseGrams('ClickHouse'): ['Cli','lic','ick','lick','ckH','kHo','ckHo','lickHo','Hou','ous','Hous','use']

该函数由 GitHub 团队提出，在构建搜索索引时，可以作为传统 n-grams 的一种合适替代方案。

Image

stringBytesUniq 

stringBytesUniq 在 ClickHouse 25.6 中引入，用于统计一个字符串中不同字节的数量。下面来看几个示例：

SELECT    stringBytesUniq('ClickHouse') AS ch,    stringBytesUniq('Alexey Milovidov') AS alexey,    stringBytesUniq('AAAAA') AS a;
┌─ch─┬─alexey─┬─a─┐│ 10 │     11 │ 1 │└────┴────────┴───┘

Image

financialInternalRateOfReturn 

financialInternalRateOfReturn 是在 ClickHouse 25.7 中新增的函数，用于计算按年度折算后的回报率 (例如某项投资的收益率)。

例如，假设我们在 2020 年以 113 美元买入 Apple 股票，在 2021、2022、2023 和 2024 年期间没有任何操作，并在 2025 年以 231 美元卖出。可以通过运行下面的查询来计算内部收益率：

SELECT financialInternalRateOfReturn([-113, 0, 0, 0, 0, 231])
┌─financialInt⋯0, 0, 231])─┐│      0.15373669910090634 │└──────────────────────────┘

这相当于在 2020 年到 2025 年期间，每一年都获得了约 15% 的回报。我们可以通过运行下面的查询来验证这一点：

SELECT 113 * power(1.15373669910090634, 5);
┌─multiply(113⋯009064, 5))─┐│       230.99999999999997 │└──────────────────────────┘

此外，还有一个相关函数 financialInternalRateOfReturnExtended，用于在现金流发生在不规则时间点 (即具体日期) 的情况下计算回报率。

因此，我们仍然使用 Apple 股票的示例，不过这一次改为使用买入和卖出时的具体日期：

SELECT financialInternalRateOfReturnExtended(  [-113, 231],  [toDate('2020-09-11'), toDate('2025-03-05')]);
┌─financialInt⋯5-03-05')])─┐│      0.17295574412431242 │└──────────────────────────┘

这一次计算得到的回报率略高于 17%。

Image

toInterval

toInterval 是在 ClickHouse 25.4 中引入的函数，用于根据一个数值以及对应的单位字符串生成一个 interval 值。

此前已经存在用于完成该任务的多个独立函数 (例如 toIntervalSecond、toIntervalMinute、toIntervalDay 等)，而 toInterval 将这些功能统一到了一个函数之中。

下面来看一下它的用法：

SELECT    toInterval(5, 'second') AS seconds,    toTypeName(seconds) AS secType,    toInterval(3, 'day') AS days,    toTypeName(days) AS daysType,    toInterval(2, 'month') AS months,    toTypeName(months) AS monthsType;
┌─seconds─┬─secType────────┬─days─┬─daysType────┬─months─┬─monthsType────┐│       5 │ IntervalSecond │    3 │ IntervalDay │      2 │ IntervalMonth │└─────────┴────────────────┴──────┴─────────────┴────────┴───────────────┘

我们也可以使用该函数在现有的 DateTime 值基础上增加一段时间，如下所示：

WITH toDateTime('2025-12-17 12:32:12') AS currentTimeSELECT    currentTime,    currentTime + toInterval(7, 'day') + toInterval(23, 'hour') AS nextWeek;
┌─────────currentTime─┬────────────nextWeek─┐│ 2025-12-17 12:32:12 │ 2025-12-25 11:32:12 │└─────────────────────┴─────────────────────

Image

timeSeriesRange 

timeSeriesRange 是在 25.8 版本中引入的函数，用于生成一段时间戳范围。它的作用类似于 range 函数，但针对的是 DateTime 类型。

该函数会返回一个数组结果，不过我们可以借助 arrayJoin 函数将数组展开为多行记录：

SELECT arrayJoin(  timeSeriesRange(    '2025-06-01 00:00:00'::DateTime,     '2025-06-01 00:01:00'::DateTime,     10)) AS ts;
┌──────────────────ts─┐│ 2025-06-01 00:00:00 ││ 2025-06-01 00:00:10 ││ 2025-06-01 00:00:20 ││ 2025-06-01 00:00:30 ││ 2025-06-01 00:00:40 ││ 2025-06-01 00:00:50 ││ 2025-06-01 00:01:00 │└─────────────────────┘

接下来，就可以计算距离这些时间点分别已经过去了多长时间

WITH toDateTime('2025-12-17 12:32:12') AS currentTimeSELECT arrayJoin(  timeSeriesRange(    '2025-06-01 00:00:00'::DateTime,     '2025-06-01 00:01:00'::DateTime,     10)) AS ts,  formatReadableTimeDelta(now() - ts) AS timeAgo;
┌──────────────────ts─┬─timeAgo────────────────────────────────────────────────┐│ 2025-06-01 00:00:00 │ 6 months, 16 days, 14 hours, 10 minutes and 41 seconds ││ 2025-06-01 00:00:10 │ 6 months, 16 days, 14 hours, 10 minutes and 31 seconds ││ 2025-06-01 00:00:20 │ 6 months, 16 days, 14 hours, 10 minutes and 21 seconds ││ 2025-06-01 00:00:30 │ 6 months, 16 days, 14 hours, 10 minutes and 11 seconds ││ 2025-06-01 00:00:40 │ 6 months, 16 days, 14 hours, 10 minutes and 1 second   ││ 2025-06-01 00:00:50 │ 6 months, 16 days, 14 hours, 9 minutes and 51 seconds  ││ 2025-06-01 00:01:00 │ 6 months, 16 days, 14 hours, 9 minutes and 41 seconds  │└─────────────────────┴────────────────────────────────────────────────────────┘

/END/

试用阿里云 ClickHouse企业版

轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G

图片
图片

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：[email protected]

图片图片

KEEP THE THREAD GOING

更多来源文章