مجموعة بيانات أسعار العقارات في المملكة المتحدة

تعرّف على كيفية استخدام الإسقاطات لتحسين أداء الاستعلامات التي تشغّلها بشكل متكرر باستخدام مجموعة بيانات العقارات في المملكة المتحدة، التي تتضمن بيانات عن الأسعار المدفوعة للعقارات في إنجلترا وويلز

تتضمن هذه البيانات الأسعار المدفوعة لشراء العقارات في إنجلترا وويلز. والبيانات متاحة منذ عام 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 © حقوق النشر التابعة للتاج وحق قاعدة البيانات لعام 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);

​

معالجة البيانات مسبقًا وإدراجها

سنستخدم الدالة url لدفق البيانات إلى ClickHouse. نحتاج أولًا إلى إجراء بعض المعالجة المسبقة على البيانات الواردة، ويشمل ذلك:

• تقسيم postcode إلى عمودين مختلفين: postcode1 وpostcode2، لأن ذلك أفضل للتخزين والاستعلامات
• تحويل الحقل time إلى تاريخ لأنه لا يحتوي إلا على الوقت 00:00
• تجاهل الحقل UUid لأننا لا نحتاج إليه في التحليل
• تحويل type وduration إلى حقول Enum أسهل قراءةً باستخدام الدالة transform
• تحويل الحقل is_new من سلسلة نصية مكوّنة من محرف واحد (Y/N) إلى حقل UInt8 بقيمة 0 أو 1
• حذف العمودين الأخيرين لأن قيمتهما متطابقة دائمًا (وهي 0)

تقوم الدالة url بدفق البيانات من خادم الويب إلى جدول ClickHouse الخاص بك. يُدرِج الأمر التالي 5 ملايين صف في جدول uk_price_paid:

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;

انتظر حتى يتم إدراج البيانات — فقد يستغرق ذلك دقيقة أو دقيقتين حسب سرعة الشبكة.

​

تحقّق من البيانات

لنتأكد من أن العملية نجحت عبر معرفة عدد الصفوف التي أُدرجت:

وقت تشغيل هذا الاستعلام، كانت مجموعة البيانات تحتوي على 27,450,499 صفًا. لنرَ حجم تخزين هذا الجدول في ClickHouse:

لاحظ أن حجم الجدول لا يتجاوز 221.43 MiB!

​

نفّذ بعض الاستعلامات

لننفّذ بعض الاستعلامات لتحليل البيانات:

​

الاستعلام 1: متوسط السعر لكل سنة

​

الاستعلام 2. متوسط السعر سنويًا في لندن

يبدو أن شيئًا ما حدث لأسعار المنازل في عام 2020! لكن ذلك على الأرجح ليس مفاجئًا…

​

الاستعلام 3. الأحياء الأكثر تكلفة

​

تسريع الاستعلامات باستخدام الإسقاطات

يمكننا تسريع هذه الاستعلامات باستخدام الإسقاطات. راجع “الإسقاطات” للاطلاع على أمثلة تتعلق بمجموعة البيانات هذه.

​

جرّبه في Playground

تتوفر مجموعة البيانات أيضًا في Online Playground.