Бази даних: сортування та групування результатів

У багатьох випадках нам потрібно не лише вибирати з бази ті дані, що задовольняють деякій умові, але й розташовувати їх у деякому певному порядку. Для цього існує ключове слово sql ORDER. Розглянемо один із останніх запитів попереднього уроку:

SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id

Припустимо, що нам треба відсортувати цей результат, скажімо, по першому стовпчику song. Запит буде таким:

SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
ORDER BY song

За замовчуванням сортування відбувається у порядку збільшення. Якщо нам треба сортувати у порядку зменшення, після назви поля додається DESC. Розглянемо сортування у порядку зменшення за колонкою album:

SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
ORDER BY album DESC

Звичайно, що сортувати можна і не за одною колонкою. Наприклад, відсортуємо результат спочатку за колонкою performer у прямому порядку, а потім за колонкою song – у зворотньому:

SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
ORDER BY performer, song DESC

 

Сортувати можна не тільки по колонках, що є у результаті, але й по інших полях у таблиці. Так, відсортуємо результат за роком виходу пісні (це поле не видно у результаті, проте рядки буде відсортовано саме так):

SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
ORDER BY mp3.year

 

Зауважимо, що сортування рядків також залежить від індексу. Так, якщо ми сортуємо результат по полю song, то нам буде необхідний індекс на таблицю mp3 по полю title. У даному випадку, звичайно (п’ять рядків у таблиці), індекс не потрібен. Проте якщо кількість рядків буде вимірюватися сотнями та тисячами, без індексу такі запити будуть істотно гальмувати.

Результат можна також обмежувати кількістю рядків. Дійсно, якщо у нас в архіві буде кілька тисяч файлів, то немає сенсу продивлятися їх усі. Для обмеження кількості рядків використовується ключове слово sql LIMIT.

Припустимо, що нам треба побачити лише три рядки з попереднього результату. Запит буде таким:

SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
ORDER BY mp3.year
LIMIT 3

 

Іноді потрібно відраховувати це обмеження не від першого рядка, а дещо далі. Наприклад, якщо ми виводимо рядки по сторінках, і нам треба вивести рядки, скажімо, з 10-го по 20-й. у цьому випадку до LIMIT додається ще одне число, наприклад:

SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
ORDER BY mp3.year
LIMIT 2, 3

Цей запит виведе три рядки, пропустивши два перших.

 

Досі ми розглядали виборку з таблиці по рядках, тобто кожен рядок результату відносився до якогось рядка у певній таблиці. Проте у багатьох випадках необхідно мати якусь сумарну інформацію по рядках, наприклад, суму значень деякого поля таблиці. Або мінімальне, максимальне чи середнє значення поля. Або кількість рядків, що задовольняють деякій умові. Ці операції називаються групуванням. Для цього призначені функції SQL, що мають назву агрегатних (aggregate). Найпростіша агрегатна функція – це count(). Вона повертає кількість рядків. Використовувати її просто:

SELECT count(*) FROM mp3

Спробуйте виконати цей запит, і ви побачите, що результатом буде кількість рядків у вашій таблиці mp3. Звичайно, що до цього запиту можна додавати і умови. Наприклад, щоб підрахувати кількість рядків у mp3 для пісень, що старші за 1990 рік, ми пишемо

SELECT count(*) FROM mp3 WHERE year<1990

Наступна агрегатна функція, що ми її розглянемо – це sum(). Вона повертає суму значень поля. Наприклад, щоб підрахувати суму розмірів файлів у таблиці mp3, ми пишемо так:

SELECT sum(filesize) FROM mp3

Звичайно, до такого запиту теж можна додавати умови WHERE.

 

Так само користуємося функціями min() – мінімальне значення поля, max() – максимальне значення, avg() – середнє значення. Функції можна використовувати у запитах і не по одній, і не по одному й тому ж самому полю, наприклад:

SELECT count(*), sum(filesize), min(year), max(year), avg(filesize) 
FROM mp3

Матимемо приблизно такий результат:

 

count(*)	sum(filesize)	min(year)	max(year)	avg(filesize)
5	26939091	1973	2000	5387818.2000

Групувати рядки можна як по всій таблиці, що ми розглянули вище, так і по деякому полю. Для цього існує ключове слово GROUP. Наприклад, ми хочемо вибрати суму розмірів файлів для пісень по кожному виконавцеві. Спочатку я покажу запит, що вибирає пісню з назвою виконавця та розміром. Як побудувати такий запит, має вже бути зрозуміло.

SELECT mp3.title AS song, mp3.filesize AS filesize, 
    performers.name AS performer
FROM mp3, performers
WHERE mp3.perf_id=performers.id

Тепер нам треба згрупувати ці пісні за назвою виконавця (performer). Звісно, що назва пісні тут не буде мати значення, бо у групі їх буде кілька, а розмір файлу ми будемо сумувати. Запит буде такий:

SELECT count(*) AS song_count,
    sum(mp3.filesize) AS summary_size, 
    performers.name AS performer
FROM mp3, performers
WHERE mp3.perf_id=performers.id
GROUP BY performer

Так, дійсно, перша колонка являє собою кількість рядків у групі (тобто кількість пісень, що належать одному конкретному виконавцеві), друга – сумарний розмір файлів у групі, а третя – назву виконавця, що по ньому формується група.

Цей момент може бути не дуже зрозумілим, якщо це так, то я рекомендую перечитати його ще раз та усвідомити добре і назавжди.

Звісно, результат таких запитів можна так само сортувати за допомогою ORDER BY та обмежувати за допомогою LIMIT. Слід лише запам’ятати порядок вказування цих слів, бо він є обов’язковим. Спочатку – GROUP BY, за ним – ORDER BY, і тільки потім – LIMIT.

Повторимо засвоєне на уроці:

1. Рядки результату, що отриманий за допомогою SELECT, можна сортувати за допомогою ORDER BY
2. Можна обмежувати кількість рядків у результаті за допомогою LIMIT
3. Можна групувати рядки результату та підраховувати сумму значень поля, кількість рядків у групі та інші агрегатні параметри групи за допомогою агрегатних функцій та GROUP BY

Матеріали за темою:
Бази даних: ключі, індекси та складні запити.
Бази даних: ключі, індекси та складні запити.
Бази даних: ключі, індекси та складні запити.
Бази даних: ключі, індекси та складні запити.
Бази даних: ключі, індекси та складні запити.