النوافذ التحليلية والترتيب الحتمي

WITH charges AS (
 SELECT 101 shipment_id, 7 customer_id, 10 charge FROM dual UNION ALL
 SELECT 102, 7, 5 FROM dual UNION ALL
 SELECT 103, 7, 8 FROM dual
)
SELECT shipment_id, charge,
 SUM(charge) OVER (PARTITION BY customer_id ORDER BY shipment_id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_charge
FROM charges ORDER BY shipment_id;

SHIPMENT_ID | CHARGE | RUNNING_CHARGE
101         | 10     | 10
102         | 5      | 15
103         | 8      | 23

يضم التقسيم عميلاً واحداً؛ يجمع إطار الصفوف الصريح 10 ثم 15 ثم 23.

SELECT customer_id, shipment_id, booked_at
FROM (
 SELECT customer_id, shipment_id, booked_at,
 ROW_NUMBER() OVER (PARTITION BY customer_id
 ORDER BY booked_at DESC NULLS LAST, shipment_id DESC) AS rn
 FROM shipments
)
WHERE rn = 1
ORDER BY customer_id;

يبقى صف واحد على الأكثر لكل عميل، ويكون المفتاح الفائز حتمياً لمجموعة مدخلات ثابتة.