التحديد الديناميكي للأعمدة

تُعدّ ميزة التحديد الديناميكي للأعمدة من ميزات ClickHouse القوية لكنها غير مستغلة بما يكفي، إذ تتيح لك تحديد الأعمدة باستخدام التعبيرات النمطية بدلًا من تسمية كل عمود على حدة. ويمكنك أيضًا تطبيق الدوال على الأعمدة المطابقة باستخدام المُعدِّل APPLY، مما يجعلها مفيدة للغاية في مهام تحليل البيانات وتحويلها.

سنتعلّم كيفية استخدام هذه الميزة بالاستعانة بـمجموعة بيانات سيارات الأجرة في نيويورك، والتي يمكنك العثور عليها أيضًا في ClickHouse SQL playground.

تحديد الأعمدة التي تطابق نمطًا

لنبدأ بحالة شائعة: تحديد الأعمدة التي تحتوي على _amount فقط من مجموعة بيانات سيارات الأجرة في مدينة نيويورك. بدلًا من إدخال اسم كل عمود يدويًا، يمكننا استخدام التعبير COLUMNS مع تعبير منتظم:

FROM nyc_taxi.trips
SELECT COLUMNS('.*_amount')
LIMIT 10;

جرّب هذا الاستعلام في SQL playground

يعرض هذا الاستعلام أول 10 صفوف، ولكن للأعمدة فقط التي تتطابق أسماؤها مع النمط .*_amount (أي أحرف متبوعة بـ "_amount").

    ┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┐
 1. │           9 │          0 │            0 │          9.8 │
 2. │           9 │          0 │            0 │          9.8 │
 3. │         3.5 │          0 │            0 │          4.8 │
 4. │         3.5 │          0 │            0 │          4.8 │
 5. │         3.5 │          0 │            0 │          4.3 │
 6. │         3.5 │          0 │            0 │          4.3 │
 7. │         2.5 │          0 │            0 │          3.8 │
 8. │         2.5 │          0 │            0 │          3.8 │
 9. │           5 │          0 │            0 │          5.8 │
10. │           5 │          0 │            0 │          5.8 │
    └─────────────┴────────────┴──────────────┴──────────────┘

لنفترض أننا نريد أيضًا إرجاع الأعمدة التي تتضمن المصطلحين fee أو tax. يمكننا تحديث التعبير النمطي ليشملهما أيضًا:

SELECT COLUMNS('.*_amount|fee|tax')
FROM nyc_taxi.trips
ORDER BY rand() 
LIMIT 3;

جرّب هذا الاستعلام في SQL playground

   ┌─fare_amount─┬─mta_tax─┬─tip_amount─┬─tolls_amount─┬─ehail_fee─┬─total_amount─┐
1. │           5 │     0.5 │          1 │            0 │         0 │          7.8 │
2. │        12.5 │     0.5 │          0 │            0 │         0 │         13.8 │
3. │         4.5 │     0.5 │       1.66 │            0 │         0 │         9.96 │
   └─────────────┴─────────┴────────────┴──────────────┴───────────┴──────────────┘

اختيار أنماط متعددة

يمكننا دمج عدة أنماط للأعمدة ضمن استعلام واحد:

SELECT 
    COLUMNS('.*_amount'),
    COLUMNS('.*_date.*')
FROM nyc_taxi.trips
LIMIT 5;

جرّب هذا الاستعلام في SQL playground

   ┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┬─pickup_date─┬─────pickup_datetime─┬─dropoff_date─┬────dropoff_datetime─┐
1. │           9 │          0 │            0 │          9.8 │  2001-01-01 │ 2001-01-01 00:01:48 │   2001-01-01 │ 2001-01-01 00:15:47 │
2. │           9 │          0 │            0 │          9.8 │  2001-01-01 │ 2001-01-01 00:01:48 │   2001-01-01 │ 2001-01-01 00:15:47 │
3. │         3.5 │          0 │            0 │          4.8 │  2001-01-01 │ 2001-01-01 00:02:08 │   2001-01-01 │ 2001-01-01 01:00:02 │
4. │         3.5 │          0 │            0 │          4.8 │  2001-01-01 │ 2001-01-01 00:02:08 │   2001-01-01 │ 2001-01-01 01:00:02 │
5. │         3.5 │          0 │            0 │          4.3 │  2001-01-01 │ 2001-01-01 00:02:26 │   2001-01-01 │ 2001-01-01 00:04:49 │
   └─────────────┴────────────┴──────────────┴──────────────┴─────────────┴─────────────────────┴──────────────┴─────────────────────┘

تطبيق الدوال على جميع الأعمدة

يمكننا أيضًا استخدام المُعدِّل APPLY لتطبيق الدوال على جميع الأعمدة. على سبيل المثال، إذا أردنا العثور على القيمة القصوى لكل واحد من تلك الأعمدة، فيمكننا تنفيذ الاستعلام التالي:

SELECT COLUMNS('.*_amount|fee|tax') APPLY(max)
FROM nyc_taxi.trips;

جرّب هذا الاستعلام في SQL playground

   ┌─max(fare_amount)─┬─max(mta_tax)─┬─max(tip_amount)─┬─max(tolls_amount)─┬─max(ehail_fee)─┬─max(total_amount)─┐
1. │           998310 │     500000.5 │       3950588.8 │           7999.92 │           1.95 │         3950611.5 │
   └──────────────────┴──────────────┴─────────────────┴───────────────────┴────────────────┴───────────────────┘

أو ربما نرغب في رؤية المتوسط بدلًا من ذلك:

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg)
FROM nyc_taxi.trips

جرّب هذا الاستعلام في SQL playground

   ┌─avg(fare_amount)─┬───────avg(mta_tax)─┬────avg(tip_amount)─┬──avg(tolls_amount)─┬──────avg(ehail_fee)─┬──avg(total_amount)─┐
1. │ 11.8044154834777 │ 0.4555942672733423 │ 1.3469850969211845 │ 0.2256511991414463 │ 3.37600560437412e-9 │ 14.423323722271563 │
   └──────────────────┴────────────────────┴────────────────────┴────────────────────┴─────────────────────┴────────────────────┘

تحتوي هذه القيم على الكثير من المنازل العشرية، لكن لحسن الحظ يمكننا معالجة ذلك بربط الدوال معًا. في هذه الحالة، سنستخدم الدالة avg، تليها الدالة round:

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(round)
FROM nyc_taxi.trips;

جرّب هذا الاستعلام في SQL playground

   ┌─round(avg(fare_amount))─┬─round(avg(mta_tax))─┬─round(avg(tip_amount))─┬─round(avg(tolls_amount))─┬─round(avg(ehail_fee))─┬─round(avg(total_amount))─┐
1. │                      12 │                   0 │                      1 │                        0 │                     0 │                       14 │
   └─────────────────────────┴─────────────────────┴────────────────────────┴──────────────────────────┴───────────────────────┴──────────────────────────┘

لكن ذلك يقرّب المتوسطات إلى أعداد صحيحة. وإذا أردنا التقريب إلى منزلتين عشريتين مثلًا، فيمكننا فعل ذلك أيضًا. وإلى جانب قبول الدوال، فإن المُعدِّل APPLY يقبل أيضًا تعبير لامبدا، مما يمنحنا المرونة لجعل الدالة round تقرّب قيم المتوسط إلى منزلتين عشريتين:

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(x -> round(x, 2))
FROM nyc_taxi.trips;

جرّب هذا الاستعلام في SQL playground

   ┌─round(avg(fare_amount), 2)─┬─round(avg(mta_tax), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(tolls_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(total_amount), 2)─┐
1. │                       11.8 │                   0.46 │                      1.35 │                        0.23 │                        0 │                       14.42 │
   └────────────────────────────┴────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴─────────────────────────────┘

استبدال الأعمدة

الأمور تسير على ما يرام حتى الآن. لكن لنفترض أننا نريد تعديل إحدى القيم مع إبقاء القيم الأخرى كما هي. على سبيل المثال، قد نرغب في مضاعفة المبلغ الإجمالي وقسمة ضريبة MTA على 1.1. يمكننا فعل ذلك باستخدام المُعدِّل REPLACE، الذي يستبدل عمودًا مع إبقاء الأعمدة الأخرى كما هي.

FROM nyc_taxi.trips 
SELECT 
  COLUMNS('.*_amount|fee|tax')
  REPLACE(
    total_amount*2 AS total_amount,
    mta_tax/1.1 AS mta_tax
  ) 
  APPLY(avg)
  APPLY(col -> round(col, 2));

جرّب هذا الاستعلام في SQL playground

   ┌─round(avg(fare_amount), 2)─┬─round(avg(di⋯, 1.1)), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(tolls_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(mu⋯nt, 2)), 2)─┐
1. │                       11.8 │                     0.41 │                      1.35 │                        0.23 │                        0 │                    28.85 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴──────────────────────────┘

استبعاد الأعمدة

يمكننا أيضًا استبعاد حقل باستخدام المُعدِّل EXCEPT. على سبيل المثال، لإزالة العمود tolls_amount، نكتب الاستعلام التالي:

FROM nyc_taxi.trips 
SELECT 
  COLUMNS('.*_amount|fee|tax') EXCEPT(tolls_amount)
  REPLACE(
    total_amount*2 AS total_amount,
    mta_tax/1.1 AS mta_tax
  ) 
  APPLY(avg)
  APPLY(col -> round(col, 2));

جرّب هذا الاستعلام في SQL playground

   ┌─round(avg(fare_amount), 2)─┬─round(avg(di⋯, 1.1)), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(mu⋯nt, 2)), 2)─┐
1. │                       11.8 │                     0.41 │                      1.35 │                        0 │                    28.85 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┴──────────────────────────┘