ClickHouse: פונקציות Windows מאפס

מדריכים
Pandas

ClickHouse היא מערכת ניהול מסדי נתונים ממיושמת בצורה מקבילית, מבוססת עמודות, ומותאמת במיוחד לעבודות אנליטיות. זהו מוצר פתוח המפתח על ידי Yandex, חברת מנוע חיפוש. אחת המאפיינים המרכזיים של ClickHouse היא התמיכה בפונקציות אנליטיות מתקדמות, כולל פונקציות חלון.

פונקציות החלון הוצגו לראשונה בשנות ה-90 של המאה הקודמת על ידי SQL Server, ומאז הפכו לתכונה סטנדרטית במסדי נתונים רומנציה רבים, כולל ClickHouse. היום, פונקציות החלון הן כלי חיוני למحللי נתונים ומפתחים ומשמשות באופן נרחב בתעשיות רבות.

פונקציות אלו, המכונות גם פונקציות אנליטיות, הן סוג של פונקציות המבצעות חישובים על סלולרי חלון של שורות. הן משמשות לביצוע של סוגים שונים של ניתוח על קבוצות נתונים, כגון חישוב סכומים מתקדמים, ממוצעים נעים ודירוגים. פונקציות החלון הן כלי עוצמתי לניתוח נתונים ויכולות לפשט באופן משמעותי כתיבת שאילתות מורכבות.

ClickHouse תומכת במגוון רחב של פונקציות חלון, כולל פונקציות מובנות עבור דירוג, אחוז דירוג, התפלגות מצטברת, מספר שורה, ו-סכומים מתקדמים. בנוסף, היא גם תומכת בפונקציות חלון מוגדרות על ידי המשתמש, המאפשרות למשתמשים ליצור פונקציות מUSTOM עבור מקרים שימושים ספציפיים.

במאמר זה, אציג את המושג של פונקציות חלונים ואספק סקירה מקיפה של הפונקציות החלוניות הזמינות ב-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];

בואו נפרק כל חלק של התחביר:

  1. [column_list]: זהו רשימת העמודות שאתה רוצה להחזיר בשאילתה.
  2. [windows_function_name]([argument_list]): זהו שם הפונקציה חלון שברצונך להשתמש ורשימת הארגומנטים לפונקציה זו.
  3. AS [alias_name]: פירוט זה הוא אופציונלי ומשמש לתת שם זהות לפלט של הפונקציה חלון.
  4. 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.

  1. 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 כדי לציין את הטווח של השורות עבור פונקציית החלון לפעול עליהם.
  2. 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.

  1. SUM(sales) OVER (PARTITION BY product_name ORDER BY date): פונקציית Windows זו מחשבת את הסכום המתגבש של המכירות לכל מוצר על ידי סיכום המכירות עבור כל שורה, מחולק על פי שם המוצר, ומסודר על פי התאריך. מסגרת החלון מוגדרת עם PARTITION BY product_name לחלוקת הנתונים לחלקים על פי שם המוצר, וORDER BY date לציון הסדר שבו פונקציית החלון מעבדת את השורות.
  2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC): פונקציית Windows זו מחשבת את דירוג כל מוצר בקטגוריה שלו על פי המכירות הכוללות. פונקציית 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 #2:

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
)

  1. ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank: פונקציית החלון זו מקצה דירוג ייחודי לכל מטופל על פי סכום המתקדם של ימי ההשהיה שלהם. הפונקציה מקצה דירוג לכל שורה על פי הסדר המוצג עם ORDER BY running_total_days DESC, מה שאומר שהמטופלים עם הסכום המתקדם הגבוה ביותר של ימי ההשהיה יקבלו את הדירוג הנמוך ביותר. פונקציית ROW_NUMBER() היא פונקציה מובנית ב-ClickHouse המקצה מספר ייחודי לכל שורה בתוך מסגרת החלון המוגדרת.
  2. (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