Düzensiz sensör okumalarının zaman ağırlıklı ortalamasını hesaplayan bir sorgu oluşturun, her değeri bir sonraki örneğe kadar geçerli olduğu süre ile ağırlıklandırın, yalnızca ANSI SQL pencere fonksiyonlarını kullanarak ve prosedürel mantık olmadan.

Sorunun cevabı

Sorunun Tarihi

Zaman ağırlıklı ortalamalar, endüstriyel IoT ve finansal zaman serisi analizinde kritik bir ölçüt olarak ortaya çıktı. Basit aritmetik ortalamalar gerçekliği çarpıtır çünkü sensör değerleri bir sonraki ölçüme kadar devam eder. ANSI SQL:2003 standardından önce, bu ortalamaların hesaplanması prosedürel imleçler veya pahalı kendine birleştirmeler gerektiriyordu. Bu yöntemler O(n²) zaman karmaşıklığında çalışıyordu.

LEAD ve LAG pencere fonksiyonlarının tanıtılması bu alanı devrim niteliğinde değiştirdi. Tek geçiş, set bazlı aralık hesaplamalarına izin verdiler ve O(n) zamanında çalışıyorlar. Bu, veritabanı katmanında milyarlarca satırda gerçek zamanlı analitiklerin yapılmasını mümkün kılıyor.

Problem

readings adlı bir tablo verildiğinde, device_id, ts (zaman damgası) ve value sütunlarıyla, hedef zaman ağırlıklı ortalamayı hesaplamaktır. Her satır, bir sonraki okuma kadar geçen zaman farkına orantılı olarak katkıda bulunmalıdır. Matematiksel olarak bu, $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$ şeklindedir.

Son satır bir sınır koşulunu sunar. Sonraki zaman damgasına sahip değildir, bu nedenle aralığı ya sıfır olarak tanımlanmalı, mevcut zamana kadar uzatılmalı ya da bilinen bir bitiş zamanına kadar sınırlandırılmalıdır. Çözüm, tamamen açıklayıcı kalmak için imleçler, kullanıcı tanımlı fonksiyonlar veya kendine birleştirmelerden kaçınmalıdır.

Çözüm

Bir sonraki zaman damgasını mevcut satıra projekte etmek için LEAD pencere fonksiyonunu kullanın. Ağırlığı elde etmek için epoch farkını hesaplayın. Ardından standart zaman ağırlıklı ortalama formüllerini uygulayın.

WITH weighted AS (
  SELECT
    device_id,
    value,
    ts,
    COALESCE(
      EXTRACT(EPOCH FROM (LEAD(ts) OVER (PARTITION BY device_id ORDER BY ts) - ts)),
      0
    ) AS duration_seconds
  FROM readings
)
SELECT
  device_id,
  SUM(value * duration_seconds) / NULLIF(SUM(duration_seconds), 0) AS time_weighted_avg
FROM weighted
GROUP BY device_id;

Bu yaklaşım, pencerenin her cihaz için sıfırlanmasını sağlamak için PARTITION BY kullanır. Böylece farklı sensörlerden gelen zaman damgalarının karışmasını önler. COALESCE, terminal satırı sıfır ağırlık atayarak etkili bir şekilde paydadan dışlar.

Hayattan bir durum

Bir ilaç üretim hattı 200 biyoreaktörü izliyor. Her biri düzensiz aralıklarla sıcaklık verisi yayıyor—ısıtma aşamalarında her 10 saniyede bir, ama duraklamalarda her 30 dakikada bir. Kalite ekibi, uyumu sağlamak için günlük zaman ağırlıklı bir ortalama talep etti. Basit bir ortalama, hızlı ısıtma örneklerini aşırı ya da stabil tutmaları düşük oy verme potansiyeline sahip olabilirdi, bu da tehlikeli sıcaklık dalgalanmalarını maskeleyebilirdi.

Önerilen bir çözüm tüm verileri Python pandas DataFrame’ine çıkarmak oldu. Mühendisler, zaman damgalarıyla diff() hesaplayacak ve ağırlıklı ortalamayı hesaplayacaktı. Esnek olmasına rağmen, bu yaklaşım ağ üzerinden gigabaytlarca veri transferi yapıyordu. Ayrıca, 90 günlük yüksek frekanslı verileri işleme sırasında analiz istasyonunu çökerterek sorunu artırdı.

Diğer bir alternatif, her cihaz için mevcut satırdan daha büyük olan MIN(ts) değerini bulmak için ilişkili bir alt sorgu kullandı. Bu, 1.000 satırdan oluşan test setlerinde doğru bir şekilde çalıştı. Ancak, daha karmaşık hale geldiğinden doğrusal bir düşüş gösterdi ve bir reaktörün tam tarihini almak 45 dakikasını aldı.

Ekip, ANSI SQL pencere fonksiyonu yaklaşımını benimsedi. Hesaplamayı PostgreSQL kümesinin içinde tutarak, sorgu paralel sıralı taramaları kullandı ve ağ yükünü önledi. Son uygulama, tüm reaktörler boyunca 50 milyon satırı 12 saniyeden kısa sürede işledi. Bu, operatörlerin termal sapmaları dakikalar içinde fark etmesine izin veren gerçek zamanlı pano güncellemeleri sağladı.

Adayların sıklıkla gözden kaçırdığı noktalar

Her partition'da sonraki zaman damgasının olmadığı son gözlemle nasıl başa çıkıyorsunuz?

Adaylar, sıklıkla sınır koşulunu atlarlar. Bu, terminal satırın aralığının NULL olarak değerlendirilmesine neden olur ve SQL toplamları bunu göz ardı eder. Son okumanın katkısı düştüğü için ortalama çarpılır. Doğru yaklaşım, ortalamanın bilinen bir bitiş zamanına kadar uzatılıp uzatılmayacağını belirlemek için COALESCE kullanarak sıfır veya EXTRACT(EPOCH FROM (boundary_time - ts)) değerini almaktır.

SUM(value * duration) / SUM(duration) formülü matematiksel olarak zaman ağırlıklı ortalamayı nasıl ifade eder ve AVG(value) kullanırsanız ne olur?

Bu, ağırlık $w_i$ olarak süreyi kullanan ağırlıklı aritmetik ortalamayı hesaplar. Adaylar, bunu geometrik bir ortalama ile karıştırır ya da AVG(value * duration) kullanmayı denerler, bu da normalizasyon olmadan ürünlerin toplamını üretir. AVG(value) kullanımı, her satırı eşit şekilde ele alır ve uniform zaman adımları varsayar, bu da daha uzun süren değerlerin daha büyük etki yaratması gerekliliğini ihlal eder.

Zamansal boşluklar veya aynı partition içindeki tekrarlanan zaman damgaları LEAD fonksiyonunun davranışını nasıl etkiler ve neden PARTITION BY device_id önemlidir?

Adaylar bazen LEAD fonksiyonunun pencere tasarımında fiziksel sıralar üzerindeki düzeni kullandığını unuturlar. PARTITION BY device_id olmadan, işlev farklı sensörler arasındaki aralıkları hesaplar ve mantıksız negatif veya büyük süreler oluşturur. Ayrıca, eğer tekrar eden zaman damgaları varsa, LEAD her şekilde bir sonraki farklı satırı döndürür, bu da sıfır saniye aralıkları yaratabilir. Adaylar, ilk önce DISTINCT veya ROW_NUMBER() filtrelemesi kullanarak ayıklamayı kararlaştırmalıdır.