Clickhouse - وظائف النوافذ غير الموجودة ...

بدأت العمل مع قواعد البيانات العمودية باستخدام BigQuery. عندما اضطررت إلى "الانتقال" إلى Clickhouse ، فوجئت بشكل غير سار بنقص وظائف النوافذ الكاملة. هناك ، بالطبع ، العديد من الوظائف للعمل مع المصفوفات ، ووظائف ذات رتبة أعلى ووظائف أخرى (وظيفة واحدة تعمل على تشغيلDifferenceStartingWithFirstValue تساوي الكثير). الفائز عام 1999 بلقب أطول كلمة Donaudampfschifffahrtsgesellschaftskapitänswitwe يتبادر إلى الذهن على الفور. والتي تُرجمت من الألمانية على أنها "أرملة قبطان شركة الشحن على نهر الدانوب".



لا يؤدي البحث عن "وظائف النافذة في Clickhouse" إلى نتائج مفيدة. هذه المقالة هي محاولة لتلخيص البيانات المتفرقة من الإنترنت ، والأمثلة مع ClickHouseMeetup وتجربتي الخاصة.

وظائف النافذة - بناء الجملة

اسمحوا لي أن أذكرك بصيغة وظائف النافذة ونوع النتيجة التي نحصل عليها. في الأمثلة ، سنستخدم لهجة Standart SQL في Google BigQuery. فيما يلي رابط للوثائق المتعلقة بوظائف النافذة (يطلق عليها اسم الوظيفة التحليلية في الوثائق - أصوات الترجمة الأكثر دقة مثل الوظائف التحليلية). و هنا قائمة وظائف نفسه.



تبدو البنية العامة كما يلي:

analytic_function_name ( [ argument_list ] ) OVER over_clause
over_clause:
  { named_window | ( [ window_specification ] ) }
window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]
window_frame_clause:
  { rows_range } { frame_start | frame_between }
rows_range:
  { ROWS | RANGE }

لنأخذ الأمر خطوة بخطوة:

1. يتم تطبيق وظيفة النافذة على مجموعة السجلات المحددة في تعبير over_clause ،
2. يتم تحديد مجموعة السجلات من خلال عبارة PARTITION BY. هنا يمكنك سرد واحد أو أكثر من الحقول التي سيتم من خلالها تحديد مجموعة السجلات. يعمل بشكل مشابه لـ GROUP BY.
   
   يتم تعريف فرز السجلات داخل مجموعة باستخدام ORDER BY.
3. يمكنك أيضًا تقييد مجموعة محددة مسبقًا من السجلات كنافذة. يمكن تحديد النافذة بشكل ثابت. على سبيل المثال ، يمكنك أن تأخذ 5 سجلات كنافذة ، 2 قبل السجل الحالي و 2 بعد السجل الحالي والسجل الحالي نفسه. سيبدو كالتالي: الصفوف بين 2 سابقًا و 2 يليهما.
   
   مثال على بناء لتحديد نافذة محددة ديناميكيًا يبدو كالتالي - RANGE BETWEEN UNBNDED PRECEDING AND CURRENT ROW. يحدد هذا البناء نافذة من السجل الأول إلى السجل الحالي وفقًا لترتيب الفرز المحدد.

كمثال ، ضع في اعتبارك حساب المبلغ التراكمي (مثال من الوثائق):

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

نتيجة:

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

ما الذي يمكن عمله في Clickhouse

لنحاول تكرار هذا المثال في ClickHouse. وبطبيعة الحال، ClickHouse لديه runningAccumulate ، arrayCumSum، و ظائف groupArrayMovingSum . ولكن في الحالة الأولى ، تحتاج إلى تحديد الحالة في استعلام فرعي ( مزيد من التفاصيل ) ، وفي الحالة الثانية ، تُرجع الدالة مصفوفة ، والتي تحتاج بعد ذلك إلى التوسيع.



سنقوم ببناء الاستعلام الأكثر عمومية. قد يبدو الطلب نفسه كما يلي:

SELECT
   items,
   summ as purchases,
   category,
   sumArray(cum_summ) as total_purchases
FROM (SELECT
         category,
         groupArray(item) AS items,
         groupArray(purchases) AS summ,
         arrayMap(x -> arraySlice(summ, 1, x), arrayEnumerate(summ)) AS cum_summ
     FROM (SELECT
               item,
               purchases,
               category
           FROM produce
           ORDER BY category, purchases)
     GROUP BY category)
   ARRAY JOIN items, summ, cum_summ
GROUP BY category, items, summ
ORDER BY category, purchases

لنأخذ الأمر خطوة بخطوة:

1. أولاً ، نقوم بإنشاء استعلام فرعي ، يتم من خلاله فرز البيانات المطلوبة (فئة ORDER BY ، المشتريات). يجب أن يتطابق الفرز مع الحقول الموجودة في تعبيري PARTITION BY و ORDER BY لوظيفة النافذة.
2. , , PARTITION BY. item .
   
   purchases , summ .
3. — ArrayMap. , func arr.
   
   arr — [1, 2, …, length(summ)], arrayEnumerate.
   
   func — arraySlice(summ, 1, x), x — arr, . summ x. , cum_sum , , .
   
   
   
   ArrayMap arrayEnumerate , , . ( 3), ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.
   
   
   
   

   arrayMap(x -> arraySlice(summ, if(x-1 > 0, x-1, 1), if(x-1 > 0, 3, 2)), arrayEnumerate(summ))

   
   
   , . 2 ClickHouse:
   
   
   
   
   • — [edited] — . [/edited]. , , arrayMap arrayFilter. . — ( — ) (alias) arrayMap, arrayFilter .
   • — . , , arrayReverse arraySlice.
   
   
   
4. تتمثل الخطوة الأخيرة في توسيع المصفوفات إلى جدول باستخدام ARRAY JOIN. نحتاج أيضًا إلى تطبيق دالة المجموع الكلي مع معدل -Array (نتيجة لذلك ، تبدو وظيفة التجميع مثل sumArray) على النتيجة التي يتم إرجاعها بواسطة وظيفة ArrayMap.

انتاج |

من الممكن محاكاة تشغيل وظائف النافذة في ClickHouse. ليس سريعًا جدًا وليست جميلة جدًا. باختصار ، يتكون خط الأنابيب من 3 خطوات:

1. الاستعلام المصنف. تعمل هذه الخطوة على تحضير مجموعة السجلات.
2. التجميع في المصفوفات وتنفيذ عمليات الصفيف. تحدد هذه الخطوة نافذة وظيفة النافذة.
3. التوسيع مرة أخرى إلى الجدول باستخدام الدالات التجميعية.