вычисляемое поле в запросе sql
SQL-Урок 6. Вычисляемые поля
1. Выполнение математических операций
Одним из способов использования расчетных полей является выполнение математических операций над выбранными данными. Давайте на примере рассмотрим как это происходит, использовав снова нашу таблицу Sumproduct. Предположим, нам нужно вычислить среднюю цену приобретения каждого товара. Для этого нужно переделить колонку Amount (сумма) на Quantity (количество):
SELECT DISTINCT Product, Amount/Quantity FROM Sumproduct
Как видим, СУБД отобрала все наименования товаров и отобразила их среднюю стоимость в отдельном столбце, который был создан во время выполнения запроса. Также можно заметить, что мы использовали дополнительный оператор DISTINCT, который нам нужен для отображения уникальных названий товаров (без него мы бы получили дублирование записей).
2. Использование псевдонимов
SELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct
Видим, наше расчетное поле получило собственное название AvgPrice. Для этого мы использовали оператор AS, после которого указали необходимое нам название. Стоит отметить, что в SQL поддерживаются только основные математические операции: сложение (+), вычитание (-), умножение (*), деление (/). Также для изменения очередности выполнения операции можно использовать круглые скобки.
Часто псевдонимы используют не только чтобы называть расчетные поля, но и для переименования действующих. Это может быть необходимым, если действующее поле имеет длинное название или название не достаточно информативным.
3. Соединение полей (конкатенация)
Кроме математических операций мы можем объединять текст и выводить его в отдельном поле. Давайте рассмотрим, каким образом можно осуществить склеивание (конкатенацию) текста. Имеем такой пример:
SELECT Month + ‘ ‘ + Product AS NewField, Quantity FROM Sumproduct
В этом примере мы соединили значение в двух столбцах и вывели результат в новое поле NewField.
Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик «Дом печати» (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:
1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком «Дом печати» (id=2):
2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:
3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:
5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик «Дом печати». Синтаксис для использования функции SUM() следущий:
Синтаксис создания представления следующий:
Вычисляемые поля (столбцы)
Представления
Синтаксис создания представлений мы уже рассматривали. После создания представлений, их можно использовать так же, как таблицы. То есть выполнять запросы к ним, фильтровать и сортировать данные, объединять одни представления с другими. С одной стороны это очень удобный способ хранения частоприменяемых сложных запросов (как в нашем примере).
Видеоуроки php + mysql
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Базовый курс SQL. Вычисляемые поля
Primary tabs
Forums:
Иногда нам нужно извлечь данные не в том формате, в котором они хранятся в таблицах. Например:
Следует знать, что в таком случае SQL предоставляет возможность произвести некоторые преобразования с данными прямо в процессе запроса. Отформатировать полученные «сырые» данные могла бы и клиентская сторона, но как правило, на сервере базы данных это происходит гораздо быстрее.
Конкатенация полей
Допустим, мы создаём список студентов участвующих в творческом конкурсе, и нам требуется указать их возраст в виде Фамилия (возраст). В PostgreSQL соединить значения двух столбцов и добавить скобки мы можем с помощью оператора ||:
Как вы можете увидеть, в результате мы имеем все 4 части, склеенные в одну строку. Но мешают пробелы, которыми было заполнено поле. Чтобы их убрать, воспользуемся функцией RTRIM(), которая удаляет все пробелы, справа от значения. Также, в случае необходимости, можете использовать LTRIM() и TRIM(), удаляющие соответственно пробелы слева от строки или пробелы и слева, и справа.
В некоторых других СУБД для конкатенации вместо «||» используется «+».
В MySQL конкатенацию можно осуществить с помощью функции CONCAT().
Псевдонимы вычисляемых полей
Наверное вы заметили, что новый столбец, который мы получили «на лету», не имеет имени. В таком случае мы не сможем обратиться к нему на стороне клиентского приложения. Чтобы решить эту проблему, дадим столбцу псевдоним. Для этого используется ключевое слово AS:
Теперь мы сможем обращаться к результату данного запроса по имени, так, как если бы это был реальный столбец.
Псевдонимы могут быть использованы и для переименования существующих столбцов таблицы. Обычно это делают для сокращения длинных неудобочитаемых заголовков, но причина может быть и любая другая. Важно помнить, что если вы хотите дать столбцу сложный псевдоним из нескольких слов, его надо будет заключить в кавычки.
Математические операции
Теперь нам нужно определить победителей конкурса. Для этого сложим результаты двух туров и отсортируем список по убыванию:
Получим практически готовую турнирную таблицу:
В данном случае столбец final_points является вычисляемым полем. В SQL на ряду со сложением (+) могут быть использованы вычитание (-), умножение (*) и деление (/). Для управления порядком вычислений используйте скобки.
Есть и другие способы расчёта суммы значений в SQL, например, с помощью функции SUM().
Подробнее функции мы рассмотрим в следующем разделе.
Использование SQL Запросов
Язык запросов состоит из одной команды SELECT. Она используется совместно с множеством опций и предложений.
Синтаксис простейшей формы оператора SELECT:
Условия при выборки данных задаются с помощью предложения WHERE.
Что бы не делать лишних запросов в базу данных, используйте переменные ORDER BY и суфиксы.
Для упорядочения данных в выборке используется предложение ORDER BY. Синтаксис:
Псевдонимы – измененные имена полей. Применяются при задании вычисляемых полей. Псевдоним помещается после имени поля или после вычисляемого предложения через ключевое поле AS: SELECT [Код товара], Заказано, Продано, Цена, Цена*Продано AS [Сумма продажи]
Группировка записей – объединение записей в соответствии со значениями некоторого заданного поля. Предложение GROUP BY. Совместно с ним используются функции агрегирования. В этом случае они применяются для вычисления итоговых значений по отдельным группам данных.
Использование вычисляемых полей
На основании данных таблицы «Продажи» вычислите для каждого товара сумму денег, полученных за проданный товар (произведение цены на количество проданного товара), и сумму, на которую заказано товаров (произведение цены на количество заказанного товара), а также разность между ними:
Псевдонимы полей
Задайте псевдонимы для вычисляемых полей в предыдущем запросе.
Функции агрегирования
В таблице «Продажи» подсчитайте количество записей в поле «Продано», минимальное и максимальное количество проданных товаров, общую сумму проданных товаров и среднее значение проданных товаров.
Группировка данных
Подсчитайте количество покупок товаров, сделанных каждым из клиентов.
В предыдущем запросе выберите только тех клиентов, которые сделали за один раз более 10 покупок, и упорядочьте результаты выборки в порядке возрастания:
Язык SQL позволяет извлечь данные из нескольких таблиц. При этом выполняется операция соединения. Способы соединения: соединение равенства, соединение неравенства, внешние соединения. Для задания вида соединения используется предложение WHERE.
Соединение равенства производится по общему для нескольких таблиц полю. Синтаксис:
При использовании внешнего соединения результат запроса будет содержать все записи одной из таблиц, даже в том случае, если в связанной с ней таблице отсутствуют совпадающие значения. Реализуется с помощью оператора OUTER JOIN:
LEFT – левое внешнее соединение, RIGHT – правое внешнее соединение, FULL – полное внешнее соединение.
Подзапрос – запрос, помещенный внутри другого запроса.
Запросы, включающие в себя несколько операторов SELECT, называются составными. Правила объединения запросов:
Примеры
Выбрать записи из двух таблиц, используя соединение равенства.
Выбрать из таблицы «Клиенты» поля, содержащие сведения об именах клиентов, а из таблицы «Продажи» — поля, в которых содержатся сведения о покупках, сделанных клиентами. Для связывания таблиц воспользуемся общим для обеих таблиц полем «Код клиента»:
Дополнить данный запрос предложением группировки и полем, содержащим функцию агрегирования.
Изменить рассмотренный выше запрос таким образом, чтобы результаты были сгруппированы по полям «Фамилия», «Имя», «Отчество» и для каждого клиента выводилось суммарное количество покупок:
Выбрать записи из трех взаимосвязанных таблиц.
Включить информацию о наименовании товара из таблицы «Товары»:
Создать левое и правое внешние соединения.
Выбрать из таблицы «Товары» список товаров, из таблицы «Продажи» — суммарное количество проданных товаров:
Создать сложный запрос, использующий подзапрос.
Выбрать из таблицы «Продажи» информацию о продажах товара с наименованием «Delphi 5»:
SQL-Урок 6. Вычисляемые поля
1. Выполнение математических операций
Одним из способов использования расчетных полей является выполнение математических операций над выбранными данными. Давайте на примере рассмотрим как это происходит, использовав снова нашу таблицу Sumproduct. Предположим, нам нужно вычислить среднюю цену приобретения каждого товара. Для этого нужно переделить колонку Amount (сумма) на Quantity (количество):
SELECT DISTINCT Product, Amount/Quantity FROM Sumproduct
Как видим, СУБД отобрала все наименования товаров и отобразила их среднюю стоимость в отдельном столбце, который был создан во время выполнения запроса. Также можно заметить, что мы использовали дополнительный оператор DISTINCT, который нам нужен для отображения уникальных названий товаров (без него мы бы получили дублирование записей).
2. Использование псевдонимов
В предыдущем примере мы рассчитывали среднюю стоимость покупки каждого товара и отобразили значение в расчетном столбце. Однако в дальнейшем, нам неудобно обращаться к этому полю, так как его название является неинформативным для нас (СУБД дала название полю — Expr1001 ). Однако мы можем назвать поле самостоятельно, заранее указав его название в запросе, то есть дать псевдоним. Давайте перепишем предыдущий пример и укажем псевдонима для расчетного поля:
SELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct
Видим, наше расчетное поле получило собственное название AvgPrice. Для этого мы использовали оператор AS, после которого указали необходимое нам название. Стоит отметить, что в SQL поддерживаются только основные математические операции: сложение (+), вычитание (-), умножение (*), деление (/). Также для изменения очередности выполнения операции можно использовать круглые скобки.
Часто псевдонимы используют не только чтобы называть расчетные поля, но и для переименования действующих. Это может быть необходимым, если действующее поле имеет длинное название или название не достаточно информативным.
3. Соединение полей (конкатенация)
Кроме математических операций мы можем объединять текст и выводить его в отдельном поле. Давайте рассмотрим, каким образом можно осуществить склеивание (конкатенацию) текста. Имеем такой пример:
SELECT Month + ‘ ‘ + Product AS NewField, Quantity FROM Sumproduct
В этом примере мы соединили значение в двух столбцах и вывели результат в новое поле NewField.