ClickHouse 中的 SQL 动态列选择

在处理包含大量列的数据集时，我们通常希望对这些列的子集进行聚合计算。

不得不逐个输入我们要操作的所有列非常繁琐，因此我很高兴地发现 ClickHouse 具有允许动态选择列的功能。

我们将使用纽约出租车数据集，尤其是 2023 年 1 月的黄色出租车数据。我们将下载该月的 Parquet 文件，然后启动 ClickHouse 本地实例并将其导入

./clickhouse local -m

CREATE TABLE trips ENGINE MergeTree 
ORDER BY (tpep_pickup_datetime) AS 
from file('yellow tripdata Jan 2023.parquet', Parquet)
select *
SETTINGS schema_inference_make_columns_nullable = 0;

我们可以通过运行以下查询查看表的架构

DESCRIBE TABLE trips
SETTINGS describe_compact_output = 1;

┌─name──────────────────┬─type──────────┐
│ VendorID              │ Int64         │
│ tpep_pickup_datetime  │ DateTime64(6) │
│ tpep_dropoff_datetime │ DateTime64(6) │
│ passenger_count       │ Float64       │
│ trip_distance         │ Float64       │
│ RatecodeID            │ Float64       │
│ store_and_fwd_flag    │ String        │
│ PULocationID          │ Int64         │
│ DOLocationID          │ Int64         │
│ payment_type          │ Int64         │
│ fare_amount           │ Float64       │
│ extra                 │ Float64       │
│ mta_tax               │ Float64       │
│ tip_amount            │ Float64       │
│ tolls_amount          │ Float64       │
│ improvement_surcharge │ Float64       │
│ total_amount          │ Float64       │
│ congestion_surcharge  │ Float64       │
│ airport_fee           │ Float64       │
└───────────────────────┴───────────────┘

现在，假设我们只想使用包含 _amount 的列。无需逐个输入这些列，我们可以使用 COLUMNS 子句返回与正则表达式匹配的列。返回前 10 行以显示 amount 列的查询如下所示

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

┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┐
│           0 │          0 │            0 │            0 │
│         120 │          0 │            0 │        120.3 │
│          45 │       9.06 │            0 │        54.36 │
│          75 │      15.06 │            0 │        90.36 │
│          55 │      14.45 │            0 │        72.25 │
│         4.5 │          0 │            0 │         6.55 │
│          10 │          0 │            0 │         10.8 │
│         115 │          5 │            0 │        120.3 │
│          78 │      15.76 │            0 │        94.56 │
│        19.5 │          0 │            0 │        21.55 │
└─────────────┴────────────┴──────────────┴──────────────┘

假设我们还想返回包含 fee 或 tax 术语的列。我们可以更新正则表达式以包含这些术语

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

Row 1:
──────
fare_amount:  9.3
mta_tax:      0.5
tip_amount:   0
tolls_amount: 0
total_amount: 13.3
airport_fee:  0

Row 2:
──────
fare_amount:  10
mta_tax:      0.5
tip_amount:   2
tolls_amount: 0
total_amount: 16
airport_fee:  0

Row 3:
──────
fare_amount:  18.4
mta_tax:      0.5
tip_amount:   1
tolls_amount: 0
total_amount: 23.4
airport_fee:  0

我们还可以使用 APPLY 函数对每列应用函数。例如，如果我们想找到所有这些列的最大值，我们可以运行以下查询

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

Row 1:
──────
max(fare_amount):  1160.1
max(mta_tax):      53.16
max(tip_amount):   380.8
max(tolls_amount): 196.99
max(total_amount): 1169.4
max(airport_fee):  1.25

或者，我们可能想查看平均值

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

Row 1:
──────
avg(fare_amount):  18.36706861234277
avg(mta_tax):      0.48828997712900174
avg(tip_amount):   3.3679406710521764
avg(tolls_amount): 0.5184906575852216
avg(total_amount): 27.020383107155837
avg(airport_fee):  0.10489592293640923

这些值包含很多小数位数，但幸运的是，我们可以通过链接函数来解决这个问题。在这种情况下，我们将应用 avg 函数，然后是 round 函数

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

Row 1:
──────
round(avg(fare_amount)):  18
round(avg(mta_tax)):      0
round(avg(tip_amount)):   3
round(avg(tolls_amount)): 1
round(avg(total_amount)): 27
round(avg(airport_fee)):  0

但这将平均值四舍五入到整数。如果我们想四舍五入到例如 2 位小数，我们也可以这样做。除了接受函数之外，APPLY 函数还接受 lambda，这使我们可以灵活地让 round 函数将平均值四舍五入到 2 位小数

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

Row 1:
──────
round(avg(fare_amount), 2):  18.37
round(avg(mta_tax), 2):      0.49
round(avg(tip_amount), 2):   3.37
round(avg(tolls_amount), 2): 0.52
round(avg(total_amount), 2): 27.02
round(avg(airport_fee), 2):  0.1

到目前为止一切顺利。但是，假设我们想调整其中一个值，同时保留其他值不变。例如，也许我们想将总金额翻倍并将 MTA 税除以 1.1。我们可以使用 REPLACE 子句来完成此操作，该子句将替换一列，同时保留其他列不变。

FROM 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))
FORMAT Vertical;

Row 1:
──────
round(avg(fare_amount), 2):               18.37
round(divide(avg(mta_tax), 1.1), 2):      0.44
round(avg(tip_amount), 2):                3.37
round(avg(tolls_amount), 2):              0.52
round(multiply(avg(total_amount), 2), 2): 54.04
round(avg(airport_fee), 2):               0.1

我们可以看到这两列都被替换了，而其他列与之前的查询中的列相同。排除列

我们还可以使用 EXCEPT 子句选择排除一个字段。例如，要删除 tolls_amount 列，我们将编写以下查询

FROM 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))
FORMAT Vertical;

Row 1:
──────
round(avg(fare_amount), 2):               18.37
round(divide(avg(mta_tax), 1.1), 2):      0.44
round(avg(tip_amount), 2):                3.37
round(multiply(avg(total_amount), 2), 2): 54.04
round(avg(airport_fee), 2):               0.1

tolls_amount 现已被删除，而其他列保留下来。

希望您已经了解到，即使对于没有那么多列的数据集，ClickHouse 的动态列选择功能也能为我们在 SQL 查询中节省大量输入。

在您自己的数据上尝试使用这些子句，并告诉我们您的使用情况！