Row_number, Lag, Lead Oracle Analytical Functions

Alıntı: https://blog.kodcu.com/author/yunus-bayhan/

Oracle dbde veri sorgulamada kullanabileceğimiz çok hızlı çalışan analitik fonksiyonları bulunmaktadır. Bu yazımızda row_number, lead ve lag fonksiyonlarını inceleyeceğiz.

Örnekte kullanacağımız tabloları oluşturalım.

  -- Tanım tablosu
CREATE TABLE ANALITIK_SAMPLE
(
   S_ID       INTEGER NOT NULL,
   S_LABEL    VARCHAR2 (255),
   S_TYPE     NUMBER (3) DEFAULT 0 ,
   S_ACTIVE   NUMBER (1) DEFAULT 1
)
 -- İş listesi
CREATE TABLE ANALITIK_SAMPLE_WORK_LIST
(
   S_WORK_ID      INTEGER NOT NULL,
   S_ID           INTEGER NOT NULL,
   WL_DATE        DATE DEFAULT SYSDATE ,
   WL_USER_CODE   VARCHAR2 (25),
   WL_text        VARCHAR2(1500)
)
 -- Tanım tablosuna test verisi girelim.
 SET DEFINE OFF;
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (2, 'SAMPLE2', 1, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (1, 'SAMPLE1', 1, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (3, 'SAMPLE3', 1, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (4, 'SAMPLE4', 1, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (5, 'SAMPLE5 ', 2, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (6, 'SAMPLE6', 2, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (7, 'SAMPLE7', 2, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (8, 'SAMPLE8', 2, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (9, 'SAMPLE9 ', 3, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (10, 'SAMPLE10', 3, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (11, 'SAMPLE11', 3, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (12, 'SAMPLE12', 3, 0);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (13, 'SAMPLE13', 3, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (14, 'SAMPLE14', 3, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (15, 'SAMPLE15', 3, 1);
Insert into ANALITIK_SAMPLE
   (S_ID, S_LABEL, S_TYPE, S_ACTIVE)
 Values
   (16, 'SAMPLE16', 3, 0);
COMMIT;
-- İş listesi tablomuz için test verisi giren bir script yazalım 
SET SERVER_OUTPUT ON -- Mesaj çıktısını aç
DECLARE
   --Örnek iş listesi tablomuza kayıt yapana prosedurumuz

   PROCEDURE insert_s_analitik (p_user_code IN VARCHAR2)
   IS
   BEGIN
      INSERT INTO analitik_sample_work_list asw (asw.s_work_id,
                                                 asw.s_id,
                                                 asw.wl_date,
                                                 asw.wl_text,
                                                 asw.wl_user_code)
         (SELECT   -- sonraki sequnexe değerini al
                   seq_analitik_sample.NEXTVAL, 
                   a_s.S_ID,
                   SYSDATE,        -- db saatini al
                   'SAMPLE_TEXT DATA ' || a_s.s_id, 
                    -- ornek olması için text yaz
                   p_user_code       -- kullanıcı kodu
            FROM   analitik_sample a_s);                     

      COMMIT;           -- Yapılan değişiklikleri kaydet
   EXCEPTION          -- herhangi bir hata olursa
      WHEN OTHERS
      THEN
         ROLLBACK;     -- tüm değişiklikleri geri al
         DBMS_OUTPUT.put_line ('Insert yapılamadı');        -- hata mesajı ver
   END;
BEGIN
   insert_s_analitik ('YB');
END;

Yazdığımız script günlük hayatta çok karşımıza çıkmasada hem test verisi girmek için hem yazım tarzını görmek için iyi bir örnek.
Row_number : Çektiğiniz sorgu içinde kaçıncı sırada olduğunu getirir. Gruplama yaparak o grup içinde kaçıncı sırada olduğunu bulabilir veya hangi kolona göre sıralama yapması gerektiğini seçebiliriz.

Select row_number()
 over (partition by [gruplanacak kolon adı] 
 order by [siralanacak kolon adları]) 
 from table_adi
SELECT   ROW_NUMBER ()
            OVER (PARTITION BY A_S.S_TYPE
                  ORDER BY ASW.S_WORK_ID ASC NULLS LAST)
            AS grup_sira, -- Gruptaki sirayi getir
         ASW.S_ID, -- diger bilgileri getir
         ASW.S_WORK_ID,
         ASW.WL_DATE,
         ASW.WL_TEXT,
         ASW.WL_USER_CODE,
         A_S.S_TYPE,
         A_S.S_LABEL
  FROM   analitik_sample_work_list asw, ANALITIK_SAMPLE a_S
  WHERE   ASW.S_ID = A_S.S_ID

LEAD fonksiyonu belirtilen kolon için n satır sonraki değerini döndürür.

 SELECT   lead ([kontrol edilecek kolon],n,[o grup için sonraki kolon yok ise donen değer])
            OVER (PARTITION BY [gruplanacak kolon adı]
                  ORDER BY [sıralama yapılacak kolonlar] )
            AS sonraki_deger

İki farklı satır arasında s_id farkını bulan sql

	SELECT   lead (ASW.s_id,1,'999')
            OVER (PARTITION BY A_S.S_TYPE
                  ORDER BY ASW.S_WORK_ID ASC NULLS LAST)
            AS onceki_deger,
         ASW.S_ID as simdiki_deger,
         lead (ASW.s_id,1,'999')
            OVER (PARTITION BY A_S.S_TYPE
                  ORDER BY ASW.S_WORK_ID ASC NULLS LAST)
            -asw.s_id as fark,
         ASW.S_WORK_ID,
         ASW.WL_DATE,
         ASW.WL_TEXT,
         ASW.WL_USER_CODE,
         A_S.S_TYPE,
         A_S.S_LABEL
  FROM   analitik_sample_work_list asw, ANALITIK_SAMPLE a_S
WHERE   ASW.S_ID = A_S.S_ID

LAG fonksiyonu belirtilen kolon için n satır önceki değerini döndürür. Yazımı lead fonksiyonu ile aynıdır.

		SELECT   lag(ASW.s_id,1,'999')
            OVER (PARTITION BY A_S.S_TYPE
                  ORDER BY ASW.S_WORK_ID ASC NULLS LAST)
            AS onceki_deger,
         ASW.S_ID as simdiki_deger,
         (lag(ASW.s_id,1,'999')
            OVER (PARTITION BY A_S.S_TYPE
                  ORDER BY ASW.S_WORK_ID ASC NULLS LAST)
            -asw.s_id) as fark,
         ASW.S_WORK_ID,
         ASW.WL_DATE,
         ASW.WL_TEXT,
         ASW.WL_USER_CODE,
         A_S.S_TYPE,
         A_S.S_LABEL
  FROM   analitik_sample_work_list asw, ANALITIK_SAMPLE a_S
WHERE   ASW.S_ID = A_S.S_ID AND rownum < 25
Advertisements

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

%d bloggers like this: