סריקת שורות SQL לעמודות: מדריך מקיף

מה זה אומר לשנות שורות לעמודות ב־SQL?

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

שקול את הדוגמה הבאה: יש לי טבלה עם עסקאות מכירות יומיות, וכל שורה רשומה את התאריך, שם המוצר וסכום המכירות.

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

מסורתית, פעולות סיבוב דרשו שאילתות SQL מורכבות עם אגירת תנאים. במהלך הזמן, יישומי SQL חלו שינויים, עם רבים מבסיסי הנתונים המודרניים כיום כוללים אופרטורים PIVOT ו־UNPIVOT שמאפשרים המרות יעילות וישירות יותר.

הבנת שורות סיבוב SQL לעמודות

פעולת הסיבוב ב־SQL משנה נתונים על ידי המרת ערכי שורה לעמודות. הבא הוא התחביר והמבנה הבסיסי של סיבוב SQL עם החלקים הבאים:

• SELECT: ההצהרה SELECT מתייחסת לעמודות המתאימות להחזרה בטבלת הסיבוב של SQL.

• תת-שאילתה: התת-שאילתה מכילה את מקור הנתונים או הטבלה שיש לכלול בטבלת הציר של SQL.

• ציר: אופרטור ה-PIVOT מכיל את הקיבוצים והמסננים שיש להחיל על טבלת הציר.

-- בחירת עמודות סטטיות ועמודות מקורפצות
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- שאילתת משנה המגדירה את המידע המקורי לציר
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- פונקציית קיבוץ שמתקבלת על העמודה של הערך ויוצרת עמודות חדשות
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

נבחן את הדוגמה שלב אחרי שלב הבאה כדי להדגים כיצד להמיר שורות לעמודות ב-SQL. שקול את טבלת ה-SalesData למטה.

דוגמה לטבלה להמרה באמצעות אופרטור SQL PIVOT. תמונה מאת המחבר.

אני רוצה להמיר נתונים אלו כדי להשוות את מכירות היומיות של כל מוצר. אני אתחיל על ידי בחירת השאילתה המשנית שתמבנה את אופרטור ה־PIVOT.

-- שאילתה משנית המגדירה נתוני מקור עבור הפיבוט
SELECT Date, Product, Sales
FROM SalesData;

כעת, אשתמש באופרטור PIVOT כדי להמיר ערכי Product לעמודות ולגורם את Sales באמצעות האופרטור SUM.

-- בחר תאריך ועמודות מפוטרות עבור כל מוצר
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- שאילתה משנית לאחזור תאריך, מוצר, ועמודות מכירות
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- אגרגציה של מכירות לפי מוצר, מבצעת פיבוט של ערכי מוצר לעמודות
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

המרת פלט דוגמתי באמצעות שורות פיבוט SQL לעמודות. תמונה מאת המחבר.

בעוד שהצבת נתונים מפשטת את סיכום הנתונים, טכניקה זו עשויה להיות עם בעיות פוטנציאליות. האתגרים הפוטנציאליים עם הצבת נתונים ב־SQL ואיך לטפל בהם הם:

• שמות עמודה דינמיים: כאשר ערכי ההצבה (לדוגמה, סוגי מוצרים) אינם ידועים, קידוד קבוע של שמות עמודות לא יעבוד. חלק ממסדי הנתונים, כמו SQL Server, תומכים ב־SQL דינמי עם תהליכי אחסון כדי למנוע את הבעיה הזו, בעוד שאחרים דורשים טיפול בזה בשכבת היישום.

• התמודדות עם ערכים NULL: כאשר אין נתונים עבור עמודה מסוימת שעברה היטב, התוצאה עשויה לכלול NULL. ניתן להשתמש ב־COALESCE כדי להחליף את ערכי NULL באפס או במציין אחר.

• התאמה בין מסדי נתונים: לא כל מסדי הנתונים תומכים ישירות באופרטור PIVOT. ניתן להשיג תוצאות דומות באמצעות הצהרות CASE וצבירה תנאית אם הדיאלקט של ה-SQL שלך אינו תומך בכך.

שורות Pivot ב- SQL לעמודות: דוגמאות ושימושים

שיטות שונות משמשות לסיבוב נתונים ב־SQL, תלוי במסד הנתונים שבו משתמשים או בדרישות אחרות. בעוד האופרטור PIVOT נמצא בשימוש נרחב ב־SQL Server, שיטות אחרות, כמו הצהרות CASE, מאפשרות המרות במסד הנתונים בצורה דומה ללא תמיכה ישירה ב־PIVOT. אני אספר על שתי השיטות הנפוצות לסיבוב נתונים ב־SQL, ואדבר על היתרונות והחסרונות.

שימוש באופרטור PIVOT

האופרטור PIVOT, הזמין ב־SQL Server, מספק דרך ישירה לסיבוב שורות לעמודות על ידי ציון פונקציית כוח והגדרת העמודות לסיבוב.

שקול את הטבלה הבאה בשם sales_data.

דוגמה לטבלת הזמנות להמרה באמצעות אופרטור PIVOT. תמונה מאת המחבר.

אני אשתמש באופרטור PIVOT כדי לגולל את הנתונים כך שסכום ההכנסות של כל שנה יוצג בעמודות.

-- השתמש בPIVOT לגילול ההכנסות לפי שנה
SELECT *
FROM (
    -- בחר את העמודות הרלוונטיות מטבלת המקור
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- גלל את ההכנסות לכל שנה
    SUM(sales_revenue)
    -- צור עמודות עבור כל שנה
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

דוגמה להפיכת פלט באמצעות PIVOT בSQL. תמונה על ידי המחבר.

השימוש באופרטור PIVOT כולל יתרונות ומגבלות הבאות:

• יתרונות: השיטה יעילה כאשר העמודות מאופשרות. יש בה תחביר קריא ופשוט יותר.

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

פיבוט ידני עם הצהרות CASE

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

לדוגמה, ניתן לבצע פיבוט ידני של נתונים בטבלת sales_data עם הצהרות CASE.

-- איגוד הכנסות מכירות לפי שנה באמצעות הצהרות CASE
SELECT 
    -- חישוב סך כל הכנסות מכירות לכל שנה
    SUM(CASE WHEN sale_year = 2020 THEN sales_revenue ELSE 0 END) AS sales_2020,
    SUM(CASE WHEN sale_year = 2021 THEN sales_revenue ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN sale_year = 2022 THEN sales_revenue ELSE 0 END) AS sales_2022,
    SUM(CASE WHEN sale_year = 2023 THEN sales_revenue ELSE 0 END) AS sales_2023
FROM 
    sales_data;

דוגמה להמרת פלט באמצעות הצהרת CASE ב־SQL. תמונה מאת המחבר.

שימוש בהצהרת CASE לצורך המרה כוללת את היתרונות והמגבלות הבאים:

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

• הגבלות: שאילתות עשויות להפוך למורכבות וארוכות אם יש הרבה עמודות לצירופים. עקב הבדיקות התנאיות הרבות, השיטה עשוייה לרוץ מעט איטי יותר מהאופרטור PIVOT.

שיקולי ביצועים בעת צירופים שורות לעמודות

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

שיטות מומלצות

הנה שיטות המומלצות לשיפור הביצועים ולייעול השאילתות שלך.

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

• להימנע מפיבוטים מקוננים: עריכת מספר פעולות פיבוט בשאילתה אחת יכולה להיות קשה לקריאה ולביצוע איטי יותר. פשוט על ידי פיצול השאילתה לחלקים או בשימוש בטבלה זמנית.

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

מניעת מכשולים נפוצים

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

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

• שימוש ב-SQL דינמי עבור פיבוטים תדירים: שימוש ב-SQL דינמי יכול להאט את ביצועי המערכת עקב רקומפילציה של השאילתה. כדי למנוע בעיה זו, יש לשמור או להגביל פיבוטים דינמיים לסקנריואים מסוימים ולשקול טיפול בעמודות דינמיות בשכבת היישום כשזה אפשרי.

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

• ערכים חסרים בעמודות שהופכות: פעולות של הפיכה תמיד מייצרות ערכים NULL כאשר אין נתונים עבור עמודה ספציפית. אלו עשויים להאט את השאילתות ולהקשות על פרשנות התוצאות. כדי למנוע בעיה זו, ניתן להשתמש בפונקציות כמו COALESCE כדי להחליף ערכים NULL בערך ברירת מחדל.

• בדיקה עם דוגמאות נתונים בלבד: שאילתות פיבוט עשויות להתנהג באופן שונה עם קבצי נתונים גדולים עקב דרישות זיכרון ועיבוד גבוהות. תמיד כדאי לבדוק שאילתות פיבוט על נתונים אמיתיים או דוגמאות נתונים מייצגות כדי להעריך בדיוק את ההשפעה על ביצועים.

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

יישומים ספציפיים לבסיסי נתונים

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

SQL Server

SQL Server מספק את אופרטור ה־PIVOT המובנה, שהוא ישיר כאשר משנים שורות לעמודות. אופרטור ה־PIVOT קל לשימוש ומשתלב עם פונקציות הגיבורה העוצמתיות של SQL Server. תכונות המפיכה ב־SQL כוללות את הבאות:

• תמיכה ישירה ב־PIVOT ו־UNPIVOT: אופרטור ה־PIVOT של SQL Server מאפשר שינוי מהיר של שורה לעמודה. אופרטור ה־UNPIVOT יכול גם להפוך את התהליך הזה.

• אפשרויות איגוד: אופרטור ה־PIVOT מאפשר פונקציות איגוד שונות, כמו SUM, COUNT, ו־AVG.

ההגבלה של אופרטור ה־PIVOT ב־SQL Server היא שהערכים של העמודות המתוצקים חייבים להיות ידועים מראש, מה שהופך אותו לפחות גמיש עבור נתונים שמשתנים דינמית.

בדוגמה למטה, אופרטור ה־PIVOT ממיר ערכי Product לתוך עמודות ומגדיר את Sales באמצעות האופרטור SUM.

-- בחירת תאריך ועמודות מנופחות עבור כל מוצר
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- שאילתה משנית לאחזור תאריך, מוצר ועמודות מכירות
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- איגוד מכירות לפי מוצר, מנפח ערכי מוצר לעמודות
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

אני ממליץ לך לקחת את הקורס Introduction to SQL Server של DataCamp כדי לשלט ביסודות של SQL Server לצורכי ניתוח נתונים.

MySQL

ב־MySQL חסרת תמיכה טבעית באופרטור ה־PIVOT. בכל זאת, אפשר להשתמש בהצהרת CASE כדי להמיר שורות לעמודות באופן ידני ולשלב פונקציות כוללות אחרות כמו SUM, AVG, ו־COUNT. אף על פי ששיטה זו גמישה, היא יכולה להפוך למורכבת אם יש לך הרבה עמודות להמרה לעמודות.

השאילתה למטה משיגה את אותה הפלט כמו בדוגמת PIVOT של SQL Server על ידי אגירה תנאית של מכירות עבור כל מוצר באמצעות ההצהרה CASE.

-- בחר תאריך ועמודות מצורבות לכל מוצר
SELECT 
    Date,
    -- השתמש ב־CASE כדי ליצור עמודה עבור מכירות מחשב נייד ועכבר
    SUM(CASE WHEN Product = 'Laptop' THEN Sales ELSE 0 END) AS Laptop,
    SUM(CASE WHEN Product = 'Mouse' THEN Sales ELSE 0 END) AS Mouse
FROM SalesData
GROUP BY Date;

אורקל

אורקל תומך באופרטור PIVOT, שמאפשר את ההמרה הישירה של שורות לעמודות. כמו ב־SQL Server, עליך לציין באופן פרטני את העמודות להמרה.

בשאילתה למטה, האופרטור PIVOT ממיר ערכי ProductName לעמודות ומגיר ערכי SalesAmount באמצעות האופרטור SUM.

SELECT *
FROM (
    -- בחירת נתוני מקור
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- אגירת מכירות לפי מוצר, יצירת עמודות מצורבות
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

דוגמה על שינוי הפלט באמצעות אופרטור PIVOT ב-Oracle. תמונה מאת המחבר.

טכניקות מתקדמות להמרת שורות לעמודות ב-SQL

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

פיבוט דינמי

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

נניח שיש לנו טבלת SalesData ואנו יכולים ליצור Pivot דינמי שמתאים אם מוצרים חדשים מתווספים. בשאילתה למטה, @columns בונה דינמית את רשימת העמודות הפיבוט, ו־sp_executesql מפעיל את ה־SQL שנוצר.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- שלב 1: יצירת רשימת מוצרים ייחודים לפיבוט
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- שלב 2: בניית שאילתת SQL דינמית
SET @sql = N'
SELECT Date, ' + @columns + '
FROM 
    (SELECT Date, Product, Sales FROM SalesData) AS source
PIVOT
(
    SUM(Sales)
    FOR Product IN (' + @columns + ')
) AS pivot_table;';
-- שלב 3: ביצוע ה־SQL הדינמי
EXEC sp_executesql @sql;

טיפול במספר עמודות

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

בדוגמה למטה, פיבוטתי את עמודות Sales ו־Quantity לפי Product.

-- צירוף מכירות וכמות למחשב נייד ועכבר לפי תאריך
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- צירוף עבור מכירות
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Sales FROM SalesData) AS source
        PIVOT
            (SUM(Sales) FOR Product IN ([Laptop], [Mouse])) AS pivot_sales
    ) p1
JOIN
    (
        -- צירוף עבור כמות
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Quantity FROM SalesData) AS source
        PIVOT
            (SUM(Quantity) FOR Product IN ([Laptop], [Mouse])) AS pivot_quantity
    ) p2
ON p1.Date = p2.Date;

דוגמה על ייצוג פלט של עמודות מרובות באמצעות אופרטור PIVOT ב-SQL. תמונה מאת המחבר.

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

מסקנה

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

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