אם אתם עובדים עם דאטה ביום יום, יש סיכוי טוב שאתם כבר מסתדרים מצוין עם SELECT, WHERE, JOIN ו-GROUP BY. אתם יודעים לשלוף טבלאות, לסנן, לסכם ולהצמיד טבלאות. אבל בשלב מסוים מרגישים תקרה: השאלות העסקיות נהיות מורכבות יותר, השאילתות מתארכות, ומתחילים להסתבך עם תתי שאילתות שקשה להבין גם יום אחרי שכתבתם אותן.
כאן נכנסות לתמונה שתי יכולות מתקדמות שהופכות את החיים של Data Analyst להרבה יותר קלים ועוצמתיים:
Common Table Expressions (CTEs) ו-Window Functions.
במאמר הזה נעשה סדר, נבין מה כל אחת מהטכניקות האלה נותנת לנו, נראה דוגמאות קונקרטיות שנוגעות לעבודה היומיומית של דאטה אנליסט, ונראה איך מחברים את הכל יחד ל-workflow נקי, קריא ומקצועי.
בסוף המאמר מחכה לכם גם כיוון ברור לצעד הבא בלימודים ובקריירה.
רענון קצר: איפה נגמר ה"SQL בסיסי" ומתחיל המתקדם?
כמעט כל דאטה אנליסט מתחיל עובר דרך התחנה הזו:
SELECT – שליפה של עמודות רלוונטיות
FROM – בחירת הטבלאות
WHERE – סינון רשומות לפי תנאים
JOIN – חיבור בין טבלאות על בסיס מפתחות
GROUP BY ו-HAVING – סיכום נתונים (COUNT, SUM, AVG וכו')
Subqueries – שאילתה בתוך שאילתה לצורך סינון או חישוב נוסף
הכלים האלה מספיקים לשאילתות בסיסיות ובינוניות. אבל כשצריך, למשל:
לחשב מדדים לאורך זמן לכל לקוח
להראות גם את השורה וגם את הממוצע של הקבוצה
לבצע חישובים צעד אחר צעד בצורה קריאה
לדרג רשומות בתוך קבוצה (Top N לכל קטגוריה)
מתחילים להרגיש את הצורך בשלב הבא: Window Functions ו-CTEs.
מה זה CTE ולמה זה משנה לדאטה אנליסט?
CTE, Common Table Expression, הוא בעצם "תת שאילתה עם שם" שנכתבת מעל ה-SELECT הראשי. הוא מאפשר לכם לפרק שאילתה מורכבת לשלבים הגיוניים, לקרוא לכל שלב בשם ולהתייחס אליו כאילו הייתה זו טבלה זמנית.
הסינטקס הבסיסי:
WITH cleaned_orders AS (
SELECT
order_id,
customer_id,
order_date,
amount
FROM raw_orders
WHERE status = 'COMPLETED'
AND amount > 0
)
SELECT *
FROM cleaned_orders;
מה הרווחתם כאן?
קריאות: במקום תת שאילתה ארוכה בתוך FROM, יש "טבלה" בשם cleaned_orders
עבודה בשלבים: קודם מנקים, אחר כך מחשבים
שימוש חוזר: אפשר להגדיר כמה CTEs ולחבר ביניהם
CTE לעבודה שלב אחר שלב
דוגמה לתהליך ניתוח קלאסי:
ניקוי טבלת הזמנות
חישוב סכומי רכישה ללקוח
סימון לקוחות "גדולים" מול "רגילים"
WITH cleaned_orders AS (
SELECT
order_id,
customer_id,
order_date,
amount
FROM raw_orders
WHERE status = 'COMPLETED'
),
customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM cleaned_orders
GROUP BY customer_id
)
SELECT
customer_id,
total_amount,
CASE
WHEN total_amount >= 10000 THEN 'VIP'
ELSE 'REGULAR'
END AS customer_segment
FROM customer_totals;
במקום מפלצת של תתי שאילתות מקוננות, יש שלושה שלבים ברורים, כמו שהייתם מסבירים את הלוגיקה באנליזה עסקית.
כמה CTEs במקביל
אפשר להגדיר מספר CTEs אחד אחרי השני ואז לחבר ביניהם:
WITH
monthly_orders AS (
SELECT
customer_id,
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS monthly_amount
FROM cleaned_orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
customer_avg AS (
SELECT
customer_id,
AVG(monthly_amount) AS avg_monthly_amount
FROM monthly_orders
GROUP BY customer_id
)
SELECT *
FROM customer_avg;
CTE רקורסיבי: היררכיות או טבלאות תאריכים
CTE יכול להיות גם רקורסיבי, כלומר לקרוא לעצמו. זה שימושי למשל לבניית טבלת Calendar בלי טבלת תאריכים קיימת, או לניווט בהיררכיות (מנהל → עובד → עובד).
דוגמה פשוטה ליצירת טבלת תאריכים:
WITH RECURSIVE calendar AS (
SELECT DATE '2025-01-01' AS d
UNION ALL
SELECT d + INTERVAL '1 day'
FROM calendar
WHERE d < DATE '2025-12-31'
)
SELECT *
FROM calendar;
כדאטה אנליסט, זה נותן לכם שליטה מלאה על שלבי העבודה, במיוחד כשיש הרבה לוגיקה לפני שמגיעים לדוח הסופי.
Window Functions: לראות גם את השורה וגם את התמונה הגדולה
פונקציות אגרגציה רגילות (כמו SUM או AVG) מחזירות שורה אחת לכל קבוצה. Window Functions מחזירות ערך לכל שורה, אבל תוך הסתכלות על קבוצה רחבה יותר, חלון.
הסינטקס הכללי:
<function>() OVER (
PARTITION BY <עמודות לחלוקה לקבוצות>
ORDER BY <עמודה לסדר בתוך הקבוצה>
)
PARTITION BY: על מי עובדים ביחד (למשל לפי customer_id או לפי city)
ORDER BY: סדר כרונולוגי או עסקי בתוך כל קבוצה (למשל לפי order_date)
פונקציות דירוג: ROW_NUMBER, RANK, DENSE_RANK
Top N מוצרים בכל קטגוריה
נניח שיש לכם טבלת מכירות לפי מוצר וקטגוריה:
SELECT
category,
product_name,
SUM(amount) AS total_sales
FROM sales
GROUP BY category, product_name;
עכשיו אתם רוצים שלושת המוצרים המובילים בכל קטגוריה. כאן נכנסת ROW_NUMBER:
WITH product_totals AS (
SELECT
category,
product_name,
SUM(amount) AS total_sales
FROM sales
GROUP BY category, product_name
),
ranked_products AS (
SELECT
category,
product_name,
total_sales,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY total_sales DESC
) AS rn
FROM product_totals
)
SELECT *
FROM ranked_products
WHERE rn <= 3;
ROW_NUMBER נותנת מספר רץ בלי קשר לשוויון.
RANK נותנת אותו דירוג לערכים זהים ומדלגת במספר הבא.
DENSE_RANK נותנת אותו דירוג לערכים זהים בלי לדלג במספר הבא.
השימוש הזה מצוין בדוחות: דירוג עובדים לפי ביצועים, חנויות לפי מכירות, קמפיינים לפי ROI ועוד.
LAG ו-LEAD: השוואה לפרק זמן קודם או הבא
כאן נכנסים הטרנדים. פונקציות כמו LAG ו-LEAD מאפשרות להשוות כל שורה לשורה הקודמת או הבאה בחלון.
שינוי מול החודש הקודם לכל לקוח
WITH monthly AS (
SELECT
customer_id,
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS monthly_revenue
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT
customer_id,
month,
monthly_revenue,
LAG(monthly_revenue) OVER (
PARTITION BY customer_id
ORDER BY month
) AS prev_month_revenue,
monthly_revenue
– LAG(monthly_revenue) OVER (
PARTITION BY customer_id
ORDER BY month
) AS diff_from_prev
FROM monthly;
כאן אתם מקבלים לכל לקוח את הסכום החודשי, את סכום החודש הקודם ואת ההפרש ביניהם.
מכאן אפשר להמשיך למדדים נוספים כמו אחוז שינוי, זיהוי צניחות חריגות ואיתור לקוחות בסיכון נטישה.
FIRST_VALUE, LAST_VALUE ו-סכומים/ממוצעים עם חלון: מדדי ריצה ו-rolling
מדד ריצה: מכירות מצטברות לאורך השנה
SELECT
order_date,
SUM(amount) AS daily_amount,
SUM(SUM(amount)) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_total
FROM orders
GROUP BY order_date
ORDER BY order_date;
ה-SUM הפנימי הוא סיכום יומי. ה-SUM עם OVER יוצר מדד ריצה, כמה מכרנו מתחילת התקופה ועד כל יום.
ממוצע נע של שבעה ימים
SELECT
order_date,
SUM(amount) AS daily_amount,
AVG(SUM(amount)) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7_days
FROM orders
GROUP BY order_date
ORDER BY order_date;
זה מדד מאוד נפוץ לעולם האונליין: החלקת רעשים יומיים והסתכלות על מגמה אמיתית.
FIRST_VALUE ו-LAST_VALUE
דוגמה: לראות לכל לקוח מה היה סכום הרכישה הראשונה, מה היה סכום הרכישה האחרונה ומה הפער ביניהם.
WITH customer_orders AS (
SELECT
customer_id,
order_date,
amount,
FIRST_VALUE(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS first_amount,
LAST_VALUE(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS last_amount
FROM orders
)
SELECT DISTINCT
customer_id,
first_amount,
last_amount,
last_amount – first_amount AS diff
FROM customer_orders;
חשוב לשים לב: LAST_VALUE בלי הגדרת חלון מתאימה עלול להחזיר תוצאה פחות אינטואיטיבית. לכן מגדירים במפורש את טווח השורות.
Paging עם ROW_NUMBER: חלוקת תוצאות לעמודים
בממשקי Web, BI או בחלוקה לעמודים, משתמשים הרבה ב-ROW_NUMBER כדי לייצר עמודים.
WITH ordered_customers AS (
SELECT
customer_id,
first_name,
last_name,
ROW_NUMBER() OVER (
ORDER BY created_at DESC
) AS rn
FROM customers
)
SELECT *
FROM ordered_customers
WHERE rn BETWEEN 51 AND 100;
כאן rn בין 51 ל-100 מייצג את עמוד 2 אם גודל העמוד הוא 50.
שילוב CTEs ו-Window Functions: workflow אמיתי של Data Analyst
נחבר הכל לדוגמה קצת יותר קרובה לחיי היום יום.
מטרה: להבין מי הלקוחות החזקים בחצי השנה האחרונה, מה חלקם בכלל ההכנסות ומה קורה עם הטרנד שלהם.
שלבים:
- ניקוי הזמנות רלוונטיות
- חישוב סכום חודשי לכל לקוח
- חישוב סך כל ההכנסות בחצי השנה
- חישוב אחוז מכלל ההכנסה ודינמיקת שינוי
WITH cleaned_orders AS (
SELECT
customer_id,
order_date,
amount
FROM orders
WHERE status = 'COMPLETED'
AND order_date >= CURRENT_DATE – INTERVAL '6 months'
),
monthly AS (
SELECT
customer_id,
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS monthly_revenue
FROM cleaned_orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
customer_total AS (
SELECT
customer_id,
SUM(monthly_revenue) AS total_6m_revenue
FROM monthly
GROUP BY customer_id
),
total_revenue AS (
SELECT SUM(total_6m_revenue) AS total
FROM customer_total
)
SELECT
c.customer_id,
c.total_6m_revenue,
ROUND(
100.0 * c.total_6m_revenue / t.total,
2
) AS percent_of_total,
m.month,
m.monthly_revenue,
LAG(m.monthly_revenue) OVER (
PARTITION BY m.customer_id
ORDER BY m.month
) AS prev_month_revenue,
m.monthly_revenue
– LAG(m.monthly_revenue) OVER (
PARTITION BY m.customer_id
ORDER BY m.month
) AS diff_from_prev
FROM customer_total c
JOIN total_revenue t ON 1 = 1
JOIN monthly m ON m.customer_id = c.customer_id
WHERE c.total_6m_revenue >= 5000
ORDER BY c.total_6m_revenue DESC, m.month;
התוצאה היא טבלת אנליזה שמאפשרת לכם לזהות לקוחות מפתח, להבין מה חלקם בעוגת ההכנסות ולראות עבור כל אחד אם המגמה חיובית או שלילית.
במקום עשרות שורות קוד בשפה אחרת או לוגיקה מפוזרת באקסל, הכל מרוכז בשאילתה אחת קריאה ומסודרת.
טיפים חשובים, ביצועים וטעויות נפוצות
כדי לעבוד נכון עם CTEs ו-Window Functions כדאי לשים לב לכמה נקודות:
תמיד לשאול מה ה-PARTITION הנכון: לפי לקוח, לפי מוצר, לפי אזור גיאוגרפי ועוד
תמיד לציין ORDER BY בתוך ה-OVER כשיש משמעות לזמן או לסדר
לשים לב לערכי NULL: בעבודה עם LAG ו-LEAD השורה הראשונה בכל קבוצה תחזיר NULL, לפעמים כדאי לעטוף ב-COALESCE
לא להגזים בכמות CTEs: אם השאילתה נהיית מסובכת מדי, אולי צריך לפרק אותה לשתי שאילתות נפרדות או לטבלה חומרית (Materialized View)
לבדוק Execution Plan בטבלאות ענק כדי לוודא שהשאילתה רצה בצורה יעילה
לא לפחד להוסיף שכבת ביניים אם זה משפר קריאות. עדיף קוד קריא שקל לתחזק מאשר "טריק קצר" שאף אחד לא יבין עוד חודש
איך לתרגל ולהכניס את זה לתיק עבודות?
כדי שהכלים האלה יהפכו לחלק טבעי מהעבודה שלכם:
קחו דוחות קיימים שאתם מכירים ונסו לכתוב אותם מחדש עם CTEs ו-Window Functions במקום תתי שאילתות מסורבלות
תתרגלו על בסיסי נתונים פתוחים כמו AdventureWorks או Northwind
נסו לבנות use cases אמיתיים:
funnel אנליטי לפי שלבי מוצר
cohort analysis לפי חודש הצטרפות
מדדי ריצה ו-moving averages לקמפיינים שיווקיים
שלבו את ה-SQL בכלי BI (Power BI, Tableau) או בפייתון, ותראו איך הלוגיקה מתורגמת לדשבורדים ותובנות
תיק עבודות שמכיל דוגמאות של CTEs ו-Window Functions ברמת דאטה אנליסט מראה למעסיק שאתם יודעים להתמודד עם דאטה מורכב באמת.
הצעד הבא: להפוך את SQL לכלי עבודה יומיומי ולא רק "שפה"
אם הגעתם עד לכאן, כנראה שאתם מבינים ש-SQL לדאטה אנליסטים זה הרבה יותר מ-SELECT ו-JOIN. CTEs ו-Window Functions הם הכלים שמבדילים בין מי ש"כותב שאילתות" לבין אנליסט שיודע לבנות מודלים אנליטיים נקיים, מדויקים וברי תחזוקה.
בקורס דאטה אנליסט של מכללת ג’ון ברייס לוקחים את כל מה שדיברנו עליו כאן צעד קדימה. עובדים על בסיסי נתונים אמיתיים, מתרגלים CTEs, Window Functions, שילוב SQL עם Python וכלי BI, ולומדים איך להפוך שאלות עסקיות מורכבות לשאילתות ברורות שמייצרות תובנות.
במקום ללמוד כל טכניקה בנפרד, אתם בונים תהליך מלא: איסוף נתונים, ניקוי, ניתוח, ויזואליזציה והצגת תובנות למקבלי החלטות.
בעידן שבו הארגונים עוברים יותר ויותר ל-Data Driven, שליטה ב-SQL מתקדם היא אחד הנכסים הכי חזקים שכל Data Analyst יכול להביא לשולחן.
