Skip to main content
Skip to main content
Edit this page

英国房地产支付价格

该数据集包含自 1995 年以来有关英格兰和威尔士房地产价格的数据。未压缩的大小约为 4 GiB，在 ClickHouse 中大约需要 278 MiB。

来源：https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads 字段说明：https://www.gov.uk/guidance/about-the-price-data

包含 HM Land Registry data © Crown copyright and database right 2021.。此数据集需在 Open Government License v3.0 的许可下使用。

创建表​

预处理和插入数据​

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

  • 将postcode 拆分为两个不同的列 - postcode1 和 postcode2，因为这更适合存储和查询
  • 将time 字段转换为日期因为它只包含 00:00 时间
  • 忽略 UUid 字段，因为我们不需要它进行分析
  • 使用 transform 函数将 Enum 字段 type 和 duration 转换为更易读的 Enum 字段
  • 将 is_new 字段从单字符串（ Y/N) 到 [UInt8](/docs/zh/sql-reference/data-types/int-uint.md#uint8-uint16-uint32-uint64-uint256-int8-int16-int32-int64 -int128-int256) 字段为 0 或 1
  • 删除最后两列，因为它们都具有相同的值（即 0）

url 函数将来自网络服务器的数据流式传输到 ClickHouse 表中。以下命令将 500 万行插入到 uk_price_paid 表中：

需要等待一两分钟以便数据插入，具体时间取决于网络速度。

验证数据​

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

在执行此查询时，数据集有 27,450,499 行。让我们看看 ClickHouse 中表的大小是多少：

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

运行一些查询​

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

查询 1. 每年平均价格​

结果如下所示：

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

结果如下所示：

2020 年房价出事了！但这并不令人意外...

查询 3. 最昂贵的社区​

结果如下所示：

使用 Projection 加速查询​

Projections 允许我们通过存储任意格式的预先聚合的数据来提高查询速度。在此示例中，我们创建了一个按年份、地区和城镇分组的房产的平均价格、总价格和数量的 Projection。在执行时，如果 ClickHouse 认为 Projection 可以提高查询的性能，它将使用 Projection（何时使用由 ClickHouse 决定）。

构建投影​

让我们通过维度 toYear(date)、district 和 town 创建一个聚合 Projection：

填充现有数据的 Projection。 （如果不进行 materialize 操作，则 ClickHouse 只会为新插入的数据创建 Projection）：

Test Performance​

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

查询 1. 每年平均价格​

结果是一样的，但是性能更好！

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

Same result, but notice the improvement in query performance:

查询 3. 最昂贵的社区​

注意：需要修改 (date >= '2020-01-01') 以使其与 Projection 定义的维度 (toYear(date) >= 2020) 匹配：

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

在 Playground 上测试​

也可以在 Online Playground 上找到此数据集。