Pivot/Unpivot işlemleri

PaylaşTweet about this on TwitterShare on FacebookEmail this to someoneShare on Google+Share on LinkedInShare on TumblrShare on RedditPin on PinterestDigg thisFlattr the authorShare on StumbleUponBuffer this page

11g ile gelen güzel komutlardan ikisi… Rotate rows into colums veya columns into rows olayı (: Bu komutlardan öncede satırları sütün olarak getirmenin yolları vardı (ben case veya decode kullanarak yapıyordum mesela..) ama bu şekilde basit bir komut kullanarak isteğimizi yerine getirmek daha güzel… Elimizde şu şekilde bir verimiz olduğunu düşünelim.

pivot example

Prod_id 13,14,15 olan kayıtların ilk dört ay için trend değişimini görmek istiyor olalım.

  SELECT   A.PROD_ID, B.CALENDAR_MONTH_NUMBER, SUM (A.AMOUNT_SOLD) TOTAL
    FROM   sales a, times b
   WHERE       A.TIME_ID = B.TIME_ID
           AND a.prod_id IN (13, 14, 15)
           AND B.CALENDAR_MONTH_NUMBER IN (1, 2, 3, 4)
GROUP BY   A.PROD_ID, B.CALENDAR_MONTH_NUMBER
ORDER BY   2, 1

Elde edilen sonuç kümesi şu şekilde olacak;

İstediğimiz sonucu alıyoruz ancak görsel anlamda çok kullanışlı bir veri değil bu.  prod_id leri satır satır getirip yanlarında da amount_sold'un aylara göre değişimini getirerek daha kolay analiz edilir bir veri seti elde etmek çok daha güzel olur sanırım. Bu şekilde bir veri seti elde etmek için 11g ile gelen PIVOT (& UNPIVOT)  komutunu kullanabiliriz. Versiyonlar ilerledikçe ihtiyaçlarımızı daha kolay gidermek adına yeni komutlar ekleniyor, her komutu öğrenmek mi zor yoksa eski yöntemler ile istenilen veriyi elde etmek mi zor siz karar verin artık (: neyse PIVOT komutu;

SELECT   *
  FROM   (SELECT   B.CALENDAR_MONTH_NUMBER, A.PROD_ID, A.AMOUNT_SOLD TOTAL
                      FROM   sales A, times B
                  WHERE   A.TIME_ID = B.TIME_ID AND A.PROD_ID IN (13, 14, 15))
PIVOT (SUM(TOTAL) FOR CALENDAR_MONTH_NUMBER IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR))
 ORDER BY PROD_ID

Evet bu sorgunun sonucunda;

Bence süper oldu (: Birde eski yönteme bakalım isterseniz(11g ye geçme fırsatınız olmamışsa sizin için eski yöntem değildir tabi)

  SELECT   A.PROD_ID,
           SUM(CASE
                  WHEN B.CALENDAR_MONTH_NUMBER = 1 THEN A.AMOUNT_SOLD
                  ELSE 0
               END)
              Jan,
           SUM(CASE
                  WHEN B.CALENDAR_MONTH_NUMBER = 2 THEN A.AMOUNT_SOLD
                  ELSE 0
               END)
              Feb,
           SUM(CASE
                  WHEN B.CALENDAR_MONTH_NUMBER = 3 THEN A.AMOUNT_SOLD
                  ELSE 0
               END)
              Mar,
           SUM(CASE
                  WHEN B.CALENDAR_MONTH_NUMBER = 4 THEN A.AMOUNT_SOLD
                  ELSE 0
               END)
              Apr
    FROM   sales A, times B
   WHERE   A.TIME_ID = B.TIME_ID AND A.PROD_ID IN (13, 14, 15)
GROUP BY   A.PROD_ID

Farkı farketmişsinizdir sanırım. Aynı şekilde decode kullanarakta yapabiliriz. Özellikle 6.0 6i gibi forms versiyonlarında bu işlemi yapacaksanız "case" desteklenmediği için decode ile yapmak zorundasınız.

Başka bir özellik ise PIVOT içerisinde birden fazla aggregate function kullanılabilmesi;

SELECT   *
  FROM   (SELECT   B.CALENDAR_MONTH_NUMBER, A.PROD_ID, A.AMOUNT_SOLD TOTAL
                     FROM   sales A, times B
                 WHERE   A.TIME_ID = B.TIME_ID AND A.PROD_ID IN (13, 14, 15))
 PIVOT (SUM(TOTAL) AS TOTAL, AVG(TOTAL) AS AVERAGE FOR CALENDAR_MONTH_NUMBER IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR))
 ORDER BY PROD_ID;

Bu sorgu sonucunda elde ettiğimiz sonuç da şu şekilde olacaktır;

UNPIVOT: Adından da anlaşılabileceği gibi PIVOT komutunun tam tersini yapmak için kullanılıyor. Örnek tablomuzu şu şekilde yaratalım;

CREATE TABLE test_sales AS SELECT * FROM ( SELECT B.CALENDAR_MONTH_NUMBER, A.PROD_ID, A.AMOUNT_SOLD TOTAL FROM SALES A, TIMES B WHERE A.TIME_ID = B.TIME_ID AND A.PROD_ID IN (13, 14, 15) ) PIVOT (SUM(TOTAL) FOR CALENDAR_MONTH_NUMBER IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR)) ORDER BY PROD_ID

Oluşturduğumu tablo;

Bu tablodan eski veri setimizi elde etmek için kullanacağımız sorgu da şu şekilde;

SELECT   * FROM   test_sales UNPIVOT ( total FOR month IN ( JAN , FEB , MAR , APR ) ) ORDER BY   prod_id;

 

Birde pivot işleminde kullanabileceğimiz XML yapısı var. Sorgu sonucumuzu XML tagları ile alabiliyoruz.

SELECT   *
  FROM   (SELECT   B.CALENDAR_MONTH_NUMBER, A.PROD_ID, A.AMOUNT_SOLD TOTAL
                      FROM   sales A, times B
                  WHERE   A.TIME_ID = B.TIME_ID AND A.PROD_ID IN (13, 14, 15))
PIVOT XML (SUM(TOTAL) FOR CALENDAR_MONTH_NUMBER IN (ANY))
 ORDER BY PROD_ID

veya

SELECT   *
  FROM   (SELECT   B.CALENDAR_MONTH_NUMBER, A.PROD_ID, A.AMOUNT_SOLD TOTAL
                      FROM   sales A, times B
                  WHERE   A.TIME_ID = B.TIME_ID AND A.PROD_ID IN (13, 14, 15))
PIVOT XML (SUM(TOTAL) FOR CALENDAR_MONTH_NUMBER IN (SELECT DISTINCT C.CALENDAR_MONTH_NUMBER FROM times c))
 ORDER BY PROD_ID

Bu sorgunun sonucunda elde ettiğimiz çıktı da şu şekilde;

<PivotSet>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">1</column>
    <column name = "SUM(TOTAL)">561419.63</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">2</column>
    <column name = "SUM(TOTAL)">688305.42</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">3</column>
    <column name = "SUM(TOTAL)">497222.11</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">4</column>
    <column name = "SUM(TOTAL)">388703.12</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">5</column>
    <column name = "SUM(TOTAL)">484327.18</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">6</column>
    <column name = "SUM(TOTAL)">368498.32</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">7</column>
    <column name = "SUM(TOTAL)">652743.91</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">8</column>
    <column name = "SUM(TOTAL)">635017.33</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">9</column>
    <column name = "SUM(TOTAL)">398182.83</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">10</column>
    <column name = "SUM(TOTAL)">626677.65</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">11</column>
    <column name = "SUM(TOTAL)">347776.61</column>
  </item>
  <item>
    <column name = "CALENDAR_MONTH_NUMBER">12</column>
    <column name = "SUM(TOTAL)">663394.29</column>
  </item>
</PivotSet>

 

PIVOT & UNPIVOT kullanımı bu şekilde unutmayın 11g ile kullanabildiğimiz komutlar bunlar.

PaylaşTweet about this on TwitterShare on FacebookEmail this to someoneShare on Google+Share on LinkedInShare on TumblrShare on RedditPin on PinterestDigg thisFlattr the authorShare on StumbleUponBuffer this page
pl/sql delimited list oluşturmak
Rank, Dense_Rank, First ve Last komutları ile derecelendirme

Your comments

Loading Facebook Comments ...

Leave a Reply