İşe alım süreci boyunca ekibim için birçok yetenekli adayla tanışmanın mutluluğunu yaşadım. İşimiz karmaşık veri kümelerini ele almayı gerektirdiğinden, her adayın akıllı çözümler bulma yeteneğini ölçmek benim için önemliydi. Yeterliliklerini değerlendirmek için pencere işlevleriyle ilgili deneyimlerini sordum. Birçoğu bu işlevleri biliyor olsa da, çok azı bunları etkili bir şekilde kullanabildi. SQL'deki Pencere fonksiyonları neredeyse 20 yıldır ortalıkta olmasına rağmen birçok SQL geliştiricisi hala bunları kavramakta zorluk çekiyor. Deneyimli geliştiricilerin bile ne yaptığını gerçekten anlamadan kodu kopyalayıp yapıştırması alışılmadık bir durum değildir. Bu makale size yardımcı olmak için burada! Pencere işlevlerini anlaşılması kolay bir şekilde açıklayacağım ve bunların gerçek dünyada nasıl çalıştıklarını size gösterecek örnekler sunacağım. StackOverflow'un Pek çok sorunu çözebilecek harika analitik araçlardır. Örneğin, müşteri kimliği gibi ortak bir özelliği paylaşan bir dizi satırı hesaplamanız gerektiğini varsayalım. Pencere fonksiyonlarının kullanışlı olduğu yer burasıdır! Toplama işlevleri gibi çalışırlar ancak bunları bir arada gruplamak yerine her satırın benzersizliğini korumanıza olanak tanırlar. Ayrıca, pencere işlevlerinin sonuçları çıktı seçiminde ekstra bir alan olarak görünür. Bu, analitik raporlar hazırlarken, hareketli ortalamaları hesaplarken ve toplamları hesaplarken veya farklı ilişkilendirme modellerini çözerken son derece faydalıdır. Pencere işlevlerini duydunuz mu? SQL ve pencere fonksiyonlarının dünyasına hoş geldiniz! Yeni başlıyorsanız doğru yerdesiniz. Bu makale yeni başlayanlar için uygundur, açık açıklamalar içerir ve karmaşık terminoloji veya ileri düzey kavramlar içermez. Konuya tamamen yeni olsanız bile kolaylıkla takip edebileceksiniz. İçeriğe Genel Bakış Pencere İşlevleriyle Kullanılan İşlev Türleri Toplama işlevleri Sıralama işlevleri Değer fonksiyonları Toplu Pencere İşlevleri Temel Çıkarımlar Pencere İşlevleriyle Kullanılan İşlev Türleri Pencere işlevlerinin bir dizi satıra (pencere adı verilen) uygulanabileceği üç ana işlev türü vardır: bunlar toplama, sıralama ve değer işlevleridir. Aşağıdaki resimde her kategoriye giren farklı işlevlerin adlarını görebilirsiniz. Toplama işlevleri Bunlar bir veri grubu üzerinde matematiksel işlemler gerçekleştirerek tek bir kümülatif değer elde edilmesini sağlar. Ortalama, toplam satır sayısı, maksimum veya minimum değerler veya her pencere veya bölüm içindeki toplam toplam dahil olmak üzere çeşitli toplamları hesaplamak için kullanılırlar. SUM: sütundaki tüm değerleri toplar COUNT: NULL değerler hariç sütundaki değerlerin sayısını hesaplar AVG: sütundaki ortalama değeri bulur MAX: sütundaki en yüksek değeri tanımlar MIN: sütundaki en düşük değeri tanımlar Sıralama işlevleri Bunlar bir bölümdeki her satıra bir sıra veya sıra vermek için kullanılır. Bu, sıralı sayılar atamak veya sıralamayı belirli değerlere dayandırmak gibi belirli kriterleri değerlendirerek yapılır. ROW_NUMBER: bir bölümdeki her yeni kayda sıralı bir sıralama numarası atar RANK: sonuç kümesindeki her satırın sıralamasını belirtir. Bu durumda sistem aynı değerleri tespit ederse bunlara aynı sıralamayı atayacak ve bir sonraki değere atlayacaktır. DENSE_RANK: sonuç kümesinin bir bölümündeki her satıra bir sıra atar. RANK işlevinden farklı olarak işlev, sonraki değerleri atlamadan aynı değerler için sıraları döndürür. NTILE: Mevcut hattın hangi gruba ait olduğunu belirlememizi sağlar. Grup sayısı parantez içinde verilmiştir. Değer fonksiyonları Bunlar, bir gruptaki farklı satırlar arasındaki değerleri karşılaştırmayı kolaylaştırır ve aynı zamanda değerleri o gruptaki ilk veya son değerle karşılaştırmanıza olanak tanır. Bu, bir penceredeki farklı satırlar arasında kolayca hareket edebileceğiniz ve pencerenin başındaki veya sonundaki değerleri kontrol edebileceğiniz anlamına gelir. LAG veya LEAD: kendi kendine birleştirme işlemi gerçekleştirmeye gerek kalmadan önceki veya sonraki satırdaki verilere erişin. Bu işlevler, zaman içindeki farkları hesaplamak gibi, aynı sonuç kümesi veya bölüm içindeki bir satırı başka bir satırla karşılaştırmayı gerektiren sorunları çözerken özellikle faydalıdır. FIRST_VALUE veya LAST_VALUE: tanımlanmış bir pencere veya bölümden ilk veya son değeri alır. Bu işlevler özellikle belirli bir zaman dilimi içindeki farkları hesaplamak istediğinizde kullanışlıdır. Pencere işlevlerine başlamak için varsayımsal bir 'maaş' tablosu oluşturalım ve onu verilerle dolduralım. Tablo oluşturma: create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real ) Tablonun doldurulması: insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700) 'Maaş' tablosunu başarıyla doldurup doldurmadığımızı kontrol edelim: select * from salary Bir sonraki sorgu tablomuzdaki çalışanların isimlerini ve maaşlarını gösterecektir: select employee_name, salary from salary Maaşların toplamının, ortalama maaşın, maksimum, minimum ve satır sayısının hesaplanması, toplu işlevlerin bazı yaygın kullanım durumlarıdır: Toplama fonksiyonu uygulandığında maaşlar toplanıp tek satırda gösterilir. Peki ya 'maaş' tablosundan çalışanların isimlerini ve maaşlarını, üçüncü sütunda ise tüm maaşların toplamını görüntülemek istersek? Bu değer tüm satırlar için aynı olmalıdır. Bir pencere işlevini kullanmak harika bir fırsat! select employee_name, salary, sum(salary) over() as sum_salary from salary fonksiyonunun her satırındaki maaşların toplamını hesaplayan pencere fonksiyonuna daha yakından bakalım. sum(salary) over() ifadesi, işlevin üzerinde çalıştığı bir pencereyi veya bir dizi satırı tanımlar. Örneğimizde pencere tablonun tamamıdır, yani fonksiyon tüm satırlara uygulanacaktır. over() ifadesi yalnızca den önce istenen işlevlerle eşleştirildiğinde çalışır. over() over() Örneğin, ; burada bir toplama işlevidir. Ve ifadesinin tamamı bir toplam pencere işlevidir. sum(salary) over() sum() sum(salary) over() Daha önce de söylediğim gibi pencere fonksiyonlarının uygulandığı tüm fonksiyonlar üç gruba ayrılabilir: toplama, sıralama ve değer fonksiyonları. Toplama işlevleri , , , , () ifadesiyle birlikte bir grup toplama pencere işlevi oluşturur. sum() count() avg() min() max() over() Bu makalede, bu özel pencere işlevleri türüne odaklanacağız. Toplu Pencere İşlevleri Örneklere dönelim! Çalışanların isimlerini rica edelim; maaşları; tüm maaşların toplamı; ortalama, maksimum ve minimum maaş; çalışan sayısı. select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary Artık pencere işlevlerinin ne olduğunu daha net anladığımıza göre, bunların işinizde yararlı olabileceği bazı durumları inceleyelim. select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc Dördüncü sütunda her maaş için toplam maaş bütçesinin yüzdesini hesapladık. Jessa'nın maaşı tüm maaş bütçesinin neredeyse %15'ini oluşturuyor. Ayrıca yüzdelerini hesaplayan formülü sıralamaya yerleştirdiğimizi unutmayın. Bir pencere işlevi yalnızca çıktı içinde değil aynı zamanda de bulunabilir. salary/sum(salary)over() order by select order by Başka bir örnek: Maaşları şirketin ortalama maaşıyla karşılaştıralım. select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() Gördüğümüz gibi Andrew'un maaşı ortalamanın 2110 dolar altında, Jessa'nınki ise ortalamanın 1690 üzerinde. Üç sütun isteyelim: çalışanın adı, departmanı ve maaşı. Ayrıca bunları departmanlara göre sıralayacağız. select employee_name, department, salary from salary order by department Şimdi aynı üç sütunu ve ayrıca tüm çalışanların maaşlarının toplamını içeren bir sütunu isteyeceğiz. Bunun bir pencere fonksiyonuyla yapılabileceğini zaten biliyorsunuz. select employee_name, department, salary, sum(salary)over() from salary order by department Peki ya son sütunda gösterildiği gibi tüm maaşların toplamını değil de her departmanın maaşlarının toplamını talep etmek istersek: ifadesine parametreye ekleyerek bunu yapabiliriz: over() partition by select employee_name, department, salary, sum(salary)over(), sum(salary)over(partition by department) from salary order by department Partition by pencere fonksiyonunu tüm satırlara (pencerenin tamamına) değil, sütun bölümlerine uygulamamıza olanak tanır. Basit bir gruplandırmaya benzemiyor mu? Her departmanın maaşlarının toplamını hesaplamak için departmanlara göre bir gruplama (pencere fonksiyonlarının argo bölümündeki bölümler) yapar ve tutarı hesaplarız: select department, sum(salary) from salary group by department Temelde, gruplandırma ile arasındaki fark, grup başına bir satır döndürmesi, buna göre ise işlevin sonuçları ile toplama fonksiyonunun sonuçlarıyla aynı olmasına rağmen tüm satırlara toplama sağlamasıdır. bir gruba dayalı işlev. partition by group by partition by group by Pencere fonksiyonlarına geri dönelim: select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department Pencere fonksiyonunu, özellikle de parametreye kullanarak, her çalışanın maaşının, departmanın maaşlarının toplamından payını hesaplayabiliriz. Veya örneğin maaşları departmandaki ortalama maaşla karşılaştırmak için. partition by Temel Çıkarımlar Özetlemek gerekirse: Bir pencere işlevi, bir şekilde geçerli satırla ilişkili olan bir dizi satır boyunca hesaplama gerçekleştirir. Pencere fonksiyonlarının uygulandığı ana fonksiyon türleri toplama, sıralama ve değer fonksiyonlarıdır. Bir pencere işlevini kullanmak için, sorgu sonuç kümesindeki bir pencereyi (bir dizi satır) tanımlayan yan tümcesini uygulamanız gerekir. Pencere işlevi daha sonra penceredeki her satır için bir değer hesaplar; over() Toplama işlemini gerçekleştirmek istediğiniz sütunu belirtmek için, cümlesini cümlesine eklemeniz gerekir. , gruplandırmaya benzer ancak grup başına bir satır yerine, toplama işlevi uygulanmış tüm satırları döndürür. partition by over() Partition by Şimdilik bu kadar! Sonraki birkaç makalede, yeni başlayanlara uygun basit örneklerle daha gelişmiş SQL kavramlarını keşfedeceğim, o yüzden bizi izlemeye devam edin!
