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


محمد الميداوي

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

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

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

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

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

Customer Payment_type Amount
Peter Credit 100
Peter Credit 300
John Credit 1000
John Debit 500

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

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

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

Customer Credit Debit
Peter 400 0
John 1000 500

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

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

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

Customer credit_transaction_count debit_transaction_count
Peter 2 0
John 1 1

ضمّ القوائم 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;

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

TotalSalary
2500

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

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

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

DepartmentId TotalSalary
1 2000
2 500

المتوسط AVG

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

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

city_name population year
New York City 8,550,405 2015
New York City ... ...
New York City 8,000,906 2005

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

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

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

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

city_name avg_population
New York City 8,250,754

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

Count

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

SELECT count(*) TotalRows
FROM employees;

النتيجة:

TotalRows
4

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

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

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

DepartmentId NumEmployees
1 3
2 1

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

SELECT count(ManagerId) mgr
FROM EMPLOYEES;

النتيجة:

mgr
3

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

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

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

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

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

ContinentCode
OC
EU
AS
NA
NA
AF
AF

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

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).

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

نوع البيانات التنسيق
time hh:mm:ss[.nnnnnnn]
date YYYY-MM-DD
smalldatetime YYYY-MM-DD hh:mm:ss
datetime YYYY-MM-DD hh:mm:ss[.nnn]
datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn]
datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+/-]hh:mm

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

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

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

Datename
Saturday

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

SELECT GETDATE() as Systemdate

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

Systemdate
2017-01-14 11:11:47.7230728

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

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

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

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

SalesOrderID Processing time
43659 7
43660 7
43661 7
43662 7

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

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

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

Added20MoreDays
2017-02-03 00:00:00.000

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

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

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

SELECT customer_id, lower(customer_last_name) FROM customer;

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

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

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

SELECT @@SERVERNAME AS 'Server'

الناتج:

Server
SQL064

في 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

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

Cast Convert
David Hamiltion was hired on 2003-02-04 David Hamiltion was hired on 04/02/03

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

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

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

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

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

Date in English
2012-08-13 00:00:00.0000000

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

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

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

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

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

النتيجة:

Result
Sales

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

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

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

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

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

BusinessEntityID SalesYTD Bonus?
274 559697.5639 Bonus
275 3763178.1787 Bonus
285 172524.4512 No Bonus

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

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

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

SELECT SIGN(-20) AS 'Sign'

الناتج:

Sign
-1

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

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

SELECT POWER(50, 3) AS Result

النتيجة:

Result
125000

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

تُستخدم الدوال التحليلية لحساب قيمة معيّنة بناءً على مجموعة من القيم. على سبيل المثال، يمكنك استخدام الدوال التحليلية لحساب المجاميع الجارية (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;

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

BusinessEntityID SalesYTD Lead value Lag value
274 559697.5639 3763178.1787 0.0000
275 3763178.1787 4251368.5497 559697.5639
276 4251368.5497 3189418.3662 3763178.1787
277 3189418.3662 1453719.4653 4251368.5497
278 1453719.4653 2315185.6110 3189418.3662
279 2315185.6110 1352577.1325 1453719.4653

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‎. ينتج عن تطبيق هذه الدالة على مجموعة النتائج قائمة مؤلفة من قيم الصف التي يكون التوزيع التراكمي عندها أعلى من المئين المحدّد.

BusinessEntityID JobTitle SickLeaveHours Cumulative Distribution Percentile Discreet
272 Application Specialist 55 0.25 56
268 Application Specialist 56 0.75 56
269 Application Specialist 56 0.75 56
267 Application Specialist 57 1 56

يمكنك أيضًا استخدام دالة المئين المتصل - 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;

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

BusinessEntityID JobTitle SickLeaveHours Cumulative Distribution Percentile Discreet Percentile Continuous
272 Application Specialist 55 0.25 56 56
268 Application Specialist 56 0.75 56 56
269 Application Specialist 56 0.75 56 56
267 Application Specialist 57 1 56 56

FIRST_VALUE

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

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

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

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

StateProvinceID Name TaxRate FirstValue
74 Utah State Sales Tax 5.00 74
36 Minnesota State Sales Tax 6.75 74
30 Massachusetts State Sales Tax 7.00 74
1 Canadian GST 7.00 74
57 Canadian GST 7.00 74
63 Canadian GST 7.00 74

LAST_VALUE

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

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

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

TerritoryID StartDate BusinessentityID LastValue
1 2005-07-01 00.00.00.000 280 283
1 2006-11-01 00.00.00.000 284 283
1 2005-07-01 00.00.00.000 283 283
2 2007-01-01 00.00.00.000 277 275
2 2005-07-01 00.00.00.000 275 275
3 2007-01-01 00.00.00.000 275 277

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;

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

BusinessEntityID JobTitle SickLeaveHours Percent Rank Cumulative Distribution
267 Application Specialist 57 0 0.25
268 Application Specialist 56 0.333333333333333 0.75
269 Application Specialist 56 0.333333333333333 0.75
272 Application Specialist 55 1 1
262 Assitant to the Cheif Financial Officer 48 0 1
239 Benefits Specialist 45 0 1
252 Buyer 50 0 0.111111111111111
251 Buyer 49 0.125 0.333333333333333
256 Buyer 49 0.125 0.333333333333333
253 Buyer 48 0.375 0.555555555555555
254 Buyer 48 0.375 0.555555555555555

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

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

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

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

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

id example unique_tag
1 example unique_tag
2 foo simple
42 bar simple
3 baz hello
51 quux world

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

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

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

id name tag flag
1 example unique_tag false
2 foo simple true
42 bar simple true
3 baz hello false
51 quux world false

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

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

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

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

ID STATUS STATUS_TIME STATUS_BY
1 ONE 2016-09-28-19.47.52.501398 USER_1
3 ONE 2016-09-28-19.47.52.501511 USER_2
1 THREE 2016-09-28-19.47.52.501517 USER_3
3 TWO 2016-09-28-19.47.52.501521 USER_2
3 THREE 2016-09-28-19.47.52.501524 USER_4

يجب أن تُرتب العناصر بحسب قيمة الحقل ‎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

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

date amount
2016-03-12 200
2016-03-11 -50
2016-03-14 100
2016-03-15 100
2016-03-10 -250

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

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

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

date amount running
2016-03-10 -250 -250
2016-03-11 -50 -300
2016-03-12 200 -100
2016-03-14 100 0
2016-03-15 100 -100

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

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

SELECT your_columns, COUNT(*) OVER() as Ttl_Rows FROM your_data_set
id name Ttl_Rows
1 example 5
2 foo 5
3 bar 5
4 baz 5
5 quux 5

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

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

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

User_ID Completion_Date
1 2016-07-20
1 2016-07-21
2 2016-07-20
2 2016-07-21
2 2016-07-22

إن استخدمت القيمة 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

الخرج سيكون:

User_ID Completion_Date Row_Num
1 2016-07-21 1
2 2016-07-22 1

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



1 شخص أعجب بهذا


تفاعل الأعضاء


لا توجد أيّة تعليقات بعد



يجب أن تكون عضوًا لدينا لتتمكّن من التعليق

انشاء حساب جديد

يستغرق التسجيل بضع ثوان فقط


سجّل حسابًا جديدًا

تسجيل الدخول

تملك حسابا مسجّلا بالفعل؟


سجّل دخولك الآن