跳到主要内容

如何在表的每一列中计算空值/零值的比率

·2 分钟阅读
了解如何计算 ClickHouse 表中每列的空值或零值比率，以优化稀疏列序列化。

如何在表的每一列中计算空值/零值的比率​

如果列是稀疏的（为空或主要包含零），ClickHouse 可以以稀疏格式对其进行编码并自动优化计算 - 数据在查询期间不需要完全解压缩。 实际上，如果您知道列的稀疏程度，则可以使用 ratio_of_defaults_for_sparse_serialization 设置来定义其比率，以优化序列化。

这个方便的查询可能需要一段时间，但它会分析表中的每一行，并确定指定表中每一列中为零（或默认值）的值的比率

SELECT *
    APPLY x -> (x = defaultValueOfArgumentType(x)) APPLY avg APPLY x -> round(x, 3)
FROM table_name
FORMAT Vertical

例如，我们在名为 sensors 的 环境传感器数据集 表上运行了上述查询，该表有超过 200 亿行和 19 列

SELECT *
    APPLY x -> (x = defaultValueOfArgumentType(x)) APPLY avg APPLY x -> round(x, 3)
FROM sensors
FORMAT Vertical

这是响应


Row 1:
──────
round(avg(equals(sensor_id, defaultValueOfArgumentType(sensor_id))), 3):                 0
round(avg(equals(sensor_type, defaultValueOfArgumentType(sensor_type))), 3):             0.159
round(avg(equals(location, defaultValueOfArgumentType(location))), 3):                   0
round(avg(equals(lat, defaultValueOfArgumentType(lat))), 3):                             0.001
round(avg(equals(lon, defaultValueOfArgumentType(lon))), 3):                             0.001
round(avg(equals(timestamp, defaultValueOfArgumentType(timestamp))), 3):                 0
round(avg(equals(P1, defaultValueOfArgumentType(P1))), 3):                               0.474
round(avg(equals(P2, defaultValueOfArgumentType(P2))), 3):                               0.475
round(avg(equals(P0, defaultValueOfArgumentType(P0))), 3):                               0.995
round(avg(equals(durP1, defaultValueOfArgumentType(durP1))), 3):                         0.999
round(avg(equals(ratioP1, defaultValueOfArgumentType(ratioP1))), 3):                     0.999
round(avg(equals(durP2, defaultValueOfArgumentType(durP2))), 3):                         1
round(avg(equals(ratioP2, defaultValueOfArgumentType(ratioP2))), 3):                     1
round(avg(equals(pressure, defaultValueOfArgumentType(pressure))), 3):                   0.83
round(avg(equals(altitude, defaultValueOfArgumentType(altitude))), 3):                   1
round(avg(equals(pressure_sealevel, defaultValueOfArgumentType(pressure_sealevel))), 3): 1
round(avg(equals(temperature, defaultValueOfArgumentType(temperature))), 3):             0.532
round(avg(equals(humidity, defaultValueOfArgumentType(humidity))), 3):                   0.544

1 row in set. Elapsed: 992.041 sec. Processed 20.69 billion rows, 1.39 TB (20.86 million rows/s., 1.40 GB/s.)

从以上结果来看

  • sensor_id 列根本不稀疏。 实际上，每一行都有一个非零值
  • sensor_type 列只有大约 15.9% 的时间是稀疏的
  • P0 列非常稀疏：99.9% 的值是零
  • pressure 列相当稀疏，为 83%
  • 而 temperature 列有 53.2% 的值缺失或为零

就像我们说的那样，这是一个方便的查询，用于计算 ClickHouse 表中列的稀疏程度！