SQL Pencere İşlevlerine Giriş

Nefret etmeyi sevdiğiniz (ama bilmeniz gereken) çok güçlü özellik

SQL pencere işlevleri, bazı son derece güçlü ve kullanışlı özellikler sağlar. Ancak birçokları için standart SQL'e çok yabancı oldukları için öğrenmeleri ve anlamaları zordur, garip sözdizimlerine sahiptirler ve çoğu zaman kaçınılır.

Pencere işlevleri, toplamaya benzer hesaplama işlevleri olarak basitçe açıklanabilir, ancak GROUP BY yan tümcesi aracılığıyla normal toplamanın birleştiği yerde, toplanan bireysel satırları gizler, pencere işlevlerinin tek tek satırlara erişimi vardır ve bu satırlardan bazı öznitelikleri bu satırlara ekleyebilir. sonuç kümesi.

Bu SQL pencere işlevleri eğitiminde, pencere işlevleriyle başlamanızı sağlayacağım, yararlarını ve bunları ne zaman kullanacağınızı açıklayacağım ve kavramlara yardımcı olacak gerçek örnekler vereceğim.

Verilerinize Açılan Bir Pencere

SQL'de en çok kullanılan ve önemli özelliklerden biri, veri satırlarını belirli şekillerde toplama veya gruplama yeteneğidir. Bununla birlikte, bazı durumlarda, neyin gerekli olduğuna bağlı olarak gruplama son derece karmaşık hale gelebilir.

Hiç bir sıralama, en iyi x listesi veya benzerini elde etmek için sorgunuzun sonuçları arasında dolaşmak istediniz mi? Verilerinizi tam olarak bir görselleştirme aracı için hazırlamak istediğiniz, ancak bunu neredeyse imkansız ya da buna değmeyecek kadar karmaşık bulduğunuz herhangi bir analitik projeniz oldu mu?

Pencere işlevleri işleri kolaylaştırabilir. Sorgunuzun sonucunu aldıktan sonra, yani WHERE yan tümcesinden ve herhangi bir standart toplamadan sonra, pencere işlevleri kalan satırlarda (veri penceresi ) hareket edecek ve istediğinizi elde edecektir.

Bakacağımız pencere işlevlerinden bazıları şunlardır:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Daha kolay

OVER yan tümcesi, bir pencere işlevini belirten şeydir ve her zaman ifadeye dahil edilmelidir. OVER yan tümcesindeki varsayılan, tüm satır kümesidir. Örnek olarak, bir şirket veritabanındaki bir çalışan tablosuna bakalım ve şirkete başladıkları zaman da dahil olmak üzere her bir çalışanın bilgileriyle birlikte her satırdaki toplam çalışan sayısını gösterelim.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Yukarıdakiler, pek çok pencere işlevi gibi, daha tanıdık, penceresiz bir şekilde de yazılabilir - bu basit örnekte çok da kötü değil:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Ama şimdi çalışanla aynı ayda işe başlayan çalışan sayısını arka arkaya göstermek istediğimizi varsayalım. Sayıyı her satır için yalnızca o aya daraltmamız veya kısıtlamamız gerekecek. Bu nasıl yapılır? Pencere PARTITION yan tümcesini şu şekilde kullanırız:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Bölümler, pencereyi belirli bir değere veya değerlere göre bölümlere ayırmanıza izin verir. Her bölüme genellikle pencere çerçevesi denir.

Daha da ileriye götürmek için, diyelim ki sadece aynı ayda kaç çalışanın işe başladığını bulmakla kalmadık, aynı zamanda o ay hangi sırayla başladıklarını da göstermek istedik. Bunun için tanıdık ORDER BY yan tümcesini kullanabiliriz. Ancak, bir pencere işlevi içinde ORDER BY , bir sorgunun sonunda olduğundan biraz farklı davranır.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Bu durumda, ORDER BY pencereyi bölümün başlangıcından (bu durumda çalışanın başladığı ay ve yıl) geçerli satıra gidecek şekilde değiştirir. Böylece, sayım her bölümde yeniden başlar.

Sırala

Pencere işlevleri, sıralama amaçları için çok yararlı olabilir. COUNT toplama işlevini kullanmanın, Çalışanların şirkete hangi sırayla katıldığını görmemizi sağladığını daha önce görmüştük. ROW_NUMBER() , RANK() ve DENSE_RANK() gibi pencere sıralama işlevlerini de kullanabilirdik.

Farklar, bir sonraki ay yeni bir çalışan ekleyip bölümü kaldırdıktan sonra görülebilir:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Farkları görebilirsiniz. ROW_NUMBER() , belirli bir bölüm içinde sıralı bir sayı verir (ancak bir bölümün yokluğunda tüm satırlardan geçer). RANK() , ORDER BY yan tümcesine dayalı olarak her satırın sıralamasını verir. Bağları gösterir ve ardından bir sonraki sıralamayı atlar. DENSE_RANK ayrıca bağları da gösterir, ancak daha sonra hiç bağ yokmuş gibi bir sonraki ardışık değerle devam eder.

Diğer sıralama işlevleri şunları içerir:

• CUME_DIST - Bir bölüm içindeki geçerli satırın göreli sırasını hesaplar
• NTILE – Her pencere bölümü için satırları mümkün olduğunca eşit olarak böler
• PERCENT_RANK – Geçerli satırın yüzde sıralaması

Bu örnekte ayrıca, tek bir sorguda birden çok Pencere işlevine sahip olabileceğinize dikkat edin; hem bölüm hem de sıra her birinde farklı olabilir!

Satırlar ve Aralıklar ve Çerçeveler, Oh My

OVER() yan tümcesinde pencere çerçevenizi daha fazla tanımlamak veya sınırlamak için ROWS ve RANGE kullanabilirsiniz. ROWS yan tümcesi ile, bölümünüze dahil edilen satırları, geçerli satırdan önceki veya sonraki satırlar olarak belirleyebilirsiniz.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Bu örnekte, pencere çerçevesi ilk satırdan mevcut satır eksi 1'e gider ve pencere boyutu her satır için artmaya devam eder.

Menzil biraz farklı çalışır ve farklı bir sonuç alabiliriz.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Aralık, geçerli satırla aynı ORDER BY değerlerine sahip pencere çerçevesindeki satırları içerecektir. Bu nedenle, ORDER BY benzersiz değilse RANGE ile kopyalar almanız mümkündür.

Bazıları, ROWS fiziksel bir operatör olarak tanımlarken, RANGE mantıksal bir operatördür. Her durumda, ROWS ve RANGE için varsayılan değerler her zaman UNBOUNDED PRECEDING AND CURRENT ROW .

Başka ne?

Çoğu standart toplama işlevi, Pencere işlevleriyle çalışır. Örneklerde zaten COUNT gördük. Diğerleri SUM , AVG , MIN , MAX , vb. içerir.

Pencere işlevleriyle, LAG ve LEAD ve FIRST_VALUE ve LAST_VALUE kullanarak hem önceki kayıtlara hem de sonraki kayıtlara erişebilirsiniz. Örneğin, her satırda içinde bulunulan aya ait satış rakamını ve geçen ayın satış rakamı arasındaki farkı göstermek istediğinizi varsayalım. Bunun gibi bir şey yapabilirsiniz:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Temel olarak, SQL Pencere İşlevleri Çok Güçlüdür

Bu, SQL pencere işlevlerine hızlı bir giriş olsa da, yapabildiklerini görmek için ilginizi çekeceğini umuyoruz. Pencere işlevlerinin, toplama işlevlerinin yaptığına benzer hesaplamalar gerçekleştirdiğini, ancak bunları oldukça güçlü kılan tek tek satırlardaki verilere erişime sahip olmaları gibi ek bir fayda sağladığını öğrendik. Her zaman OVER yan tümcesini içerirler ve PARTITION BY , ORDER BY ve bir dizi toplama ( SUM , COUNT , vb.) ve diğer konumsal işlevleri ( LEAD , LAG ) içerebilirler. Ayrıca pencere çerçevelerini ve bunların veri bölümlerini nasıl kapsadıklarını öğrendik.

SQL'in farklı çeşitlerinin pencere işlevlerini farklı şekilde uygulayabileceğini ve bazılarının tüm pencere işlevlerini veya yan tümcelerini uygulamamış olabileceğini unutmayın. Kullanmakta olduğunuz platformun belgelerini kontrol ettiğinizden emin olun.

Bir SQL geliştiricisi olarak, SQL veritabanı performansınızı ayarlamakla ilgileniyorsanız, Geliştiriciler için SQL Veritabanı Performans Ayarına bakın .

Mutlu pencereleme!

Belirli uygulamalar hakkında daha fazla bilgi için bkz.

• PostgreSQL uygulaması için PostgreSQL'in Pencere İşlevleri belgeleri.
• SELECT - OVER Cümlesi (Transact-SQL) Microsoft belgeleri.
• SQL Server uygulamalarına ve onun 2. bölümüne harika bir genel bakış için SQL Server'daki Pencere İşlevleri .