الدرس الرابع عشر - دوال التعامل مع البيانات

تستعرض هذه المقالة عددًا من أنواع الدوال، مثل الدوال التجميعية (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 القياسي، إلا أنّ كلّ أنظمة قواعد البيانات العلائقية تدعمها.

  • 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;

يتطلب الترتيب استخدام استعلامً فرعي (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