使用ClickHouse进行SQL动态列选择

本文字数：4073；估计阅读时间：11 分钟

作者：Mark Needham

审校：庄晓东（魏庄）

本文在公众号【ClickHouseInc】首发

在处理包含大量列的数据集时，我们通常希望在其中的一部分列上做聚合计算。

如果，不得不手工键入所有要操作的列，那就会相当的繁琐，所以我很高兴地了解到：ClickHouse具有允许动态列选择的功能。

导入NYC出租车数据集

我们将使用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子句返回与正则表达式匹配所需要的列。查询返回前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查询中节省大量输入。

在您自己的数据上尝试这些查询，然后告诉我们您的使用体验！

​​联系我们

手机号：13910395701

邮箱：Tracy.Wang@clickhouse.com

满足您所有的在线分析列式数据库管理需求