SQL LIFE

SQL Common Table Expression ( Recursive )

sql common table expression nedir, sql common table expression  kullanımı sql server cte kullanımı

Merhaba;
Bu yazımda sql cte, sql common table expression kavramına ve recursive özelliğine değineceğiz. Common table expression (CTE)  SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW terimleri ile oluşturulan geçici bir datasettir. Performans olarak sorgularımızda optimizasyon sağlar.
Hiyerarşik liste raporlamalarında kullanileceğimiz bir kavramdır. örnek vermek gerekirse insan kaynakları organigram yapısı raporlaması için kullanılabilir.  

Common table expression (CTE)  SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW terimleri ile oluşturulan geçici bir datasettir. CTE türetilmiş tabloya benzer fakat bahsettiğimiz klasik tablo gibi bir nesne olarak saklanmaz, sadece o sorgu süresi boyunca yaşar.

SQL standart özelliklerindendir,SAP HANA’da da bu özelliği görüyoruz.
Avantajı ise bir CTE kendi kendini referans göstererek aynı sorguda birden fazla kullanılabilir.
Bu özelliği sayesinde recursive olarak kullanılabilir.
Klasik view yerine kullanılabilir yani metadata’da view tanımı depolamaya gerek kalmaz.
Sonuç tablosunu aynı sorgu ifadesinde birden fazla kez kullanabilirsiniz.
 
Özyinelemeli (Recursive)  CTE tanımı, en az iki CTE sorgu tanımlaması, bir bağlantı üyesi ve özyinelemeli bir üye içermelidir. Çok sayıda bağlantı(anchor) elemanları ve özyinelemeli elemanlar tanımlanabilir; Bununla birlikte, tüm bağlantı üyesi sorgu tanımları ilk özyinelemeli üye tanımından önce konmalıdır. CTE'nin kendisini referans almadıkları sürece, tüm CTE sorgu tanımları bağlantı üyesidir. 
 
 
Bağlantı üyeleri şu küme operatörlerinden biriyle birleştirilmelidir: UNION ALL, UNION, INTERSECT veya EXCEPT. UNION ALL, son bağlantı üyesi ve ilk özyinelemeli üye arasında izin verilen ve yalnızca birden çok özyinelemeli üyeyi birleştiren tek küme operatörüdür.
Özyinelemeli üyeye ait bir sütunun veri türü, bağlantı üyesindeki ilgili sütunun veri türü ile aynı olmalıdır.        
 

UNION ALL:  2 veya daha fazla data set’i birleştirmek için kullanılır,distinct’leme yapmaz.

 

 UNION: 2 ve ya daha fazla data set’i birleştirmek için kullanılır, distinct’leme yapar.

 

INTERSECT: 2 ve ya daha fazla data set’i birleştirilirken eğer bir sorguda olan kayıt diğerinde yoksa Bu kayıt atlanır,sadece her iki sorgudan gelen ortak kayıtları getirir.
 

EXCEPT: ilk select sorgusunda olupda ikinci select sorgusunda olmayan kayıtları döndürür.

 
Şimdi parent-child ilişkisini irdeleyen bir işlem yapalım. Verdiğimiz ID’ye bağlı kayıtları getirelim.
Bu hiyerarşi olarak da düşünebiliriz veya soy ağacı gibi. Verdiğimiz ID ‘nin çocukları ve onu çocukları derken o kaydın bütün alt node larını, torunlarını getirsin.
Bu konuyu işlerken kullanacağımız tabloyu oluşturalım.


CREATE TABLE [dbo].[t_demo](
       [Parent] [varchar](2) NULL,
       [ID] [varchar](2) NULL,
       [Type_] [varchar](1) NULL,
       [Ord] [int] NULL,
       [Amount] [int] NULL
) ON [PRIMARY]
GO
 
 
with ct (parent,id,type_,ord,Level_) as(
select parent,id,type_,ord, 0 as Level_ from t_demo where id = 'A1'
       union all
select t_demo.parent ,t_demo.id,t_demo.type_,t_demo.ord,Level_+1
from t_demo
inner join ct on t_demo.parent = ct.id
)
 
select * from ct  order by id
 

ilk select ifadesinde A1 id’li kaydın verisini alıyoruz ve bu kaydın çocuklarına erişmek için
ikinci select ifadesinde içinde olduğumuz  ct isimli view’a tekrar join oluyoruz işte burada recursion işlemi yaparak içinde ct common table expression’ı kendini tekrar tekrar kullanıyor.
Recursive CTE işlemi gerçekleşmiş oluyor.
Sonuç olarak hiyerarşik bir liste yaratmış bulunuyoruz.


common table expression - sqllife

 


Şimdi ise tersten gidip verilen kaydın atalarına gidelim. C1 id'li kaydın atalarını bulmak istediğimizde ise ilk select ifadesinden dönen kaydın parent kolonu recursion işlemine sokulur.

 
with ct (parent,id,type_,ord,Level_) as(
select parent,id,type_,ord, 0 as Level_ from t_demo where id = 'C1'
	union all
select t_demo.parent ,t_demo.id,t_demo.type_,t_demo.ord,Level_+1 
from t_demo
inner join ct on t_demo.id = ct.parent 
)

select * from ct  order by Level_



Tablo için dummy veri için bu soruyu kullanabilirsiniz.
 
INSERT INTO t_demo VALUES ( null, 'A1', 'a', 1, 50 , 2018 );
INSERT INTO t_demo VALUES ( 'A1', 'B1', 'b', 1, 120 , 2018 );
INSERT INTO t_demo VALUES ( 'A1', 'B2', 'c', 2, 90, 2018 );
INSERT INTO t_demo VALUES ( 'B1', 'C1', 'a', 1, 40, 2018 );
INSERT INTO t_demo VALUES ( 'B1', 'C2', 'b', 2, 60, 2018 );
INSERT INTO t_demo VALUES ( 'B2', 'C3', 'c', 3, 75, 2018 );
INSERT INTO t_demo VALUES ( 'B2', 'C4', 'a', 4, 30, 2018 );
INSERT INTO t_demo VALUES ( 'C3', 'D1', 'b', 1, 10, 2017 );
INSERT INTO t_demo VALUES ( 'C3', 'D2', 'c', 2, 25, 2017 );
INSERT INTO t_demo VALUES ( 'C4', 'D3', 'a', 3, 30, 2017 );
INSERT INTO t_demo VALUES ( null, 'A2', 'b', 2, 80, 2017 );
INSERT INTO t_demo VALUES ( 'A2', 'B3', 'c', 3, 45, 2017 ); 
INSERT INTO t_demo VALUES ( 'A2', 'C4', 'a', 4, 30, 2017 );


https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx ​
#
Ezgim Çelik
They didn't know it was impossible, so they did it - Mark Twain