动态列选择 - ClickHouse Documentation

动态列选择是 ClickHouse 一项强大但尚未得到充分利用的功能。它允许你使用正则表达式选择列，而无需逐一指定每个列名。你还可以使用 APPLY 修饰符对匹配的列应用函数，这使它在数据分析和转换任务中特别实用。我们将借助 New York taxis 数据集学习如何使用这一功能。你也可以在 ClickHouse SQL playground 中找到该数据集。

选择匹配某个模式的列

先来看一个常见场景：从 NYC taxi 数据集中只选出包含 _amount 的列。无需手动输入每个列名，我们可以使用 COLUMNS 表达式结合正则表达式来实现：

FROM nyc_taxi.trips
SELECT COLUMNS('.*_amount')
LIMIT 10;

在 SQL playground 中试用此查询

此查询返回前 10 行，但仅返回名称与模式 .*_amount 匹配的列 (即任意字符后跟 “_amount”) 。

    ┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┐
│           9 │          0 │            0 │          9.8 │
│           9 │          0 │            0 │          9.8 │
│         3.5 │          0 │            0 │          4.8 │
│         3.5 │          0 │            0 │          4.8 │
│         3.5 │          0 │            0 │          4.3 │
│         3.5 │          0 │            0 │          4.3 │
│         2.5 │          0 │            0 │          3.8 │
│         2.5 │          0 │            0 │          3.8 │
│           5 │          0 │            0 │          5.8 │
│           5 │          0 │            0 │          5.8 │
    └─────────────┴────────────┴──────────────┴──────────────┘

假设我们还想返回包含 fee 或 tax 的列。我们可以更新正则表达式，把它们也包含进来：

SELECT COLUMNS('.*_amount|fee|tax')
FROM nyc_taxi.trips
ORDER BY rand() 
LIMIT 3;

在 SQL playground 中尝试此查询

   ┌─fare_amount─┬─mta_tax─┬─tip_amount─┬─tolls_amount─┬─ehail_fee─┬─total_amount─┐
1. │           5 │     0.5 │          1 │            0 │         0 │          7.8 │
2. │        12.5 │     0.5 │          0 │            0 │         0 │         13.8 │
3. │         4.5 │     0.5 │       1.66 │            0 │         0 │         9.96 │
   └─────────────┴─────────┴────────────┴──────────────┴───────────┴──────────────┘

选择多个匹配模式

我们可以在一次查询中组合多个列匹配模式：

SELECT 
    COLUMNS('.*_amount'),
    COLUMNS('.*_date.*')
FROM nyc_taxi.trips
LIMIT 5;

在 SQL playground 中试用此查询

   ┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┬─pickup_date─┬─────pickup_datetime─┬─dropoff_date─┬────dropoff_datetime─┐
│           9 │          0 │            0 │          9.8 │  2001-01-01 │ 2001-01-01 00:01:48 │   2001-01-01 │ 2001-01-01 00:15:47 │
│           9 │          0 │            0 │          9.8 │  2001-01-01 │ 2001-01-01 00:01:48 │   2001-01-01 │ 2001-01-01 00:15:47 │
│         3.5 │          0 │            0 │          4.8 │  2001-01-01 │ 2001-01-01 00:02:08 │   2001-01-01 │ 2001-01-01 01:00:02 │
│         3.5 │          0 │            0 │          4.8 │  2001-01-01 │ 2001-01-01 00:02:08 │   2001-01-01 │ 2001-01-01 01:00:02 │
│         3.5 │          0 │            0 │          4.3 │  2001-01-01 │ 2001-01-01 00:02:26 │   2001-01-01 │ 2001-01-01 00:04:49 │
   └─────────────┴────────────┴──────────────┴──────────────┴─────────────┴─────────────────────┴──────────────┴─────────────────────┘

对所有列应用函数

我们也可以使用 APPLY 修饰符，对每一列应用函数。例如，如果我们想找出这些列各自的最大值，可以运行以下查询：

SELECT COLUMNS('.*_amount|fee|tax') APPLY(max)
FROM nyc_taxi.trips;

在 SQL playground 中试用此查询

   ┌─max(fare_amount)─┬─max(mta_tax)─┬─max(tip_amount)─┬─max(tolls_amount)─┬─max(ehail_fee)─┬─max(total_amount)─┐
1. │           998310 │     500000.5 │       3950588.8 │           7999.92 │           1.95 │         3950611.5 │
   └──────────────────┴──────────────┴─────────────────┴───────────────────┴────────────────┴───────────────────┘

或者，我们也可以改为查看平均值：

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg)
FROM nyc_taxi.trips

在 SQL playground 中试试这个查询

   ┌─avg(fare_amount)─┬───────avg(mta_tax)─┬────avg(tip_amount)─┬──avg(tolls_amount)─┬──────avg(ehail_fee)─┬──avg(total_amount)─┐
1. │ 11.8044154834777 │ 0.4555942672733423 │ 1.3469850969211845 │ 0.2256511991414463 │ 3.37600560437412e-9 │ 14.423323722271563 │
   └──────────────────┴────────────────────┴────────────────────┴────────────────────┴─────────────────────┴────────────────────┘

这些值有很多位小数，不过好在我们可以通过串联函数来解决这个问题。在这里，我们先应用 avg 函数，再应用 round 函数：

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(round)
FROM nyc_taxi.trips;

在 SQL playground 中试试这个查询

   ┌─round(avg(fare_amount))─┬─round(avg(mta_tax))─┬─round(avg(tip_amount))─┬─round(avg(tolls_amount))─┬─round(avg(ehail_fee))─┬─round(avg(total_amount))─┐
1. │                      12 │                   0 │                      1 │                        0 │                     0 │                       14 │
   └─────────────────────────┴─────────────────────┴────────────────────────┴──────────────────────────┴───────────────────────┴──────────────────────────┘

但这样会把平均值四舍五入为整数。如果我们想将其四舍五入到比如 2 位小数，也同样可以。除了接受函数外，APPLY 修饰符还接受 lambda，这让我们可以灵活地让 round 函数将平均值四舍五入到 2 位小数：

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(x -> round(x, 2))
FROM nyc_taxi.trips;

在 SQL playground 中试运行此查询

   ┌─round(avg(fare_amount), 2)─┬─round(avg(mta_tax), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(tolls_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(total_amount), 2)─┐
1. │                       11.8 │                   0.46 │                      1.35 │                        0.23 │                        0 │                       14.42 │
   └────────────────────────────┴────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴─────────────────────────────┘

替换列

到目前为止都没问题。不过，假设我们想调整其中一个值，同时保持其他值不变。例如，我们可能想将总金额翻倍，并将 MTA 税除以 1.1。我们可以使用 REPLACE 修饰符来实现：它会替换某一列，同时保持其他列不变。

FROM nyc_taxi.trips 
SELECT 
  COLUMNS('.*_amount|fee|tax')
  REPLACE(
    total_amount*2 AS total_amount,
    mta_tax/1.1 AS mta_tax
  ) 
  APPLY(avg)
  APPLY(col -> round(col, 2));

在 SQL playground 中试试此查询

   ┌─round(avg(fare_amount), 2)─┬─round(avg(di⋯, 1.1)), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(tolls_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(mu⋯nt, 2)), 2)─┐
1. │                       11.8 │                     0.41 │                      1.35 │                        0.23 │                        0 │                    28.85 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴──────────────────────────┘

排除列

我们也可以使用 EXCEPT 修饰符来排除某一列。例如，要去掉 tolls_amount 列，可以编写如下查询：

FROM nyc_taxi.trips 
SELECT 
  COLUMNS('.*_amount|fee|tax') EXCEPT(tolls_amount)
  REPLACE(
    total_amount*2 AS total_amount,
    mta_tax/1.1 AS mta_tax
  ) 
  APPLY(avg)
  APPLY(col -> round(col, 2));

在 SQL playground 中试运行此查询

   ┌─round(avg(fare_amount), 2)─┬─round(avg(di⋯, 1.1)), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(mu⋯nt, 2)), 2)─┐
1. │                       11.8 │                     0.41 │                      1.35 │                        0 │                    28.85 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┴──────────────────────────┘

​选择匹配某个模式的列

​选择多个匹配模式

​对所有列应用函数

​替换列

​排除列

选择匹配某个模式的列

选择多个匹配模式

对所有列应用函数

替换列

排除列