# PostgreSQL’de En Yakın Değere Sahip Satırı Seçmek
PostgreSQL veritabanınızda belirli bir değere en yakın olan satırı bulmanız mı gerekiyor? Örneğin, bir e-ticaret sitesinde kullanıcının aradığı ürüne en yakın fiyatlı ürünü göstermek, bir hava durumu uygulamasında kullanıcının konumuna en yakın hava istasyonunun verilerini almak veya bir sensör ağında en yakın sensörün ölçümlerini almak isteyebilirsiniz. Bu makalede, PostgreSQL’de bu sorunu nasıl çözebileceğinizi, temel seviyeden ileri seviyeye kadar adım adım, gerçek dünya örnekleriyle ve performans optimizasyon teknikleriyle açıklayacağız.
Öğrenme Yol Haritası
Bu makale, PostgreSQL’de en yakın değere sahip satırı seçme konusunda farklı seviyelerdeki bilgi birikimine sahip kullanıcılar için bir yol haritası sunmaktadır.
Yeni Başlayan: Temel ORDER BY ve LIMIT kullanımını öğrenecek, basit bir örnek üzerinde çalışacağız.
Orta Seviye: Gerçek dünya senaryolarını ele alacak, performans optimizasyon tekniklerini ve farklı yaklaşım yöntemlerini keşfedeceğiz.
İleri Seviye: Karmaşık sorguların performans analizini yapacak, edge case’leri (olağan dışı durumlar) ele alacak ve daha verimli çözümler geliştireceğiz.
PostgreSQL’de Temel Kavramlar: ORDER BY ve LIMIT
PostgreSQL’de en yakın değeri bulmak için öncelikle verileri sıralamamız gerekir. Bunun için ORDER BY deyimini kullanırız. ORDER BY deyimi, belirtilen sütuna göre sonuç kümesini sıralar. ASC (artan) veya DESC (azalan) sıralaması belirtilebilir. LIMIT deyimi ise, sorgu sonucunda döndürülecek satır sayısını sınırlar.
Örneğin, urunler adlı bir tablomuz olduğunu ve bu tablonun fiyat adlı bir sütunu olduğunu varsayalım. 100 TL’ye en yakın fiyatlı ürünü bulmak için aşağıdaki sorguyu kullanabiliriz:
SELECT *
FROM urunler
ORDER BY ABS(fiyat - 100)
LIMIT 1;Bu sorgu, fiyat sütunundaki değerlerin 100 TL’den farkının mutlak değerine göre (ABS()) sonuçları sıralar ve en küçük farka sahip (yani 100 TL’ye en yakın) ilk satırı döndürür.
PostgreSQL’de En Yakın Değeri Bulmak: Adım Adım Uygulama
Şimdi, daha detaylı bir örnek üzerinde çalışalım. Bir hava durumu uygulaması için, kullanıcı konumuna en yakın hava istasyonunu bulmamız gerekiyor. hava_istasyonlari adlı bir tablomuz olduğunu ve bu tablonun enlem, boylam ve istasyon_adi sütunlarını içerdiğini varsayalım. Kullanıcının konumu kullanici_enlem ve kullanici_boylam değişkenlerinde saklı olsun.
1. Mesafe Hesaplama: İki koordinat arasındaki mesafeyi hesaplamak için, büyük daire mesafesi formülünü kullanabiliriz. PostgreSQL’de bu formül, aşağıdaki gibi bir fonksiyon kullanılarak uygulanabilir:
CREATE OR REPLACE FUNCTION mesafe_hesapla(enlem1 double precision, boylam1 double precision, enlem2 double precision, boylam2 double precision)
RETURNS double precision AS $$
DECLARE
radyan_enlem1 double precision := radians(enlem1);
radyan_boylam1 double precision := radians(boylam1);
radyan_enlem2 double precision := radians(enlem2);
radyan_boylam2 double precision := radians(boylam2);
radyan_fark_enlem double precision := radyan_enlem2 - radyan_enlem1;
radyan_fark_boylam double precision := radyan_boylam2 - radyan_boylam1;
BEGIN
RETURN 6371 * acos(cos(radyan_enlem1) * cos(radyan_enlem2) * cos(radyan_boylam2 - radyan_boylam1) + sin(radyan_enlem1) * sin(radyan_enlem2));
END;
$$ LANGUAGE plpgsql;Bu fonksiyon, iki koordinat arasındaki mesafeyi kilometre cinsinden hesaplar.
2. En Yakın İstasyonu Bulma: Şimdi, bu fonksiyonu kullanarak en yakın hava istasyonunu bulabiliriz:
SELECT istasyon_adi, mesafe_hesapla(kullanici_enlem, kullanici_boylam, enlem, boylam) AS mesafe
FROM hava_istasyonlari
ORDER BY mesafe
LIMIT 1;Bu sorgu, her hava istasyonu için kullanıcı konumuna olan mesafeyi hesaplar, mesafelere göre sıralar ve en kısa mesafeye sahip ilk istasyonun adını ve mesafeyi döndürür.
Gerçek Dünya Örneği: En Yakın Ürünü Bulma (E-ticaret)
Bir e-ticaret sitesi düşünelim. Kullanıcılar, belirli bir ürün aradıklarında, stoğu bulunan ve fiyat olarak en yakın ürünü görmek isteyebilirler. urunler tablomuzda urun_adi, fiyat, stok_adedi ve kategori_id sütunları olsun. Kullanıcı 150 TL’lik bir ürünü arıyor ve aynı kategorideki en yakın fiyatlı ürünü bulmak istiyor.
SELECT urun_adi, fiyat
FROM urunler
WHERE kategori_id = (SELECT kategori_id FROM urunler WHERE fiyat = 150) --Aynı kategori şartı
AND stok_adedi > 0
ORDER BY ABS(fiyat - 150)
LIMIT 1;Bu sorgu, öncelikle 150 TL’lik ürünün kategorisini bulur ve sonra aynı kategorideki ürünlerden stokta olanları, fiyat farkına göre sıralayarak en yakın fiyatlı ürünü döndürür.
Performans Optimizasyonu
Veri setiniz büyükse, yukarıdaki sorguların performansı düşebilir. Performansı artırmak için aşağıdaki teknikleri kullanabilirsiniz:
* İndeksler: enlem, boylam, fiyat ve kategori_id sütunlarına indeks eklemek sorgu performansını önemli ölçüde artırabilir.
* Giyotin (PostGIS): Konumsal verilerle çalışıyorsanız, PostGIS uzantısını kullanarak daha verimli coğrafi sorgular yazabilirsiniz. PostGIS, coğrafi veriler için optimize edilmiş fonksiyonlar ve indeksleme mekanizmaları sağlar. ST_DWithin fonksiyonu, belirli bir mesafe içindeki noktaları bulmak için kullanılabilir.
* CTE (Common Table Expression): Karmaşık sorguları daha okunaklı ve optimize edilebilir hale getirmek için CTE’ler kullanabilirsiniz.
* Fonksiyon indeksleri: mesafe_hesapla fonksiyonu için bir fonksiyon indeksi oluşturarak performansı iyileştirebilirsiniz.
İleri Seviye Teknikler: Edge Case’ler ve Performans Analizi
* Çoklu En Yakın Değerler: Eğer birden fazla ürün aynı mesafede ise, LIMIT 1 yerine LIMIT N kullanarak birden fazla sonucu alabilirsiniz.
* Boş Sonuçlar: Eğer en yakın değere sahip bir satır bulunmazsa, sorgu boş bir sonuç kümesi döndürür. Bu durumu kontrol etmek için COUNT(*) fonksiyonunu kullanabilirsiniz.
* Performans Analizi: EXPLAIN ANALYZE komutu, sorguların performansını analiz etmek ve iyileştirme alanlarını belirlemek için kullanılabilir.
Sıkça Sorulan Sorular (SSS)
1. En yakın değere sahip birden fazla satır varsa ne olur? LIMIT deyimini kullanarak döndürülecek satır sayısını sınırlayabilirsiniz. Örneğin, LIMIT 3 komutu en yakın üç satırı döndürür.
2. PostGIS kullanmanın avantajları nelerdir? PostGIS, coğrafi veriler için optimize edilmiş fonksiyonlar ve indeksleme mekanizmaları sağlayarak performansı önemli ölçüde artırır.
3. Performans sorunlarıyla karşılaştığımda ne yapmalıyım? EXPLAIN ANALYZE komutunu kullanarak sorgunuzun performansını analiz edin ve indeksler, CTE’ler ve PostGIS gibi optimizasyon tekniklerini deneyin. Veritabanınızın yapılandırmasını ve donanım özelliklerini de göz önünde bulundurun.
4. Fonksiyon indekslerinin amacı nedir? Fonksiyon indeksleri, fonksiyonların sonuçlarına göre indeksleme yaparak, fonksiyon çağrılarını içeren sorguların performansını artırır.
5. Hangi durumlarda büyük daire mesafesi formülünü kullanmalıyım? Yeryüzünde iki nokta arasındaki en kısa mesafeyi (geodesik mesafe) hesaplamak için büyük daire mesafesi formülü kullanılır. Bu formül, özellikle uzun mesafeler için daha doğrudur.
Yazar: Fatih Soysal