动态列选择 是一个强大但未被充分利用的 ClickHouse 功能，它允许您使用正则表达式选择列，而无需单独命名每一列。您还可以使用 APPLY 修饰符将函数应用于匹配的列，这使其在数据分析和转换任务中极其有用。
我们将通过 纽约出租车数据集 来学习如何使用此功能，您也可以在 ClickHouse SQL 游乐场 中找到该数据集。
选择匹配模式的列
让我们从一个常见场景开始：从纽约出租车数据集中选择仅包含 _amount 的列。我们可以使用 COLUMNS 表达式和正则表达式，而无需手动输入每个列名：
FROM nyc_taxi.trips
SELECT COLUMNS('.*_amount')
LIMIT 10;
在 SQL 游乐场中尝试此查询
该查询返回前 10 行，但仅返回名称匹配模式 .*_amount（以 "_amount" 结尾的任意字符）的列。
┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┐
1. │ 9 │ 0 │ 0 │ 9.8 │
2. │ 9 │ 0 │ 0 │ 9.8 │
3. │ 3.5 │ 0 │ 0 │ 4.8 │
4. │ 3.5 │ 0 │ 0 │ 4.8 │
5. │ 3.5 │ 0 │ 0 │ 4.3 │
6. │ 3.5 │ 0 │ 0 │ 4.3 │
7. │ 2.5 │ 0 │ 0 │ 3.8 │
8. │ 2.5 │ 0 │ 0 │ 3.8 │
9. │ 5 │ 0 │ 0 │ 5.8 │
10. │ 5 │ 0 │ 0 │ 5.8 │
└─────────────┴────────────┴──────────────┴──────────────┘
假设我们还想返回包含 fee 或 tax 的列。
我们可以更新正则表达式以包含这些内容：
SELECT COLUMNS('.*_amount|fee|tax')
FROM nyc_taxi.trips
ORDER BY rand()
LIMIT 3;
在 SQL 游乐场中尝试此查询
┌─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 游乐场中尝试此查询
┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┬─pickup_date─┬─────pickup_datetime─┬─dropoff_date─┬────dropoff_datetime─┐
1. │ 9 │ 0 │ 0 │ 9.8 │ 2001-01-01 │ 2001-01-01 00:01:48 │ 2001-01-01 │ 2001-01-01 00:15:47 │
2. │ 9 │ 0 │ 0 │ 9.8 │ 2001-01-01 │ 2001-01-01 00:01:48 │ 2001-01-01 │ 2001-01-01 00:15:47 │
3. │ 3.5 │ 0 │ 0 │ 4.8 │ 2001-01-01 │ 2001-01-01 00:02:08 │ 2001-01-01 │ 2001-01-01 01:00:02 │
4. │ 3.5 │ 0 │ 0 │ 4.8 │ 2001-01-01 │ 2001-01-01 00:02:08 │ 2001-01-01 │ 2001-01-01 01:00:02 │
5. │ 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 游乐场中尝试此查询
┌─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 游乐场中尝试此查询
┌─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 游乐场中尝试此查询
┌─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 │
└─────────────────────────┴─────────────────────┴────────────────────────┴──────────────────────────┴───────────────────────┴──────────────────────────┘
但这会将平均值四舍五入为整数。如果我们想四舍五入到两位小数，我们也可以这样做。除了接受函数，APPLY 修饰符还接受一个 lambda，这给了我们灵活性，以便让 round 函数将我们的平均值四舍五入到两位小数：
SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(x -> round(x, 2))
FROM nyc_taxi.trips;
在 SQL 游乐场中尝试此查询
┌─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 游乐场中尝试此查询
┌─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 游乐场中尝试此查询
┌─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 │
└────────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┴──────────────────────────┘