跳到主要内容
跳到主要内容

英国房产价格数据集

投影是一种提高频繁运行查询性能的好方法。我们将使用英国房产数据集来演示投影的强大功能，该数据集包含关于英格兰和威尔士的房地产价格支付数据。从1995年以来，数据集的未压缩大小约为4 GiB（在 ClickHouse 中仅占约278 MiB）。

创建表​

预处理并插入数据​

我们将使用 url 函数将数据流传送到 ClickHouse。我们需要首先预处理一些传入的数据，包括：

  • 将 postcode 拆分为两个不同的列 - postcode1 和 postcode2，这对于存储和查询更好
  • 将 time 字段转换为日期，因为它只包含00:00时间
  • 忽略 UUid 字段，因为我们不需要它进行分析
  • 使用 transform 函数将 type 和 duration 转换为更具可读性的 Enum 字段
  • 将 is_new 字段从单字符字符串（Y/N）转换为 UInt8 字段，其值为0或1
  • 删除最后两列，因为它们的值都是相同的（即0）

url 函数将数据从 Web 服务器流入您的 ClickHouse 表。以下命令将 500 万行插入到 uk_price_paid 表中：

等待数据插入 - 这将根据网络速度需要一分钟或两分钟。

验证数据​

让我们通过查看插入了多少行来验证它是否成功：

在运行此查询时，数据集具有 27,450,499 行。让我们看看在 ClickHouse 中表的存储大小：

请注意，表的大小仅为 221.43 MiB！

运行一些查询​

让我们运行一些查询来分析数据：

查询 1. 每年的平均价格​

结果如下所示：

查询 2. 伦敦每年的平均价格​

结果如下：

2020年房价发生了些变化！但这可能并不意外...

查询 3. 最贵的社区​

结果如下：

让我们使用投影加速查询​

投影 允许您通过以您想要的任何格式存储预聚合数据来提高查询速度。在这个例子中，我们创建了一个投影，跟踪按年、区和城镇分组的平均价格、总价格和物业数量。在查询时，如果 ClickHouse 认为投影可以提高查询性能，它将使用您的投影（您不需要做任何特殊的事来使用投影 - ClickHouse 会为您决定何时使用投影）。

构建一个投影​

让我们按维度 toYear(date)、district 和 town 创建一个聚合投影：

为现有数据填充投影。 （如果不进行物化，投影将仅为新插入的数据创建）：

测试性能​

让我们再次运行相同的三个查询：

查询 1. 每年的平均价格​

结果是相同的，但性能更好！

查询 2. 伦敦每年的平均价格​

结果相同，但请注意查询性能的改进：

查询 3. 最贵的社区​

条件（date >= '2020-01-01'）需要修改以匹配投影维度（toYear(date) >= 2020）：

结果是一样的，但请注意查询性能的改善：

在 Playground 中测试​

数据集也可以在 在线 Playground 上使用。