سلسلة sql للمحترفين دوال التعامل مع البيانات في SQL

تستعرض هذه المقالة عددًا من أنواع الدوال، مثل الدوال التجميعية (Aggregate Functions) والدوال التحليلية (Analytic Functions) والدوال العددية.

الدوال التجميعية aggregate functions

تستعرض هذه الفقرة مجموعة من الدوال التجميعية المُستخدمة في SQL، وهي دوال تأخذ مجموعة من القيم، وتعيد قيمة واحدة.

التجميع الشرطي Conditional aggregation

إليك جدول المدفوعات التالي:

تحسب الشيفرة التالية المجموع الكلي لرصيد أو دين كل موظف في الجدول:

select customer,
     sum(case when payment_type = 'credit' then amount else 0 end) as credit,
     sum(case when payment_type = 'debit' then amount else 0 end) as debit
from payments
group by customer

سنحصل على النتيجة التالية:

إليك الآن المثال التالي:

select customer,
     sum(case when payment_type = 'credit' then 1 else 0 end) as credit_transaction_count,
     sum(case when payment_type = 'debit' then 1 else 0 end) as debit_transaction_count
from payments
group by customer

هذا هو الخرج الناتج:

دورة علوم الحاسوب

دورة تدريبية متكاملة تضعك على بوابة الاحتراف في تعلم أساسيات البرمجة وعلوم الحاسوب

اشترك الآن

ضمّ القوائم List Concatenation

تجمّع عملية ضمّ القوائم (List Concatenation) عناصر عمود أو تعبيرًا عن طريق دمج القيم في سلسلة نصية واحدة لكل مجموعة. يمكن أيضًا تحديد سلسلة نصية لفصل القيم (إما سلسلة نصية فارغة أو فاصلة عند حذفها)، كما يمكن تحديد ترتيب القيم المُعادة. ورغم أنّها ليست جزءًا من معيار SQL القياسي، إلا أنّ كلّ أنظمة قواعد البيانات العلائقية تدعمها.

• MySQL

SELECT ColumnA
   , GROUP_CONCAT(ColumnB ORDER BY ColumnB SEPARATOR ',') AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

• Oracle و DB2

SELECT ColumnA
   , LISTAGG(ColumnB, ',') WITHIN GROUP (ORDER BY ColumnB) AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

• PostgreSQL

SELECT ColumnA
   , STRING_AGG(ColumnB, ',' ORDER BY ColumnB) AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

• SQL Server قبل 2016

 WITH CTE_TableName AS (
   SELECT ColumnA, ColumnB
       FROM TableName)
SELECT t0.ColumnA
 ,   STUFF((
     SELECT ',' + t1.ColumnB
     FROM CTE_TableName t1
     WHERE t1.ColumnA = t0.ColumnA
     ORDER BY t1.ColumnB
         FOR XML PATH('')), 1, 1, '') AS ColumnBs
   FROM CTE_TableName t0
 GROUP BY t0.ColumnA
 ORDER BY ColumnA;

• SQL Server 2017 و SQL Azure

SELECT ColumnA
 ,   STRING_AGG(ColumnB, ',') WITHIN GROUP (ORDER BY ColumnB) AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

• SQLite بدون ترتيب:

SELECT ColumnA
   , GROUP_CONCAT(ColumnB, ',') AS ColumnBs
 FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

يتطلب الترتيب استخدام استعلامً فرعي (subquery)، أو تعبيرًا جدوليًا CTE، وهو مجموعة ننائج مؤقتة يمكنك الرجوع إليها داخل عبارات SELECT أو INSERT أو UPDATE أو DELETE الأخرى:

 WITH CTE_TableName AS (
    SELECT ColumnA, ColumnB
       FROM TableName
       ORDER BY ColumnA, ColumnB)
SELECT ColumnA
       , GROUP_CONCAT(ColumnB, ',') AS ColumnBs
    FROM CTE_TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

SUM

تجمع الدالة ‎Sum‎ قيم صفوف مجموعة النتائج. وفي حال حذف العبارة group by، فستُجمَع قيم كلّ الصفوف.

المثال التالي لا يستخدم العبارة group by:

select sum(salary) TotalSalary
from employees;

سنحصل على الخرج التالي:

إليك مثال يستخدم group by:

select DepartmentId, sum(salary) TotalSalary
from employees
group by DepartmentId;

الخرج الناتج:

المتوسط AVG

تعيد الدالة التجميعية ‎‎AVG()‎‎ متوسط قيم تعبير معيّن، والتي عادةً ما تكون قيمًا رقمية في عمود.

لنفترض أنّ لدينا جدولًا يحتوي على تعداد سكان مدن العالم. مثلا، سجلّ مدينة نيويورك سيكون من هذا القبيل:

يحسب الاستعلام التالي متوسط عدد سكان مدينة نيويورك في الولايات المتحدة الأمريكية في السنوات العشر الماضية:

select city_name, AVG(population) avg_population
from city_population
where city_name = 'NEW YORK CITY';

لاحظ كيف لم توضع السنة في الاستعلام، وذلك لأنّنا نريد حساب متوسط عدد السكان بمرور الوقت.

سنحصل على النتائج التالية:

تنبيه: تحوّل الدالة AVG القيم إلى أعداد، وهذا أمر ينبغي أن تأخذه بالحسبان دائمًا، خصوصا عندما تعمل بقيم التاريخ والوقت.

Count

يمكنك استخدام الدالة Count لحساب عدد الصفوف:

SELECT count(*) TotalRows
FROM employees;

النتيجة:

يعدّ المثال التالي الموظفين في كل قسم:

SELECT DepartmentId, count(*) NumEmployees
FROM employees
GROUP BY DepartmentId;

الخرج الناتج:

يمكنك العدّ بحسب الأعمدة أو التعابير مع عدم احتساب القيم المعدومة ‎NULL‎:

SELECT count(ManagerId) mgr
FROM EMPLOYEES;

النتيجة:

(هناك قيمة واحدة فقط معدومة في العمود managerID)

يمكنك أيضًا استخدام DISTINCT داخل دالة أخرى (مثل COUNT) لتجبنّب إعادة العناصر المكرّرة على النحو التالي:

 SELECT COUNT(ContinentCode) AllCount
  ,         COUNT(DISTINCT ContinentCode) SingleCount
 FROM Countries;

ستعيد الشيفرة أعلاه قيمًا مختلفة. إذ لن تحسب SingleCount إلا عدد القارّات الفريدة (أي غير المكررة)، وذلك على خلاف AllCount التي ستعيد التكرارات أيضًا.

إذا طبّقنا الشيفرة أعلاه على جدول القارات التالي:

فسنحصل على الخرج التالي:

AllCount: 7 SingleCount: 5 

القيمة الدنيا Min

تبحث الدالة Min عن أصغر قيمة في العمود:

select min(age) from employee;

سيعيد المثال أعلاه أصغر قيمة في العمود ‎age‎ من جدول ‎employee‎.

القيمة القصوى Max

تبحث الدالة Max عن القيمة القصوى في العمود:

select max(age) from employee;

سيعيد المثال أعلاه أكبر قيمة في العمود ‎age‎ من جدول ‎employee‎.

الدوال العددية والصفّية Scalar/Single Row Functions

توفّر SQL العديد من الدوال العددية (scalar functions) المُضمّنة. والتي تأخذ قيمة واحدة كمُدخل، وتعيد قيمة واحدة لكل صفّ في مجموعة النتائج.

يمكنك استخدام الدوال العددية في أيّ موضع تكون التعابير جائزة فيه داخل ‏‏‏‏عبارات T-SQL .

التاريخ والوقت

في SQL، يُستخدم النوعان date و time لتخزين المعلومات المتعلقة بالوقت. يتضمّن هذان النوعان الوقت (time) والتاريخ (date) والتوقيت الصغير (smalldatetime) والتوقيت (datetime) والتوقيت 2 - مبني على 24 ساعة - (datetime2) والتوقيت الإزاحي - أي فارق التوقيت مع التوقيت العالمي الموحد UTC‏ - (datetimeoffset).

لكل واحد من هذه الأنواع تنسيق خاص كما يوضّح الجدول التالي:

تعيد الدالة ‎DATENAME‎ اسم أو جزء محدّد من قيمة التاريخ.

SELECT DATENAME (weekday,'2017-01-14') as Datename

الخرج الناتج عن الشيفرة أعلاه:

يمكنك استخدام الدالة ‎GETDATE‎ لتحديد التاريخ والوقت الحاليين لجهاز الكمبيوتر الذي ينفّذ شيفرة SQL الحالية كما هو موضّح في المثال التالي (لا تشمل هذه الدالة اختلاف المنطقة الزمنية.)

SELECT GETDATE() as Systemdate

الخرج الناتج:

تعيد الدالة ‎DATEDIFF‎ الفرق بين تاريخين. ويحدد المعامل الأوّل الممرّر إلى هذه الدالة الجزء الذي تريد استخدامه من التاريخ لحساب الاختلاف. يمكن أن يساوي: year أو month أو week أو day أو hour أو minute أو second أو millisecond. يحدّد المعامل الثاني والثالث تاريخ البداية وتاريخ الانتهاء اللذين تريد حساب الفرق الزمني بينها على التوالي.

إليك المثال التالي:

SELECT SalesOrderID, DATEDIFF(day, OrderDate, ShipDate)
AS 'Processing time'
FROM Sales.SalesOrderHeader

الخرج الناتج:

تتيح لك الدالة ‎DATEADD‎ إضافة مجال زمني إلى جزء محدّد من التاريخ كما يوضّح المثال التالي:

SELECT DATEADD (day, 20, '2017-01-14') AS Added20MoreDays

الخرج الناتج:

التعديلات على الحروف Character modifications

توفّر SQL بعض الدوال التي يمكنها معالجة الأحرفِ، مثلا، يمكن تحويل الأحرف إلى أحرف كبيرة أو صغيرة، أو تحويل الأرقام إلى أرقام منسّقة تنسيقًا خاصًّا.

تحوّل الدالة ‎lower(char)‎ الأحرف المُمرّرة إليها إلى أحرف صغيرة.

SELECT customer_id, lower(customer_last_name) FROM customer;

يعيد الاستعلام أعلاه الاسم الأخير صغيرًا، أي يحوّل SMITH إلى smith.

دوال الإعدادات والتحويل

الدالة ‎@@SERVERNAME‎ هي إحدى أمثلة دوال الإعدادات في SQL. توفّر هذه الدالة اسم الخادم المحلي الذي ينفّذ تعليمات SQL.

SELECT @@SERVERNAME AS 'Server'

الناتج:

في SQL، تحدث معظم عمليات تحويلات البيانات ضمنيًا، ودون أيّ تدخل من المستخدم. إن أردت تنفيذ عملية تحويل لا يمكن إجراؤها ضمنيًا، فيمكنك استخدام الدالتين ‎CAST‎ أو ‎CONVERT‎.

صياغة ‎CAST‎ أبسط من صياغة ‎CONVERT‎، بيْد أنّ إمكانياتها محدودة. سنستخدم في المثال التالي كلا الدالتين ‎CAST‎ و ‎CONVERT‎ لتحويل نوع بيانات الوقت (datetime) إلى النوع ‎varchar‎. تستخدم الدالة ‎CAST‎ دائمًا التنسيق الافتراضي. على سبيل المثال، تُمثّل التواريخ والأوقات بالتنسيق YYYY-MM-DD. بالمقابل، تستخدم الدالة ‎CONVERT‎ تنسيق التاريخ والوقت الذي تحدّده أنت. سنختار في المثال التالي التنسيق 3، والذي يمثّل التنسيق dd / mm / yy.

USE AdventureWorks2012
GO
SELECT FirstName + ' ' + LastName + ' was hired on ' +
     CAST(HireDate AS varchar(20)) AS 'Cast',
     FirstName + ' ' + LastName + ' was hired on ' +
     CONVERT(varchar, HireDate, 3) AS 'Convert'
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
GO

ستحصل على الخرج التالي:

هناك مثال آخر على دوال التحويل، وهي الدالة ‎PARSE‎. تحوّل هذه الدالة سلسلة نصية إلى نوع بيانات آخر.

في صياغة الدالة، عليك تحديد السلسلة النصية التي ترغب في تحويلها متبوعة بالكلمة المفتاحية ‎AS‎، ثمّ تكتب نوع البيانات المطلوب. اختياريًا، يمكنك أيضًا تحديد الإعداد الثقافي، والذي يحدّد تنسيق السلسلة النصية. في حال لم تحدّده، فستُستخدم لغة الجلسة.

إذا تعذّر تحويل السلسلة النصية إلى تنسيق عددي أو تاريخ أو وقت ، فسيُطرَح خطأ. وسيتعيّن عليك حينئِذ استخدام ‎CAST‎ أو ‎CONVERT‎ لإجراء عملية التحويل.

SELECT PARSE('Monday, 13 August 2012' AS datetime2 USING 'en-US') AS 'Date in English'

الخرج التالي:

الدوال المنطقية والرياضية

تقدّم SQL دالتين منطقيتين، وهما CHOOSE و IIF. تعيد الدالة ‎CHOOSE‎ عنصرًا من قائمة من القيم استنادًا إلى فهرسه في القائمة.

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

في المثال التالي، سنستخدم الدالة ‎CHOOSE‎ لإعادة المُدخَل الثاني في قائمة الإدارات.

SELECT CHOOSE(2, 'Human Resources', 'Sales', 'Admin', 'Marketing' ) AS Result;

النتيجة:

تعيد الدالة ‎IIF‎ القيمة true إن تحقّق شرطها، خلاف ذلك، تُعيد القيمة false.

في صياغة عبارة الشرط، يحدّد معامل التعبير الشرطي (booleanexpression) التعبير المنطقي. فيما يحدّد المعامل الثاني (truevalue) القيمة التي يجب إعادتها إذا لم يتحقّق الشرط، ويحدّد المعامل الثالث (false_value) القيمة التي يجب أن تُعاد خلاف ذلك.

يستخدم المثال التالي الدالة IIF لإعادة إحدى قيمتين. إذا كانت مبيعات الموظف السنوية تتجاوز 200000، فسيكون ذلك الموظف مؤهّلاً للحصول على مكافأة. خلاف ذلك لن يكون مؤهّلا للحصول على مكافأة.

SELECT BusinessEntityID, SalesYTD,
     IIF(SalesYTD > 200000, 'Bonus', 'No Bonus') AS 'Bonus?'
FROM Sales.SalesPerson
GO

هذا هو الناتج:

تتضمّن SQL العديد من الدوال الرياضية التي يمكنك استخدامها لإجراء عمليات حسابية على المُدخلات ثمّ إعادة نتائج عددية.

أحد أمثلة ذلك هي الدالة ‎SIGN‎، والتي تُعيد قيمة تمثّل إشارة التعبير. إذ تشير القيمة ‎‎-1 إلى تعبير سلبي، فيما تشير القيمة ‎‎+1 إلى تعبير موجب ، أمّا 0 فيشير إلى الصفر!

في المثال التالي، القيمة المُدخلة هي عدد سالب، لذا تُعاد ‎‎النتيجة ‎‎-1.

SELECT SIGN(-20) AS 'Sign'

الناتج:

هناك دالة رياضية أخرى، وهي الدالة ‎POWER‎. والتي تحسب أسّ تعبير مرفوع إلى قوة محددة.

في صياغة الدالة، يحدّد المعامل الأول التعبير العددي، فيما يحدّد المعامل الثاني الأسّ.

SELECT POWER(50, 3) AS Result

النتيجة:

الدوال التحليلية

تُستخدم الدوال التحليلية لحساب قيمة معيّنة بناءً على مجموعة من القيم. على سبيل المثال، يمكنك استخدام الدوال التحليلية لحساب المجاميع الجارية (running totals)، أو النسب المئوية، أو النتيجة الأكبر داخل مجموعة.

LAG و LEAD

توفر الدالة ‎LAG‎ البيانات الخاصّة بالصفوف التي تسبق الصف الحالي في مجموعة النتائج. على سبيل المثال ، في عبارة ‎SELECT‎، يمكنك موازنة قيم الصف الحالي مع قيم الصف السابق. يمكنك استخدام تعبير عددي لتحديد القيم التي يجب موازنتها.

يمثّل معامل الإزاحة (offset) عدد الصفوف السابقة للصف الحالي التي ستُستخدم في المقارنة. في حال عدم تحديده، فستُستخدم القيمة الافتراضية 1.

يحدّد المعامل الافتراضي default القيمة التي يجب إعادتها عندما يكون التعبير الموجود في الموضع offset معدومًا (‎NULL‎). إذا لم تحدّد قيمة لهذا المعامل، فستُستخدم القيمة الافتراضية ‎NULL‎.

توفّر الدالة ‎LEAD‎ بيانات عن الصفوف التي تعقُب الصفّ الحالي في مجموعة الصفوف. على سبيل المثال، في عبارة ‎SELECT‎، يمكنك موازنة قيم الصف الحالي مع قيم الصف اللاحق. يمكن تحديد القيم التي يجب موازنتها باستخدام تعبير رقمي.

يمثّل معامل الإزاحة (offset) عدد الصفوف اللاحقة للصف الحالي التي ستُستخدم في المقارنة. يحدد المعامل default القيمة التي ينبغي أن تُعاد عندما يكون التعبير الموجود عند موضع الإزاحة معدومًا (‎NULL‎). إذا لم تحدد هذين المعاملين، فستُستخدم القيمتان الافتراضيتان لهذين المعاملين، واللتان تساويان 1 و ‎NULL‎ على التوالي.

يستخدم المثال التالي الدالتين LEAD و LAG لمقارنة قيم المبيعات الحالية لكل موظف مع قيم الموظفين المذكورين قبله وبعده، مع ترتيب السجلات بناءً على قيمة العمود BusinessEntityID.

SELECT BusinessEntityID, SalesYTD,
LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

الخرج الناتج:

PERCENTILEDISC و PERCENTILECONT

تسرد الدالة ‎PERCENTILE_DISC‎ قيمة أوّل مُدخَل يكون التوزيع التراكمي (cumulative distribution) عنده أعلى من المئين الذي قدّمته باستخدام المعامل ‎numeric_literal‎.

تُجمَّع القيم حسب مجموعة الصفوف (rowset) أو حسب التوزيع (partition) كما هو محدّد في عبارة ‎WITHIN GROUP‎.

تشبه ‎PERCENTILE_CONT‎ الدالة ‎PERCENTILE_DISC‎، بيْد أنّها تُعيد متوسّط مجموع أول مُدخل يحقق الشرط مع المُدخل التالي.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
    CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
    AS "Cumulative Distribution",
    PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
       OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

لإيجاد القيمة التي تطابق أو تتجاوز المئين 0.5، عليك تمرير المئين كقيمة عددية حرفية (numeric literal) إلى دالة المئين الكسري ‎PERCENTILE_DISC‎. ينتج عن تطبيق هذه الدالة على مجموعة النتائج قائمة مؤلفة من قيم الصف التي يكون التوزيع التراكمي عندها أعلى من المئين المحدّد.

يمكنك أيضًا استخدام دالة المئين المتصل - Percentile Continuous‏ - ‎PERCENTILE_CONT‎، والتي ينتج عن تطبيقها على مجموعة النتائج متوسط مجموع قيمة النتيجة مع أعلى قيمة موالية تحقق الشرط.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
    CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
    AS "Cumulative Distribution",
    PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
       OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
    PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
       OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;

الخرج الناتج:

FIRST_VALUE

يمكنك استخدام الدالة ‎FIRST_VALUE‎ لتحديد القيمة الأولى في مجموعة نتائج مرتّبة:

SELECT StateProvinceID, Name, TaxRate,
     FIRST_VALUE(StateProvinceID)
     OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

في هذا المثال، تُستخدم الدالة ‎FIRST_VALUE‎ لإعادة قيمة الحقل ‎ID‎ الخاص بالولاية أو المقاطعة التي لها أدنى معدّل للضريبة. فيما تُستخدم العبارة ‎OVER‎ لترتيب معدّلات الضريبة للحصول على أدنى معدّل.

إليك جدول الضرائب:

LAST_VALUE

تعيد الدالة ‎LAST_VALUE‎ القيمة الأخيرة في مجموعة نتائج مرتبة.

SELECT TerritoryID, StartDate, BusinessentityID,
    LAST_VALUE(BusinessentityID) 
    OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

يستخدم المثال أعلاه الدالة ‎LAST_VALUE‎ لإعادة القيمة الأخيرة لكل مجموعة من الصفوف في مجموعة القيم المُرتبة.

PERCENTRANK و CUMEDIST

تحسب الدالة ‎PERCENT_RANK‎ ترتيب الصفّ بالنسبة لمجموعة الصفوف. تُحسب النسبة المئوية نسبةً إلى عدد الصفوف في المجموعة التي تقلّ قيمتها عن الصف الحالي.

تُعطى للقيمة الأولى في مجموعة النتائج دائمًا النسبة المئوية 0. بالمقابل، فالنسبة المئوية للقيمة العليا - أو الأخيرة - في المجموعة تساوي دائمًا 1.

تحسب الدالة ‎CUME_DIST‎ الموضع النسبي (relative position) لقيمة معيَّنة في مجموعة من القيم من خلال تحديد النسبة المئوية للقيم التي تصغُر أو تساوي تلك القيمة. تُسمّى هذه العملية التوزيع التراكمي (cumulative distribution).

سنستخدم في هذا المثال عبارة ‎ORDER‎ لتقسيم - أو تصنيف - الصفوف التي أعَادتها العبارة ‎SELECT‎ بناءً على المسمّيات الوظيفية للموظّفين، مع ترتيب النتائج في كل مجموعة على أساس عدد ساعات الإجازات المرضية التي استخدمها الموظفون.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
     AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
     AS "Cumulative Distribution"
FROM Employee;

الخرج الناتج:

ترتّب الدالة ‎PERCENT_RANK‎ المُدخلات في كل مجموعة. فمقابل كل مُدخل، تحسب النسبة المئوية للمدخلات الأخرى في المجموعة التي لها قيم أصغر من المُدخل الممرّر.

الدالة ‎CUME_DIST‎ مشابهة للدالة السايقة، بيْد أنّها تُعيد النسبة المئوية للقيم التي تصغُر القيمة الحالية أو تساويها.

دوال النافذة Window Functions

التحقق من وجود قيم مكررة في عمود

لنفترض أن لدينا جدول البيانات التالي:

يعيد المثال التالي كل هذه الصفوف مع راية تحدّد ما إذا كان الوسم tag مُستخدمًا من قبل صفّ آخر.

SELECT id, name, tag, COUNT(*) OVER (PARTITION BY tag) > 1 AS flag FROM items

سنحصل على الخرج التالي:

في حالة لم تكن قاعدة بياناتك تدعم OVER و PARTITION، فيمكنك استخدام الشيفرة التالية للحصول على النتيجة نفسها:

SELECT id, name, tag, (SELECT COUNT(tag) FROM items B WHERE tag = A.tag) > 1 AS flag FROM items A

إيجاد السجلات الخارجة عن التسلسل باستخدام الدالة LAG

إليك الجدول التالي:

يجب أن تُرتب العناصر بحسب قيمة الحقل ‎STATUS‎، بداية من القيمة "ONE" ثمّ "TWO" ثمّ "THREE".

لاحظ أنّ التسلسل في الجدول غير مرتب، إذ أنّ هناك انتقالًا فوريًا من "ONE" إلى "THREE". عليك إيجاد طريقة للعثور على المستخدمين (‎STATUS_BY‎) الخارجين عن الترتيب.

تساعد الدالة التحليلية ‎LAG()‎ في حل هذه المشكلة، إذ تعيد لكل صفّ، قيمة الصف السابق له:

SELECT * FROM (
 SELECT
  t.*,
 LAG(status) OVER (PARTITION BY id ORDER BY status_time) AS prev_status
 FROM test t
) t1 WHERE status = 'THREE' AND prev_status != 'TWO'

في حالة لم تكن قاعدة بياناتك تدعم LAG، يمكنك استخدام الشيفرة التالية للحصول على النتيجة نفسها:

SELECT A.id, A.status, B.status as prev_status, A.status_time, B.status_time as prev_status_time
FROM Data A, Data B
WHERE A.id = B.id
AND   B.status_time = (SELECT MAX(status_time) FROM Data where status_time < A.status_time and id =
A.id)
AND   A.status = 'THREE' AND NOT B.status = 'TWO'

حساب المجموع الجاري running total

إليك جدول البيانات التالي:

بحسب المثال التالي المجموع الجاري للعمود amount في الجدول أعلاه:

SELECT date, amount, SUM(amount) OVER (ORDER BY date ASC) AS running
FROM operations
ORDER BY date ASC

الخرج الناتج:

إضافة إجمالي الصفوف المُختارة لكل صف

يضيف المثال التالي إجمالي الصفوف المختارة لكل صف:

SELECT your_columns, COUNT(*) OVER() as Ttl_Rows FROM your_data_set

بدلاً من استخدام استعلامين، الأول للحصول على المجموع، والثاني للحصول على الصفّ، يمكنك استخدام التجميع - aggregate - كدالة نافذة (window function) واستخدام مجموعة النتائج الكاملة كنافذة (window). يمكن أن يجنّبك هذا تعقيدات عمليات الضمّ الذاتي (self joins) الإضافية.

الحصول على أحدث N صفًّا في عدة مجموعات

إليك البيانات التالية:

إن استخدمت القيمة n = 1 في المثال التالي، ستحصل على أحدث صفّ لكل معرِّف ‎user_id‎:

;with CTE as
(SELECT *,
 ROW_NUMBER() OVER (PARTITION BY User_ID
 ORDER BY Completion_Date DESC) Row_Num
FROM Data)
SELECT * FORM CTE WHERE Row_Num <= n

الخرج سيكون:

ترجمة -وبتصرّف- للفصول من 42 إلى 45 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: دوال التعامل مع النصوص في SQL
• المقال السابق: مواضيع متقدمة في SQL
• النسخة العربية الكاملة من كتاب ملاحظات للعاملين بلغة SQL 1.0.0