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

استكشف ClickHouse من خلال بيانات أسعار العقارات في المملكة المتحدة

في هذا الدليل العملي، ستستكشف ClickHouse باستخدام مجموعة بيانات UK Property Price، التي تضم بيانات أسعار بيع العقارات في إنجلترا وويلز منذ عام 1995.

​

المتطلبات الأساسية

لإكمال هذا الدليل العملي، ستحتاج إلى ما يلي:

• حساب ClickHouse Cloud (تحصل على رصيد مجاني بقيمة 300 دولار عند التسجيل)
• خدمة ClickHouse Cloud

1. أنشئ الجدول

   1. اختر SQL console من القائمة الجانبية اليسرى
   2. انقر على علامة التبويب + بجوار أيقونة الصفحة الرئيسية لإنشاء استعلام جديد
   3. في محرر SQL، اكتب الاستعلام التالي، ثم انقر على Run:

   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);
   

   انتبه إلى ORDER BY (postcode1, postcode2, addr1, addr2) الذي يحدد كيفية ترتيب ClickHouse للبيانات على القرص. إن اختيار مفتاح أساسي فعّال في ClickHouse يتوافق مع أنماط الوصول لديك أمر بالغ الأهمية لأداء الاستعلامات وكفاءة التخزين. راجع “اختيار مفتاح أساسي” لمزيد من التفاصيل.

   راجع https://www.gov.uk للاطلاع على وصف الحقول.

2. عالِج البيانات مسبقًا ثم أدرِجها

   يمكنك استخدام الدالة url لتدفق البيانات إلى ClickHouse، لكن يلزم إجراء بعض المعالجة المسبقة أولاً. يُدرج الاستعلام أدناه أكثر من 25 مليون صف في الجدول uk_price_paid، وينفّذ خطوات المعالجة المسبقة التالية:

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

   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;
   

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

3. تحقّق من صحة البيانات

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

   SELECT count()
   FROM uk.uk_price_paid
   

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

   SELECT formatReadableSize(total_bytes)
   FROM system.tables
   WHERE name = 'uk_price_paid'
   

   لاحظ أن حجم الجدول لا يتجاوز 221.43 MiB، في حين يبلغ حجم مجموعة البيانات الأصلية قبل ضغطها نحو 4 GiB. يوفّر ClickHouse ضغطًا ممتازًا للبيانات دون أي إعداد مسبق، كما يتيح لك ضبط الضغط بدقة أكبر لكل عمود عند الحاجة.

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

   بعد تحميل البيانات، جرّب الاستعلامات التالية لتلمس مدى السرعة التي تُعيد بها الاستعلامات التحليلية نتائجها. يحسب الاستعلام أدناه متوسط السعر لكل سنة على مستوى البيانات كاملةً:

   SELECT
     toYear(date) AS year,
     round(avg(price)) AS price,
     bar(price, 0, 1000000, 80
   )
   FROM uk.uk_price_paid
   GROUP BY year
   ORDER BY year
   

   يطبّق الاستعلام التالي عامل تصفية لحساب متوسط السعر لكل سنة في لندن:

   SELECT
     toYear(date) AS year,
     round(avg(price)) AS price,
     bar(price, 0, 2000000, 100
   )
   FROM uk.uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year
   

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

   يُظهر الاستعلام التالي الأحياء الأعلى سعرًا:

   SELECT
     town,
     district,
     count() AS c,
     round(avg(price)) AS price,
     bar(price, 0, 5000000, 100)
   FROM uk.uk_price_paid
   WHERE date >= '2020-01-01'
   GROUP BY
     town,
     district
   HAVING c >= 100
   ORDER BY price DESC
   LIMIT 100
   

​

الخطوات التالية

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

يمكنك الآن:

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