英国の不動産価格データセット

イングランドとウェールズの不動産取引価格データを含む英国の不動産価格データセットを使って、頻繁に実行するクエリのパフォーマンスを向上させるプロジェクションの使い方を学びます

このデータには、イングランドおよびウェールズの不動産取引で支払われた価格が含まれています。データは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-paid-data
• HM Land Registry data © Crown copyright and database right 2021 を含みます。このデータは Open Government Licence v3.0 の下で提供されています。

​

テーブルを作成

CREATE DATABASE uk;

CREATE TABLE uk.uk_price_paid
(
    price UInt32,
    date Date,
    postcode1 LowCardinality(String),
    postcode2 LowCardinality(String),
    type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
    is_new UInt8,
    duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    addr1 String,
    addr2 String,
    street LowCardinality(String),
    locality LowCardinality(String),
    town LowCardinality(String),
    district LowCardinality(String),
    county LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (postcode1, postcode2, addr1, addr2);

​

データを前処理して挿入する

データを ClickHouse にストリーミングするために、url 関数を使用します。まず、取り込むデータの一部を前処理する必要があります。具体的には、次の処理を行います。

• postcode を 2 つの別々のカラム postcode1 と postcode2 に分割する。これは保存効率とクエリ性能の両方の面で有利です
• time フィールドは時刻部分が常に 00:00 のため、日付に変換する
• 分析には不要なため、UUid フィールドを無視する
• transform 関数を使用して、type と duration を、より読みやすい Enum フィールドに変換する
• is_new フィールドを、1 文字の文字列 (Y/N) から 0 または 1 の UInt8 フィールドに変換する
• 最後の 2 つのカラムはどちらも同じ値 (0) なので削除する

url 関数は、Web サーバーから ClickHouse テーブルにデータをストリーミングします。次のコマンドは、uk_price_paid テーブルに 500 万行を挿入します。

INSERT INTO uk.uk_price_paid
SELECT
    toUInt32(price_string) AS price,
    parseDateTimeBestEffortUS(time) AS date,
    splitByChar(' ', postcode)[1] AS postcode1,
    splitByChar(' ', postcode)[2] AS postcode2,
    transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
    b = 'Y' AS is_new,
    transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM url(
    'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',
    'CSV',
    'uuid_string String,
    price_string String,
    time String,
    postcode String,
    a String,
    b String,
    c String,
    addr1 String,
    addr2 String,
    street String,
    locality String,
    town String,
    district String,
    county String,
    d String,
    e String'
) SETTINGS max_http_get_redirects=10;

データが挿入されるまで待機してください。ネットワーク速度によっては1～2分かかります。

​

データを検証する

正しく取り込まれたことを、挿入された行数を確認して検証してみましょう。

このクエリを実行した時点では、このデータセットには 27,450,499 行ありました。次に、ClickHouse でこのテーブルのストレージサイズを見てみましょう。

このテーブルのサイズは、わずか 221.43 MiB です。

​

クエリをいくつか実行する

データを分析するため、クエリをいくつか実行してみましょう。

​

クエリ 1. 年別の平均価格

​

クエリ 2. ロンドンの年別平均価格

2020年の住宅価格には何かが起きていますね！ とはいえ、おそらく驚くほどのことではないでしょう…

​

クエリ 3. 最も価格が高い地区

​

プロジェクションによるクエリの高速化

これらのクエリはプロジェクションで高速化できます。このデータセットでの例については、“Projections” を参照してください。

​

Playgroundで試す

このデータセットは Online Playground でも利用できます。