Mevlüt Becerikli

Oracle 11G ile PIVOT Fonksiyonu geldi. Ama ben yazımı 10G de bu işlemi SQL ile nasıl yapabileceğimizi göstericem. Veri setimiz;

SELECT * FROM test_data

Bizden istenilen PIVOT tablo;

• IL değerlerine ait
• GRUP değerleri bazında
• ADET değerlerinin toplamları

Sorgumuz;

SELECT il,
SUM(DECODE(grup,10,adet)) grup_10,
SUM(DECODE(grup,20,adet)) grup_20,
SUM(DECODE(grup,30,adet)) grup_30,
SUM(DECODE(grup,40,adet)) grup_40
FROM test_data
GROUP BY il
ORDER BY il

Sonuç çıktımız;

Yeni bir soru yeni bir yazı!!! Konumuz; çok sık kullanılan tarih formatlama ile ilgili. Başlayalım;

SELECT sysdate AS bugun, TO_CHAR(sysdate,’WW’) AS hafta FROM dual

BUGUN HAFTA
——- ———
10/13/2010 4:52:54 PM         41

Bu ifadeden anlaşılıyorki bu sorgu çalıştığında ben 2010 yılının 41. haftasonundayım. Bir sorgu daha ;

SELECT TO_CHAR(date ‘2010-01-01′,’WW’) AS ilk, TO_CHAR(date ‘2010-12-31′,’WW’) AS son FROM dual

ILK       SON
—-    —-
01        53

Upsss!!!! 01 tamam da bize bir yıl 52 haftadır değilmiydi ??? Nerden çıktı bu 53 ifadesi ??? 52 haftadan kalan bir kaç günü de sanırım ayrı bir hafta yerine sayıyor diye düşündüm. Biraz araştırınca WW için şöyle bir açıklama ile karşılaşıyorum :

“Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.”.

Demek ki 2010 yılı için, hafta hesabını  PAZARTESİ‘ den değil de yılın ilk günü olan CUMA‘ dan başlayarak hesaplıyor. Peki benim istediğim gibi PAZARTESİ‘ den başlayacak bir format yokmu diye araştırınca IW ile tanışıyorum;

SELECT TO_CHAR(date ‘2010-01-01′,’IW’) AS ilk, TO_CHAR(date ‘2010-12-31′,’IW’) AS son FROM dual

ILK    SON
—-   —-
53     52

52 sonucunu elde ettik. IW için açıklamaya bakınca ise;

“Week of year (1-52 or 1-53) based on the ISO standard.”.

Yani, ben, ne olursa olsun yılın ilk haftasını yılın ilk PAZARTESİ günü başlatırım ve öyle hesaplarım diyor.
Ama neden 1 Ocak 2010 için 53 değeri geldi ??? Burda bir üst seviye olan yıl değerine bakayım diyorum ve orda da ISO Standardı yada değil gibi bir ayrımının burda da olduğunu görüyorum :

• YYYY (4-digit year)
• IYYY (4-digit year based on the ISO standard)

İşi biraz özetlemek ve daha iyi özümsemek için aşağıdaki sorguyu da siz yorumlayın;

select to_char(date ‘2009-12-26′ + level, ‘YYYY.MM.DD’) TARIH,
to_char(date ‘2009-12-26′ + level, ‘YYYY’)       YIL,
to_char(date ‘2009-12-26′ + level, ‘WW’)         HAFTA,
to_char(date ‘2009-12-26′ + level, ‘IYYY’)       YIL_ISO,
to_char(date ‘2009-12-26′ + level, ‘IW’)         HAFTA_ISO,
to_char(date ‘2009-12-26′ + level, ‘DAY’)        GUN
from dual
connect by level <= 10

Sonuç çıktımız;

Oracle Dökümantasyonlarında geçen bir ifade ile sözlerimizi bitirelim ve dökümantasyonun önemini ve bazen ne kadar kritik olabileceğini düşünelim ;

“The week numbers returned by the WW format mask are calculated according to the following algorithm: int(dayOfYear+6)/7. This algorithm does not follow the ISO standard (2015, 1992-06-15).”

“WW No Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.”

Saygılar…

Kaynak : http://www.techonthenet.com/oracle/functions/to_char.php

COL-2-ROW (String Aggregation) isimli yazımız da belli bir gruplama işlemine tabi tutarak o gruba ait sütundaki string değerlerini nasıl virgül ile birleştirebileceğimizi görmüştük. Bunun tersi bir  durum için ne yapmamız gerek ???

Biraz vakit ayırıp CONNECT BY LEVEL ın nimetlerinden faydalanınca içime sinen bir ortaya çıktı. Aşağıdaki ifadeyi bir IDE ye kopyalayıp direk çalıştırabilirsiniz;

WITH test_data
AS (SELECT ‘1,q,a,z,2,w,s,x,2,3,4′ AS input FROM dual)
SELECT SUBSTR(input,(level*2-1),1) AS result
FROM test_data
CONNECT BY level <= LENGTH(REPLACE(input,’,',”))

LENGTH(REPLACE(input,’,',”)) ifadesi ile kaç elemanımız   (çıkacak satır/kayıt sayısı) var onu buluyoruz. Sonrasında öss hazırlık dönemlerimde DENKLEMLER konusunda  ki 2n-1 formulasyonu ile arzulanan sonuç elde ediliyor;

RESULT
——
1
q
a
z
2
w
s
x
2
3
4

Yine bekleriz…

Sürekli takipte olduğum güzel bir forum da “Extract Only Numbers From The Columns” başlıklı bir soru ile gelmişti. Biraz PLSQL ile çözülür diye bir kahve eşliğinde aşağıdaki gibi bir ortam oluşturup 2-3 satır PL/SQL kodu karaladım;

Tablomuz;

CREATE TABLE MEVLUT.TEST_DETAY
(
ID           VARCHAR2(50 BYTE),
DESCRIPTION  VARCHAR2(100 BYTE)
)

Veri Ekleyelim;

INSERT INTO test_detay values (1,’duyuru 49600 üyeye gönderildi’);
INSERT INTO test_detay values (2,’ilan edildi (53988 üye)’);
INSERT INTO test_detay values (3,’Baska üye kalmadi :)’);
INSERT INTO test_detay values (4,’38854 kullanici baglanti kurdu’);
INSERT INTO test_detay values (9,’Tüm kullanicilar üye mi? Sanmiyorum! 38854 kullanicidan “çok azi” üye( tahmini 31111) ‘);

Kodumuz;

DECLARE
varStr    VARCHAR2(100);
rslt      VARCHAR2(100);
splitter  VARCHAR2(100) := ”;
BEGIN
FOR myCursor IN (SELECT id, description FROM test_detay) LOOP
varStr := myCursor.description;
FOR i IN 1..LENGTH(varStr)
LOOP
splitter := SUBSTR(varstr,i,1);
IF (INSTR(’0123456789′,splitter) > 0) THEN
rslt := rslt || splitter;
ELSE
IF (LENGTH(rslt) > 0) THEN
DBMS_OUTPUT.PUT_LINE(myCursor.ID || ‘ - ‘ || rslt);
rslt := ”;
END IF;
END IF;
END LOOP;
rslt := ”;
END LOOP;
END;

Ama sonra Regular Expression’ ın gücüne bir kere daha şahit oldum ;

SELECT  * FROM  test_detay WHERE regexp_like (description, ‘[[:digit:]]’)

Sonuç çıktısını vermiyorum!!! Çalışıp çalışmadığını siz deneyerek görün…
Saygılar…

Kayıtlarımız, kendi belirlediğimiz mantığa uygun biçimde (Filtreleme, Gruplama..vs) sıralamak (Ranking) ve bu sıraya ait bir numarası ihtiyacımız olabilir.  Bu durumda Oracle veritabanı için hali hazırda olan Analitik Fonksiyonlar‘ dan faydalanabiliriz. Faydalanmak istemezseniz, umarım iç içe (SubQuery) sorgu yazmasını iyi biliyorsunuzdur
Veri setimiz aşağıdaki gibi olsun;

Kişileri tutara göre sıralama istersek;

SELECT rownum SIRA, AD, TUTAR
FROM (SELECT AD, TUTAR FROM TABLO ORDER  BY TUTAR DESC);

veya

SELECT ROW_NUMBER() OVER (ORDER BY TUTAR DESC) SIRA, AD,  TUTAR FROM TABLO;

SEN : Ne gerek var şimdi bu ROW_NUMBER() fonksiyonuna ???
BEN : TUTAR değeri 35 olan değerler arasında neye göre sıralama yaptı bu SQL ler???
SEN : Hoş ikiside aynı sonucu verdi diceksiniz.
ORACLE : Bu aşamada RANK() fonksiyonu işe karışıyor!!!

SELECT RANK() OVER(ORDER BY TUTAR DESC) SIRA, AD, TUTAR FROM TABLO;

SEN : HOPPAAA! Bu sefer 6 dan 9 a atladı sıralama!!!
ORACLE : Bu aşamada DENSE_RANK() fonksiyonu işe karışıyor!!!

SELECT DENSE_RANK() OVER (ORDER BY TUTAR DESC) SIRA, AD, TUTAR FROM TABLO;

SEN : Tüm yukarıdaki anlatımda sadece TUTAR a göre sıralama yaptık. Peki başka bir kritere (TARIH) göre gruplayarak, her bir grup içinde sıralama yapmak istersek ???
ORACLE : Bu aşamada PARTITION BY ifadesi işe karışıyor!!!

SELECT DENSE_RANK() OVER (PARTITION BY TARIH ORDER BY TUTAR DESC) SIRA, TARIH, AD, TUTAR FROM TABLO;

NOT : NULL değerler normalde sıralama işlemlerinde ilk sırada gelir. Bunu biz kontrol edip ilk yada son sıraya koyabiliriz. Bunun için son SQL ifademizi aşağıdeki gibi değiştirebiliriz;

SELECT DENSE_RANK() OVER (PARTITION BY TARIH ORDER BY TUTAR DESC NULLS FIRST) SIRA, TARIH, AD, TUTAR FROM TABLO;
SELECT DENSE_RANK() OVER (PARTITION BY TARIH ORDER BY TUTAR DESC NULLS LAST) SIRA, TARIH, AD, TUTAR FROM TABLO;

SEN : Ben tüm insanları 2 ye bölmek istiyorum!
ORACLE : Sana NTILE() fonksiyonunu veriyorum ;

SELECT NTILE(2) OVER (ORDER BY TUTAR DESC) SIRA, AD,  TUTAR FROM TABLO;

…
..
.
Bu makale uzadıkça uzar. Zira Oracle da 30+ adet Analitik Fonksiyon var.
Devamı gelcek ama sabırsızlar için ;

TÜM SQL FONKSIYONLARI İÇİN :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm

Bir tarih (Date) yada zaman aralığını (Interval) oluşturan her bir parçayı elde etmek için EXTRACT fonksiyonunu kullınırız. Bu parçalar aşağıdaki gibi olabilir ;

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
• TIMEZONE_HOUR
• TIMEZONE_MINUTE
• TIMEZONE_REGION
• TIMEZONE_ABBR

Fonksiyon kullanımı ise şu şekildedir;

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

Örnek;

Bu yazımız, Cognos‘ un  Report Studio ürünü içinde Query Explorer sekmesinde (Insertable Objects) bulunan SQL komponenti kullanımı hakkındadır.

Aşağıdaki SQL ifademizde görüldüğü gibi Adana iline ait ilçeler raporunu hazırlayalım;

SELECT  ILCE_KEY as ILCE_KODU, IL_KODU, IL_ADI, ILCE_ADI
FROM D_ILCE  WHERE IL_KODU = ‘001′

Raporumuzu dinamik hale getirip, kullanıcının giriş yapabileceği ve istediği şehrin ilçelerini listeleyebileceği bir rapor haline getirmek istersek ve bunu yukarıda yazdığımız SQL in içine yerleştirmek istersek Macro ifadeleri ile tanışmış oluyoruz. Parametre (Prompt) sayfamızda “pIl” isimli bir “Text Box Prompt” olduğunu varsarsak sorgumuz şu şekilde olacaktır;

SELECT  ILCE_KEY as ILCE_KODU, IL_KODU, IL_ADI, ILCE_ADI
FROM D_ILCE
WHERE IL_KODU = #prompt(’pIl’)#

Bursa’ nın illeri;

Saygılar…

Soru :

Veritabanımızda bir tablo sütununda farklı (aşağıdaki gibi) formatlarda telefon numaraları var.  Bizden bu verileri tek tip formata çevirmemiz isteniyor.

Mevcut Formatlar :

+1 123 456 7890
+1-123-456-7890
+1-123/456 7890

İstenen :

+1.123.4567890

Çözüm :

SELECT TRANSLATE(’+1 123 456 7890′, ‘ /-’,'…’) FROM DUAL
SELECT TRANSLATE(’+1-123-456-7890′, ‘ /-’,'…’) FROM DUAL
SELECT TRANSLATE(’+1-123/456 7890 ‘, ‘ /-’,'…’) FROM DUAL

Açıklama :

TRANSLATE komutunun kullanımı aşağıdaki gibidir;

TRANSLATE( ilgili_metin , degistirilecekler, eklenecekler )
ilgili_metin : İçeriği değiştirilecek metin
degistirilecekler : İçeriği değiştirilecek metin içerisinden değişecek karakterlerin dizisi
eklenecekler : degiceşekler kısmında her bir karakterin yerine gelecek karakterleriden oluşan dizi

Bu açıklamadan sonra gelelim bizim çözümümüze. Bu komut ile ilgili_metin içinde yer alan ve değiştirmeyi istediğimiz karakterleri sırası ile degistirilecekler kısmına yazıyoruz. Her bir karakterin yerine gelmesini istediğimiz karakterleri gene aynı sırada olmak kaydıyla eklenecekler kısmına yazıyoruz.

Burada önemli; komutun tek bir karakter bazında çalışması (Yani “12″ yerine “34″ yapamıyoruz, “1″ yerine “3″ ve “2″ yerine “4″ olacak şekilde çalışıyor) ve degistirilecekler ile eklenecekler dizilerindeki karakterlerin sıralarının aynı olmasıdır.

NOT : Aynı soruna içiçe REPLACE komutu ile de çözüm üretebiliriz. Ama  TRANSLATE komutu sanırım bu okuması-yazması karışık ve hataya açık durumdan kurtulmak için geliştirilmiş;

SELECT REPLACE(REPLACE(REPLACE(’+1 123 456 7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL
SELECT REPLACE(REPLACE(REPLACE(’+1-123-456-7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL
SELECT REPLACE(REPLACE(REPLACE(’+1-123/456 7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL

İyi Çalışmalar…

Bu yazıda klasik insert ifadesinden biraz uzaklaşıp nasıl çoklu veri girişi işlemi yapacağımızı göreceğiz. Konunun detayını bende ihtiyaç doğrultusunda öğrendim. Bu özellik Oracle 9i den sonra gelmiş. Farklı varyasyonları var;

1. Koşulsuz çoklu giriş
2. Koşullu çoklu giriş
3. Koşullu ama koşula ilk uyan verinin girişi
4. Pivot Tablo yapısında çoklu veri girişi

1. KOŞULSUZ ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

INSERT ALL
INTO <tablo_ad> VALUES <sutun_degerleri)
INTO <tablo_ad> VALUES <sutun_degerleri)
…
SELECT <sutun_isimleri> FROM <tablo_ad>;

Örnek :

INSERT ALL
INTO tbl_tip VALUES (tip_id)
INTO tbl_marka VALUES (marka_id)
SELECT marka_id, tip_id FROM tbl_arabalar;

SELECT ifadesinden gelen verileri INSERT ALL kısmında ki tablolara ekliyoruz.

2. KOŞULLU ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

INSERT ALL
WHEN (<condition>) THEN
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
WHEN (<condition>) THEN
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
ELSE
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
SELECT <sutun_isimleri> FROM <tablo_ad>;

Örnek :

INSERT ALL
WHEN (marka_id = 1) THEN
INTO tbl_bmw VALUES (plaka)
WHEN (marka_id = 2) THEN
INTO tbl_audi VALUES (plaka)
SELECT marka_id, plaka FROM tbl_arabalar;

SELECT ifadesinden gelen verileri INSERT ALL kısmında ki WHEN koşuluna göre ilgili tablolara ekliyoruz. Burda ilk şarta uysa bile tüm koşullara bakılıyor.

3. KOŞULLU AMA KOŞULA İLK UYAN VERİNİN GİRİŞİ ( INSERT FIRST)

Syntax:

Koşullu INSERT ALL ile aynıdır sadece INSERT ALL yerine INSERT FIRST ifadesi kullanılır. Farkı; ilgili koşulları sırası ile değerlendirir ve istenen şartı ilk sağlayan koşul da veri girişini yapar ve sıradaki diğer şartlara bakmaz.

Örnek:

INSERT FIRST
WHEN (model < 1980) THEN
INTO tbl_hurda VALUES (plaka)
WHEN (model < 2000) THEN
INTO tbl_normal VALUES (plaka)
WHEN (model > 2010) THEN
INTO tbl_lux VALUES (plaka)
SELECT marka_id, plaka FROM tbl_arabalar;

Bu örnekte, araçları modellerine göre ayırıyor ve buna göre farklı tablolara atıyoruz. Burda şartı sağlayan ilk koşulda işlem yapılıp diğer koşullar es geçiliyor.

4. PİVOT TABLO YAPISINDA ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

Koşulsuz veri girişi ile aynıdır. Burda asıl amaç ilişkisel veritabanı mantığına aykırı olan ve yatay olarak genişliğe sahip bir tablonun ilişkisel hale getirilmesi işidir (Belki başka amaç içinde kullanılabilir).

Örnek :

INSERT ALL
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PAZARTESI’,tut_pazartesi)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’SALI’,tut_sali)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CARSAMBA’,tut_carsamba)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PERSEMBE’,tut_persembe)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CUMA’,tut_cuma)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CUMARTESI’,tutCumartesi)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PAZAR’,tut_cumartesi)
SELECT satis_id, urun_ad, tut_pazartesi, tut_sali, tut_carsamba, tut_persembe, tut_cuma, tut_cumartesi, tut_pazar
FROM satis;

Bu örnekte “satis_id / urun_ad / tut_pazartesi / tut_sali / tut_carsamba / tut_persembe / tut_cuma / tut_cumartesi / tut_pazar” alanlarını sahip bir tabloyu “id / urun_ad /gun / tutar” alanlarına sahip bir tabloya atmış oluyoruz

İyi Çalışmalar…

SQL *Plus’ ta bir SQL script çalıştırdım. Gayet güzel çalıştı. Ama script dosyası içeriğini okuması kolay olsun diye formatladım. Tekrar çalıştırdığımda hata verdi…

SP2-0042: unknown command “FROM” - rest of line ignored.
SP2-0734: unknown command beginning “SELECT…” - rest of line ignored.

Sorunun kaynağının değişiklik yaparken eklenen YENİ SATIR KARAKTERİ olduğu anlaşıldı. SQL* Plus ortamında bunun da çözümü mevcut. Script i çalıştırmadan önce ;

SQL> set sqlblanklines on

ifadesini çalıştırın…

İyi Çalışmalar…