Veri Bilimciler için SQL Komutları

Bir veri bilimci veya veri analisti olarak, veriler doğal olarak işinizin merkezindedir. Verilerinizi toplayabileceğiniz birçok kaynak vardır. Çoğu zaman veriler bir SQL veritabanında bulunur; bu durumda SQL sorgu komutlarını anlamak bu rolü başarıyla yerine getirmek için kritik olabilir. Bu makale sizi temel komutlardan başlayarak veri analisti veya veri bilimci olarak çalışırken işinize yarayacak daha gelişmiş işlemlere kadar götürecek.

Yukarıda listelenen komutları anlamak için, bu komutların nasıl kullanılabileceğini göstermek amacıyla açık kaynaklı DVD kiralama veritabanını kullanacağız. Varlık ilişki diyagramını (ERD) buradan bulabilirsiniz.

Bu makalenin amacı doğrultusunda, veritabanını yerel olarak kurduk ve tüm sorguları pgAdmin4 ile tamamladık; yani PostgreSQL kullanıyoruz. Komutların çoğu, hatta tamamı diğer SQL sunucularında da çalışmalıdır. PostgreSQL'in kurulumu ve veritabanının ayarlanması bu makalenin kapsamı dışındadır; bu süreçlere ilişkin tavsiyeler için DataCamp'in PostgreSQL'e Yeni Başlayanlar Rehberi gibi diğer kaynaklara göz atmanızı öneririz.

Basit Veri Getirme

SELECT FROM

Getirmenin en basit örneği, veritabanındaki belirli bir tablodan tüm içeriği almaktır. 'Category' tablosunda yer alan tüm film kategorilerini bilmek istersek, şu komutu girebilirsiniz:

SELECT *
FROM category

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Burada FROM'dan sonra tablo adını belirttik. Tablodaki tüm içeriği istediğimiz için, her şeyi istediğimizi belirtmek amacıyla SELECT'ten sonra * kullandık.

DISTINCT

Yalnızca benzersiz değerlerle ilgilendiğimiz durumlar da vardır. Örneğin, film tablosundaki tüm MPAA derecelendirmelerini bilmek istersek? DISTINCT kullanmak, yalnızca benzersiz değerleri seçmemize olanak tanır:

SELECT DISTINCT(rating)
FROM film

Şu sonucu döndürür:

Basit Koşullarla Veri Getirme

WHERE

Belirli koşulları sağlayan belirli kayıtları getirmek istediğimiz durumlar da vardır. WHERE ifadesini kullanarak veriyi döndürmeden önce sağlanması gereken koşulları belirtebiliriz. Özü itibarıyla, koşula göre kayıtları filtreliyoruz. Örneğin, yalnızca PG-13 dereceli filmleri bilmek istersek?

SELECT title
FROM film
WHERE rating = 'PG-13'

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Ek olarak, kira ücreti derecelendirmesi 4,5 veya daha yüksek olan filmlerin listesini de çıkarabiliriz:

SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

ORDER BY

Döndürülen sonucun sıralanması, veriyi daha iyi anlamamız açısından sıklıkla faydalıdır. Yukarıdaki PG-13 filmler listesinde, filmleri en uzundan en kısaya doğru ve karşılık gelen film süresiyle birlikte sıralamak istersek ne olur? ORDER BY kullanarak bunu kolayca yapabiliriz:

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

LIMIT

Ayrıca, bazen yalnızca sınırlı sayıda kayıtla ilgileniriz. Burada aslında PG-13 olan en uzun ilk 10 filmle ilgileniyoruz; bu nedenle LIMIT ifadesini kullanabiliriz.

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10

Şu sonucu döndürür:

Toplamalar

GROUP BY & COUNT( )

Toplamalar, veri kümesinin özetini alarak içgörü elde etmek için sıklıkla kullanılır. Toplamalar genellikle GROUP BY ifadesiyle birlikte kullanılır. Örneğin, her müşterinin şimdiye kadar kaç kiralama yaptığını bilmemiz gerekiyorsa, kiralama sayısını sayabilir ve sonuçları en çok kiralama yapan üstte olacak şekilde sıralayabiliriz.

SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

SUM( )

Peki, her müşterinin ne kadar para harcadığı? Bunu SUM toplama fonksiyonunu kullanarak yapabiliriz. Burada en çok harcama yapanın üstte olmasını istiyoruz.

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

AVG( ) & HAVING

Gruplanmış bir toplama hesapladıktan sonra HAVING kullanarak ek koşullar ekleyebiliriz. Örneğin, ortalama olarak 115 dakikadan uzun filmlere sahip MPAA derecelendirme kategorilerini bilmek istiyoruz:

SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115

Şu sonucu döndürür:

MIN( ) & Takma Ad

Toplama kullanırken her zaman gruplama gerekli değildir. En kısa film süresini bilmek istiyorsanız, basitçe MIN fonksiyonunu kullanabiliriz. Ayrıca sütunun 'shortest_movie_length' olarak görünmesini istiyoruz; bunu da 'AS' kullanarak bir takma ad atayarak yapabiliriz.

SELECT MIN(length) AS shortest_movie_length
FROM film

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Tablo adları için de takma ad kullanabilirsiniz. Bu, özellikle tabloları birleştirirken (aşağıya bakın) uzun tablo adlarıyla çalışırken faydalıdır. Ayrıca, hangi tür takma ad kullandığımıza dikkat etmek isteriz; çünkü bu, kodun okunabilirliğini ciddi şekilde değiştirebilir.

Faydalı olan başka toplama fonksiyonları da vardır; bunlar hakkında daha fazla bilgiyi SQL'e Giriş kursunda öğrenebilirsiniz.

Birleştirmeler

INNER JOIN (JOIN)

Muhtemelen yalnızca tek bir tabloya bakarak yapabileceklerimizin sınırlı olduğunu fark etmişsinizdir. Çoğu zaman veriye birden fazla tablodan bakmak isteriz. İşte bu noktada JOIN'leri kullanırız. Tabloları ortak bir anahtar üzerinden birleştirebiliriz. Örneğin, İngilizce olmayan filmleri arıyorsak:

SELECT film.title, language.name
FROM film
JOIN language
    ON film.language_id = language.language_id
WHERE language.name != 'English'

(Lütfen bu veritabanında İngilizce dışında film olmadığını unutmayın. Yukarıdaki sorguyla boş bir tablo elde edeceksiniz.)

Birden fazla tabloyu da birleştirebilirsiniz. Örneğin, aşağıda kategorileri ve bunlara karşılık gelen ortalama derecelendirmeyi bilmek istiyoruz. Ayrıca değerleri ortalama derecelendirmeye göre, en yüksekten başlayarak sıraladık.

SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN gibi birçok birleştirme türü vardır. Burada, varsayılan olarak INNER JOIN olan JOIN kullandık; bu, yalnızca her iki tabloda da bulunan kayıtları geri alacağınız anlamına gelir. Bu birleştirmeler arasındaki farklar hakkında daha fazla bilgi edinmek isterseniz, SQL'de Verileri Birleştirme kursuna göz atabilirsiniz.

Veri Türlerini Değiştirme

CAST( )

Yukarıda para tutarlarını hesapladığımız örnekte, SQL'in değerleri yalnızca sayı olarak gördüğünü fark etmiş olabilirsiniz. Bunları CAST fonksiyonunu kullanarak para birimine dönüştürebiliriz:

SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Değerleri dönüştürebileceğimiz tek şey para değildir. Sayıları ondalığa (float), metne, hatta tarih ve saate de dönüştürebiliriz.

ROUND( )

Benzer şekilde, sayıları yuvarlayabiliriz. Yukarıda ortalama kiralama derecelendirmesini hesapladığımız örnekte, hesaplanan değerlerde çok sayıda ondalık basamak var. ROUND fonksiyonunu kullanarak iki ondalık basamağa yuvarlayabiliriz:

SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Karmaşık Koşullar

CASE İfadesi

Yukarıdaki birçok fonksiyon ve ifadeyi birleştirerek oldukça karmaşık bir sorgu gerçekleştirebileceğinizi hayal edebilirsiniz. Ancak, temellerin yeterli olmadığı durumlar vardır. Örneğin, film uzunluğu kategorilerini içeren bir sütun eklemek isteseydik. Filmleri 50 dakikadan kısaysa 'Short Film', 150 dakikadan uzunsa 'Long Film' ve geri kalanı için 'Medium Film' olarak etiketleyebiliriz. Bunu bir CASE ifadesi kullanarak yapabiliriz:

SELECT title, length,
CASE
    WHEN length < 50 THEN 'Short Film'
    WHEN length > 150 THEN 'Long Film'
    ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Gördüğünüz gibi, belirtilen koşul sağlandığında sonuç döndürülür. Bu, programlama dillerindeki 'if-else' ifadelerine benzer.

Alt Sorgular

Şimdi de uzunluk olarak en uzun filmlerin hangileri olduğunu ve bunların başlıklarını bilmek isteyebiliriz. Bunun için, yukarıda tanıttığımız WHERE ifadesini daha karmaşık bir koşulla birlikte kullanabiliriz:

SELECT title, length
FROM film
WHERE length = (SELECT max(length)
                FROM film)

Şu sonucu döndürür:

Karmaşık sorgular alt sorgular kullanılarak yapılabilir. Bu örnekte, tüm komedi filmlerinin ortalama kiralama derecelendirmesinden daha yüksek kiralama derecelendirmesine sahip film adlarının listesini bilmek istiyoruz:

SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
    ON film.film_id=cat_id.film_id
JOIN category AS cat
    ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Bu örnekte önce komedi filmleri için ortalama film kiralama derecelendirmesini buluyoruz. Yukarıda parantez içinde görülen bu sorguda bulunan değer, daha sonra film başlıklarını bulabildiğimiz WHERE ifadesinde bir koşul olarak kullanılır.

Ortak Tablo İfadeleri (CTE'ler)

Daha karmaşık bir örnekte, ihtiyaç duyulan bilgiyi çıkardığımız geçici bir tabloyu kaydetmek için ortak tablo ifadesi (CTE) kullanabiliriz. Bu örnekte, her personelin haftada ortalama kaç kiralama işlediğini bilmek istiyoruz.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id

Şu sonucu döndürür:

Yukarıdaki durumda, haftalık kiralama sayılarını 'weekly_rentals' adlı geçici bir tabloda kaydediyoruz; bunun üzerinde nihai sonucu almak için başka bir sorgu çalıştırabiliyoruz.

Pencere Fonksiyonları

Pencere fonksiyonları, önemli olan ve sizi bir üst seviyeye taşıyabilecek başka bir SQL beceri setidir. Pencere fonksiyonları orta-ileri seviye beceriler olarak kabul edilse de, birçok veri analisti ve bilimci bunların işlerinde vazgeçilmez olduğuna inanır. Bu nedenle, onlarla aşina olmak için biraz zaman ayırmaya değer.

OVER( ) & PARTITION BY Diyelim ki her personelin haftalık kiralama sayısını ve genel toplam kiralama sayısını, personel kimliği ve hafta numarasıyla birlikte bulmak istiyoruz. Aşağıda gösterildiği gibi yukarıdaki örnekte oluşturulan CTE'yi yeniden kullanabiliriz:

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Koddaki OVER ifadesi bunun bir pencere fonksiyonu olduğunu belirtir. Burada, pencere fonksiyonunun (burada SUM) her satıra uygulandığını söylüyoruz. Pencere fonksiyonunu belirli 'bölümler'de uygulamak için PARTITION BY ile birlikte de kullanabiliriz. Diyelim ki şimdi (diğer sütunlara ek olarak) genel toplam yerine hafta başına toplam kiralama sayısını bulmak istiyoruz. Yine, aşağıdaki örnekte önceden oluşturulan CTE'yi kullanıyoruz.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals

Şu sonucu döndürür (yalnızca ilk 10 satır gösterilmiştir):

Ek sütun, kümülatif toplam yerine haftalık toplam kiralama sayılarını içerir.

Burada, birçok şirkette veri analisti veya bilimci olmak için önemli olan pencere fonksiyonlarına yalnızca birkaç basit örnekle giriş yaptık. Bu komutlar hakkında daha fazla bilgi edinmek için verilen bağlantıları kullanmanızı öneririz. Ayrıca, açık kaynaklı bir veritabanını alıp bu farklı komutları denerken yanıtlamak üzere ilgili soruları düşünerek becerilerinizi pratik edebilirsiniz; ya da Orta Düzey SQL kursuna göz atarak SQL becerilerinizi öğrenebilir veya tazeleyebilirsiniz.