Zaman aralıkları: çakışma, eşzamanlılık ve birleştirme

Aralık verisi her yerde

Bir teslimat kuryenin restorandan aldığı an (picked_at) ile teslim ettiği an (delivered_at) arasında sürer. Otel rezervasyonları, toplantı odaları, abonelik dönemleri, sunucu oturumları hep aynı yapıdadır: [başlangıç, bitiş).

1. İki aralık ne zaman çakışır?

A ve B aralıkları ancak şu koşulla çakışır:

A.baslangic < B.bitis AND B.baslangic < A.bitis

Dört farklı durum (biri diğerini kapsar, soldan, sağdan, iç içe) bu tek koşulla yakalanır. Çakışmama koşulu ise tersidir: biri diğeri başlamadan biter. Aynı kuryenin aynı anda iki teslimat taşıdığı durumlar:

SELECT a.courier_id, a.order_id, b.order_id AS diger
FROM lk_orders a
JOIN lk_orders b
  ON b.courier_id = a.courier_id
 AND a.order_id < b.order_id
 AND a.picked_at < b.delivered_at
 AND b.picked_at < a.delivered_at
WHERE a.status = 'teslim' AND b.status = 'teslim'
LIMIT 10;

a.order_id < b.order_id her çifti bir kez sayar.

2. Aynı anda kaç iş sürüyor? (olay yöntemi)

Her aralığı iki olaya böl: başlangıçta +1, bitişte −1. Olayları zamana göre sırala ve kümülatif topla; her andaki toplam, o anda süren iş sayısıdır.

WITH olay AS (
  SELECT picked_at AS an, 1 AS d FROM lk_orders WHERE status = 'teslim'
  UNION ALL
  SELECT delivered_at, -1 FROM lk_orders WHERE status = 'teslim'
),
k AS (
  SELECT an, SUM(d) OVER (ORDER BY an, d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS yolda
  FROM olay
)
SELECT MAX(yolda) AS en_yogun FROM k;

Sıralamada aynı anda biten ve başlayan iki olay varsa ORDER BY an, d önce −1'i işler; böylece biten iş, başlayanla “aynı anda” sayılmaz.

3. Çakışan aralıkları birleştirmek

Bir kuryenin “meşgul” dönemleri: iç içe ya da çakışan teslimatları tek bir dönemde birleştir. Bu, boşluklar ve adalar probleminin aralık hâlidir: bir aralık, önceki aralıkların en geç bitişinden sonra başlıyorsa yeni bir ada başlatır.

WITH s AS (
  SELECT picked_at AS bas, delivered_at AS bit,
         MAX(delivered_at) OVER (ORDER BY picked_at ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS onceki_bitis
  FROM lk_orders
  WHERE courier_id = 55 AND status = 'teslim' AND picked_at >= '2024-12-01' AND picked_at < '2025-01-01'
),
i AS (
  SELECT bas, bit, SUM(CASE WHEN onceki_bitis IS NULL OR bas > onceki_bitis THEN 1 ELSE 0 END)
           OVER (ORDER BY bas ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ada
  FROM s
)
SELECT ada, MIN(bas) AS baslangic, MAX(bit) AS bitis, COUNT(*) AS teslimat
FROM i GROUP BY ada ORDER BY ada;