ClickHouse: وظائف Windows من الصفر

Pandas

ClickHouse هو نظام إدارة قواعد بيانات محوسب وموزع على حد سواء، يتم تحسينه للحسابات التحليلية، ويعتمد التخزين على الأعمدة. إنه منتج مفتوح ال原始码 تم تطويره بواسطة Yandex، شركة محرك بحث. من أهم ميزات ClickHouse هو دعمه للوظائف التحليلية المتقدمة، بما في ذلك وظائف النافذة.

تم تقديم وظائف النافذة لأول مرة في أواخر التسعينيات بواسطة SQL Server، ومنذ ذلك الحين، أصبحت ميزة قياسية في العديد من قواعد البيانات العلائقية، بما في ذلك ClickHouse. اليوم، تعتبر وظائف النافذة أداة لا غنى عنها لمحللي البيانات والمطورين وتستخدم على نطاق واسع في العديد من الصناعات.

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

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

في هذه المقالة، سأقدم مفهوم الوظائف النافذة وأقدم نظرة شاملة على الوظائف النافذة المتاحة في ClickHouse. سأقدم أيضًا أمثلة على كيفية استخدام هذه الوظائف في السيناريوهات العالمية الحقيقية. هذه المقالة مخصصة للمطورين ذوي الخبرة الذين يعرفون بالفعل SQL ويرغبون في تعلم المزيد عن الوظائف النافذة في ClickHouse.

أمثلة عالمية حقيقية على استخدام الوظائف النافذة

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

تحليل المالية

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

E-commerce Analytics

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

تحليلات الصحة

تتيح وظائف Windows في مجال الرعاية الصحية معالجة معلومات المرضى، مثل علامات حيوية، نتائج الفحوصات الطبية، واستخدام الأدوية. على سبيل المثال، يمكن للمطورين استخدام وظائف Windows لحساب المتوسط المتحرك لمعدل ضربات قلب المريض، المجموع المتراكم لجرعات الأدوية للمريض، وترتيب المرضى بناءً على نتائج الفحوصات الطبية.

هذه مجرد بعض الأمثلة على السيناريوهات العملية العديدة حيث يمكن للمطورين استخدام وظائف Windows. المشكل الأساسي هو أن وظائف Windows يمكن استخدامها لإجراء تحليلات متقدمة على مجموعات بيانات واسعة ويمكن أن تسهم في تبسيط كتابة استعلامات معقدة.

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

في ClickHouse، يتم استخدام وظائف النافذة في فئة SELECT من الاستعلام لإجراء حسابات على مجموعة من الصفوف. بناء جملة الأساسي لاستخدام وظيفة النافذة في ClickHouse هو كما يلي:

SQL

SELECT
  [column_list],
  [windows_function_name]([argument_list])
    OVER ([PARTITION BY [partition_column_list]]
         [ORDER BY [order_column_list]]
         [ROWS [BETWEEN [start_offset] AND [end_offset]]])
  AS [alias_name]
FROM [table_name];

دعونا نفصل كل جزء من البناء التالي:

[column_list]: هذه هي قائمة الأعمدة التي ترغب في إرجاعها في الاستعلام.
[windows_function_name]([argument_list]): هذا هو اسم وظيفة النافذة التي ترغب في استخدامها وقائمة الوسائط لتلك الوظيفة.
AS [alias_name]: هذه الفئة اختيارية وتستخدم لتعيين اسم مستعار لناتج وظيفة النافذة.
OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]]): هذا هو تحديد إطار النافذة لوظيفة النافذة.

PARTITION BY [partition_column_list]: يعد هذا الجزء اختياريًا ويقسم مجموعة النتائج إلى قسمين بناءً على القيم في الأعمدة المحددة.
ORDER BY [order_column_list]: يجب أن يكون هذا الجزء إلزاميًا لتحديد ترتيب معالجة الصفوف من قبل الدالة النافذة.
ROWS [BETWEEN [start_offset] AND [end_offset]]: يعد هذا الجزء اختياريًا ويستخدم لتحديد نطاق الصفوف التي تعمل عليها الدالة النافذة. يمكن أن تكون start_offset و end_offset أعدادًا صحيحة إيجابية أو سلبية أو قيم خاصة مثل UNBOUNDED PRECEDING أو CURRENT ROW.

إليك مثالًا على استخدام دالة نافذة في ClickHouse:

SQL

SELECT
  date,
  product_id,
  sales,
  SUM(sales) OVER (PARTITION BY product_id ORDER BY date) AS running_total
FROM sales_data;

I use the SUM windows function to calculate the running total of sales for each product, grouped by the product_id column. The window frame is specified with PARTITION BY product_id to divide the result set into partitions based on the product_id and ORDER BY date to specify the order in which the windows function processes the rows. The output of the windows function is given an alias name running_total.

من المهم ملاحظة أن الدوال النافذة في ClickHouse يمكن استخدامها فقط في جزء SELECT من الاستعلام ولا يمكن استخدامها في جزء WHERE أو HAVING. علاوة على ذلك، يمكن دمج الدوال النافذة مع وظائف أخرى، مثل الوظائف التجميعية، لإجراء تحليلات بيانات أكثر تعقيدًا.

التحليل المالي باستخدام الدوال النافذة

في صناعة المال، تتبع أداء الاستثمارات على مر الزمن أمر حاسم لاتخاذ القرارات. يمكن للدوال النافذة في ClickHouse إجراء تحليلات متقدمة للبيانات المالية، مثل حساب المتوسطات المتحركة والمجموع الجاري للعائد اليومي على الاستثمار.

لنفترض أن لدينا جدول بيانات لأسعار الأسهم اليومية لسهم واحد. نهدف إلى حساب المتوسط المتحرك لمدة 50 يومًا للسعر المغلق والمجموع الجاري لعائد الاستثمار اليومي.

توليد البيانات:

SQL

CREATE TABLE stock_prices (
  date Date,
  symbol String,
  open Float32,
  close Float32,
  high Float32,
  low Float32,
  volume UInt64
) ENGINE = MergeTree(date, (symbol, date), 8192);

INSERT INTO stock_prices
SELECT
  toDate('yyyy-MM-dd', d),
  'AAAA',
  rand(),
  rand(),
  rand(),
  rand(),
  rand() * 100000
FROM generateDates('2022-01-01', '2023-02-10') d;

I create a table stock_prices to store daily stock prices for the symbol AAAA. I then insert randomly generated data into the table for the years 2022–2023.

طلب SQL:

SQL

SELECT
  date,
  symbol,
  close,
  AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS moving_average,
  SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100 AS running_return
FROM stock_prices
WHERE symbol = 'AAAA';

I use windows functions to perform financial analysis on the stock price data.

AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW): تقوم هذه الدالة النافذة بحساب متوسط سعر الإغلاق لمدة 50 يومًا عن طريق أخذ متوسط 50 صفًا قبل الصف الحالي والصف الحالي نفسه (أو أقل إذا كان عدد الأيام أقل من 50)، مصنفة حسب التاريخ. يتم تحديد الإطار النافذة باستخدام ORDER BY date لتحديد الترتيب الذي تعالج به الدالة النافذة الصفوف و ROWS BETWEEN 49 PRECEDING AND CURRENT ROW لتحديد نطاق الصفوف التي يجب أن تعمل عليها الدالة النافذة.
SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100: تقوم هذه الدالة النافذة بحساب المجموع المتداول لعائد الاستثمار اليومي عن طريق جمع العوائد اليومية، والتي يتم حسابها كفرق بين سعر الإغلاق الحالي وسعر الإغلاق السابق مقسومًا على سعر الإغلاق السابق. يتم استخدام الدالة lag لاسترداد قيمة صف سابق في نفس الفرز، ويتم تحديد الإطار النافذة باستخدام ORDER BY date لضمان حساب العوائد بالترتيب الصحيح.

يعرض نتائج الاستعلام تاريخ الصفقة، الرمز، سعر الإغلاق، متوسط السعر المتحرك لمدة 50 يومًا، والمجموع المتداول لعائد الاستثمار اليومي للرمز AAAA.

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

E-commerce Analytics With Windows Functions

تحليل بيانات المبيعات في صناعة التجارة الإلكترونية أمر حاسم لفهم سلوك العملاء واتخاذ قرارات تجارية صائبة. قد تقوم وظائف Windows في ClickHouse بتحليل بيانات التجارة الإلكترونية المعقدة، مثل حساب المجاميع المتراكمة وترتيب المنتجات حسب المبيعات.

لنتخيل أن لدينا جدول بيانات المبيعات اليومية لموقع تجارة واحد فقط. لترتيب المنتجات حسب المبيعات الإجمالية، سنحسب المبيعات المتراكمة.

توليد البيانات:

SQL

CREATE TABLE sales_data (
  date Date,
  product_name String,
  product_category String,
  sales UInt64
) ENGINE = MergeTree(date, (product_name, date), 8192);

INSERT INTO sales_data
SELECT
  toDate('yyyy-MM-dd', d),
  'Product ' || toString(intDiv(rand() * 100, 1)),
  'Category ' || toString(intDiv(rand() * 5, 1)),
  rand() * 1000
FROM generateDates('2022-01-01', '2023-02-10') d;

I create a table sales_data to store daily sales data for a single e-commerce store. I then insert randomly generated data into the table for the years 2022–2023.

طلب SQL:

SQL

SELECT
  product_name,
  product_category,
  SUM(sales) OVER (PARTITION BY product_name ORDER BY date) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC) AS rank
FROM sales_data;

I use windows functions to perform e-commerce analytics on sales data.

SUM(sales) OVER (PARTITION BY product_name ORDER BY date): تقوم هذه الوظيفة الزمنية بحساب المبيعات المتراكمة لكل منتج عن طريق جمع المبيعات لكل صف، مقسمة حسب اسم المنتج، ومرتبة حسب التاريخ. تم تحديد الإطار الزمني باستخدام PARTITION BY product_name لتقسيم البيانات إلى أقسام حسب اسم المنتج، و ORDER BY date لتحديد ترتيب معالجة الصفوف بواسطة الوظيفة الزمنية.
ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC): تقوم هذه الوظيفة الزمنية بحساب رتبة كل منتج داخل فئته حسب إجمالي مبيعاته. تولد الدالة ROW_NUMBER رقمًا فريدًا لكل صف داخل قسم، وتم تحديد الإطار الزمني باستخدام PARTITION BY product_category لتقسيم البيانات إلى أقسام حسب فئة المنتج، و ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC لترتيب البيانات داخل كل قسم بترتيب تنازلي حسب المبيعات المتراكمة.

إنتاج الاستعلام يعيد اسم المنتج وفئة المنتج والمجموع المتتالي للمبيعات والترتيب لكل منتج حسب إجمالي مبيعاته داخل فئة المنتج.

باستخدام وظائف النوافذ في ClickHouse، يمكن لمحللي التجارة الإلكترونية إجراء تحليل معقد لبيانات المبيعات في الوقت الحقيقي واتخاذ قرارات مستنيرة بناءً على النتائج.

تحليل الصحة مع وظائف النوافذ

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

ضع في اعتبارك السيناريو التالي: لدينا جدول بيانات للمرضى في مستشفى يشمل بيئة المرضى والتاريخ الطبي والعلاج الحالي. نهدف إلى تحديد المجموع المتتالي لأيام الإقامة لكل مريض وترتيبهم حسب إجمالي أيام الإقامة الكلية.

توليفة البيانات:

SQL

CREATE TABLE patient_data (
  admission_date Date,
  discharge_date Date,
  patient_id String,
  age UInt16,
  gender String,
  condition String
) ENGINE = MergeTree(admission_date, (patient_id, admission_date), 8192);

INSERT INTO patient_data
SELECT
  toDate('yyyy-MM-dd', d1),
  toDate('yyyy-MM-dd', d2),
  'Patient ' || toString(intDiv(rand() * 10000, 1)),
  rand() % 90 + 10,
  if(rand() % 2 = 0, 'Male', 'Female'),
  'Condition ' || toString(intDiv(rand() * 100, 1))
FROM generateDates('2022-01-01', '2023-02-10') d1
JOIN generateDates('2022-01-01', '2023-02-10') d2 ON d1 <= d2;

I create a table patient_data to store patient data for a hospital. I then inserted randomly generated data into the table for the years 2022–2023. Each row represents a patient’s hospitalization, including the admission date, discharge date, patient ID, age, gender, and medical condition.

طلب SQL رقم 1:

SQL

SELECT
  patient_id,
  age,
  gender,
  condition,
  SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date) AS running_total_days
FROM patient_data;

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

SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date): تقوم هذه الدالة النافذة بحساب المجموع المتجاوز لأيام الإقامة في المستشفى لكل مريض عن طريق جمع عدد الأيام بين تاريخ الالتحاق وتاريخ الإخراج لكل سطر، مقسمة حسب معرّف المريض، ومرتبة حسب تاريخ الالتحاق. يتم تحديد الإطار النافذ مع PARTITION BY patient_id لتقسيم البيانات إلى قسم بناءً على معرّف المريض و ORDER BY admission_date لتحديد الترتيب الذي تعالج به الدالة النافذة الصفوف.

تعطي نتائج الاستعلام معرّف المريض والعمر والجنس والحالة والمجموع المتجاوز لأيام تواجده في المستشفى.

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

طلب SQL الثاني:

SQL

SELECT
  patient_id,
  age,
  gender,
  condition,
  running_total_days,
  ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank
FROM (
  SELECT
    patient_id,
    age,
    gender,
    condition,
    SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date) AS running_total_days
  FROM patient_data
)

ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank: تقوم هذه الدالة النافذة بتعيين رتبة فريدة لكل مريض بناءً على مجموعهم المتجاوز لأيام الإقامة في المستشفى. تعيّن الدالة رتبة لكل سطر بناءً على الترتيب المحدد مع ORDER BY running_total_days DESC، مما يعني أن المرضى ذوي المجموع المتجاوز الأعلى لأيام الإقامة في المستشفى سيُعطى الرتبة الأدنى. تعد ROW_NUMBER() دالة مدمجة في ClickHouse تعين رقم فريد لكل سطر داخل الإطار المحدد النافذ.
(SELECT ...): تقوم الاستعلام الداخلي بحساب المجموع المتراكم لأيام البره المرضية لكل مريض باستخدام وظيفة SUM النافذة. ثم يتم استخدام نتيجة الاستعلام الداخلي كمدخلات للاستعلام الخارجي، حيث يتم تطبيق وظيفة ROW_NUMBER النافذة لترتيب المرضى حسب إجمالي أيام البره المرضية لديهم.

توفر نتائج الاستعلام رتبة كل مريض وعمره وجنسه وحالته الصحية والمجموع المتراكم لأيام البره.

فيما يلي عينة من البيانات التي يمكن أن يولدها هذا الاستعلام:

لتصور نتائج الاستعلام، يمكنك استخدام أدوات تصور البيانات المختلفة مثل Matplotlib و Seaborn و Plotly وغيرها في Python. فيما يلي مثال على كيفية تصور نتائج الاستعلام باستخدام Matplotlib:

Python

import matplotlib.pyplot as plt
import pandas as pd
from sqlalchemy import create_engine

# تأسيس اتصال بقاعدة البيانات ClickHouse
engine = create_engine("clickhouse://:/")

# تنفيذ الاستعلام SQL وتخزين النتائج في قالب Pandas DataFrame
df = pd.read_sql_query("", engine)

# رسم النتائج باستخدام رسم بياني شريطي
plt.bar(df['patient_id'], df['running_total_days'], color=df['rank'])
plt.xlabel("Patient ID")
plt.ylabel("Running Total of Hospitalization Days")
plt.title("Healthcare Analytics with Windows Functions in ClickHouse")
plt.show()

في هذا الكود، أستخدم أولاً وظيفة create_engine من مكتبة SQLAlchemy لتأسيس اتصال بقاعدة البيانات ClickHouse. ثم يتم استخدام وظيفة read_sql_query لتنفيذ الاستعلام SQL وتخزين النتائج في قالب Pandas DataFrame. أخيراً، يتم إنشاء الرسم الشريطي باستخدام وظيفة bar من مكتبة Matplotlib، حيث تمثل المحور السيني رقم المريض، والمحور الصادي يمثل المجموع المتراكم لأيام البره المرضية، ولون كل شريط يمثل رتبة المريض.

I successfully used ClickHouse’s windows functions in those examples to evaluate healthcare data and rank patients based on their total hospitalization days. This analysis can uncover patterns and trends in patient data, which can help to inform clinical decision-making and improve patient outcomes.

خاتمة

أخيرًا، مع ClickHouse، تعد دالات النوافذ أداة قوية لتحليل البيانات الشامل وإجراءات التجميع. تسمح للمطورين بإجراء حسابات معقدة ضمن الاستعلام، مثل المجاميع المتناقصة، الترتيب، والنسب المئوية، والتي ستتطلب عادة استعلامات متعددة أو حتى معالجة البيانات المسبقة. قد تسهم دالات النوافذ بشكل كبير في تسهيل تحليل البيانات والتجميع عن طريق توفير آلية موجزة وسريعة لإجراء هذه الحسابات.

ومع ذلك، يجب أن نتذكر أن دالات النوافذ قد تكون مكلفة من الناحية الحسابية، خاصة مع مجموعات البيانات الكبيرة. يمكن تقليل هذا من خلال استخدام المؤشرات المناسبة وبناء الاستعلامات بحكمة. ومع ذلك، من المهم للغاية فهم آثار أداء دالات النوافذ واستخدامها باعتدال.

Source:
https://dzone.com/articles/clickhouse-windows-functions-from-scratch