PostgreSQL – как правильно работать. Часть 2

Продолжим разговор о работе с реляционной базой данных PostgreSQL. Начало читайте по этой ссылке.

Несколько слов о циклах

Используя подзапрос, может быть вызвана любая функция, а также прописаны разнообразные условия ее реализации. Следующий важный момент – циклы. Можно использовать их для повтора нескольких команд в функциях на pgSQL.

Loop

Существуют различные подзапросы с циклами. Первый – Loop. Имеет вид:

Отвечает за организацию безусловного цикла, который будет повторяться бесконечно до тех пор, пока не наткнется на exit или return. Для подзапросов допускается использование метки в операторах exit и continue. Это необходимо для того, чтобы указывать, какой цикл имеет отношение к оным.

Exit

Выше указана форма представления соответствующей «команды». Запомнить необходимо такие данные:

1. При отсутствии метки осуществляется завершение самого внутреннего цикла.
2. Далее будет выполняться оператор, который следует за end loop.
3. При наличии метки, она должна иметь отношение к внутреннему или внешнему циклу. Допускается метка блока.
4. В именованном блоке/цикле обработка будет завершаться, а управление – переходить к следующему оператору после соответствующего end.
5. Используется exit со всеми типами циклов, а не только с безусловными.
6. Когда применяется для выхода из блока, управление перейдет к оператору, следующим за окончанием блока.
7. При применении подзапроса для выхода обязательно прописывать метку.

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

Continue

Если метки нет, реализовывается следующая итерация в пределах самого внутреннего цикла. Все остальные операторы будут пропущены, управление перейдет к управляющему выражению цикла (при наличии). Это необходимо, чтобы узнать, требуется ли еще один проход или нет.

При наличии метки она будет указывать на метку цикла, обработка которого предусматривается подзапросом. А если есть when, следующая итерация начнется тогда, когда логическое выражение выступит в качестве истины. В противном случае осуществится переход к оператору, идущему после continue.

Continue можно использовать со всеми типами циклов.

While

While в данном случае выполняет некий набор команд до тех пор, пока истинно логическое выражение. Оно будет проверяется перед каждым входом в тело цикла.

For для целочисленных

Здесь:

1. Итерации будут выполнять по диапазону целых чисел.
2. Имя автоматически получает тип int. Существует в пределах цикла.
3. Для верхней и нижней границ диапазона выражения вычисляются один раз – когда осуществляется вход в цикл.
4. При отсутствии by шаг в итерации 1, в противном случае применяется значение by, вычисляемое один раз. Происходит это тоже при входе в цикл.
5. При наличии reverse в подзапросе после каждой итерации размер шага вычитается.

А еще стоит запомнить – если нижняя граница цикла больше верхней, то тело оного вообще не будет обрабатываться. Ошибка не возникнет.

По результатам запроса

Есть еще один вариант For – по результатам запроса.

Цель может выступать в качестве строковой переменной, record или разделенными запятыми списком скалярных. Соответствующее «хранилище информации» присваивает себе строки результата запроса. Для каждой будет выполняться тело цикла.

Если цикл завершается exit, последняя присвоенная строка в подзапросе станет доступна после цикла. В виде запроса может задаваться любая команда SQL, отвечающая за возврат строк. Чаще всего – select. Возможно применение служебных команд.

Оконная функция

Оконные функции отвечают за вычисление набора строк, тем или иным способом связанных с текущей строкой. Возможно сравнение агрегатной функции. При использовании оконной операции несколько строк не группируются в одну. Они продолжают отдельное самостоятельное существование. Внутри оконные функции, как и агрегатные, обращаются не только к текущим строкам результата запросов.

Вот пример в postgres, который демонстрирует сравнение зарплаты каждого сотрудника со средним показателем по соответствующим отделам:

Здесь происходит следующее:

1. Первые 3 столбца будут извлекаться из empdalary.
2. Для каждой таблички будет иметься строка результата.
3. Четвертый столбец select средние значения, вычисленные по всем строкам с одинаковым значением depname, что и текущая строчка.
4. Вызов оконной функции всегда включает в себя over, которое идет после названия и аргументов.
5. Over отвечает за planning, как именно происходит разделение строки запроса для обработки через «окна».
6. Объединение происходит посредством partition by.
7. Оконная функция предусматривает план работы по вычислению строк, попадающих в один раздел с текущей.
8. Можно определять порядок обработки строчек через order by, прописанные в over.

Также стоит запомнить, что для каждой строки имеется набор строк в ее разделе, который носит название рамки. Изначально включает в себя все строки от начала раздела до текущей строки и строк, равных текущей по значению order by. Без последнего «оператора» состоит из всех строк раздела.

Для того, чтобы фильтровать или группировать strings, нужно использовать вложенные запросы:

Если происходит вычисление нескольких запросов, для одинаково определенных окно можно прописывать для каждой отдельное over. Но тогда план будет предусматривать дублирование. Это приведет к ошибкам. Поэтому для определения окна требуется выделить window, а потом сослаться на него в over.

Выше дан пример реализации соответствующей особенности в Postgre.

Подробнее о подзапросах

Изучение в PostgreSQL оконных функций – не слишком трудная задача, если разобраться в принципах и plan составления запросов. Стоит учитывать следующие факты о вложенности:

• подзапрос носит название inner select;
• основной запрос, который содержит вложенный – это outer select (или outer query).

Теперь вложенные функции можно рассмотреть более подробно на примере работы с теми или иными операторами.

Where в помощь

Чаще всего select будет обнаруживаться в операторе where. Это – вложенные подзапросы. Вот пример кода:

SELECT p.product_id, p.product_name
FROM products p
WHERE p.category_id IN
   (SELECT c.category_id
    FROM categories c
    WHERE c.category_id > 25
    AND c.category_name like 'S%');

В нем часть оператора select будет иметь следующий вид:

(SELECT c.category_id
 FROM categories c
 WHERE c.category_id > 25
 AND c.category_name like 'S%');

После того, как компьютер выполнил соответствующий Select, удастся обнаружить все category_id из таблицы category, где искомое значение больше 25, а name начинается с буквы S. Этот limit поможет составить условия поиска. Далее будет задействован select для того, чтобы фильтровать результаты основной «команды» с применением in.

Рассмотренная операция может иметь иную интерпретацию. Добавьте inner join, а не select, чтобы запрос имел такой вид:

А рассмотренном примере соответствующий вариант будет более эффективен. Стоит помнить и о том, что не все select можно преобразовать в joins-соединения.

Оператор From

Второй вариант использования Select – это в операторе from. Оные носят название представлений встроенного характера.

Здесь select был создан через:

Этот вариант обладает именем subquery1. Соответствующее название используется для ссылки на нужный select или любое из его полей.

Оператор Select

Далее план предусматривает изучение оператора select. В нем тоже могут встречать вложенные «команды». Это далеко не limit.

Запомнить стоит такие факты:

1. Select используется для того, чтобы получать расчеты с агрегатными функциями.
2. Не хочется применять агрегатную функцию к основному query.

Пример предусматривает создание вложенной операции в Select так:

Данный вариант имеет псевдоним subquery2. Соответствующее название будет использоваться для ссылки на подобный query или любое его поле.

Размещение subquery в Select заключается в том, что оный должен отвечать за возврат одного значения. Из-за этого имеет место частое применение агрегатных функций.

А для того, чтобы лучше разбираться не только в Select, но и в limit, insert into и PostgreSQL лучше, подойдут специальные курсы. В дистанционной форме люди смогут обучаться основам работы с БД и их командами.

Интересует PostgreSQL? Обратите внимание на специализированный курс Otus!

Почему нужны курсы

Дистанционные курсы выделяются следующими моментами:

• хорошо продуманные программы, составленные опытными программерами;
• постоянное кураторство;
• возможность узнать, что такое select и inner «с нуля»;
• предложения не только для новичков, но и для опытных разработчиков;
• доступность – можно подключиться с любого места, где есть интернет;
• совместимость с жизнью, семьей и работой;
• выдача электронного сертификата по выпуску.

Так удастся сконцентрироваться на определенных направлениях в IT и информационных технологиях, получая бесценные знания и практику.