בעיה נפוצה שלא היה לה פתרון פשוט על ידי פקודה עד גרסה 11 היא סיבוב טבלה על ציר. בגרסה 11 ישנו פתרון יעיל ומהיר לבעיה על ידי צמד פקודות: Pivot ו-Unpivot שלא מצריך ניפנופי ידיים והסברים על מימוש שליפה שתבצע את זה.

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

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

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

EMP_NAME             PROD_NAME            AMOUNT
-------------------- -------------------- ----------------------
emp1                 prod1                11
emp1                 prod2                22
emp1                 prod3                33
emp2                 prod1                111
emp2                 prod2                222
emp2                 prod3                333
emp3                 prod1                1111
emp3                 prod2                2222
emp3                 prod3                3333

PIVOT

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

כאמור, עד לגרסה 11 היו לנו כמה פתרונות שהעיקרי שבינהם היה שימוש ב-case כדי להזיז את הנתונים מרמת שורה לעמודה:

select emp_name,
sum(case when prod_name ='prod1' then
                        amount else null end) prod1,
sum(case when prod_name ='prod2' then
                        amount else null end) prod2,
sum(case when prod_name ='prod3' then
                        amount else null end) prod3
 from zelkayam2.test_model_pivot
group by emp_name;

פתרון אחר ומסובך לכתיבה והבנה אך מהיר יותר בביצועים היה שימוש בפקודת model:

select emp_name, prod1_sales_sum, prod2_sales_sum, prod3_sales_sum
from zelkayam2.test_model_pivot
model return updated rows
dimension by (emp_name, prod_name)
measures ( 0 prod1_sales_sum,
           0 prod2_sales_sum,
           0 prod3_sales_sum,
           AMOUNT)
rules upsert all
          (
            prod1_sales_sum[any,'na'] = amount[cv(),'prod1'],
            prod2_sales_sum[any,'na'] = amount[cv(),'prod2'],
            prod3_sales_sum[any,'na'] = amount[cv(),'prod3']
          );

החל מגרסה 11 אנו יכולים להשתמש בפקודה החדשה PIVOT כדי לבצע את אותה פונקציונליות בצורה פשוטה (וקצרה) בהרבה:

SELECT *
  FROM zelkayam2.test_model_pivot
  PIVOT (SUM (amount)
             FOR prod_name IN  ('prod1' AS prod1_sales_sum,
                                            'prod2' AS prod2_sales_sum,
                                            'prod3' AS prod3_sales_sum));

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     9 |   333 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY PIVOT|                  |     9 |   333 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | TEST_MODEL_PIVOT |     9 |   333 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

שורה 3 מגדירה מה יהיה הערך שאותו נשים בעמודות.
שורה 4 מגדירה לפי איזה עמודה נבצע את הפירוק לעמודות.
שורות 4-6 מגדירות את הערכים שלפיהם נשים את הערכים בעמודות החדשות.

כמובן ששלושת המקרים מחזירים את אותה התוצאה:

EMP_NAME             PROD1_SALES_SUM        PROD2_SALES_SUM        PROD3_SALES_SUM
-------------------- ---------------------- ---------------------- ----------------------
emp3                 1111                   2222                   3333
emp1                 11                     22                     33
emp2                 111                    222                    333

UNPIVOT

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

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

SELECT *
FROM
  ( 
  SELECT emp_name, 'prod1' AS prod_name, prod1_sales_sum FROM zelkayam2.test_model_unpivot
  UNION ALL
  SELECT emp_name, 'prod2' AS prod_name, prod2_sales_sum FROM zelkayam2.test_model_unpivot
  UNION ALL
  SELECT emp_name, 'prod3' AS prod_name, prod3_sales_sum FROM zelkayam2.test_model_unpivot
  );

------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |     9 |   288 |     9   (0)| 00:00:01 |
|   1 |  VIEW               |                    |     9 |   288 |     9   (0)| 00:00:01 |
|   2 |   UNION-ALL         |                    |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST_MODEL_UNPIVOT |     3 |    75 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_MODEL_UNPIVOT |     3 |    75 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| TEST_MODEL_UNPIVOT |     3 |    75 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

או משתמשים במכפלה קרטזית כדי לחסוך סריקות (אבל לא זכרון):

select emp_name,
case 
  when b.lv = 1 then 'prod1'
  when b.lv = 2 then 'prod2'
  when b.lv = 3 then 'prod3' end prod_name,
case 
  when b.lv = 1 then a.prod1_sales_sum 
  when b.lv = 2 then a.prod2_sales_sum 
  when b.lv = 3 then a.prod3_sales_sum end prod_sales_sum
from zelkayam2.test_model_unpivot a, (select level lv from dual connect by level <= 3) b;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     3 |   192 |     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN          |                    |     3 |   192 |     5   (0)| 00:00:01 |
|   2 |   VIEW                         |                    |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    CONNECT BY WITHOUT FILTERING|                    |       |       |            |          |
|   4 |     FAST DUAL                  |                    |     1 |       |     2   (0)| 00:00:01 |
|   5 |   BUFFER SORT                  |                    |     3 |   153 |     5   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL           | TEST_MODEL_UNPIVOT |     3 |   153 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(LEVEL<=3)

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

select emp_name ,prod_name, amount
from zelkayam2.test_model_unpivot 
model 
return updated rows
dimension by (emp_name, 'prod_name' prod_name)
measures (prod1_sales_sum, prod2_sales_sum, prod3_sales_sum, 0 amount)
rules upsert all
(
    amount[any,'prod1'] = prod1_sales_sum[cv(),'prod_name'],
    amount[any,'prod2'] = prod2_sales_sum[cv(),'prod_name'],
    amount[any,'prod3'] = prod3_sales_sum[cv(),'prod_name']
);

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     3 |   153 |     3   (0)| 00:00:01 |
|   1 |  SQL MODEL ORDERED |                    |     3 |   153 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_MODEL_UNPIVOT |     3 |   153 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

SELECT *
  FROM zelkayam2.test_model_unpivot 
  UNPIVOT (amount 
                             FOR prod_name IN (prod1_sales_sum AS 'prod1',
							   prod2_sales_sum AS 'prod2',
							   prod3_sales_sum AS 'prod3'));

------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |     9 |   288 |     9   (0)| 00:00:01 |
|*  1 |  VIEW               |                    |     9 |   288 |     9   (0)| 00:00:01 |
|   2 |   UNPIVOT           |                    |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST_MODEL_UNPIVOT |     3 |   153 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("unpivot_view_006"."AMOUNT" IS NOT NULL)

שורה 3 מגדירה את העמודה החדשה שבה יהיו הנתונים (amount).
שורה 4 מגדירה את העמודה החדשה שבה יהיו הנתונים החותכים (prod_name).
שורות 4-6 מגדירות את הערכים שיחליפו כל אחת מהעמודות שמסתובבות על הציר.

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