סיבוב טבלה על ציר - ilDBA Portal

סיבוב טבלה על ציר

15/09/2011 | פורסם על ידי

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

נתראה ברשומה הבאה!
זהר אלקיים

לרשומה המקורית בבלוג של זהר: סיבוב טבלה על ציר.

The following two tabs change content below.
זהר אלקיים הוא Oracle DBA מאז 1998 ויועץ בכיר מאז 2007 ובעל תואר Oracle ACE Associate. הוא מתמחה בתשתיות בסיסי נתונים, ארכיטקטורה ושיפור ביצועים. זהר הוא גם מדריך, מרצה בכנסים ובלוגר באתר www.realdbamagic.com מאז 2010. כיום הוא משמש כיום כ-CTO בחברת בריליקס ומנהל את האתר ilDBA.

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

הפונקציה COALESCE

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף [...]

טיפ: איך לזכור ב-SQLPLUS שאתה בסביבת היצור

כמה פעמים קרה לנו שעבדנו עם sqlplus  מול סביבת יצור ביחד עם עוד כמה חלונות פתוחים ועם קצת לחץ? בקיצור כרגיל… מכוון שהכול כל כך לחוץ, הרצנו איזה פקודה הרסנית, ומיד הצטערנו עליה מאוד: אוי! הרצתי [...]
עבודה

עבודה תחת סביבת FireWall

אורי לרנר בטיפ קצר ושימושי על עבודה בסביבת אורקל [...]
Indexes

Indexes in SQL Server 2

ערן שקד ממשיך בסדרת מאמרים על אינדקסים בSQL [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: