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

Ön Hazırlık ve Gereksinimler

Uygulamaları gerçekleştirmek için PostgreSQL 16+ veya MySQL 8.4+ gibi güncel bir ilişkisel veritabanı yönetim sistemi (RDBMS) kullanmanız önerilir. Bu örneklerde SQL standartlarına sadık kalınmış olup, veritabanı üzerinde okuma yetkisine sahip bir kullanıcı hesabına ihtiyacınız olacaktır.

• Veritabanı: PostgreSQL 16 veya güncel bir SQL motoru.
• Araçlar: DBeaver, pgAdmin veya terminal üzerinden SQL istemcisi.
• Veri Seti: İşlem geçmişi (transactions) içeren bir tablo yapısı.

Adım 1: Z-Skoru (Z-Score) ile İstatistiksel Aykırı Değer Tespiti

Z-skoru, bir değerin ortalamadan kaç standart sapma uzaklıkta olduğunu gösterir. SQL kullanarak verilerinizdeki normal dağılımın dışına çıkan kayıtları tespit etmek için bu yöntemi kullanabiliriz. Aşağıdaki kod, işlem tutarlarında ortalamanın 3 standart sapma dışına çıkan şüpheli kayıtları listeler.

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

Bu sorgu, WITH yan tümcesi ile önce genel istatistikleri hesaplar, ardından ana sorguda her satırı bu değerlerle kıyaslar. 3 standart sapma kuralı, verilerin %99.7'sini kapsar; bu sınırın dışındakiler genellikle anomali olarak kabul edilir.

Adım 2: Zaman Serisi Analizi ile Hız Aşımı Tespiti

Bir kullanıcı kısa sürede çok fazla işlem yapıyorsa bu durum bir bot saldırısı veya veri giriş hatası olabilir. SQL'in LAG() pencere fonksiyonunu kullanarak, bir kullanıcının ardışık iki işlemi arasındaki süreyi hesaplayabiliriz.

SELECT kullanici_id, islem_tarihi,
       islem_tarihi - LAG(islem_tarihi) OVER (PARTITION BY kullanici_id ORDER BY islem_tarihi) as gecen_sure
FROM islemler
WHERE gecen_sure < INTERVAL '1 second';

Bu kod bloğu, aynı kullanıcıya ait ardışık işlemler arasındaki farkı saniyeler bazında hesaplar. Eğer 1 saniyeden kısa sürede işlem yapılmışsa, bu kayıtları "hızlı işlem anormalliği" olarak işaretleyebiliriz.

Adım 3: Veri Tutarlılığı ve Eksik Değer Kontrolü

Veritabanı anormallikleri sadece aykırı değerlerle sınırlı değildir; bazen verinin kendisi mantıksal olarak hatalıdır. Örneğin, bir siparişin tutarının negatif olması veya tarih alanının gelecekte olması bir anormalliktir.

SELECT * FROM siparisler
WHERE siparis_tutari  CURRENT_DATE;

Bu temel sorgu, iş mantığınıza aykırı olan "kirli verileri" tespit eder. Bu tür kontrolleri düzenli olarak bir "cron job" veya veritabanı tetikleyicisi (trigger) ile çalıştırmak, veri kalitesini artırır.

Adım 4: Gruplandırma ile Davranışsal Anormallik Tespiti

Bazı durumlarda anormallik, tekil bir kayıtta değil, bir grubun toplam davranışında gizlidir. Örneğin, normalde günlük 100 işlem alan bir kategorinin aniden 5000 işlem alması bir anormalliktir.

SELECT kategori_id, COUNT(*) as islem_sayisi
FROM islemler
WHERE islem_tarihi >= CURRENT_DATE - INTERVAL '1 day'
GROUP BY kategori_id
HAVING COUNT(*) > (SELECT AVG(gunluk_ort) FROM (SELECT COUNT(*) as gunluk_ort FROM islemler GROUP BY kategori_id) as alt_sorgu) * 5;

Bu sorgu, günlük işlem ortalamasının 5 katından fazla işlem alan kategorileri bulur. Bu yöntem, sistemdeki ani yük artışlarını veya olası siber saldırıları tespit etmek için oldukça etkilidir.

Anormallik Tespit Yöntemlerinin Karşılaştırılması

Adım 5: Güvenlik ve Performans Optimizasyonu

Anormallik tespiti sorguları genellikle büyük veri tabloları üzerinde çalıştığı için performans sorunlarına yol açabilir. Bu sorguları üretim ortamında (production) çalıştırırken mutlaka dizinleme (indexing) yapmalısınız.

CREATE INDEX idx_islem_tarihi_kullanici ON islemler(kullanici_id, islem_tarihi);

Dizinleme, sorgu süresini milisaniyelere indirir. Ancak çok sık yazma işlemi yapılan tablolarda aşırı dizinleme performans kaybına neden olabilir, bu yüzden dikkatli olunmalıdır.

Kritik Güvenlik Uyarısı: Veritabanı sorgularınızda asla kullanıcıdan gelen verileri doğrudan sorgu içine yerleştirmeyin. SQL Injection riskine karşı her zaman "Prepared Statements" (Hazırlanmış İfadeler) kullanın. Ayrıca, anormallik tespit sorgularınızı "read-only" bir kullanıcı ile çalıştırarak veritabanı güvenliğini artırın.

Sıkça Sorulan Sorular

Anormallik tespiti için veritabanı tetikleyicileri (triggers) kullanılmalı mı?

Küçük ölçekli ve anlık kontroller için tetikleyiciler kullanılabilir. Ancak büyük veri setlerinde ve karmaşık istatistiksel analizlerde tetikleyiciler veritabanını yavaşlatacağı için, bu kontrolleri bir arka plan işi (background job) olarak çalıştırmak daha sağlıklıdır.

Verilerim normal dağılım göstermiyorsa ne yapmalıyım?

Z-skoru yerine "IQR" (Çeyrekler Arası Açıklık) yöntemini kullanabilirsiniz. Bu yöntem, verinin dağılım şeklinden bağımsız olarak aykırı değerleri tespit etmede daha dirençlidir.

Bu sorgular veritabanı performansını nasıl etkiler?

Karmaşık sorgular, özellikle GROUP BY ve OVER fonksiyonları yoğun CPU kullanımı gerektirir. Bu analizleri yoğun saatler dışında veya veritabanının bir kopyası (replica) üzerinde çalıştırmanız önerilir.

Hangi verilerin anomali olduğunu nasıl belirlerim?

Anomali, iş mantığınıza göre değişir. Bir finans uygulamasında 100 TL'lik bir işlem normal olabilirken, bir e-ticaret sitesinde 100.000 TL'lik bir sipariş anomali olabilir. Eşik değerlerinizi iş ihtiyaçlarınıza göre dinamik olarak belirlemelisiniz.

SQL dışında bir dil kullanmalı mıyım?

SQL, veritabanı içindeki ham veriyi temizlemek ve tespit etmek için en hızlı yoldur. Ancak çok karmaşık makine öğrenmesi modelleri gerekiyorsa, veriyi SQL ile çekip Python gibi dillerde işlemek daha verimli olabilir.

Anomali Tespit Sistemlerinde Hata Ayıklama ve Doğrulama Stratejileri

Anomali tespit sorgularınızı canlı veritabanında çalıştırmadan önce, sistemin yanlış pozitif (false positive) veya yanlış negatif (false negative) sonuçlar üretmediğinden emin olmalısınız. Yanlış pozitifler, normal işlemleri anomali olarak işaretleyerek operasyonel iş akışını aksatabilir.

Test Veri Seti Oluşturma

Üretim ortamındaki verileri bozmadan, sentetik verilerle sorgularınızı test etmek en güvenli yoldur. Aşağıdaki SQL bloğu, belirli bir aralıkta normal veriler ve araya serpiştirilmiş "anomali" verileri oluşturmanıza yardımcı olur:

-- Test tablosu oluşturma
CREATE TABLE test_islemler (
    id INT PRIMARY KEY,
    tutar DECIMAL(10,2),
    islem_tarihi TIMESTAMP
);

-- Normal veriler ekleme
INSERT INTO test_islemler (id, tutar, islem_tarihi)
SELECT i, (RANDOM() * 100) + 50, NOW() - (i || ' minutes')::INTERVAL
FROM generate_series(1, 100) AS i;

-- Bilinçli anomali ekleme
INSERT INTO test_islemler (id, tutar, islem_tarihi)
VALUES (101, 5000.00, NOW()); -- Çok yüksek tutarlı anomali

Yanlış Pozitif Oranını İzleme

Sorgularınızın başarısını ölçmek için bir "Doğruluk Tablosu" tutmanız önerilir. Tespit edilen bir anomalinin gerçekten bir hata mı yoksa istisnai bir durum mu olduğunu işaretleyen bir sütun ekleyerek modelinizi eğitebilirsiniz.

Büyük Ölçekli Veritabanlarında Performans Yönetimi

Milyonlarca satırlık tablolarda anomali tespiti yapmak, veritabanı kaynaklarını (CPU ve I/O) tüketebilir. Bu durumu engellemek için "Materialized View" (Materyalleştirilmiş Görünüm) kullanımı kritik öneme sahiptir.

Materyalleştirilmiş Görünümler ile Özetleme

Her sorguda tüm tabloyu taramak yerine, saatlik veya günlük özet tablolar oluşturarak anomali tespitini bu özet veriler üzerinden yapın.

-- Özet tablo oluşturma
CREATE MATERIALIZED VIEW mv_saatlik_ozet AS
SELECT 
    date_trunc('hour', islem_tarihi) AS saat,
    AVG(tutar) AS ortalama_tutar,
    STDDEV(tutar) AS standart_sapma
FROM islemler
GROUP BY 1;

-- Anomali tespiti için bu görünümü sorgulama
SELECT * FROM mv_saatlik_ozet 
WHERE standart_sapma > 500;

İpucu: Eğer veritabanınız çok yoğunsa, bu sorguları yoğun saatlerin dışında (örneğin gece 03:00) çalışacak şekilde bir cron job veya veritabanı zamanlayıcısı ile planlayın. Bu sayede canlı trafik üzerindeki yükü minimize edebilirsiniz.

İndeksleme Stratejisi

Anomali tespit sorgularınızda kullandığınız WHERE ve GROUP BY sütunlarına mutlaka indeks ekleyin. Özellikle zaman serisi analizlerinde tarih sütunu üzerinde B-Tree indeksi bulunması, sorgu sürenizi saniyelerden milisaniyelere düşürecektir.

-- Performans artırıcı indeks
CREATE INDEX idx_islem_tarihi_tutar ON islemler (islem_tarihi, tutar);

Sonuç

SQL ile veri anormallik tespiti, sisteminizin sağlığını korumak için kullanabileceğiniz en temel ve etkili yöntemdir. İstatistiksel analizler, zaman serisi takipleri ve mantıksal kontroller ile veritabanınızdaki hataları erkenden yakalayabilirsiniz. Bir sonraki adım olarak, bu tespit ettiğiniz anomalileri otomatik olarak raporlayan bir bildirim sistemi (e-posta veya Slack entegrasyonu) kurarak izleme sürecinizi otomatize edebilirsiniz.