5 - توابع

• توابع آماری
• گروه‌بندی
• توابع در Clauseهای دیگر

توابع آماری

تابع آماری (Statistical Function) برنامه‌ای داخلی است که یک پارامتر می‌گیرد و یک مقدار خلاصه بازمی‌گرداند. پارامتر می‌تواند نام ستون یا یک عبارت باشد. پنج تابع آماری استاندارد SQL عبارت‌اند از:

توابع آماری پارامتر می‌پذیرند

پارامترها نام ستون یا عبارت هستند و باید همیشه داخل پرانتز قرار گیرند. فاصله داخل یا خارج پرانتز اختیاری است و در مثال‌ها فقط برای خوانایی نمایش داده شده است.

توابع آماری مقدار خلاصه بازمی‌گردانند

SUM و AVG فقط با ستون‌های عددی قابل استفاده‌اند. COUNT، MIN و MAX را می‌توان برای هر نوع ستون به کار برد. هر تابع آماری روی یک ستون یا عبارت واحد عمل می‌کند.

COUNT پارامترهای گوناگونی می‌پذیرد

COUNT(*) تعداد سطرها را می‌دهد. COUNT(column) سطرهایی را می‌شمارد که ستون مشخص‌شده در آن‌ها Null نیست. COUNT(DISTINCT column) تعداد مقادیر یکتای غیرNull ستون را برمی‌گرداند.

توابع آماری - ادامه

اگر Clause SELECT فقط شامل توابع آماری باشد، SQL «جمع کل» پرس‌وجو را نمایش می‌دهد. جدول نتیجه یک سطر دارد و برای هر تابع آماری یک ستون ایجاد می‌شود. برای تغییر نام ستون‌ها می‌توان از Alias استفاده کرد.

SELECT AVG(POP)
FROM COUNTRIES
WHERE LANGUAGE = 'ENGLISH';

SELECT MIN(POP) AS LOWEST,
       MAX(POP) AS HIGHEST
FROM COUNTRIES
WHERE LANGUAGE = 'ENGLISH';

• در Clause SELECT فقط تابع‌ها مشخص می‌شوند.
• نتیجه فقط یک سطر دارد.
• برای هر تابع یک ستون در نتیجه وجود دارد.
• می‌توان Alias ستون تعیین کرد.

توابع آماری - ادامه

در نحو فعلی، تابع‌ها به Clause SELECT افزوده شده‌اند.

SELECT [ DISTINCT ] col [ AS alias ] list,
       func [ AS alias ] list
FROM table
WHERE comparison
ORDER BY col [ DESC ] list

-- Example
MIN(POP) AS LOWEST

تمرین‌ها

1. تعداد کل نیروها را در جدول ARMIES پیدا کنید. پاسخ باید 17,846,400 باشد.
2. حداقل، حداکثر و میانگین نرخ سواد را برای فرانسوی‌زبان‌ها نشان دهید. حداقل 18٪، حداکثر 100٪ و میانگین 51.38٪ است.
3. در یک پرس‌وجو تعداد کشورها (190) و تعداد زبان‌های متمایز (79) را محاسبه کنید.

گروه‌بندی

اگر Clause SELECT هم نام ستون و هم تابع داشته باشد، SQL زیرجمع‌ها (Subtotals) را نمایش می‌دهد. ستون‌های مشخص‌شده باید در Clause GROUP BY نیز فهرست شوند. SQL جدول را به گروه‌ها تقسیم می‌کند، برای هر گروه زیرجمع محاسبه می‌کند و به‌ازای هر گروه یک سطر نمایش می‌دهد.

SELECT JOB,
       COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
ORDER BY JOB;

SELECT JOB, COUNTRY,
       COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB, COUNTRY
ORDER BY JOB, COUNTRY;

• ستون‌های گروه در Clause SELECT مشخص می‌شوند.
• همان ستون‌ها در GROUP BY تکرار می‌شوند.
• GROUP BY پس از Clauseهای FROM و WHERE قرار می‌گیرد.

گروه‌بندی - ادامه

در نحو فعلی، Clause GROUP BY پس از WHERE افزوده شده است.

SELECT [ DISTINCT ] col [ AS alias ] list,
       func [ AS alias ] list
FROM table
WHERE comparison
GROUP BY collist
ORDER BY ...

-- Example
GROUP BY JOB, COUNTRY

تمرین‌ها

1. هر زبان و تعداد کل افرادی را که به آن زبان صحبت می‌کنند نمایش دهید. نتیجه را به Hebrew، Spanish، English و French محدود کنید.
2. حداقل، حداکثر و میانگین نرخ سواد را برای زبان‌های بالا فهرست کنید.
3. در یک پرس‌وجو تعداد مردان و زنان را در هر یک از کشورهای Canada و France محاسبه کنید. درون هر کشور بر اساس جنسیت مرتب کنید.

امتیاز اضافه

4. تعداد افراد هر جنسیت را برای هر شغل فهرست کنید. افرادی را که جنسیتشان ناشناخته است وارد نکنید. نتیجه را درون هر شغل بر اساس جنسیت مرتب کنید. جدول نتیجه باید 12 سطر داشته باشد.

توابع در Clauseهای دیگر

از آنجا که SQL مرتب‌سازی نتیجه را پس از همه پردازش‌های دیگر انجام می‌دهد، می‌توان تابع را در Clause ORDER BY نیز مشخص کرد. تابع را می‌توان مستقیم نوشت یا از طریق Alias ستونی که در SELECT تعریف شده است به آن ارجاع داد.

SELECT JOB, COUNT(*)
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
ORDER BY COUNT(*) DESC;

SELECT JOB, COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
ORDER BY TOTAL DESC;

• توابع را می‌توان در ORDER BY استفاده کرد.
• تابع می‌تواند مستقیماً وارد شود.
• تابع می‌تواند از طریق Alias ستون ارجاع داده شود.

توابع در Clauseهای دیگر - ادامه

تابع‌ها را نمی‌توان در Clause WHERE نوشت، زیرا WHERE پیش از گروه‌بندی و اجرای توابع ارزیابی می‌شود. اما تابع‌ها می‌توانند در Clause HAVING بیایند؛ Clauseای که پس از گروه‌بندی پردازش می‌شود.

SELECT JOB, COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
ORDER BY TOTAL DESC;

SELECT JOB, COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
HAVING TOTAL > 30
ORDER BY TOTAL DESC;

• Clause HAVING پس از GROUP BY می‌آید.
• HAVING از نظر مفهوم مانند WHERE است، با این تفاوت که...
• HAVING پس از گروه‌بندی اجرا می‌شود.

توابع در Clauseهای دیگر - ادامه

در نحو فعلی، Clause HAVING افزوده شده و ORDER BY برای پشتیبانی از توابع توسعه یافته است.

SELECT [ DISTINCT ] col [ AS alias ] list,
       expr [ AS alias ] list,
       func [ AS alias ] list
FROM table
WHERE comparison
GROUP BY collist
HAVING comparisons_with_funcs
ORDER BY col [ DESC ] list,
         pos [ DESC ] list,
         expr [ DESC ] list,
         func [ DESC ] list

-- Example
HAVING COUNT(*) > 30
ORDER BY COUNT(*) DESC

تمرین‌ها

1. تعداد نویسندگان در هر کشور را بشمارید و بر اساس تعداد به‌صورت نزولی مرتب کنید. 12 سطر وجود دارد.
2. زبان‌هایی را فهرست کنید که کمتر از یک میلیون نفر به آن‌ها صحبت می‌کنند. 11 سطر وجود دارد.
3. برای هر زبانی که حداقل نرخ سوادش با حداکثر آن برابر نیست، حداقل، حداکثر و میانگین نرخ سواد را پیدا کنید. نتیجه 13 سطر دارد.

امتیاز اضافه

4. زبان، مساحت کل و جمعیت کل را برای زبان‌هایی نمایش دهید که در بیش از یک کشور صحبت می‌شوند. بزرگ‌ترین مساحت کل را در بالا قرار دهید. 13 سطر وجود دارد.