博客 / 工程

ClickHouse 的 SQL 动态列选择

Mark Needham

2023年11月28日 - 7 分钟阅读

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

手动输入所有要操作的列非常繁琐，因此我很高兴了解到 ClickHouse 具有允许动态列选择的功能。

我们将使用 NYC 出租车数据集，特别是 2023 年 1 月份黄色出租车的数据。我们将下载该月的 Parquet 文件，然后启动 ClickHouse Local 实例并导入它。

./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 子句来返回与正则表达式匹配的列。一个返回 amount 列前 10 行的查询如下所示：

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 查询的输入。

在您自己的数据上尝试这些子句，并告诉我们您的进展如何！

分享这篇文章

订阅我们的新闻通讯

随时了解功能发布、产品路线图、支持和云产品！

正在加载表单...

博客 / 工程

ClickHouse 的 SQL 动态列选择

导入 NYC 出租车数据集

动态选择列

将函数应用于所有列

替换列

结论

订阅我们的新闻通讯

最新文章