Sql & Veritabanı İle Uygulamalar İçin Veri Anomali Tespiti Nasıl Yapılır?

Gereksinimler ve Ön Hazırlık

Uygulamalarınızda veri anomali tespiti yapmak için modern bir ilişkisel veritabanı yönetim sistemi (RDBMS) kullanmanız gerekmektedir. Örneklerimizde PostgreSQL ve standart SQL sözdizimi temel alınmıştır. Çalışma ortamınızda şu araçların kurulu olduğundan emin olun:

• PostgreSQL 16 veya üzeri (Window fonksiyonları desteği için).
• SQL sorgularını test edebileceğiniz bir arayüz (pgAdmin, DBeaver veya komut satırı).
• İstatistiksel hesaplamalar yapabilmek için veritabanı üzerinde okuma yetkisine sahip bir kullanıcı hesabı.

Ön hazırlık olarak, üzerinde çalışacağımız islemler tablosunu oluşturalım. Bu tablo, kullanıcıların para transferlerini temsil eden bir yapıya sahip olacaktır.

CREATE TABLE islemler (
    id SERIAL PRIMARY KEY,
    kullanici_id INT NOT NULL,
    tutar DECIMAL(12, 2) NOT NULL,
    islem_tarihi TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Adım 1: Eşik Değerleri (Threshold) ile Basit Anomali Tespiti

En temel anomali tespiti yöntemi, önceden tanımlanmış sabit eşik değerlerini kullanmaktır. Örneğin, bir kullanıcının tek seferde yapabileceği maksimum transfer tutarını belirleyerek, bu değerin üzerindeki tüm işlemleri "anomali" olarak işaretleyebiliriz.

-- 50.000 TL üzerindeki işlemleri şüpheli olarak işaretle
SELECT id, kullanici_id, tutar 
FROM islemler 
WHERE tutar > 50000;

Bu yöntem hızlıdır ancak dinamik değildir. Farklı kullanıcı profilleri için farklı limitler gerekebilir. Bu nedenle, sadece statik limitlere güvenmek yerine, verinin kendi dağılımını analiz etmemiz gerekir.

Adım 2: Z-Skoru ile İstatistiksel Anomali Tespiti

Z-skoru, bir değerin ortalamadan ne kadar uzak olduğunu standart sapma cinsinden ifade eder. Z-skoru 3'ten büyük veya -3'ten küçük olan veriler, istatistiksel olarak "aykırı değer" (outlier) kabul edilir. SQL'de bu işlemi AVG() ve STDDEV() fonksiyonları ile gerçekleştirebiliriz.

WITH istatistikler AS (
    SELECT AVG(tutar) as ortalama, STDDEV(tutar) as sapma 
    FROM islemler
)
SELECT i.*
FROM islemler i, istatistikler s
WHERE ABS(i.tutar - s.ortalama) > (3 * s.sapma);

Bu sorgu, tüm veriler arasındaki genel sapmayı hesaplar. Eğer verinizde büyük bir varyans varsa, bu yöntem çok sayıda hatalı pozitif sonuç üretebilir. Bu yüzden veriyi gruplandırarak (örneğin kullanıcı bazlı) analiz etmek daha sağlıklıdır.

Adım 3: Hareketli Ortalama ile Zaman Serisi Analizi

Zaman serisi verilerinde anomali tespiti için hareketli ortalama (moving average) kullanılır. Bir kullanıcının son 10 işlemine bakarak, güncel işlemin bu ortalamadan çok sapıp sapmadığını kontrol ederiz. PostgreSQL'in AVG() OVER() pencere fonksiyonu burada devreye girer.

SELECT id, kullanici_id, tutar,
       AVG(tutar) OVER (PARTITION BY kullanici_id ORDER BY islem_tarihi ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) as son_10_islem_ortalamasi
FROM islemler;

Bu sorgu, her bir işlem için kullanıcının önceki 10 işleminin ortalamasını hesaplar. Eğer mevcut tutar, bu ortalamanın 2 katından fazlaysa, bu bir anomali sinyalidir.

Veritabanı Anomali Tespit Yöntemleri Karşılaştırması

Adım 4: SQL Trigger ile Otomatik Tespit ve Uyarı Mekanizması

Anomali tespiti sadece bir sorgu değil, aynı zamanda bir koruma mekanizması olmalıdır. Bir işlem veritabanına kaydedilmeden önce veya hemen sonra, bir tetikleyici (trigger) ile anomalileri yakalayabilir ve bunları bir anomali_log tablosuna yazabiliriz.

CREATE TABLE anomali_log (
    id SERIAL PRIMARY KEY,
    islem_id INT,
    tespit_zamani TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION kontrol_et_anomali() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.tutar > 100000 THEN
        INSERT INTO anomali_log(islem_id) VALUES (NEW.id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_anomali_tespit
AFTER INSERT ON islemler
FOR EACH ROW EXECUTE FUNCTION kontrol_et_anomali();

Güvenlik Uyarısı: Veritabanı tetikleyicileri (triggers) ağır işlemlerde performansı düşürebilir. Üretim ortamında (production) karmaşık hesaplamaları tetikleyicilerde değil, arka plan işçilerinde (background workers) yapmanızı öneririz.

Adım 5: SQL Injection'a Karşı Güvenli Sorgulama

Anomali tespiti yaparken kullandığınız parametreleri (örneğin kullanıcı ID'si veya tarih aralıkları) doğrudan sorguya eklemeyin. SQL Injection riskini önlemek için her zaman parametreli sorgular (prepared statements) kullanın.

-- Güvensiz kullanım (YAPMAYIN):
-- "SELECT * FROM islemler WHERE kullanici_id = " + input_id;

-- Güvenli kullanım (DOĞRU):
PREPARE sorgu_plan (int) AS 
SELECT * FROM islemler WHERE kullanici_id = $1;
EXECUTE sorgu_plan(123);

Bu yapı, veritabanının sorgu planını önceden derlemesini sağlar ve dışarıdan gelen verinin bir komut olarak çalıştırılmasını engeller.

Sorumluluk Reddi: Bu rehberde paylaşılan kod örnekleri eğitim amaçlıdır. Veritabanı güvenliği, şifreleme ve erişim kontrolü, sisteminizin genel güvenliği için bir bütün olarak ele alınmalıdır. Kritik finansal sistemlerde profesyonel güvenlik denetimi şarttır.

Sıkça Sorulan Sorular

Anomali tespiti için en iyi yöntem hangisidir?

Tek bir "en iyi" yöntem yoktur. Verinizin yapısına göre değişir. Finansal verilerde Z-skoru ve hareketli ortalama kombinasyonu genellikle en iyi sonuçları verir.

SQL ile gerçek zamanlı tespit mümkün mü?

Evet, ancak tetikleyiciler (triggers) yerine veritabanı seviyesinde çalışan 'Stream Processing' araçlarını kullanmak daha performanslıdır.

Aykırı değerleri silmeli miyim?

Asla doğrudan silmeyin. Bu verileri ayrı bir tabloda (anomali_log) saklayıp incelemek, sistemin neden bu şekilde davrandığını anlamanızı sağlar.

Veritabanı performansı nasıl etkilenir?

Karmaşık hesaplamalar (STDDEV gibi) büyük tablolarda yavaştır. Bu işlemleri indekslenmiş kolonlar üzerinde yapmaya özen gösterin.

Hangi veritabanı motoru daha iyidir?

PostgreSQL, pencere fonksiyonları ve istatistiksel genişletmeleri (extensions) ile anomali tespiti için en güçlü ilişkisel veritabanıdır.

İleri Seviye Optimizasyon: Büyük Veri Kümelerinde Performans Yönetimi

Milyonlarca satırlık tablolarda anomali tespiti yapmak, veritabanı kaynaklarını ciddi oranda tüketebilir. Özellikle AVG(), STDDEV() veya pencere fonksiyonlarını (window functions) tüm tablo üzerinde çalıştırmak, sistemin kilitlenmesine neden olabilir. Bu durumu yönetmek için "Parçalı Analiz" (Partitioning) ve "Özet Tablolar" (Materialized Views) stratejilerini kullanmalısınız.

Performansı artırmak için en etkili yöntem, veriyi zaman dilimlerine bölerek analiz etmektir. Örneğin, tüm geçmiş veriyi değil, sadece son 24 saati veya son 7 günü kapsayan bir görünüm (view) oluşturarak sorgu maliyetini düşürebilirsiniz.

Özet Tablolar ile Sorgu Hızlandırma

PostgreSQL gibi sistemlerde, her sorguda hesaplama yapmak yerine, verileri periyodik olarak özetleyen tablolar oluşturmak anomali tespitini milisaniyeler seviyesine indirir.

-- Saatlik özet tablosu oluşturma
CREATE MATERIALIZED VIEW hourly_metrics_summary AS
SELECT 
    date_trunc('hour', created_at) AS hour_bucket,
    AVG(value) AS avg_val,
    STDDEV(value) AS stddev_val
FROM sensor_data
GROUP BY 1;

-- İndeks ekleyerek sorguyu hızlandırın
CREATE INDEX idx_hour_bucket ON hourly_metrics_summary(hour_bucket);

Anomali Tespitinde Hata Ayıklama ve Doğrulama Süreçleri

Bir anomali tespit algoritması kurduğunuzda, sistemin "yanlış pozitif" (false positive) üretip üretmediğini doğrulamak kritik bir adımdır. Eğer sisteminiz normal bir işlemi anomali olarak işaretliyorsa, eşik değerleriniz (threshold) çok katı olabilir. Bu durumu yönetmek için bir "Denetim Tablosu" (Audit Table) oluşturmanız önerilir.

Doğrulama İçin Denetim Tablosu Kullanımı

Tespit edilen anomalileri doğrudan bir log tablosuna yazarak, sistemin başarısını zaman içerisinde ölçebilirsiniz. Bu yöntem, modelinizin hassasiyetini (precision) ve geri çağırma (recall) oranını manuel olarak takip etmenizi sağlar.

-- Anomali kayıt tablosu
CREATE TABLE anomaly_audit_log (
    id SERIAL PRIMARY KEY,
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    anomaly_value FLOAT,
    threshold_used FLOAT,
    is_confirmed BOOLEAN DEFAULT FALSE
);

-- Anomaliyi loglayan bir prosedür örneği
CREATE OR REPLACE PROCEDURE log_anomaly(val FLOAT, thresh FLOAT)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO anomaly_audit_log(anomaly_value, threshold_used)
    VALUES (val, thresh);
END;
$$;

Hata Ayıklama İpuçları:

• Veri Dağılımını Kontrol Edin: Verileriniz normal dağılıma (Gaussian) sahip değilse, Z-Skoru yöntemi yanıltıcı sonuçlar verebilir. Bu durumda medyan tabanlı (Median Absolute Deviation) yöntemlere geçiş yapın.
• Boş Değerleri (NULL) Yönetin: AVG() fonksiyonu NULL değerleri yok sayar, ancak bu durum hesaplamalarınızda sapmalara yol açabilir. COALESCE(value, 0) kullanarak boş değerleri sıfır ile normalize edin.
• Zaman Damgası Hassasiyeti: Sunucu saatleri ile uygulama saatleri arasındaki farklar, zaman serisi analizlerinde "hayalet anomaliler" yaratabilir. Daima UTC zaman dilimini kullanın.

Sonuç

SQL ile veri anomali tespiti, sisteminizin sağlığını korumak için atabileceğiniz en proaktif adımlardan biridir. Statik eşiklerden başlayarak, zamanla daha karmaşık istatistiksel modellere geçiş yapabilirsiniz. Bir sonraki adım olarak, veritabanınızdaki verileri dışa aktarıp Python'un pandas veya scikit-learn kütüphaneleri ile daha gelişmiş makine öğrenmesi tabanlı anomali tespiti tekniklerini araştırmanızı öneririm. Veritabanı seviyesindeki temel analizler, bu tür ileri seviye çalışmalar için en sağlam temeli oluşturacaktır.