دليل عملي متقدم

تحتوي مجموعة بيانات سيارات الأجرة في مدينة نيويورك على تفاصيل ملايين رحلات سيارات الأجرة، بما في ذلك أعمدة لمبلغ الإكرامية والرسوم ونوع الدفع وغيرها. أنشئ جدولًا لتخزين هذه البيانات.

1. اتصل بـ SQL Console:

   • في ClickHouse Cloud، اختر خدمة من القائمة المنسدلة، ثم اختر SQL Console من قائمة التنقل الجانبية اليسرى.
   • في ClickHouse ذاتية الإدارة، اتصل بـ SQL Console على https://_hostname_:8443/play. راجع مسؤول ClickHouse للحصول على التفاصيل.

2. أنشئ جدول trips التالي في قاعدة البيانات default:

   CREATE TABLE trips
   (
       `trip_id` UInt32,
       `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
       `pickup_date` Date,
       `pickup_datetime` DateTime,
       `dropoff_date` Date,
       `dropoff_datetime` DateTime,
       `store_and_fwd_flag` UInt8,
       `rate_code_id` UInt8,
       `pickup_longitude` Float64,
       `pickup_latitude` Float64,
       `dropoff_longitude` Float64,
       `dropoff_latitude` Float64,
       `passenger_count` UInt8,
       `trip_distance` Float64,
       `fare_amount` Float32,
       `extra` Float32,
       `mta_tax` Float32,
       `tip_amount` Float32,
       `tolls_amount` Float32,
       `ehail_fee` Float32,
       `improvement_surcharge` Float32,
       `total_amount` Float32,
       `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
       `trip_type` UInt8,
       `pickup` FixedString(25),
       `dropoff` FixedString(25),
       `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
       `pickup_nyct2010_gid` Int8,
       `pickup_ctlabel` Float32,
       `pickup_borocode` Int8,
       `pickup_ct2010` String,
       `pickup_boroct2010` String,
       `pickup_cdeligibil` String,
       `pickup_ntacode` FixedString(4),
       `pickup_ntaname` String,
       `pickup_puma` UInt16,
       `dropoff_nyct2010_gid` UInt8,
       `dropoff_ctlabel` Float32,
       `dropoff_borocode` UInt8,
       `dropoff_ct2010` String,
       `dropoff_boroct2010` String,
       `dropoff_cdeligibil` String,
       `dropoff_ntacode` FixedString(4),
       `dropoff_ntaname` String,
       `dropoff_puma` UInt16
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(pickup_date)
   ORDER BY pickup_datetime;

بعد إنشاء جدول، أضف بيانات سيارات الأجرة في مدينة نيويورك من ملفات CSV على S3.

1. يُدرج الأمر التالي نحو 2,000,000 صف في جدول trips من ملفين مختلفين على S3: trips_1.tsv.gz وtrips_2.tsv.gz:

   INSERT INTO trips
   SELECT * FROM s3(
       'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
       'TabSeparatedWithNames', "
       `trip_id` UInt32,
       `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
       `pickup_date` Date,
       `pickup_datetime` DateTime,
       `dropoff_date` Date,
       `dropoff_datetime` DateTime,
       `store_and_fwd_flag` UInt8,
       `rate_code_id` UInt8,
       `pickup_longitude` Float64,
       `pickup_latitude` Float64,
       `dropoff_longitude` Float64,
       `dropoff_latitude` Float64,
       `passenger_count` UInt8,
       `trip_distance` Float64,
       `fare_amount` Float32,
       `extra` Float32,
       `mta_tax` Float32,
       `tip_amount` Float32,
       `tolls_amount` Float32,
       `ehail_fee` Float32,
       `improvement_surcharge` Float32,
       `total_amount` Float32,
       `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
       `trip_type` UInt8,
       `pickup` FixedString(25),
       `dropoff` FixedString(25),
       `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
       `pickup_nyct2010_gid` Int8,
       `pickup_ctlabel` Float32,
       `pickup_borocode` Int8,
       `pickup_ct2010` String,
       `pickup_boroct2010` String,
       `pickup_cdeligibil` String,
       `pickup_ntacode` FixedString(4),
       `pickup_ntaname` String,
       `pickup_puma` UInt16,
       `dropoff_nyct2010_gid` UInt8,
       `dropoff_ctlabel` Float32,
       `dropoff_borocode` UInt8,
       `dropoff_ct2010` String,
       `dropoff_boroct2010` String,
       `dropoff_cdeligibil` String,
       `dropoff_ntacode` FixedString(4),
       `dropoff_ntaname` String,
       `dropoff_puma` UInt16
   ") SETTINGS input_format_try_infer_datetimes = 0

2. انتظر حتى يكتمل INSERT. قد يستغرق تنزيل بيانات بحجم 150 MB بعض الوقت.

3. بعد اكتمال الإدراج، تحقّق من نجاح العملية:

   SELECT count() FROM trips

   ينبغي أن يُرجع هذا الاستعلام 1,999,657 صفاً.

نفّذ بعض الاستعلامات لتحليل البيانات. استكشف الأمثلة التالية أو جرّب استعلام SQL الخاص بك.

• احسب متوسط مبلغ الإكرامية:

  SELECT round(avg(tip_amount), 2) FROM trips

  الناتج المتوقع

  ┌─round(avg(tip_amount), 2)─┐
  │                      1.68 │
  └───────────────────────────┘

• احسب متوسط التكلفة حسب عدد الركاب:

  SELECT
      passenger_count,
      ceil(avg(total_amount),2) AS average_total_amount
  FROM trips
  GROUP BY passenger_count

  المخرجات المتوقعة

  تتراوح قيم passenger_count بين 0 و9:

  ┌─passenger_count─┬─average_total_amount─┐
  │               0 │                22.69 │
  │               1 │                15.97 │
  │               2 │                17.15 │
  │               3 │                16.76 │
  │               4 │                17.33 │
  │               5 │                16.35 │
  │               6 │                16.04 │
  │               7 │                 59.8 │
  │               8 │                36.41 │
  │               9 │                 9.81 │
  └─────────────────┴──────────────────────┘

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

  SELECT
      pickup_date,
      pickup_ntaname,
      SUM(1) AS number_of_trips
  FROM trips
  GROUP BY pickup_date, pickup_ntaname
  ORDER BY pickup_date ASC

  الناتج المتوقع

  ┌─pickup_date─┬─pickup_ntaname───────────────────────────────────────────┬─number_of_trips─┐
  │  2015-07-01 │ Brooklyn Heights-Cobble Hill                             │              13 │
  │  2015-07-01 │ Old Astoria                                              │               5 │
  │  2015-07-01 │ Flushing                                                 │               1 │
  │  2015-07-01 │ Yorkville                                                │             378 │
  │  2015-07-01 │ Gramercy                                                 │             344 │
  │  2015-07-01 │ Fordham South                                            │               2 │
  │  2015-07-01 │ SoHo-TriBeCa-Civic Center-Little Italy                   │             621 │
  │  2015-07-01 │ Park Slope-Gowanus                                       │              29 │
  │  2015-07-01 │ Bushwick South                                           │               5 │

• احسب مدة كل رحلة بالدقائق، ثم جمّع النتائج حسب مدة الرحلة:

  SELECT
      avg(tip_amount) AS avg_tip,
      avg(fare_amount) AS avg_fare,
      avg(passenger_count) AS avg_passenger,
      count() AS count,
      truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
  FROM trips
  WHERE trip_minutes > 0
  GROUP BY trip_minutes
  ORDER BY trip_minutes DESC

  الناتج المتوقع

  ┌──────────────avg_tip─┬───────────avg_fare─┬──────avg_passenger─┬──count─┬─trip_minutes─┐
  │   1.9600000381469727 │                  8 │                  1 │      1 │        27511 │
  │                    0 │                 12 │                  2 │      1 │        27500 │
  │    0.542166673981895 │ 19.716666666666665 │ 1.9166666666666667 │     60 │         1439 │
  │    0.902499997522682 │ 11.270625001192093 │            1.95625 │    160 │         1438 │
  │   0.9715789457909146 │ 13.646616541353383 │ 2.0526315789473686 │    133 │         1437 │
  │   0.9682692398245518 │ 14.134615384615385 │  2.076923076923077 │    104 │         1436 │
  │   1.1022105210705808 │ 13.778947368421052 │  2.042105263157895 │     95 │         1435 │

• اعرض عدد الرحلات المنطلقة من كل حي موزعًا حسب ساعات اليوم:

  SELECT
      pickup_ntaname,
      toHour(pickup_datetime) as pickup_hour,
      SUM(1) AS pickups
  FROM trips
  WHERE pickup_ntaname != ''
  GROUP BY pickup_ntaname, pickup_hour
  ORDER BY pickup_ntaname, pickup_hour

  الناتج المتوقع

  ┌─pickup_ntaname───────────────────────────────────────────┬─pickup_hour─┬─pickups─┐
  │ Airport                                                  │           0 │    3509 │
  │ Airport                                                  │           1 │    1184 │
  │ Airport                                                  │           2 │     401 │
  │ Airport                                                  │           3 │     152 │
  │ Airport                                                  │           4 │     213 │
  │ Airport                                                  │           5 │     955 │
  │ Airport                                                  │           6 │    2161 │
  │ Airport                                                  │           7 │    3013 │
  │ Airport                                                  │           8 │    3601 │
  │ Airport                                                  │           9 │    3792 │
  │ Airport                                                  │          10 │    4546 │
  │ Airport                                                  │          11 │    4659 │
  │ Airport                                                  │          12 │    4621 │
  │ Airport                                                  │          13 │    5348 │
  │ Airport                                                  │          14 │    5889 │
  │ Airport                                                  │          15 │    6505 │
  │ Airport                                                  │          16 │    6119 │
  │ Airport                                                  │          17 │    6341 │
  │ Airport                                                  │          18 │    6173 │
  │ Airport                                                  │          19 │    6329 │
  │ Airport                                                  │          20 │    6271 │
  │ Airport                                                  │          21 │    6649 │
  │ Airport                                                  │          22 │    6356 │
  │ Airport                                                  │          23 │    6016 │
  │ Allerton-Pelham Gardens                                  │           4 │       1 │
  │ Allerton-Pelham Gardens                                  │           6 │       1 │
  │ Allerton-Pelham Gardens                                  │           7 │       1 │
  │ Allerton-Pelham Gardens                                  │           9 │       5 │
  │ Allerton-Pelham Gardens                                  │          10 │       3 │
  │ Allerton-Pelham Gardens                                  │          15 │       1 │
  │ Allerton-Pelham Gardens                                  │          20 │       2 │
  │ Allerton-Pelham Gardens                                  │          23 │       1 │
  │ Annadale-Huguenot-Prince's Bay-Eltingville               │          23 │       1 │
  │ Arden Heights                                            │          11 │       1 │

7. استرجع الرحلات المتجهة إلى مطاري LaGuardia أو JFK:

   SELECT
       pickup_datetime,
       dropoff_datetime,
       total_amount,
       pickup_nyct2010_gid,
       dropoff_nyct2010_gid,
       CASE
           WHEN dropoff_nyct2010_gid = 138 THEN 'LGA'
           WHEN dropoff_nyct2010_gid = 132 THEN 'JFK'
       END AS airport_code,
       EXTRACT(YEAR FROM pickup_datetime) AS year,
       EXTRACT(DAY FROM pickup_datetime) AS day,
       EXTRACT(HOUR FROM pickup_datetime) AS hour
   FROM trips
   WHERE dropoff_nyct2010_gid IN (132, 138)
   ORDER BY pickup_datetime

   الناتج المتوقع

   ┌─────pickup_datetime─┬────dropoff_datetime─┬─total_amount─┬─pickup_nyct2010_gid─┬─dropoff_nyct2010_gid─┬─airport_code─┬─year─┬─day─┬─hour─┐
   │ 2015-07-01 00:04:14 │ 2015-07-01 00:15:29 │         13.3 │                 -34 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:09:42 │ 2015-07-01 00:12:55 │          6.8 │                  50 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:23:04 │ 2015-07-01 00:24:39 │          4.8 │                -125 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:27:51 │ 2015-07-01 00:39:02 │        14.72 │                -101 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:32:03 │ 2015-07-01 00:55:39 │        39.34 │                  48 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:34:12 │ 2015-07-01 00:40:48 │         9.95 │                 -93 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:38:26 │ 2015-07-01 00:49:00 │         13.3 │                 -11 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:41:48 │ 2015-07-01 00:44:45 │          6.3 │                 -94 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 01:06:18 │ 2015-07-01 01:14:43 │        11.76 │                  37 │                  132 │ JFK          │ 2015 │   1 │    1 │

القاموس (dictionary) هو خريطة (mapping) لأزواج المفتاح-القيمة (key-value pairs) مخزَّنة في الذاكرة. لمزيد من التفاصيل، راجع القواميس

أنشئ قاموسًا (Dictionary) مرتبطًا بجدول في خدمة ClickHouse لديك. يستند الجدول والقاموس إلى ملف CSV يحتوي على صف لكل حي في مدينة نيويورك.

تُربَط الأحياء بأسماء البلديات الخمس لمدينة نيويورك (Bronx وBrooklyn وManhattan وQueens وStaten Island)، إضافةً إلى مطار Newark‏ (EWR).

فيما يلي مقتطف من ملف CSV الذي تستخدمه معروضًا في هيئة جدول. يقابل العمود LocationID في الملف العمودين pickup_nyct2010_gid وdropoff_nyct2010_gid في جدول trips الخاص بك:

1. نفّذ أمر SQL التالي لإنشاء قاموس باسم taxi_zone_dictionary وتعبئته من ملف CSV في S3. عنوان URL الخاص بالملف هو https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv.

CREATE DICTIONARY taxi_zone_dictionary
(
  `LocationID` UInt16 DEFAULT 0,
  `Borough` String,
  `Zone` String,
  `service_zone` String
)
PRIMARY KEY LocationID
SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(HASHED_ARRAY())

3. تحقّق من نجاح العملية. ينبغي أن يُرجع ما يلي 265 صفًا، بصف واحد لكل حي:

   SELECT * FROM taxi_zone_dictionary

4. استخدم الدالة dictGet (أو صيغها المختلفة) لاسترجاع قيمة من قاموس. مرّر اسم القاموس والقيمة المطلوبة والمفتاح (وهو في مثالنا العمود LocationID من taxi_zone_dictionary).

   على سبيل المثال، يُرجع الاستعلام التالي قيمة Borough ذات LocationID يساوي 132، والتي تشير إلى مطار JFK):

   SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)

   يقع JFK في كوينز. لاحظ أن زمن استرجاع القيمة يساوي 0 تقريبًا:

   ┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
   │ Queens                                          │
   └─────────────────────────────────────────────────┘
   
   1 rows in set. Elapsed: 0.004 sec.

5. استخدم الدالة dictHas للتحقق مما إذا كان مفتاح موجودًا في القاموس. على سبيل المثال، يُرجع الاستعلام التالي القيمة 1 (أي "صحيح" في ClickHouse):

   SELECT dictHas('taxi_zone_dictionary', 132)

6. يعرض الاستعلام التالي القيمة 0 لأن 4567 ليست ضمن قيم LocationID في القاموس:

   SELECT dictHas('taxi_zone_dictionary', 4567)

7. استخدم الدالة dictGet لاسترجاع اسم الـborough في استعلام. على سبيل المثال:

   SELECT
       count(1) AS total,
       dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
   FROM trips
   WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
   GROUP BY borough_name
   ORDER BY total DESC

   يحسب هذا الاستعلام إجمالي عدد رحلات سيارات الأجرة لكل حي إداري تنتهي في مطارَي LaGuardia أو JFK. تبدو النتيجة كما يلي، ولاحظ أن عددًا كبيرًا من الرحلات لا تُعرف فيها منطقة الانطلاق:

   ┌─total─┬─borough_name──┐
   │ 23683 │ Unknown       │
   │  7053 │ Manhattan     │
   │  6828 │ Brooklyn      │
   │  4458 │ Queens        │
   │  2670 │ Bronx         │
   │   554 │ Staten Island │
   │    53 │ EWR           │
   └───────┴───────────────┘
   
   7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)

اكتب بعض الاستعلامات التي تربط taxi_zone_dictionary بجدول trips.

1. ابدأ بعبارة JOIN بسيطة تعمل على نحو مشابه لاستعلام المطار السابق أعلاه:

   SELECT
       count(1) AS total,
       Borough
   FROM trips
   JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
   WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
   GROUP BY Borough
   ORDER BY total DESC

   تبدو الاستجابة مطابقة لاستعلام dictGet:

   ┌─total─┬─Borough───────┐
   │  7053 │ Manhattan     │
   │  6828 │ Brooklyn      │
   │  4458 │ Queens        │
   │  2670 │ Bronx         │
   │   554 │ Staten Island │
   │    53 │ EWR           │
   └───────┴───────────────┘
   
   6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)

2. يعيد هذا الاستعلام صفوف الرحلات الألف ذات أعلى إكرامية، ثم يجري ربطًا داخليًا لكل صف مع القاموس:

   SELECT *
   FROM trips
   JOIN taxi_zone_dictionary
       ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
   WHERE tip_amount > 0
   ORDER BY tip_amount DESC
   LIMIT 1000