إنتقل إلى المحتوى الرئيسي
صفحة مترجمة

تُرجمت هذه الصفحة من الإنجليزية بمساعدة الذكاء الاصطناعي. إذا اختلفت النسختان، فالمرجع هو النسخة الإنجليزية.

دوال النافذة

تجري دوال النافذة عمليات حسابية على مجموعة من الصفوف المرتبطة بالصف الحالي. على عكس الدوال التجميعية التي تعيد نتيجة واحدة لمجموعة من الصفوف، تعيد دوال النافذة قيمة لكل صف مع مراعاة "نافذة" من الصفوف المرتبطة.

راجع أيضًا: [SELECT] [WITH (CTE)] [ مرجع دوال النافذة ]


بناء الجملة​

تستخدم دوال النافذة عبارة OVER لتحديد مواصفات النافذة:

SELECT <windowFunction> OVER ( <windowSpec> ) FROM <multipartIdentifier>;

windowFunctionCall::=

windowFunctionOVER'('windowSpec')'

دالة النافذة​

دوال النافذة دوال متخصصة تجري عمليات حسابية على مجموعة من الصفوف تحددها مواصفات النافذة. يمكن أن تكون إما دوال نافذة مخصصة لهذا الغرض (مثل ROW_NUMBER أو LAG) أو دوال تجميعية (مثل SUM أو COUNT) تستخدم مع عبارة OVER.

{ <aggregateFunction> | ROW_NUMBER() | RANK() | DENSE_RANK() | PERCENT_RANK()
| CUME_DIST() | NTILE(<int>) | LAG(<expr> [,<offset> [,<default>]])
| LEAD(<expr> [,<offset> [,<default>]]) | FIRST_VALUE(<expr>)
| LAST_VALUE(<expr>) | NTH_VALUE(<expr>, <int>) }

windowFunction::=

ROW_NUMBER'('')'RANK'('')'DENSE_RANK'('')'PERCENT_RANK'('')'CUME_DIST'('')'NTILE'('number')'LAG'('expression','number','expression')'LEAD'('expression','number','expression')'FIRST_VALUE'('expression')'LAST_VALUE'('expression')'NTH_VALUE'('expression','number')'aggregateFunction

مواصفات النافذة​

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

[ <windowName> ]
[ PARTITION BY <fieldList> ]
[ ORDER BY <fieldList> [ ASC | DESC ] ]
[ { ROWS | RANGE | GROUPS }
{ UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW }
| { ROWS | RANGE | GROUPS } BETWEEN
{ UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW | <int> FOLLOWING }
AND
{ <int> PRECEDING | CURRENT ROW | <int> FOLLOWING | UNBOUNDED FOLLOWING } ]

windowSpec::=

PARTITIONBYfullFieldNameCOMMAfullFieldNameORDERBYfullFieldNameCOMMAfullFieldNameASCDESCROWSRANGEGROUPSUNBOUNDEDPRECEDINGnumberPRECEDINGCURRENTROWBETWEENUNBOUNDEDPRECEDINGnumberPRECEDINGCURRENTROWnumberFOLLOWINGANDnumberPRECEDINGCURRENTROWnumberFOLLOWINGUNBOUNDEDFOLLOWING

دوال النافذة المتاحة​

الفئةالدوال
الترتيبROW_NUMBER()، RANK()، DENSE_RANK()، NTILE()
الإزاحةLAG()، LEAD()، FIRST_VALUE()، LAST_VALUE()، NTH_VALUE()
التوزيعPERCENT_RANK()، CUME_DIST()
التجميعSUM()، COUNT()، AVG()، MIN()، MAX() مع عبارة OVER

أمثلة​

الترتيب باستخدام ROW_NUMBER وRANK وDENSE_RANK​

استخدم دوال الترتيب لتعيين ترتيب للصفوف حسب قيم الأعمدة.

-- Rank contributors by contribution count
SELECT
login,
contributions,
ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num,
RANK() OVER (ORDER BY contributions DESC) as rank,
DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';

المجاميع التراكمية والنسب المئوية​

يمكن استخدام الدوال التجميعية مثل SUM وCOUNT كدوال نافذة لحساب المجاميع التراكمية والنسب المئوية.

-- Running total and percentage of contributions
SELECT
login,
contributions,
SUM(contributions) OVER (ORDER BY contributions DESC) as running_total,
SUM(contributions) OVER () as total_contributions,
ROUND(100.0 * contributions / SUM(contributions) OVER (), 2) as pct_of_total
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';

مقارنة الصفوف باستخدام LAG وLEAD​

استخدم الدالتين LAG وLEAD للوصول إلى القيم من الصفوف السابقة أو اللاحقة.

-- Compare each release to previous release
SELECT
tag_name,
name,
published_at,
LAG(tag_name, 1) OVER (ORDER BY published_at) as previous_release,
LEAD(tag_name, 1) OVER (ORDER BY published_at) as next_release,
julianday(published_at) - julianday(LAG(published_at, 1) OVER (ORDER BY published_at)) as days_since_last_release
FROM github.repos.releases
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY published_at;

إنشاء مجموعات باستخدام NTILE​

استخدم الدالة NTILE لتقسيم الصفوف إلى عدد محدد من المجموعات.

-- Group contributors into quartiles
SELECT
login,
contributions,
NTILE(4) OVER (ORDER BY contributions DESC) as quartile,
CASE NTILE(4) OVER (ORDER BY contributions DESC)
WHEN 1 THEN 'Top Contributors'
WHEN 2 THEN 'Active Contributors'
WHEN 3 THEN 'Moderate Contributors'
WHEN 4 THEN 'Occasional Contributors'
END as tier
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';

استخدام PARTITION BY للتحليل حسب المجموعات​

استخدم عبارة PARTITION BY لإجراء حسابات النافذة داخل المجموعات.

-- Track cumulative issues by state
SELECT
number,
title,
state,
created_at,
ROW_NUMBER() OVER (ORDER BY created_at) as issue_sequence,
COUNT(*) OVER (ORDER BY created_at) as cumulative_issues,
COUNT(*) OVER (PARTITION BY state ORDER BY created_at) as cumulative_by_state
FROM github.issues.issues
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY created_at;

استخدام أطر النافذة​

حدّد عبارة إطار للتحكم في الصفوف التي يتضمنها حساب النافذة.

-- Calculate moving average of commit activity
WITH weekly_totals AS (
SELECT
week,
SUM(json_each.value) as commits_this_week
FROM github.repos.stats_commit_activity, JSON_EACH(days)
WHERE owner = 'stackql'
AND repo = 'stackql'
GROUP BY week
)
SELECT
week,
commits_this_week,
ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
SUM(commits_this_week) OVER (ORDER BY week) as cumulative_commits
FROM weekly_totals
ORDER BY week;

للاطلاع على الوثائق التفصيلية لكل دالة نافذة، راجع مرجع دوال النافذة.

لمزيد من المعلومات، راجع https://sqlite.org/windowfunctions.html.