دوال النافذة: ROW_NUMBER، RANK، SUM OVER

احسب الرتب والمجاميع التراكمية والمقارنات داخل المجموعة دون دمج الصفوف بفضل OVER وPARTITION BY.

10 دقيقة · 3 أسئلة

تدمج GROUP BY الصفوف في مجموعات؛ أما دالة النافذة فتحسب تجميعًا أو رتبة مع الإبقاء على كل صف. إنها الأداة المناسبة لأعلى N في كل مجموعة، وللمجاميع التراكمية، وللمقارنات بمتوسط فريقك نفسه. متوفّرة في SQLite منذ الإصدار 3.25 (2018). حمِّل مجموعة البيانات.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);

ثلاث دوال ترتيب، وثلاثة سلوكيات عند مواجهة التعادلات: تُرقّم ROW_NUMBER دون تكرار (فضّ التعادل عشوائيًا)، وتمنح RANK المتعادلين الرتبة نفسها ثم تتخطّى (1، 2، 2، 4)، أما DENSE_RANK فلا تتخطّى (1، 2، 2، 3). وتُجنّبك عبارة WINDOW تكرار تعريف النافذة (مدعومة في SQLite وPostgreSQL، لا في SQL Server).

SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);

تُعيد PARTITION BY بدء الحساب من جديد لكل مجموعة فرعية: نافذة واحدة لكل قسم، دون دمج أي شيء. وبدمجها مع تعبير CTE، تحصل على نمط مقابلات العمل الأشهر: الأعلى أجرًا بعدد N في كل قسم. إن تعبير CTE إلزامي لأن دالة النافذة تُقيَّم بعد WHERE: فلا يمكنك التصفية على rn مباشرةً.

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;

تتحوّل التجميعات الكلاسيكية إلى نوافذ باستخدام OVER: تُنتِج SUM(salary) OVER (ORDER BY ...) مجموعًا تراكميًا صفًا صفًا. حدّد الإطار باستخدام ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW للحصول على مجموع تراكمي صفًا صفًا بدقّة.

SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_total
FROM employees;

اختبار المعرفة

تأكّد من أنك تذكّرت النقاط الأساسية في هذا الدرس.

1. بأجور 5200 و4800 و4800 و4100، أيّ رتبة تمنحها RANK() للأجر 4100؟
   • 3
   • 4
   • 2
2. لماذا تحتاج إلى تعبير CTE للتصفية على ROW_NUMBER()؟
   • يُقيَّم OVER بعد WHERE، لذا يُمنع بداخلها
   • تعبير CTE دائمًا أكثر كفاءة
   • لا تعمل ROW_NUMBER إلا داخل WITH
3. ماذا تفعل PARTITION BY dept داخل عبارة OVER؟
   • تفرز النتيجة النهائية حسب القسم
   • تُعيد بدء حساب النافذة لكل قسم
   • تدمج الصفوف في صف واحد لكل قسم