Python'dan SQL ile analiz: CTE, pencere fonksiyonları (window functions) ve pandas karşılıkları

Neden Python'dan SQL?

Veri ilanlarının neredeyse hepsinde ilk satır aynıdır: "İleri düzey SQL". Şirketin verisi bir veritabanında ya da veri ambarında (data warehouse) durur; analist, bilimci ve veri mühendisi günün büyük kısmında SQL yazar, sonucu Python'a alıp pandas ile işler, grafiğe ya da modele verir. sqlite3 ile Python dersinde bağlantı, parametreli sorgu ve transaction'ı gördün. Bu ders bir üst katman: analitik SQL. Yani CTE (Common Table Expression) ile sorguyu adım adım kurmak, pencere fonksiyonları (window functions) ile "satırı kaybetmeden" grup hesabı yapmak ve aynı işin pandas karşılığını bilmek.

Bütün örnekler Anadolu Market'in gerçek şemasında: orders (910 sipariş), order_items (1.620 satır), customers (150), products (40). veri.baglan("anadolu_market") tüm tabloları bellek içi bir SQLite veritabanına yükler; SQLite 3.25 ve sonrası pencere fonksiyonlarını destekler (tarayıcıdaki sürüm çok daha yeni).

import veri
import pandas as pd

con = veri.baglan("anadolu_market")
print(con.execute("SELECT sqlite_version()").fetchone()[0])

# pd.read_sql: sorgu sonucunu doğrudan DataFrame yapar; değerleri ? ile parametre olarak ver
sql = """
SELECT channel, status, COUNT(*) AS siparis
FROM orders
WHERE order_date >= ?
GROUP BY channel, status
ORDER BY channel, siparis DESC
"""
df = pd.read_sql(sql, con, params=("2024-01-01",))
print(df.head(6))
print(df.groupby("channel")["siparis"].sum())

Ciro tanımı: önce sözleşme

Analitik sorgunun ilk adımı SQL değil, tanımdır. Bu derste net ciro = quantity * unit_price * (1 - discount); yalnız status IN ('delivered', 'shipped') siparişler sayılır (iptal ve iade hariç). Bu tanımı her sorguda yeniden yazmak yerine bir CTE'ye koyarız ve diğer adımlar onun üzerine kurulur.

CTE: sorguyu adım adım kurmak

WITH ad AS (...) bir sorguya isim verir; sonraki adımlar bu ismi tablo gibi kullanır. İç içe alt sorgu (subquery) ile aynı işi yapar ama yukarıdan aşağı okunur, her adım ayrı test edilebilir ve aynı ara sonuç birden çok yerde kullanılabilir.

import veri
con = veri.baglan("anadolu_market")

sql = """
WITH gecerli AS (                 -- 1) iptal/iade dışı siparişler
    SELECT order_id, customer_id, order_date, channel
    FROM orders
    WHERE status IN ('delivered', 'shipped')
),
satir AS (                        -- 2) satır bazında net tutar
    SELECT g.order_id, g.channel, strftime('%Y', g.order_date) AS yil,
           i.quantity * i.unit_price * (1 - i.discount) AS net
    FROM gecerli g
    JOIN order_items i ON i.order_id = g.order_id
)
SELECT yil, channel, ROUND(SUM(net), 2) AS ciro,
       COUNT(DISTINCT order_id) AS siparis
FROM satir
GROUP BY yil, channel
ORDER BY yil, ciro DESC
"""
for satir in con.execute(sql):
    print(satir)

Pencere fonksiyonları: satırı kaybetmeden grup hesabı

GROUP BY her grubu tek satıra indirir. Pencere fonksiyonu ise her satırı yerinde bırakır ve yanına "penceresine" göre hesaplanan bir değer ekler. Kalıp hep aynıdır:

FONKSIYON(...) OVER (PARTITION BY grup ORDER BY sıra ROWS BETWEEN ... )

import veri
con = veri.baglan("anadolu_market")

sql = """
WITH aylik AS (
    SELECT strftime('%Y-%m', o.order_date) AS ay,
           SUM(i.quantity * i.unit_price * (1 - i.discount)) AS ciro
    FROM orders o JOIN order_items i ON i.order_id = o.order_id
    WHERE o.status IN ('delivered', 'shipped')
    GROUP BY ay
),
hesap AS (
    SELECT ay, ciro,
           LAG(ciro) OVER (ORDER BY ay)                                   AS onceki,
           SUM(ciro) OVER (PARTITION BY substr(ay, 1, 4) ORDER BY ay)     AS ytd,
           AVG(ciro) OVER (ORDER BY ay ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ort3
    FROM aylik
)
SELECT ay, ROUND(ciro) AS ciro,
       ROUND(100.0 * (ciro - onceki) / onceki, 1) AS mom_yuzde,
       ROUND(ytd) AS ytd, ROUND(ort3) AS ort3
FROM hesap
WHERE ay >= '2024-01'          -- filtre pencereden SONRA: Ocak'ın LAG'i Aralık 2023'ü görür
ORDER BY ay
"""
for r in con.execute(sql).fetchall()[:6]:
    print(r)

Tekilleştirme: her müşterinin son siparişi

Analitikte en sık kalıplardan biri "her X'in en son / en büyük / ilk satırı"dır. ROW_NUMBER() grubu numaralar, dış sorgu 1 numarayı alır. Eşitlik ihtimali varsa ORDER BY'a ikinci bir anahtar (ör. order_id) ekle ki sonuç her çalıştırmada aynı olsun.

import veri
con = veri.baglan("anadolu_market")

sql = """
WITH sirali AS (
    SELECT customer_id, order_id, order_date, channel,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY order_date DESC, order_id DESC) AS rn
    FROM orders
)
SELECT customer_id, order_id, order_date, channel
FROM sirali WHERE rn = 1
ORDER BY customer_id
LIMIT 4
"""
for r in con.execute(sql):
    print(r)

Boşluk ve ada (gaps and islands): ardışık günler

"Art arda kaç gün sipariş aldık?" sorusunun klasik hilesi: tarihleri sırala, her tarihten sıra numarasını çıkar. Ardışık günlerde hem tarih hem numara birer artar; fark sabit kalır. Aynı farka sahip satırlar bir "ada"dır.

import veri
con = veri.baglan("anadolu_market")

sql = """
WITH gunler AS (SELECT DISTINCT order_date AS gun FROM orders),
adalar AS (
    SELECT gun, julianday(gun) - ROW_NUMBER() OVER (ORDER BY gun) AS grup
    FROM gunler
)
SELECT MIN(gun) AS bas, MAX(gun) AS son, COUNT(*) AS gun_sayisi
FROM adalar GROUP BY grup
ORDER BY gun_sayisi DESC, bas
LIMIT 3
"""
print(con.execute(sql).fetchall())

JOIN tuzakları: satır çoğalması ve NULL

Sipariş tablosunu sipariş satırlarıyla birleştirdiğin anda tablo 910 satırdan 1.620 satıra çıkar: bir siparişte birden çok ürün vardır. Sonra COUNT(*) ile "sipariş sayısı" saymak ya da sipariş düzeyindeki bir alanı (ör. kargo ücreti) SUM ile toplamak sonucu şişirir. Buna yayılma (fan-out) denir. Üç kural:

• Birleştirmeden önce her tablonun tanecik düzeyini (grain) söyle: "orders: sipariş başına bir satır", "order_items: sipariş-ürün başına bir satır".
• Çoğalan tabloda sipariş say: COUNT(DISTINCT order_id). Daha iyisi: önce satırları sipariş düzeyine topla (CTE), sonra birleştir.
• COUNT(*) satır sayar; COUNT(sutun) NULL olmayanları sayar. customers'ta COUNT(*) 150, COUNT(email) 139'dur.

LEFT JOIN'in ikinci tuzağı: sağ tablo için koşulu WHERE'e yazarsan eşleşmeyen satırlar (NULL'lar) elenir ve LEFT JOIN sessizce INNER JOIN'e döner. "2024'te hiç sipariş vermeyen müşteriler dahil" bir sayım istiyorsan tarih koşulu ON içinde olmalı.

import veri
con = veri.baglan("anadolu_market")

yanlis = """
SELECT COUNT(*) FROM (
  SELECT c.customer_id, COUNT(o.order_id) AS n
  FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
  WHERE o.order_date >= '2024-01-01'            -- NULL'ları eler
  GROUP BY c.customer_id)
"""
dogru = """
SELECT COUNT(*), SUM(n = 0) FROM (
  SELECT c.customer_id, COUNT(o.order_id) AS n
  FROM customers c LEFT JOIN orders o
       ON o.customer_id = c.customer_id AND o.order_date >= '2024-01-01'
  GROUP BY c.customer_id)
"""
print("WHERE ile müşteri sayısı:", con.execute(yanlis).fetchone()[0])
print("ON ile (toplam, 2024'te sıfır):", con.execute(dogru).fetchone())

Tarih fonksiyonları

SQLite'ta tarih yoktur, ISO metni vardır ('2024-03-05'). ISO biçim metin olarak da doğru sıralanır, bu yüzden order_date >= '2024-01-01' çalışır. '05.03.2024' gibi bir biçim ise sıralamayı bozar; önce dönüştür (bkz. veri temizleme dersi).

Aynı iş pandas'ta: çeviri tablosu

import veri
import pandas as pd

o = veri.oku("anadolu_market", "orders")
o = o.sort_values(["customer_id", "order_date", "order_id"])
o["rn"] = o.groupby("customer_id").cumcount() + 1                    # ROW_NUMBER
o["onceki"] = o.groupby("customer_id")["order_date"].shift(1)         # LAG
o["ara_gun"] = (pd.to_datetime(o["order_date"]) - pd.to_datetime(o["onceki"])).dt.days
son = o.drop_duplicates("customer_id", keep="last")                   # her müşterinin son siparişi
print(o[o["customer_id"] == 1][["order_id", "order_date", "rn", "ara_gun"]].head(4))
print(len(son), "müşteri;", "ortalama sipariş arası:", round(o["ara_gun"].mean(), 1), "gün")

DuckDB: DataFrame'e doğrudan SQL

DuckDB, bellekteki DataFrame'i tablo gibi sorgulayan analitik bir motordur; date_trunc, QUALIFY (pencere sonucunu doğrudan filtreleme) gibi kolaylıkları vardır. Ayrıntısı polars ve DuckDB dersinde.

import duckdb
import veri

orders = veri.oku("anadolu_market", "orders")
sonuc = duckdb.sql("""
    SELECT customer_id, order_id, order_date
    FROM orders
    QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) = 1
    ORDER BY customer_id
    LIMIT 3
""").fetchall()
print(sonuc)

SQL'de mi, pandas'ta mı?

Kural: veriyi küçült, sonra çek. SELECT * ile 10 milyon satırı pandas'a alıp groupby yapmak yerine GROUP BY'ı veritabanına yaptır.

Sorgu yavaşsa motorun planına bak. SQLite'ta EXPLAIN QUERY PLAN, PostgreSQL'de EXPLAIN ANALYZE:

import veri
con = veri.baglan("anadolu_market", tablolar=["orders"])
plan = "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = ?"
print(con.execute(plan, (5,)).fetchall())        # SCAN: tüm tablo taranıyor
con.execute("CREATE INDEX ix_orders_customer ON orders(customer_id)")
print(con.execute(plan, (5,)).fetchall())        # SEARCH ... USING INDEX

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///:memory:")
pd.DataFrame({"sehir": ["İzmir", "Van"], "tutar": [100, 40]}).to_sql("satis", engine, index=False)
print(pd.read_sql(text("SELECT * FROM satis WHERE tutar > :esik"), engine, params={"esik": 50}))

import veri
con = veri.baglan("anadolu_market", tablolar=["employees"])
sql = """
WITH RECURSIVE agac(emp_id, ad, seviye) AS (
    SELECT emp_id, first_name, 0 FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.first_name, a.seviye + 1
    FROM employees e JOIN agac a ON e.manager_id = a.emp_id
)
SELECT seviye, COUNT(*) FROM agac GROUP BY seviye ORDER BY seviye
"""
print(con.execute(sql).fetchall())