Базы данных Практическое занятие 5

Создание запросов с использованием операторов `GROUP BY` и `ORDER BY`. Создание запросов с оператором `HAVING`.

В этом руководстве мы будем использовать PostgreSQL и pgAdmin 4. Убедитесь, что у вас открыт pgAdmin 4 и вы подключены к своему серверу PostgreSQL.

1. Оператор `ORDER BY`: Структурирование вывода

Оператор ORDER BY используется для сортировки результатов ваших SQL-запросов. По умолчанию порядок строк, возвращаемых оператором SELECT, не гарантирован. ORDER BY позволяет указать один или несколько столбцов для сортировки результирующего набора, либо в порядке возрастания, либо в порядке убывания.

Синтаксис:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC | DESC];

ORDER BY column_name: Указывает столбец для сортировки. Вы можете сортировать по нескольким столбцам, разделяя их запятыми.
ASC (Ascending/По возрастанию): Сортирует результаты в порядке возрастания (А-Я, 1-9). Это значение по умолчанию, если вы не укажете ASC или DESC.
DESC (Descending/По убыванию): Сортирует результаты в порядке убывания (Я-А, 9-1).

Пример:

Предположим, у нас есть таблица с именем students со следующей структурой и данными:

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(255) NOT NULL,
    course VARCHAR(255) NOT NULL,
    grade INTEGER NOT NULL
);

INSERT INTO students (student_id, student_name, course, grade) VALUES
(1, 'DAVLATNAZAROV ISLOMBEK BAXROMBEK O‘G‘LI', 'Mathematics', 85),
(2, 'BADALYAN SAMVEL SURENOVICH', 'Physics', 92),
(3, 'ISMAILOV AZIZBEK O‘KTAM O‘G‘LI', 'Computer Science', 78),
(4, 'DJUMANIYAZOV AZAMAT BAXROM O‘G‘LI', 'Mathematics', 90),
(5, 'KOMILOV UMRBEK AZAMAT O‘G‘LI', 'Physics', 88),
(6, 'KOMILOV KAMRONBEK OG‘ABOY O‘G‘LI', 'Computer Science', 95),
(7, 'KURBANOV AMIRBEK BAXTIYAROVICH', 'Linear Algebra', 70),
(8, 'OLLABERGANOV KAMOLADDIN RASULBEK O‘G‘LI', 'Calculus I', 82),
(9, 'NAZARGELDIYEV NURBOL TULEGEN O‘G‘LI', 'Probability', 98),
(10, 'MATCHANOVA SEVARA ADILBEKOVNA', 'Statistics', 85),
(11, 'SAMANDAROV IBRATBEK RASHID O‘G‘LI', 'Database Systems', 79),
(12, 'SABIROV ZOIRJON SHAKIR O‘G‘LI', 'Operating Systems', 91),
(13, 'OZADOV KAMOLADDIN RAVSHONBEK O‘G‘LI', 'Data Structures', 86),
(14, 'XUDARGANOV ASADBEK G‘AYRATJON O‘G‘LI', 'Algorithms', 93),
(15, 'SHONAZAROV AMIRBEK ALIBEK O‘G‘LI', 'Discrete Mathematics', 75);

Сценарий 1: Сортировка студентов по имени в порядке возрастания.

SQL-запрос:

SELECT student_name, course, grade
FROM students
ORDER BY student_name ASC;

Результат:

Сценарий 2: Сортировка студентов по оценке в порядке убывания.

SQL-запрос:

SELECT student_name, course, grade
FROM students
ORDER BY grade DESC;

Результат:

Сценарий 3: Сортировка по нескольким столбцам (сначала по курсу, затем по оценке внутри каждого курса в порядке убывания).

SQL-запрос:

SELECT student_name, course, grade
FROM students
ORDER BY course ASC, grade DESC;

Результат:

В этом случае результаты сначала упорядочиваются по алфавиту по course. Для студентов, обучающихся на одном и том же курсе, они затем упорядочиваются по grade в порядке убывания.

### 2. Оператор `GROUP BY`: Агрегирование данных по группам

Оператор `GROUP BY` используется для группировки строк, имеющих одинаковые значения в одном или нескольких столбцах. Он часто используется вместе с агрегатными функциями (которые мы подробно рассмотрим в следующей теме - P10) для вычисления сводной информации по каждой группе.  А пока мы сосредоточимся на понимании того, как `GROUP BY` организует ваши данные по группам.

**Синтаксис:**

```sql
SELECT column1, column2, ...
FROM table_name
WHERE condition  -- (Необязательный оператор WHERE из P5)
GROUP BY column_name1, column_name2, ...;

GROUP BY column_name1, column_name2, ...: Указывает столбцы, по которым вы хотите сгруппировать строки. Строки с одинаковой комбинацией значений в этих столбцах будут объединены в группу.
WHERE condition: Вы по-прежнему можете использовать оператор WHERE (из P5) для фильтрации строк перед их группировкой.

Важное примечание: Когда вы используете GROUP BY, любой столбец в вашем списке SELECT, который не является частью оператора GROUP BY, должен использоваться внутри агрегатной функции. В этом руководстве мы сосредоточимся на том, чтобы просто понять механизм группировки, пока не используя явно агрегатные функции в списке SELECT. Мы в основном будем выбирать сами столбцы группировки.

Пример использования таблицы students:

Сценарий 1: Группировка студентов по курсу.

SQL-запрос:

SELECT course
FROM students
GROUP BY course;

Инструкции для pgAdmin 4:

Выполните те же шаги, что и в примере ORDER BY в pgAdmin 4, чтобы выполнить этот запрос.

Результат:

Этот запрос возвращает список уникальных курсов, представленных в таблице students. Оператор GROUP BY course сгруппировал все строки с одинаковым значением course в одну группу, а затем выбрал значение course из каждой группы.

Сценарий 2: Давайте добавим student_name в оператор SELECT в предыдущем запросе.

SQL-запрос (Неверный):

SELECT course, student_name  -- student_name отсутствует в GROUP BY и не агрегирован
FROM students
GROUP BY course;

Результат (Ошибка PostgreSQL):

Этот запрос приводит к ошибке, потому что student_name находится в списке SELECT, но не является частью оператора GROUP BY и не используется с агрегатной функцией. PostgreSQL требует, чтобы неагрегированные столбцы в списке SELECT также были в операторе GROUP BY.

Сценарий 3: Группировка по курсу, а затем выбор имен студентов (демонстрация группировки, не предназначенная для практического запроса без агрегации).

Чтобы продемонстрировать концепцию группировки, мы можем изменить запрос, включив student_name в оператор GROUP BY. Это создаст группы на основе уникальных комбинаций course и student_name.

SQL-запрос (Иллюстративный для концепции группировки):

SELECT course, student_name
FROM students
GROUP BY course, student_name;

Результат:

Этот результат показывает, что каждая уникальная комбинация course и student_name рассматривается как отдельная группа. Хотя этот конкретный запрос может быть не сразу полезен без агрегации, он иллюстрирует, как GROUP BY работает с несколькими столбцами.

В заключение, GROUP BY имеет решающее значение для категоризации ваших данных и подготовки их к агрегатным вычислениям (которые появятся в P10). Он позволяет переходить от отдельных строк к наборам строк, имеющим общие характеристики.

### 3. Оператор `HAVING`: Фильтрация групп

Оператор `HAVING` используется для фильтрации групп, созданных оператором `GROUP BY`. Он похож на оператор `WHERE`, но `WHERE` фильтрует отдельные строки *перед* группировкой, а `HAVING` фильтрует группы *после* их формирования. `HAVING` всегда используется вместе с `GROUP BY`.

**Синтаксис:**

```sql
SELECT column1, column2, ...
FROM table_name
WHERE condition  -- (Необязательный оператор WHERE)
GROUP BY column_name1, column_name2, ...
HAVING group_condition;

HAVING group_condition: Указывает условие, которому должны соответствовать группы, чтобы быть включенными в окончательный результат. Это условие оценивается для каждой группы.

Важное примечание: Подобно оператору SELECT с GROUP BY, условия в операторе HAVING часто включают агрегатные функции (которые мы рассмотрим в P10). Однако в этом вводном руководстве мы проиллюстрируем концепцию фильтрации групп на основе простого условия, связанного с самим столбцом группировки.

Пример использования таблицы students:

Предположим, мы хотим найти курсы, названия которых начинаются с ‘D’. Мы можем использовать HAVING для фильтрации групп курсов.

SQL-запрос:

SELECT course
FROM students
GROUP BY course
HAVING course LIKE 'D%';

Инструкции для pgAdmin 4:

Выполните этот запрос в pgAdmin 4, как и раньше.

Результат:

Пояснение:

GROUP BY course: Группирует строки по курсу, создавая группы для ‘Computer Science’, ‘Mathematics’, ‘Physics’ и т.д.
HAVING course LIKE 'D%': Фильтрует эти группы. Проверяет, начинается ли название course для каждой группы с ‘D’. Только группы, где course начинается с ‘D’, удовлетворяют этому условию.
SELECT course: Выбирает название course из групп, прошедших условие HAVING.

Различие между WHERE и HAVING:

WHERE: Фильтрует строки перед группировкой. Он работает с отдельными строками и используется для выбора строк, которые будут участвовать в процессе группировки.
HAVING: Фильтрует группы после группировки. Он работает с группами, сформированными GROUP BY, и используется для выбора групп, которые будут включены в окончательный результат.

Пример, демонстрирующий WHERE и HAVING (пока без сложных агрегатных функций):

Допустим, мы хотим найти курсы, которые содержат символ ‘m’, и рассматривать только студентов с оценками выше 90.

SQL-запрос:

SELECT course
FROM students
WHERE grade > 90  -- Оператор WHERE для фильтрации строк перед группировкой
GROUP BY course
HAVING course LIKE '%m%'; -- Оператор HAVING для фильтрации групп после группировки

Результат:

Пояснение:

WHERE grade > 90: Фильтрует таблицу students, выбирая только те строки, где grade больше 90. Это приводит к следующему временному набору данных:

GROUP BY course: Группирует строки в этом временном наборе данных по course.
HAVING course LIKE '%m%': Фильтрует группы, сформированные на предыдущем шаге, оставляя только группу, где название course содержит символ ‘m’ (это ‘Computer Science’).
SELECT course: Выбирает название course из оставшейся группы.

4. Комбинирование `ORDER BY`, `GROUP BY` и `HAVING`

Вы можете использовать все три оператора (ORDER BY, GROUP BY и HAVING) в одном запросе для выполнения сложных манипуляций с данными. Порядок выполнения в SQL-запросе с этими операторами обычно следующий:

FROM: Указывает таблицу(ы), из которой(ых) извлекаются данные.
WHERE: Фильтрует строки на основе указанных условий.
GROUP BY: Группирует строки на основе указанных столбцов.
HAVING: Фильтрует группы на основе указанных условий.
SELECT: Выбирает столбцы, которые будут отображаться.
ORDER BY: Сортирует окончательный результирующий набор.

Пример:

Предположим, мы хотим найти курсы, заканчивающиеся на ‘s’, рассматривая только студентов с оценками выше 80, а затем отсортировать полученные курсы в алфавитном порядке.

SQL-запрос:

SELECT course
FROM students
WHERE grade > 80
GROUP BY course
HAVING course LIKE '%s'
ORDER BY course ASC;

Результат:

Практическое упражнение:

Используя таблицу students, напишите SQL-запросы для достижения следующего:

Вывести все курсы в порядке убывания по алфавиту.
Найти все курсы, которые содержат букву ‘s’ в своем названии.
Вывести курсы, содержащие букву ‘s’ в своем названии, в алфавитном порядке.

Решения (Попробуйте решить самостоятельно в первую очередь!)

-- 1. Вывести все курсы в порядке убывания по алфавиту.
SELECT course
FROM students
GROUP BY course
ORDER BY course DESC;

-- 2. Найти все курсы, которые содержат букву 's' в своем названии.
SELECT course
FROM students
GROUP BY course
HAVING course LIKE '%s%';

-- 3. Вывести курсы, содержащие букву 's' в своем названии, в алфавитном порядке.
SELECT course
FROM students
GROUP BY course
HAVING course LIKE '%s%'
ORDER BY course ASC;

Создание запросов с использованием операторов GROUP BY и ORDER BY. Создание запросов с оператором HAVING.

1. Оператор ORDER BY: Структурирование вывода

4. Комбинирование ORDER BY, GROUP BY и HAVING

Практическое упражнение:

Создание запросов с использованием операторов `GROUP BY` и `ORDER BY`. Создание запросов с оператором `HAVING`.

1. Оператор `ORDER BY`: Структурирование вывода

4. Комбинирование `ORDER BY`, `GROUP BY` и `HAVING`