ClickHouse APPLY对多列应用相同函数

ClickHouse 21 版本支持通过select修饰符将一些奇特的操作应用至多个列中。 有时对多个列应用简单函数操作并修改原始值。ClickHoue SELECT 修饰符可以使用APPLY, EXCEPT, REPLACE。下面通过几个实例快速掌握如何使用。

应用流程

日常工作中，通常会遇到相同函数需应用至多个列中，导致对多个列编写重复代码，举例：

SELECT
    *,
    formatReadableSize(read_bytes) AS read_bytes_readable,
    formatReadableSize(written_bytes) AS written_bytes_readable,
    formatReadableSize(result_bytes) AS result_bytes_readable
FROM system.query_log
WHERE (event_date = today()) AND (query_id = '58b7c683-5c86-482d-8f84-c04964129238') AND (type > 1)

上面示例希望获取日志信息，但对三个bytes结尾的列使用formatReadableSize 函数，传统写法很冗余。

我们可以使用 SELECT ... EXCEPT 语法，举例： SELECT * EXCEPT (read_bytes, written_bytes, result_bytes)。EXCEPT可以排除一些列。然后使用 COLUMNS 表达式选择多个列，可以使用正则表达式，举例： COLUMNS('bytes')。最后使用APPLY修饰符应用函数至前面选择的每个列，举例：APPLY formatReadableSize。

应用流程为：先排除字段列表，然后选择列，最后对选择列应用函数。完整示例为：

SELECT
    * EXCEPT (read_bytes, written_bytes, result_bytes),
    COLUMNS('bytes') APPLY formatReadableSize
FROM system.query_log
WHERE (event_date = today()) AND (query_id = '58b7c683-5c86-482d-8f84-c04964129238') AND (type > 1)

处理 EXCEPT APPLY 还有REPLACE ，下面通过示例进行分解说明，加深你的理解。

示例1

首先创建表并插入示例数据：

CREATE TABLE columns_transformers (i Int64, j Int16, k Int64) ENGINE = MergeTree ORDER by (i);
INSERT INTO columns_transformers VALUES (100, 10, 324), (120, 8, 23), (180, 2, 78);

APPLY

首先使用APPLY，给select子句中所有列使用函数：

SELECT * APPLY(sum) FROM columns_transformers;


┌─sum(i)─┬─sum(j)─┬─sum(k)─┐
│    400 │     20 │    425 │
└────────┴────────┴────────┘

1 rows in set. Elapsed: 0.002 sec.

EXCEPT

使用 EXCEPT 可以排除指定列，并可以和APPLY组合使用:

SELECT * EXCEPT (i) APPLY (sum)
FROM columns_transformers

┌─sum(j)─┬─sum(k)─┐
│     20 │    425 │
└────────┴────────┘

1 rows in set. Elapsed: 0.001 sec. 

REPLACE

replace 可以别名表达式修改列原来的值：

SELECT * REPLACE(i - 10 AS i) FROM columns_transformers

┌───i─┬──j─┬───k─┐
│  90 │ 10 │ 324 │
│ 110 │  8 │  23 │
│ 170 │  2 │  78 │
└─────┴────┴─────┘

3 rows in set. Elapsed: 0.001 sec. 

混合使用

下面看个稍微复杂的混合示例：

SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) from columns_transformers;

┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐
│                        2 │                        3 │
└──────────────────────────┴──────────────────────────┘

1 rows in set. Elapsed: 0.002 sec. 

SELECT * REPLACE(i - 10 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;

┌─sum(minus(i, 10))─┬─sum(k)─┐
│               370 │    425 │
└───────────────────┴────────┘

1 rows in set. Elapsed: 0.001 sec. 

上面COLUMNS('[jk]')指定多个列，也支持逗号分隔，避免产生歧义。21.10 版本之后可以在APPLY中使用lambda表达式。

总结

通过使用APPLY可以简化代码，更多信息参考官网知识库：Find Materialized View dependencies | ClickHouse Knowledge Base (tinybird.co)