الاحتفاظ بصف واحد لكل مفتاح

جزء من قسم ما وراء الأساسيات في رحلة SQL على Coddy — الدرس 26 من 27.

غالبًا ما يكون لديك جدول يظهر فيه مفتاح (عميل، أو مستشعر، أو منتج) عدة مرات، وتريد فقط الاحتفاظ بـ الأحدث، أو السجل الذي يحمل أعلى درجة.

استخدم ROW_NUMBER() بنفس الطريقة التي استخدمتها لـ top-N، ثم احتفظ بـ rn = 1.

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY purchased_at DESC
           ) AS rn
    FROM purchases
)
SELECT * FROM ranked WHERE rn = 1

تفيد عبارة PARTITION BY بـ "تصنيف واحد لكل هذا المفتاح"؛ بينما تفيد عبارة ORDER BY بـ "الأحدث أولاً". الصف رقم 1 في كل قسم هو الصف الذي ترغب في الاحتفاظ به.

التحدي

متوسط

الجداول والأعمدة المتاحة:
readings: sensor_id, recorded_at, value

لكل مستشعر لديه 3 قراءات على الأقل، قم بإرجاع قراءة واحدة وهي القراءة ذات أعلى قيمة. إذا تساوت قراءتان في الـ value، فاحتفظ بالقراءة الأحدث.

يجب أن يتضمن المخرج sensor_id، و recorded_at، و value، مرتبة حسب sensor_id.

ورقة مرجعية

استخدم ROW_NUMBER() مع PARTITION BY للاحتفاظ بأحدث (أو أفضل) صف فقط لكل مفتاح، ثم قم بالتصفية حسب rn = 1:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY purchased_at DESC
           ) AS rn
    FROM purchases
)
SELECT * FROM ranked WHERE rn = 1

PARTITION BY — يعيد تعيين التصنيف لكل مفتاح فريد
ORDER BY ... DESC — يضع الصف المطلوب أولاً (الأحدث، الأعلى، إلخ.)
WHERE rn = 1 — يحتفظ فقط بهذا الصف العلوي لكل قسم

جرّب بنفسك

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC, recorded_at DESC)
    -- COUNT(*) OVER (PARTITION BY sensor_id) لتصفية أجهزة الاستشعار التي تحتوي على >= 3 قراءات
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id