دوال النوافذ في SQLite: OVER وPARTITION BY والإطارات

دوال النوافذ في SQLite تضيف عموداً دون أن تُقلّص الصفوف

عبارة GROUP BY تختزل عدة صفوف إلى صفّ واحد، أمّا دوال النوافذ (window functions) فتعمل بمنطق مختلف تماماً: تحسب قيمة معيّنة عبر مجموعة من الصفوف المترابطة، لكنها تُبقي كلّ صفّ من المدخلات ظاهراً في الناتج. بهذه الطريقة تحصل على التفاصيل سطراً سطراً والقيمة الإجمالية في آنٍ واحد.

الصيغة العامة ثابتة دائماً: اسم الدالة، يليه OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

يعرض العمود total_all المجموع الكلي لكل الصفوف مكرراً في كل سطر، مع إبقاء الصفوف الأصلية كما هي. قارن ذلك بـ SELECT SUM(amount) FROM sales؛ ستحصل على نفس الرقم، لكنه يأتيك في صف واحد فقط. أما دوال النوافذ في SQLite فتمنحك المنظورين معاً في وقت واحد.

استخدام PARTITION BY: التجميع داخل المجموعات

عبارة OVER () الفارغة تُجمِّع على مستوى الجدول بأكمله. وعند إضافة PARTITION BY يصبح التجميع داخل المجموعات، تماماً كما يفعل GROUP BY، لكن دون دمج الصفوف أو اختزالها.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

كل صف يحصل على إجمالي منطقته ونصيبه من هذا الإجمالي. لو استخدمت GROUP BY العادي، كنت ستفقد تفاصيل كل موظف على حدة. وهذه هي الميزة الكبرى لدوال النوافذ في SQLite: التفاصيل والتجميع في استعلام واحد.

دوال الترتيب: ROW_NUMBER و RANK و DENSE_RANK

عائلة دوال الترتيب ترقّم الصفوف بناءً على ORDER BY المكتوب داخل OVER. والأنواع الثلاثة تختلف في طريقة التعامل مع القيم المتساوية (التعادل).

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

قراءة المخرجات:

• دالة ROW_NUMBER() تُعطي رقمًا فريدًا دائمًا — وعند تساوي القيم يتم كسر التعادل بشكل عشوائي. استخدمها عندما تحتاج إلى رقم ثابت ومميز لكل صف.
• دالة RANK() تمنح الصفوف المتساوية نفس الترتيب، ثم تتخطى الأرقام التالية. فإذا تعادل لاعبان في المركز الأول، يأتي اللاعب التالي في المركز 3.
• دالة DENSE_RANK() تتعامل مع التعادل بنفس الطريقة، لكنها لا تتخطى أي رقم. فالمركز التالي بعد التعادل سيكون 2.

للحصول على "أفضل N لكل مجموعة"، ادمج دوال الترتيب مع PARTITION BY ثم رشّح النتائج في استعلام خارجي — لأن WHERE لا يمكنها الإشارة مباشرةً إلى دوال النوافذ:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

أعلى موظفَين في الأجور على مستوى كل منطقة.

دالتا LAG و LEAD في SQLite: النظر إلى الصفوف المجاورة

تُرجع LAG(col) قيمة العمود col من الصف السابق ضمن النافذة، بينما تنظر LEAD(col) إلى الصف اللاحق. كلتا الدالتين مثاليتان حين تريد تتبّع التغيّر عبر الزمن أو المقارنة بين صفّ وما يسبقه أو يليه.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

الصف الأول قيمة yesterday فيه NULL — ببساطة لأنه ما فيه صف قبله. تقدر تحدد قيمة افتراضية: LAG(celsius, 1, celsius) OVER (ORDER BY day) راح يستخدم قيمة اليوم نفسه لما ما يكون فيه صف سابق.

أما LEAD فهي الصورة المعاكسة. اجمع الاثنين مع PARTITION BY للحصول على تسلسلات حسب كل مجموعة، مثل مقارنة مبيعات الشهر الحالي بمبيعات الشهر السابق داخل كل منطقة.

حساب المجموع التراكمي باستخدام إطارات النافذة

أضف ORDER BY داخل OVER، وراح تشوف دوال التجميع مثل SUM وAVG وCOUNT تبدأ بالحساب بشكل تراكمي:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

هناك أمران ينبغي الانتباه إليهما:

• SUM(amount) OVER (ORDER BY day) يُمثّل مجموعًا تراكميًا. عند استخدام ORDER BY دون تحديد إطار صريح، يكون الإطار الافتراضي هو "من بداية النافذة حتى الصف الحالي".
• العمود الثاني يستخدم إطارًا صريحًا: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW، وهذه نافذة منزلقة من ثلاثة صفوف — أي متوسط متحرك.

التصوّر الذهني للإطارات (frames): كل دالة نافذة تُحسب على إطار من الصفوف يُعرَّف نسبةً إلى الصف الحالي. أكثر الإطارات استخدامًا:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — مجموع تراكمي (وهو الإطار الافتراضي الضمني).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW — نافذة خلفية متحركة.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — كامل القسم (partition).

ROWS يَعُدّ الصفوف الفعلية واحدًا واحدًا. وهناك أيضًا RANGE الذي يُجمّع الصفوف حسب القيمة، وهو مفيد حين تكون لديك قيم متكررة في عمود ORDER BY وتريد التعامل معها كخطوة واحدة.

دوال FIRST_VALUE و LAST_VALUE و NTILE

إليك بعض دوال النوافذ الإضافية التي يستحق التعرّف عليها:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE و LAST_VALUE تُرجِع أول أو آخر قيمة داخل الإطار. لكن انتبه مع LAST_VALUE: الإطار الافتراضي ينتهي عند CURRENT ROW، فإذا أردت آخر قيمة فعلية في القسم (partition) استخدم ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• NTILE(n) يقسّم الصفوف إلى n مجموعات متساوية تقريبًا — مفيد لحساب الأرباع والمئينات أو لعمل تقسيمات شبيهة بـ A/B testing.

تسمية النافذة باستخدام WINDOW

لمّا تتكرّر نفس عبارة OVER (...) مع أكثر من عمود، يصير الكود مزعجًا ومليئًا بالتكرار. لحسن الحظ، يتيح لك SQLite تعريف النافذة مرّة واحدة وإعادة استخدامها باسمها:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

نفس الاستعلام، لكن بضوضاء أقل. عبارة WINDOW تأتي بعد WHERE وGROUP BY وHAVING، وقبل ORDER BY.

الفرق بين دوال النوافذ و GROUP BY في SQLite

كلاهما يقوم بنوع من التجميع، لكن كل واحد منهما يجيب على سؤال مختلف:

• GROUP BY يُقلِّص النتائج. يُعطيك صفًا واحدًا لكل مجموعة. استخدمه عندما يكون كل ما يهمك هو الملخص النهائي.
• دوال النوافذ تحافظ على البيانات. كل صف من المدخلات يبقى موجودًا، مع إضافة أعمدة محسوبة بجانبه.

إذا وجدت نفسك تستخدم GROUP BY ثم تعيد ربط نتائج التجميع بالجدول الأصلي، فهذه إشارة قوية على أن دالة نافذة (window function) ستُنجز المهمة في استعلام واحد.

مزالق شائعة عند استخدام window functions

• WHERE لا يستطيع الإشارة إلى دوال النوافذ. التصفية تحدث قبل حساب النوافذ. الحل: ضع الاستعلام داخل استعلام فرعي أو CTE، ثم طبّق التصفية في المستوى الخارجي.
• الإطارات الضمنية قد توقعك في فخ. SUM(x) OVER (ORDER BY y) يُعطيك مجموعًا تراكميًا، لأن الإطار الافتراضي هو RANGE UNBOUNDED PRECEDING. إذا كنت تريد مجموع القسم بالكامل، اكتب OVER (PARTITION BY ...) بدون ORDER BY، أو حدّد الإطار بشكل صريح.
• LAST_VALUE يُفاجئ الجميع في المرة الأولى. مع الإطار الافتراضي الذي ينتهي عند الصف الحالي، الدالة تُرجع القيمة الحالية، لا القيمة الأخيرة في القسم. لا بد من تجاوز الإطار الافتراضي يدويًا.
• دوال النوافذ تحتاج SQLite 3.25 أو أحدث (صدر عام 2018). أي إصدار حديث نسبيًا يدعمها، لكن بعض البيئات المضمَّنة (embedded) قد تكون متأخرة.

التالي: الأعمدة المولَّدة (Generated Columns)

دوال النوافذ تُمثّل حسابًا وقت الاستعلام. الصفحة التالية تتناول الحساب وقت التخزين: الأعمدة المولَّدة، حيث تُعرَّف قيمة العمود عبر تعبير، ويُحدَّث تلقائيًا كلما تغيّرت البيانات الأساسية.