SQL'de LAG() Fonksiyonunu Anlamak: Kapsamlı Bir Kılavuz

Kısa Cevap: LAG() Fonksiyonu Nedir?

LAG() fonksiyonu, SQL'in pencere fonksiyonlarından biridir ve başka bir sütundaki önceki satıra erişen yeni bir sütun oluşturmanıza olanak tanır. Adını, yeni oluşturduğunuz sütunda her satırın, belirttiğiniz diğer sütundaki bir önceki satırdan değer çekmek için "geriden gelmesi" gerçeğinden alır.

Temel sözdizimini çalışırken görelim. Günlük hisse senedi fiyatlarından oluşan, şu şekilde görünen iki sütunlu basit bir tablomuz olduğunu varsayalım:

Örnek hisse fiyatı verisi. Görsel: Yazar.

Aşağıdaki sorguyla, her satırda bir önceki günün fiyatını getiren yeni bir sütun oluşturabiliriz:

SELECT date, 
	price,
	LAG(price) OVER(ORDER BY date) AS one_day_before
FROM stock_price;

Ve şu sonucu elde ederiz:

LAG() fonksiyonunun hızlı örneği. Görsel: Yazar.

İlk satır için bir önceki güne ait değer olmadığından, bir [null] değerinin oluştuğuna dikkat edin.

LAG() Fonksiyonunun Temel Sözdizimi

LAG() fonksiyonu SELECT ifadesinin bir parçası olarak yazılır. En temel sözdiziminde fonksiyon şu şekilde yazılabilir:

LAG(column1) OVER(ORDER BY column2)

İşte aynı LAG() fonksiyonunun bağımsız bir sorguda uygulanmış hâli:

SELECT 
   column1, 
   column2, 
   LAG(column1) OVER (ORDER BY column2) AS previous_value 
FROM 
   table_name;

Gördüğünüz gibi, temel sözdizimi birkaç bölümden oluşur. Bunları birlikte inceleyelim:

• column1: Önceki satırın değerinin alınacağı sütundur.
• OVER(): OVER(), her pencere fonksiyonu için zorunlu bir anahtar kelimedir. Bu ifade, pencere fonksiyonunun çalışacağı çerçeveyi tanımlar. Yukarıdaki örnekte pencere fonksiyonu, sıralanmış column2 üzerinde çalışır.
• ORDER BY: ORDER BY zorunlu değildir, ancak LAG() ile kullanıldığında şiddetle önerilir; genellikle bu fonksiyon onsuz anlamlı olmaz. 
• column2: LAG() fonksiyonunun izleyeceği sıralamayı belirleyen sütundur. Sıralama için birden fazla sütun kullanılabilir.

Neden LAG() Fonksiyonunu Kullanmalı?

LAG() fonksiyonunu bu kadar iyi yapan şeyin ne olduğunu merak ediyor olabilirsiniz. Cevap şu ki, yeni oluşturulan geciken sütun, iki farklı satırdaki değerleri karşılaştırmak için kullanılabilir.

Bu nedenle LAG() fonksiyonu genellikle zaman serisi verileriyle kullanılır. Örneğin, demo veri kümemizde günlük fiyat değişimini şu sorguyla kolayca hesaplayabiliriz:

SELECT date, 
	price,
	LAG(price) OVER(ORDER BY date) AS one_day_before,
	price - LAG(price) OVER(ORDER BY date) AS daily_change
FROM stock_price; 

LAG() ile günlük değişimi hesaplama. Görsel: Yazar.

Ayrıca, daha gelişmiş bir hesaplamaya geçip bunun yerine günlük yüzde değişimleri de düşünebiliriz.

SELECT date, 
	price,
	LAG(price) OVER(ORDER BY date) AS one_day_before,
	price - LAG(price) OVER(ORDER BY date) AS daily_change,
	((price - LAG(price) OVER(ORDER BY date))*100 / 
		(LAG(price) OVER(ORDER BY date))) AS daily_perc_change
FROM stock_price; 

LAG() ile günlük yüzde değişimi hesaplama. Görsel: Yazar.

LAG() Fonksiyonunun İleri Kullanımı

Artık LAG() fonksiyonunun temel kullanımını anladığımıza göre, adım adım seviyeyi yükseltip başka neler yapabileceğimize bakalım.

Burada, 2022 başından 2024 ortasına kadar üç hayali şirketin: Welsh LLC, Jones Group ve Green-Keebler'ın aylık gelirlerini kaydeden başka bir demo veri setine geçeceğiz. Verinin yapısı şu şekilde:

Demo gelirler veri seti. Görsel: Yazar.

Birden fazla sütuna göre sıralama

Yeni veri kümemizde, gecikmeli sütun year ve month olmak üzere iki sütuna göre sıralanmalıdır. Daha önce belirttiğimiz gibi, bu, ORDER BY ifadesine iki sütun vererek yapılabilir.

Aşağıdaki sorguda, hem year hem de month sütunlarına göre sıralanmış bir gecikmeli sütun ve aylık bazda (MoM) gelir farkı sütunu oluşturuyoruz. Ayrıca, şimdilik tek bir şirkete odaklanmak için sorgumuzu bir WHERE ifadesiyle filtreliyoruz.

SELECT *,
	LAG(revenue) OVER(ORDER BY year, month) AS one_month_before,
	revenue - LAG(revenue) OVER(ORDER BY year, month) AS mom_difference
FROM revenues
WHERE company = 'Welch LLC'; 

LAG() için yıl ve aya göre sıralama. Görsel: Yazar.

LAG() çerçevesini bölümlere ayırma

Diyelim ki veri kümemizdeki üç şirket için aynı iki sütunu hesaplamak istiyoruz. LAG() fonksiyonunu şu ana kadar kullandığımız şekilde hesaplarsak, gecikmeli sütun üç şirketin tamamı üzerinde çalışır ve fark sütunu hepsinin gelirlerini birbirine karıştırır; bu ise istediğimiz şey değildir.

İstediğimiz, her şirket için önceki ayın gelirini almak ve MoM farkını yalnızca o şirket özelinde hesaplamak, ardından yeni şirket için baştan başlamaktır.

Bunu yapmak için, LAG() fonksiyonu sözdizimimize yeni bir ifade ekleriz. Bu ifade PARTITION BY'dır ve temel sözdizimimize şu şekilde eklenebilir:

LAG(column1) OVER(PARTITION BY column3 ORDER BY column2)

Örneğimizde bölümlere ayırmamız gereken sütun company'dir. Dolayısıyla, önceki sorgumuzu PARTITION BY ifadesini ekleyip WHERE ifadesini çıkararak değiştireceğiz.

SELECT *,
	LAG(revenue) OVER(PARTITION BY company ORDER BY year, month) AS one_month_before,
	revenue - LAG(revenue) OVER(PARTITION BY company ORDER BY year, month) AS mom_difference
FROM revenues;

Sonuçta, gecikmeli ve MoM sütunlarının artık yalnızca ilk şirketin aylık gelirleri üzerinde çalıştığını ve ardından bir sonrakine geçerken baştan başladığını görürüz. Bunu, aşağıdaki ekran görüntüsünde, Green-Keebler'ın son aylarını ve Jones Group'un ilk aylarını gösteren bölümde görebiliyoruz.

LAG() ile PARTITION BY kullanımı. Görsel: Yazar.

Ofseti özelleştirme

Ya değeri bir önceki satırdan değil de altı ya da on iki satır yukarıdan çekmemiz gerekirse? Başka bir deyişle, MoM yerine yıllık bazda (YoY) farkı hesaplamamız gerekirse?

Bu durumda, LAG() fonksiyonu sözdizimine yeni bir parametre ekleriz. Bu parametre ofset olarak adlandırılır ve mevcut satırın kaç satır üstünden değerin alınacağını belirtir. Sözdizimdeki yeri aşağıda gösterilmiştir:

LAG(column1, offset) OVER(PARTITION BY column3 ORDER BY column2)

Varsayılan olarak ve şimdiye dek kullandığımız şekilde, ofset değeri bire eşittir. Ancak, LAG() ifadesinde ofseti açıkça belirterek bu varsayılanı değiştirebiliriz. 

Örneğimize dönersek, YoY gelir değişimini elde etmek için, bir önceki yılın aynı ayındaki geliri almamız gerekir. Aşağıdaki sorguda 12'yi ofset olarak belirterek bunu yapabiliriz: 

SELECT *,
	LAG(revenue, 12) OVER(PARTITION BY company ORDER BY year, month) AS one_year_before,
	revenue - LAG(revenue, 12) OVER(PARTITION BY company ORDER BY year, month) AS yoy_difference
FROM revenues;

Ve sonuç şöyle olur:

LAG() ile yıllık bazda fark. Görsel: Yazar.

NULL değerleri ele alma

LAG() fonksiyonunun, önceki dönemlerin bulunmadığı satırlarda, örneğin önceki sorgumuzda 2022 yılına ait satırlarda NULL döndürdüğünü fark etmiş olabilirsiniz.

Bu, LAG() fonksiyonunun varsayılan davranışıdır; ancak "default" adlı yeni bir parametreyi açıkça belirterek değiştirilebilir. Bu parametre herhangi bir tamsayı veya ondalık sayısal değer alabilir. Fonksiyonun sözdiziminde parametrenin konumu şöyledir:

LAG(column1, offset, default) OVER(PARTITION BY column3 ORDER BY column2)

"Default" parametresinin yaygın kullanım durumu, zaman serisi verilerinde değerlerin aslında sıfırdan başlamasıdır. 

Örneğimizde, üç şirketin Ocak 2022'de (veri setimizdeki en erken tarih) kurulduğunu varsayabiliriz; dolayısıyla kuruluştan önceki geliri sıfır kabul edebiliriz. Bunu yaparak, gelirlerdeki değişimi daha doğru hesaplarız; zira ilk aylarda elde edilen her gelir pozitif bir değişim olacaktır.

Sorgumuzda, her iki LAG() ifademizde de "default" parametresini sıfır olarak şu şekilde belirteceğiz:

SELECT *,
	LAG(revenue, 12, 0) OVER(PARTITION BY company ORDER BY year, month) AS one_year_before,
	revenue - LAG(revenue, 12, 0) OVER(PARTITION BY company ORDER BY year, month) AS yoy_difference
FROM revenues;

Ve sonuç olarak, gecikmeli sütunda sıfırlar ve YoY gelir değişimi sütununda sıfırdan net gelir elde ederiz:

LAG() içinde NULL'ları sıfırla değiştirme. Görsel: Yazar.

"Default" parametresine açıkça bir değer belirtebilmek için, ofsete de açıkça bir değer belirtmenin zorunlu hâle geldiğine dikkat edin; çünkü LAG() fonksiyonunda sütun adından sonra verilen ilk sayı zaten ofset olarak alınacaktır.

"Default"u değiştirmeniz, ancak ofseti değiştirmemeniz gerekiyorsa, ofset parametresini bir olarak ayarlayın; normal şekilde davranacaktır.

LAG() Fonksiyonundan Sonra Sıralama

LAG() fonksiyonunun dayandığı sıralamanın, sonuç görünümünün sırasıyla aynı olmak zorunda olmadığını bilmek faydalıdır. Bu sıralamayı, sorgunuzda normal şekilde ORDER BY kullanarak her zaman değiştirebilirsiniz.

Örneğimizde, dış ORDER BY ifadesinde yıla ve aya göre sıralayarak, üç şirket için aynı yılın aynı ayını bir arada gösterip sonra yılın bir sonraki ayına geçecek şekilde sonucu yeniden sıralayabiliriz:

SELECT *,
	LAG(revenue, 12, 0) OVER(PARTITION BY company ORDER BY year, month) AS one_year_before,
	revenue - LAG(revenue, 12, 0) OVER(PARTITION BY company ORDER BY year, month) AS yoy_difference
FROM revenues
ORDER BY year, month;

Ve istediğimiz çıktıyı elde ederiz:

LAG() sonrasında sorguyu sıralama. Görsel: Yazar.

Yaygın Hatalar ve En İyi Uygulamalar

Sorun giderme konusunda yardıma ihtiyaç duyabileceğiniz durumlar için yaygın konulara göz atalım. 

Hatalı sıralama

• Sorun: LAG() ifadesinde ORDER BY belirtmemek hatalı sonuçlara yol açabilir. Kaynak tablonun orijinal sırası fonksiyon için uygun olsa bile, zamanla değişebileceğinden bu orijinal sıraya asla güvenmeyin.
• En İyi Uygulama: LAG() ifadesinde her zaman ORDER BY kullanın ve doğru sütuna göre sıraladığınızdan emin olun.

Hatalı bölümleme

• Sorun: PARTITION BY ifadesinin gözden kaçırılması veya yanlış sütunla kullanılması nedeniyle hatalı LAG() çerçevesi. 
• En İyi Uygulama: LAG() fonksiyonunuzun çalıştığı bölümleri iki kez kontrol edin.

Hatalı ofset

• Sorun: Yanlış ofset nedeniyle hatalı gecikmeli değerler.
• En İyi Uygulama: İhtiyacınız olan ofset değerini iki kez kontrol edin ve bazı durumlarda varsayılan ofsetin ihtiyaçlarınızı karşılamayabileceğini unutmayın.

Yanlış NULL kullanımı

• Sorun: "Default" parametresini belirtmeyerek, LAG() çıktısındaki NULL değerleri, daha uygun bir değer varken bırakmak.
• En İyi Uygulama: Veri kümenizin başlangıcından önceki değerlerin ne anlama geldiğini her zaman düşünün. Bazı durumlarda, örneğimizde gördüğümüz gibi null yerine sıfır kullanmak daha uygundur.

Ofset belirtmeden default belirtmek

• Sorun: Ofseti belirtmeden "default" parametresini belirtmek, "default" değerinin ofset değeri olarak alınmasına neden olur.
• En İyi Uygulama: "Default" parametresini açıkça belirttiyseniz, ofseti de belirtmeyi asla unutmayın.

Fonksiyon ifadesi yerine takma adları kullanma

• Sorun: Aynı LAG() ifadesini birden fazla sütunda kullanıyorsanız, ikinci sütunda da ifadenin tamamını yazmanız gerekir; takma adını değil. İlk LAG() sütununun takma adını kullanmak hata verecektir.
• En İyi Uygulama: SELECT ifadesi içinde LAG() ifadelerini her zaman tam olarak yazın.

Indeksleri yok sayma

• Sorun: LAG() fonksiyonu, tüm pencere fonksiyonları gibi, büyük veri kümelerinde hesaplama açısından pahalı olabilir. Bu nedenle, PARTITION BY ve ORDER BY ifadelerinde kullanılan sütunların indekslenmesini göz ardı etmek zayıf performansa yol açabilir.
• En İyi Uygulama: Mümkünse, PARTITION BY ve ORDER BY ifadelerinde kullanılan sütunların indekslendiğinden emin olun; böylece sorgu performansını iyileştirebilirsiniz.

Açıklamaları yok sayma

• Sorun: Açıklamalar ve dokümantasyon olmadan, özellikle birden fazla fonksiyon kullanıldığında, LAG() ve diğer pencere fonksiyonları karmaşık ve okunması/zor anlaşılır hâle gelebilir.
• En İyi Uygulama: LAG() ve diğer pencere fonksiyonlarını kullandığınızda, sorgunun neyi amaçladığını açıklamalar ekleyerek ve dokümante ederek belirtin. Bu, sorgu yeniden ziyaret edildiğinde başkalarının ve sizin, LAG() kullanımının amacını ve mantığını anlamanıza yardımcı olur.

Sonuç ve Ek Kaynaklar

Bu derste, LAG() fonksiyonunun ne olduğunu ve zaman serisi analitiğini gerçekleştirmek için nasıl güçlü bir araç olabileceğini gördük. Ayrıca, argümanlarını ve onunla ilgili ifadeleri inceledik. SQL'de zamanla ilişkili veya herhangi bir şekilde sıralı verilerle bir sonraki çalışmanızda, LAG() fonksiyonunun kullanımını ve size neler sağladığını düşünün. Başka bağlamlarda LAG(), otokorelasyonları bulmada, veriyi düzeltmede veya veri temizliğinin bir parçası olarak düzensiz aralıkları kontrol etmede yardımcıdır. 

Tek bir pencere fonksiyonunun neler yapabildiği ilginizi çektiyse, tüm aileyi öğrenebilir ve kapsamlı PostgreSQL Summary Stats and Window Functions etkileşimli kursumuzla SQL'deki analiz becerilerinizi geliştirebilirsiniz. Ve bu makaleden keyif aldıysanız, muhtemelen Associate Data Analyst in SQL Career Track yolculuğundan da keyif alacak ve sonunda SQL Associate Certification sertifikasını almaktan memnun olacaksınız!