عملگر PIVOT و UNPIVOT در پایگاه‌داده اراکل نسخه 11g

عملگر PIVOT و UNPIVOT در پایگاه‌داده اراکل نسخه ۱۱g

سعی بر آن است تا در این نوشتار چگونگی استفاده از عملگرهای جدید PIVOT و UNPIVOT در نسخه ۱۱g به همراه ارائه یک راهکار مناسب برای عملکرد مشابه در نسخه‌های قبل از ۱۱g شرح داده شود.

PIVOT
عملگر PIVOT داده‌ها را از سطرهای مختلف می‌گیرد، پس از جمع‌آوری آن‌ها را به ستون مبدل می‌سازد. برای مشاهده نحوه عملکرد این عملگر ما به ایجاد یک جدول تست نیاز داریم.

CREATE TABLE pivot_test (

id NUMBER,

customer_id NUMBER,

product_code VARCHAR2(5),

quantity NUMBER

);

INSERT INTO pivot_test VALUES (1, 1, ‘A’, 10);

INSERT INTO pivot_test VALUES (2, 1, ‘B’, 20);

INSERT INTO pivot_test VALUES (3, 1, ‘C’, 30);

INSERT INTO pivot_test VALUES (4, 2, ‘A’, 40);

INSERT INTO pivot_test VALUES (5, 2, ‘C’, 50);

INSERT INTO pivot_test VALUES (6, 3, ‘A’, 60);

INSERT INTO pivot_test VALUES (7, 3, ‘B’, 70);

INSERT INTO pivot_test VALUES (8, 3, ‘C’, 80);

INSERT INTO pivot_test VALUES (9, 3, ‘D’, 90);

INSERT INTO pivot_test VALUES (10, 4, ‘A’, 100);

COMMIT;

بنابراین داده‌های تستی ما به شکل زیر ایجاد می‌شود.

SELECT * FROM pivot_test;

ID CUSTOMER_ID PRODU QUANTITY

———- ———– —– ———-

۱ ۱ A 10

۲ ۱ B 20

۳ ۱ C 30

۴ ۲ A 40

۵ ۲ C 50

۶ ۳ A 60

۷ ۳ B 70

۸ ۳ C 80

۹ ۳ D 90

۱۰ ۴ A 100

۱۰ rows selected.

SQL>

در وضعیت اولیه عملگر PIVOT کاملاً محدود است. ما ملزم به استفاده از عبارت IN برای فهرست نمودن مقادیر مورد نیاز PIVOT می‌باشیم.

SELECT *

FROM (SELECT product_code, quantity

FROM pivot_test)

PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN (‘A’ AS a, ‘B’ AS b, ‘C’ AS c));

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY

————– ————– ————–

۲۱۰ ۹۰ ۱۶۰

۱ row selected.

SQL>

در صورتی که بخواهیم تغییری به‌وسیله customer انجام دهیم، ستون CUSTOMER_ID را به سادگی وارد فهرست انتخابی می نماییم.

SELECT *

FROM (SELECT customer_id, product_code, quantity

FROM pivot_test)

PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN (‘A’ AS a, ‘B’ AS b, ‘C’ AS c))

ORDER BY customer_id;

CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY

———– ————– ————– ————–

۱ ۱۰ ۲۰ ۳۰

۲ ۴۰ ۵۰

۳ ۶۰ ۷۰ ۸۰

۴ ۱۰۰

۴ rows selected.

SQL>

در نسخه‌های قبل از ۱۱g برای داشتن نتیجه مشابه از عملگر DECODE به همراه عملگرهای جمع استفاده می‌شد.

SELECT SUM(DECODE(product_code, ‘A’, quantity, 0)) AS a_sum_quantity,

SUM(DECODE(product_code, ‘B’, quantity, 0)) AS b_sum_quantity,

SUM(DECODE(product_code, ‘C’, quantity, 0)) AS c_sum_quantity

FROM pivot_test

ORDER BY customer_id;

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY

————– ————– ————–

۲۱۰ ۹۰ ۱۶۰

۱ row selected.

SQL>

SELECT customer_id,

SUM(DECODE(product_code, ‘A’, quantity, 0)) AS a_sum_quantity,

SUM(DECODE(product_code, ‘B’, quantity, 0)) AS b_sum_quantity,

SUM(DECODE(product_code, ‘C’, quantity, 0)) AS c_sum_quantity

FROM pivot_test

GROUP BY customer_id

ORDER BY customer_id;

CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY

———– ————– ————– ————–

۱ ۱۰ ۲۰ ۳۰

۲ ۴۰ ۰ ۵۰

۳ ۶۰ ۷۰ ۸۰

۴ ۱۰۰ ۰ ۰

۴ rows selected.

SQL>

اضافه نمودن عبارت XML به عملگر PIVOT، این قابلیت را برای ما مهیا می‌سازد که نتایج تولید شده توسط PIVOT را به فرم XML تبدیل نماییم. همچنین این عمل باعث انعطاف‌پذیری بیشتر PIVOT می‌گردد و به ما این امکان را می‌دهد که عبارت IN را با یک subquery یا عبارت ANY جایگزین نماییم.

SET LONG 10000

SELECT *

FROM (SELECT product_code, quantity

FROM pivot_test)

PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code

FROM pivot_test

WHERE id < 10));

product_code_XML

—————————————————————————————————-

<PivotSet><item><column name = “PRODUCT_CODE”>A</column><column name = “SUM_QUANTITY”>210</column></

item><item><column name = “PRODUCT_CODE”>B</column><column name = “SUM_QUANTITY”>90</column></item><

item><column name = “PRODUCT_CODE”>C</column><column name = “SUM_QUANTITY”>160</column></item><item>

۱ row selected.

SQL>

SELECT *

FROM (SELECT product_code, quantity

FROM pivot_test)

PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (ANY));

product_code_XML

—————————————————————————————————-

<PivotSet><item><column name = “PRODUCT_CODE”>A</column><column name = “SUM_QUANTITY”>210</column></

item><item><column name = “PRODUCT_CODE”>B</column><column name = “SUM_QUANTITY”>90</column></item><

item><column name = “PRODUCT_CODE”>C</column><column name = “SUM_QUANTITY”>160</column></item><item>

۱ row selected.

SQL>

بار دیگر، نتایج میتواند توسط customer به بخش‌های جداگانه تقسیم شود طوری که هر customer XML، به عنوان ردیفی جداگانه نمایش داده شود.

SET LONG 10000

SELECT *

FROM (SELECT customer_id, product_code, quantity

FROM pivot_test)

PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code

FROM pivot_test));

CUSTOMER_ID

———–

PRODUCT_CODE_XML

—————————————————————————————————-

<PivotSet><item><column name = “PRODUCT_CODE”>A</column><column name = “SUM_QUANTITY”>10</column></i

tem><item><column name = “PRODUCT_CODE”>B</column><column name = “SUM_QUANTITY”>20</column></item><i

tem><column name = “PRODUCT_CODE”>C</column><column name = “SUM_QUANTITY”>30</column></item><item><c

olumn name = “PRODUCT_CODE”>D</column><column name = “SUM_QUANTITY”></column></item></PivotSet>

<PivotSet><item><column name = “PRODUCT_CODE”>A</column><column name = “SUM_QUANTITY”>40</column></i

tem><item><column name = “PRODUCT_CODE”>B</column><column name = “SUM_QUANTITY”></column></item><ite

CUSTOMER_ID

———–

PRODUCT_CODE_XML

—————————————————————————————————-

m><column name = “PRODUCT_CODE”>C</column><column name = “SUM_QUANTITY”>50</column></item><item><col

umn name = “PRODUCT_CODE”>D</column><column name = “SUM_QUANTITY”></column></item></PivotSet>

<PivotSet><item><column name = “PRODUCT_CODE”>A</column><column name = “SUM_QUANTITY”>60</column></i

tem><item><column name = “PRODUCT_CODE”>B</column><column name = “SUM_QUANTITY”>70</column></item><i

tem><column name = “PRODUCT_CODE”>C</column><column name = “SUM_QUANTITY”>80</column></item><item><c

olumn name = “PRODUCT_CODE”>D</column><column name = “SUM_QUANTITY”>90</column></item></PivotSet>

CUSTOMER_ID

———–

PRODUCT_CODE_XML

—————————————————————————————————-

<PivotSet><item><column name = “PRODUCT_CODE”>A</column><column name = “SUM_QUANTITY”>100</column></

item><item><column name = “PRODUCT_CODE”>B</column><column name = “SUM_QUANTITY”></column></item><it

em><column name = “PRODUCT_CODE”>C</column><column name = “SUM_QUANTITY”></column></item><item><colu

mn name = “PRODUCT_CODE”>D</column><column name = “SUM_QUANTITY”></column></item></PivotSet>

۴ rows selected.

SQL>

UNPIVOT
عملگر UNPIVOT داده‌های ستونی را به سطرهای جداگانه تبدیل می‌نماید. برای مشاهده عملگر این دستور نیاز به ایجاد یک جدول تست داریم.

CREATE TABLE unpivot_test (

id NUMBER,

customer_id NUMBER,

product_code_a NUMBER,

product_code_b NUMBER,

product_code_c NUMBER,

product_code_d NUMBER

);

INSERT INTO unpivot_test VALUES (1, 101, 10, 20, 30, NULL);

INSERT INTO unpivot_test VALUES (2, 102, 40, NULL, 50, NULL);

INSERT INTO unpivot_test VALUES (3, 103, 60, 70, 80, 90);

INSERT INTO unpivot_test VALUES (4, 104, 100, NULL, NULL, NULL);

COMMIT;

بنابراین داده‌های تستی ما به صورت زیر مشاهده می‌شوند:

SELECT * FROM unpivot_test;

ID CUSTOMER_ID PRODUCT_CODE_A PRODUCT_CODE_B PRODUCT_CODE_C PRODUCT_CODE_D

———- ———– ————– ————– ————– ————–

۱ ۱۰۱ ۱۰ ۲۰ ۳۰

۲ ۱۰۲ ۴۰ ۵۰

۳ ۱۰۳ ۶۰ ۷۰ ۸۰ ۹۰

۴ ۱۰۴ ۱۰۰

۴ rows selected.

SQL>

عملگر UNPIVOT داده‌های ستونی را به سطرهای جداگانه تبدیل می‌نماید.

SELECT *

FROM unpivot_test

UNPIVOT (quantity FOR product_code IN (product_code_a AS ‘A’, product_code_b AS ‘B’, product_code_c AS ‘C’, product_code_d AS ‘D’));

ID CUSTOMER_ID P QUANTITY

———- ———– – ———-

۱ ۱۰۱ A 10

۱ ۱۰۱ B 20

۱ ۱۰۱ C 30

۲ ۱۰۲ A 40

۲ ۱۰۲ C 50

۳ ۱۰۳ A 60

۳ ۱۰۳ B 70

۳ ۱۰۳ C 80

۳ ۱۰۳ D 90

۴ ۱۰۴ A 100

۱۰ rows selected.

SQL>

چند نکته در خصوص query وجود دارد:
• نام ستون‌های مورد نیاز در این مثال QUANTITY و PRODUCT_CODE درون عبارت UNPIVOT تعریف شده‌اند و در خصوص هر نامی که در جدول وجود ندارد می‌تواند اعمال شود.
• ستون‌ها برای UNPIVOT شدن باید در عبارت IN لحاظ گردد.
• مقدار عبارت PRODUCT_CODE برابر با نام ستون مأخوذه قرار خواهد گرفت مگر آنکه آن را برابر مقدار دیگری قرار دهید.
• عبارت EXCLUDE NULLS به صورت پیشفرض مورد استفاده قرار می‌گیرد، برای جایگزینی مقدار پیشفرض از عبارت INCLUDE NULLS استفاده نمایید.
در query زیر نحوه وارد نمودن عبارت INCLUDE NULLS نمایش داده شده است.

SELECT *

FROM unpivot_test

UNPIVOT INCLUDE NULLS (quantity FOR product_code IN (product_code_a AS ‘A’, product_code_b AS ‘B’, product_code_c AS ‘C’, product_code_d AS ‘D’));

ID CUSTOMER_ID P QUANTITY

———- ———– – ———-

۱ ۱۰۱ A 10

۱ ۱۰۱ B 20

۱ ۱۰۱ C 30

۱ ۱۰۱ D

۲ ۱۰۲ A 40

۲ ۱۰۲ B

۲ ۱۰۲ C 50

۲ ۱۰۲ D

۳ ۱۰۳ A 60

۳ ۱۰۳ B 70

۳ ۱۰۳ C 80

ID CUSTOMER_ID P QUANTITY

———- ———– – ———-

۳ ۱۰۳ D 90

۴ ۱۰۴ A 100

۴ ۱۰۴ B

۴ ۱۰۴ C

۴ ۱۰۴ D

۱۶ rows selected.

SQL>

در نسخه‌های ماقبل ۱۱g برای بدست آوردن نتایج فوق از عملگر DECODE و یک جدول محوری به همراه تعداد سطرهای مناسب بهره می‌بریم. در مثال زیر از عبارت CONNECT BY در query برای ایجاد تعداد سطرهای مورد نیاز برای UNPIVOT استفاده گردیده است.

SELECT id,

customer_id,

DECODE(unpivot_row, 1, ‘A’,

۲, ‘B’,

۳, ‘C’,

۴, ‘D’,

‘N/A’) AS product_code,

DECODE(unpivot_row, 1, product_code_a,

۲, product_code_b,

۳, product_code_c,

۴, product_code_d,

‘N/A’) AS quantity

FROM unpivot_test,

(SELECT level AS unpivot_row FROM dual CONNECT BY level <= 4)

ORDER BY 1,2,3;

ID CUSTOMER_ID PRO QUANTITY

———- ———– — ———-

۱ ۱۰۱ A 10

۱ ۱۰۱ B 20

۱ ۱۰۱ C 30

۱ ۱۰۱ D

۲ ۱۰۲ A 40

۲ ۱۰۲ B

۲ ۱۰۲ C 50

۲ ۱۰۲ D

۳ ۱۰۳ A 60

۳ ۱۰۳ B 70

۳ ۱۰۳ C 80

ID CUSTOMER_ID PRO QUANTITY

———- ———– — ———-

۳ ۱۰۳ D 90

۴ ۱۰۴ A 100

۴ ۱۰۴ B

۴ ۱۰۴ C

۴ ۱۰۴ D

۱۶ rows selected.

SQL>

عملگر PIVOT و UNPIVOT در اوراکل نسخه ۱۱g

دیدگاه خود را ثبت کنید

دیدگاهتان را بنویسید لغو پاسخ

عضویت در خبرنامه

تماس با ما

راهکارها

تماس با ما

مقالات مرتبط:

شاید این موارد نیز مورد علاقه شما باشد

دیدگاه خود را ثبت کنید

دیدگاهتان را بنویسید لغو پاسخ

عضویت در خبرنامه

تماس با ما

راهکارها

تماس با ما