Oracle Pattern Matching

Herkese Selam,
Bu yazıda sizlere Oracle 12c ile beraber gelen yeni bir SQL özelliğinden bahsedicem. Umarım farkındalık anlamında faydalı bir yazı olur.

Esasında ne zamandır bu konu ile ilgili bir şeyler yazmak kafamda vardı ancak işlerden bir türlü fırsat bulup yazma şansı bulamamıştım. Ancak 2014 içerisinde İTÜ’de yaptığımız TROUG Datawarehouse SIG etkinliğinde bu konu ile ilgili bir sunum gerçekleştirmiştim. Gelen arkadaşlar dinleme fırsatı bulmuştu. en azından bu yazıda gelmeyenler için faydalı olur diye düşünüyorum.

Oracle’ın pattern matching özelliği, Oracle’ın güçlü olan analitik sorgulama alt yapısını dahada güçlendirir nitelikler taşıyor. Bu özellik ile beraber elimizdeki datanın trendini yazacağımız sorgular ile görüntüleyip business’ımıza güçlü bir input yaratabiliyoruz.

Pattern Matching özelliğini aşağıdaki business ihtiyaçlarında rahatlıkla uygulayabiliyoruz.

– Finansal Uygulamalarda.
– Ürün fiyatlarının zamana karşı değişimini incelemede.
– Hisse senetlerinin zamana karşı değişmilerini takip edip aksiyon almada.
– Güvenlik uygulamalarında sıra dışı hareketleri tesbit edip aksiyon almada.
– Fraud tesbitlerinde.
– Sensörlerden gelen verinin zamansal olarak incelenmesinde.
– Complex Event Processing ihtiyacının doğduğu yerlerde.

Bu özelliğin olmadığı zamanları düşündüğümüzde, bu tarz analizleri yapmak için tek bir sorgu yazamazdık çünkü elimizdeki teknoloji bunu yapmak için yeterli değildi. Bir dizi işlemler, bir dizi transformasyon yaparak bu sonuçlara varırdık ve belli ölçüde zaman ve kaynak tüketirdik. Bu özellik ile beraber elimizdeki datayı zaman göre etkin ve kolay bir şekilde tek bir sorgu ile inceleyip sonuçları raporlayabilmekteyiz.

Şimdi bu mekanizmanın nasıl çalıştığı anlamak için örnek bir case’üzerinden gidelim.

Elimde bir şirketin saatlin download mikatrlarını içeren aşağıdaki gibi bir data olsun.

data

 

 

 

 

 

 

Bu datanın grafiksel olarak gösterimi de aşağıdaki gibidir.

grafik

 

Şimdi case’imizi açıklayalım. Şirketimizde gece saatlerinde bir download artışı beklenmemekte kimse şirkette olmadığı için. Şirkette gece saatlerinde arka arkaya 5 saat download miktarı bir önceki saate göre arttığında bunun için bir alarm üretmek istiyorum. Aslında alarm üretmek işin 2. adımı bu kısmı gerçeklemicez. Önemli olan kısmı son 5 saat lik periodda hep artış olmuşmuyu anlayabilmek. Yani 5 saatlik bir artış trendini data üzerinden sorgu atarak görüntülemek istiyorum.

Aradığım trend aşağıda daha açık gözükmekte

graf2

 

Trendim bir yükseliş ile başlayıp art arda 5 yükseliş gösteriyor ve 5 noktalık bir trende işaret ediyor.

Şimdi teknik olarak nasıl gerçekleyeceğimize bakalım.

Öncelikle tablomuzu yaratıyoruz ve içine yukarıdada gösterdiğimiz datamızı insert ediyoruz.

 
CREATE TABLE NETWORK_TRAFIC
(
   TRAFIC_TYPE          VARCHAR2 (10),
   TRAFIC_DATE          TIMESTAMP,
   TRANSFER_AMOUNT_MB   NUMBER
);


INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 01:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  10);

INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 02:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  5);

INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 03:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  3);

INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 04:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  7);

INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 05:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  8);

INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 05:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  15);

INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 06:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  25);

INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 07:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  50);

INSERT INTO NETWORK_TRAFIC (TRAFIC_TYPE, TRAFIC_DATE, TRANSFER_AMOUNT_MB)
        VALUES (
                  'Download',
                  TO_DATE ('10.01.2015 08:00:00', 'DD.MM.YYYY HH:MI:SS'),
                  2);

COMMIT;

Evet şimdi bu trendi yakalayacak sorgumuzu yazıyoruz ve sorgunun teknik detayları açıklamaya başlıyoruz.

  SELECT *
    FROM NETWORK_TRAFIC
            MATCH_RECOGNIZE (
               PARTITION BY TRAFIC_TYPE
               ORDER BY TRAFIC_DATE
               MEASURES 
                      STRT.TRAFIC_DATE AS START_TSTAMP,
                      LAST (UP.TRAFIC_DATE) AS END_TSTAMP
               ONE ROW PER MATCH
               AFTER MATCH SKIP TO LAST UP
               PATTERN (STRT UP UP UP UP UP DOWN)
               DEFINE   
   DOWN AS DOWN.TRANSFER_AMOUNT_MB<PREV (DOWN.TRANSFER_AMOUNT_MB),   UP AS UP.TRANSFER_AMOUNT_MB > PREV (UP.TRANSFER_AMOUNT_MB))MR
   ORDER BY MR.TRAFIC_TYPE, MR.START_TSTAMP;

Evet yukarıdaki sorguyu çalıştırdığımızda aşağıdaki sonucu görmekteyiz.

sonuc

Evet sorgu sonucunda datamız içerisinde sorgumuzda verdiğimiz trendi yakaladı ve sonucu gösterdi. Dönen sonucu şöyle yorumayabiliriz. Trendimiz gece 03.00 de başladı, sabah 07.00’de bitti. Eğer datamız içinde pattern’e uygun bir seri olmasa idi sonuç dönmeyecekti.

Şimdi sorgumuzun bold ile yazılı yerlerini açıklayalım;

partition by:  datayı mantıksal olarak ne şekilde böleceğimizi anlattığımız kısım. Biz burada transfer_type alanını kullandık. 

order by: burada her mantıksal grubu hangi alana göre sıralayacağımız söyledik. Burada biz tarih olarak akışı baz aldığımız için trafic_date alanını kullandık.

measures:  burada sorgu sonucunda oluşacak kolonlarımız yani metriklerimizi belirledik. Bizim için bir başlangıç birde bitiş noktası vardı. Bunu tamamladık. 

one row per match: bu söz deyimi ile patternimize uyan bir seri ye karşılık tek bir satır kayıt üreteceğini söylüyoruz.

after match skip to last up: her eşleşmeden sonra yeni trend izlemek için başlayacağın ilk nokta bir önceki bulduğun trendin son up noktası olmasın.

pattern: burada tam olarak hangi trendimizi aradığımız tarif ettik.

(STRT UP UP UP UP UP DOWN)

Aradığımız  trendi burada tarifliyoruz.

define: bu kısımda DOWN ve UP diyerek neyi tarif ettiğimizi sisteme tanımlıyoruz.  sorgumuzu incelersek UP dediğimiz tanımın bir önceki değerden daha büyük, DOWN dediğimiz tanımız bir önceki değerden daha küçük değer olarak sisteme tanımlandığını görürsünüz.

Evet, Oracle 12c ile gelen bu yeni özelliğin kullanımı ilk etapta oldukça zor olarak gözükse de alışıldığı takdirde hızlıca yazılabildiğini test ettim. Birazcık fazla parametresi olan bir komut ama trend analizi konusunda bizi bir çok stage adımından kurtarıyor ve gün sonunda performanslı bir sonuç elde etmiş oluyoruz. Biz yukarıda çok basit bir uygulama gerçekleyerek konuyu anlatmaya çalıştık. Sizde bu örnek üzerinden ilerleyip pratik yapabilirsiniz. Elinizdeki data’nın ‘V’ trendini yada ‘W’ trendini rahatlıkla tespit edebilirsiniz. Umarım Faydalı bir yazı olmuştur.

Not: Detaylı bilgi ve örnek için aşağıdaki linkin takip edilmesini öneririm.

http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1965433.pdf

Kaynaklar:

http://docs.oracle.com/

http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1965433.pdf

 

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s