لوحة المتصدرين

تستعرض هذه المقالة عددًا من أنواع الدوال، مثل الدوال التجميعية (Aggregate Functions) والدوال التحليلية (Analytic Functions) والدوال العددية. الدوال التجميعية aggregate functions تستعرض هذه الفقرة مجموعة من الدوال التجميعية المُستخدمة في SQL، وهي دوال تأخذ مجموعة من القيم، وتعيد قيمة واحدة. التجميع الشرطي Conditional aggregation إليك جدول المدفوعات التالي: table { width: 100%; } thead { vertical-align: middle; text-align: center; } td, th { border: 1px solid #dddddd; text-align: right; padding: 8px; text-align: inherit; } tr:nth-child(even) { background-color: #dddddd; } 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 اقرأ أيضًا: المقال التالي: دوال التعامل مع النصوص في SQL المقال السابق: مواضيع متقدمة في SQL النسخة العربية الكاملة من كتاب ملاحظات للعاملين بلغة SQL 1.0.0