Как составить формулы для sql



Главная → Уроки SQL → Урок 11. Итоговые функции, вычисляемые столбцы и представления

SQL — Урок 11. Итоговые функции, вычисляемые столбцы и представления

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

• AVG() Функция возвращает среднее значение столбца.
• COUNT() Функция возвращает число строк в столбце.
• MAX() Функция возвращает самое большое значение в столбце.
• MIN() Функция возвращает самое маленькое значение в столбце.
• SUM() Функция возвращает сумму значений столбца.

С одной из них — COUNT() — мы уже познакомились в уроке 8. Сейчас познакомимся с остальными.
Предположим, мы захотели узнать минимальную, максимальную и среднюю цену на книги в нашем магазине. Тогда
из таблицы Цены (prices) надо взять минимальное, максимальное и среднее значения по столбцу price.
Запрос простой:


SELECT MIN(price), MAX(price), AVG(price) FROM prices;


Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик «Дом печати» (id=2). Составить такой запрос не так просто.
Давайте поразмышляем, как его составить:

1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком
«Дом печати» (id=2):


SELECT id_incoming FROM incoming
WHERE id_vendor=2;


2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые
осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:


SELECT id_product, quantity FROM magazine_incoming
WHERE id_incoming=(SELECT id_incoming FROM incoming WHERE id_vendor=2);


3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть
нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:


SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price FROM magazine_incoming, prices
WHERE magazine_incoming.id_product= prices.id_product AND id_incoming=
(SELECT id_incoming FROM incoming WHERE id_vendor=2);


4. В получившейся таблице явно не хватает столбца Сумма, то есть вычисляемого столбца. Возможность создания таких столбцов
предусмотрена в MySQL. Для этого надо лишь указать в запросе имя вычисляемого столбца и что он должен вычислять. В нашем
примере такой столбец будет называться summa, а вычислять он будет произведение столбцов quantity и price. Название нового
столбца отделяется словом AS:


SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price,
magazine_incoming.quantity*prices.price AS summa
FROM magazine_incoming, prices
WHERE magazine_incoming.id_product= prices.id_product AND id_incoming=
(SELECT id_incoming FROM incoming WHERE id_vendor=2);


5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик «Дом печати».
Синтаксис для использования функции SUM() следущий:


SELECT SUM(имя_столбца) FROM имя_таблицы;


Имя столбца нам известно — summa, а вот имени таблицы у нас нет, так как она является результатом запроса. Что же делать?
Для таких случаев в MySQL существуют Представления. Представление — это запрос на выборку, которому
присваивается уникальное имя и который можно сохранять в базе данных, для последующего использования.

Синтаксис создания представления следующий:


CREATE VIEW имя_представления AS запрос;


Давайте сохраним наш запрос, как представление с именем report_vendor:


CREATE VIEW report_vendor AS
SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price,
magazine_incoming.quantity*prices.price AS summa
FROM magazine_incoming, prices
WHERE magazine_incoming.id_product= prices.id_product AND id_incoming=
(SELECT id_incoming FROM incoming WHERE id_vendor=2);


6. Вот теперь можно использовать итоговую функцию SUM():


SELECT SUM(summa) FROM report_vendor;


Вот мы и достигли результата, правда для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и
представления. Да, иногда для получения результата приходится подумать, без этого никуда. Зато мы коснулись двух очень важных
тем — вычисляемые столбцы и представления. Давайте поговорим о них поподробнее.

Вычисляемые поля (столбцы)

На примере мы рассмотрели сегодня математическое вычисляемое поле. Здесь хотелось бы добавить, что использовать можно не только
операцию умножения (*), но и вычитание (-), и сложение (+), и деление (/). Синтаксис следующий:


SELECT имя_столбца_1, имя_столбца_2, имя_столбца_1*имя_столбца_2 AS имя_вычисляемого_столбца
FROM имя_таблицы;


Второй нюанс — ключевое слово AS, мы его использовали для задания имени вычисляемого столбца. На самом деле с помощью
этого ключевого слова задаются псевдонимы для любых столбцов. Зачем это нужно? Для сокращения и читаемости кода. Например,
наше представление могло бы выглядеть так:


CREATE VIEW report_vendor AS
SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa
FROM magazine_incoming AS A, prices AS B
WHERE A.id_product= B.id_product AND id_incoming=
(SELECT id_incoming FROM incoming WHERE id_vendor=2);


Согласитесь, что так гораздо короче и понятнее.

Представления

Синтаксис создания представлений мы уже рассматривали. После создания представлений, их можно использовать так же, как таблицы.
То есть выполнять запросы к ним, фильтровать и сортировать данные, объединять одни представления с другими. С одной стороны это
очень удобный способ хранения частоприменяемых сложных запросов (как в нашем примере).

Но следует помнить, что представления —
это не таблицы, то есть они не хранят данные, а лишь извлекают их из других таблиц. Отсюда, во-первых, при изменении данных
в таблицах, результаты представления так же будут меняться. А во-вторых, при запросе к представлению происходит поиск необходимых
данных, то есть производительность СУБД снижается. Поэтому злоупотреблять ими не стоит.

Предыдущий урок
Вернуться в раздел
Следующий урок

Научись программировать на Python прямо сейчас!

• Научись программировать на Python прямо сейчас
• Бесплатный курс

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.

1. Текст.

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

Пример: “Текст”,  ‘Текст’

2. Числа.

Числа должны содержать в качестве десятичного разделителя точку. Не допускаются пробелы внутри числа, разделители тысяч и знаки валюты.

Пример:  12345

3. Поля данных.

Синтаксис: {Имя_таблицы.Поле}

При использовании в формуле поле должно быть заключено в фигурные скобки {}, с указанием имени таблицы и, через точку, имени поля:

Пример: {Orders.Costs}

4. Поля формул и параметрические поля.

Синтаксис: {Имя_поля}

Имена этих полей заключаются в фигурные скобки. Перед именем поля формулы автоматически устанавливается знак @, перед именем параметрического поля – знак ?.

Пример: {@SumAccount}, {?DocDate}

5. Операции.

При использовании операций в формулах в нужном месте просто вводится знак операции. До и после знака операции можно вводить пробел, но он не обязателен.

Пример: 1 + 2

6. Функции.

Синтаксис: Имя_функции(параметры)

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

Пример: Sum({Pays.Qv})

7. Скобки.

{} – в фигурные скобки заключаются имена полей;
[] – в квадратные скобки заключаются индексы массивов;
() – в круглые скобки заключаются параметры, а также они позволяют группировать операции и определять порядок их выполнения.

8. Переменные.

В Crystal Reports переменные определяются с помощью ключевого слова, которое начинается с названия типа данных и суффикса Var.

Пример: 	NumberVar x ; 		- переменная x числового типа
	CurrencyVar CCost ; 	- переменная CCost денежного типа

Значения присваиваются переменным с помощью оператора присваивания «:=«:

X := 24 ;

Присвоить значение переменной можно непосредственно при объявлении:

StringVar LName := ”Иванов” ;

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

9. Комментарии.

Комментарии в формулах начинаются со знака «//» (двойной слэш) и действуют до конца строки. В одной строке допускается совместное написание выражения и комментария, но комментарий должен быть справа.

//Это пример комментария

Еще одна неприятная особенность: редактор формул не воспринимает набор текста в русском регистре. Приходится писать текстовые фрагменты в Блокноте, а затем копировать через буфер обмена. Формулы с русскими текстовыми фрагментами работают нормально. Если кто знает лучший способ решения этой проблемы, пишите.

Здравствуйте, уважаемые читатели блога webcodius.ru. Сегодня продолжим изучение баз данных, а именно пройдемся по математическим функциям языка sql. Использование математических функций sql поможет перенести часть логики приложения с web-сервера на сервер базы данных, тем самым разгрузив web-сервер на который обычно ложится основная нагрузка.

Сразу отмечу, что в случае ошибки все математические функции возвращают NULL. Итак, перейдем к делу.

Знаки числа

Начнем с функции ABS (x), которая возвращает абсолютное значение переданного ей числа x. Пример:

SELECT ABS (-5)
Результат: 5
SELECT ABS (5)
Результат: 5

Знак числа можно определить с помощью функции SIGN (x). Функция возвращает -1 если x отрицательное число, 1 если положительное и 0 если x является нулем. Пример:

SELECT SIGN (-5)
Результат: -1
SELECT SIGN (0)
Результат: 0
SELECT SIGN (5)
Результат: 1

Округление чисел

 Начнем с функции FLOOR (x). Возвращает ближайшее целое число не превышающее x. Пример:

SELECT FLOOR (5.5)
Результат: 5
SELECT FLOOR (5.2)
Результат: 5
SELECT FLOOR (5.7)
Результат: 5
SELECT FLOOR (5)
Результат: 5
SELECT FLOOR (-5.2)
Результат: -6

Как видно, при задании любого положительного дробного числа от 5 до 6 возвращается целое число 5. А при передаче параметра -5.2 вернется -6, так как -6 меньше -5.2.

Обратное действие выполняет функция CEILING (x). Она возвращает ближайшее целое число, которое превышает переданный параметр x. Пример:

SELECT CEILING (5.5)
Результат: 6
SELECT CEILING (5.2);
Результат: 6
SELECT CEILING (5.7)
Результат: 6
SELECT CEILING (5)
Результат: 5
SELECT CEILING (-5.5)
Результат: -5

Для округления дробного числа до ближайшего целого используется функция ROUND (x, d). Функция может принимать один или два параметра. Первый параметр x — число, которое необходимо округлить. Второй параметр d — целое число, определяющее разряд до которого необходимо округлить x.  В случае если передан один параметр x, то происходит просто округление до ближайшего целого. Например:

SELECT ROUND (50.45)
Результат: 50
SELECT ROUND (50.76)
Результат: 51

При значении аргумента, равного середине между двумя целыми числами, результат будет зависеть от конкретной СУБД, где используется язык SQL.

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

SELECT ROUND (50.76, 1)
Результат: 50,8

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

SELECT ROUND (251.55, -1)
Результат: 250

В SQL еще имеется функция, которая не округляет, а отсекает десятичную часть дробного числа. Функция TRUNCATE (x, y) возвращает число x, усеченное до y десятичных знаков:

SELECT TRUNCATE (1.999, 1)

Результат: 1.9

SELECT TRUNCATE (1.999, 0)

Результат: 1

Функции выполняющие сложные математические операции

Начнем с самого простого. Функция MOD (x, y) возвращает остаток от деления x на y. Например:

SELECT MOD (10, 3)
Результат: 1

Следующая функция EXP (x), которая возвращает значение e (Число Эйлера) возведенное в степень x. Или научным языком, возвращает экспоненту числа. Пример:

SELECT EXP (3)
Результат: 20.085536923187668

Далее рассмотрим функцию LOG (x), которая возвращает натуральный логарифм числа x. Пример:

SELECT LOG (10)
Результат: 2.302585092994046

Для получения логарифма числа x, для произвольной основы логарифма y можно пользоваться формулой LOG (x)/LOG (y). Например:

SELECT LOG (8)/LOG (2)
Результат: 3

Для получения десятичного логарифма числа x существует функция LOG10 (x). Пример:

SELECT LOG10 (100)
Результат: 2

Для возведения в степень в языке SQL есть целых две функции: POW (x, y) и POWER (x, y). Возвращают число x возведенное в степень y. Пример:

SELECT POW (2, 3)
Результат: 8
SELECT POWER (3, 2)
Результат: 9

А функция SQRT (x) вычисляет квадратный корень числа x. Пример:

SELECT SQRT (16)
Результат: 4

Чтобы использовать в своих вычисления число «пи» в SQL есть функция PI (), которая возвращает значение этого числа:

SELECT PI ()
Результат: 3.141593

Тригонометрические функции в языке SQL

Кратенько пройдемся по тригонометрическим функциям:

• COS (x) — косинус угла x;
• SIN (x) — синус угла x;
• TAN (x) — тангенс угла x;
• COT (x) — котангенс угла x.

Везде x задается в радианах. Примеры:

SELECT COS (PI ())
Результат:  -1
SELECT SIN (PI ()/2)
Результат: 1
SELECT TAN (PI ()/4)
Результат: 1
SELECT COT (PI ()/3)
Результат: 0.577350269189626

Функции ACOS (x) и ASIN (x) вычисляют соответственно арккосинус и арксинус числа x, т.е. функции возвращают величину, косинус или синус которой равен x. При этом если значение x не находится в диапазоне от -1 до 1, то функции возвращают NULL. Например:

SELECT ASIN (-1)
Результат: -1.5707963267949
SELECT ACOS (-1)
Результат: 3.14159265358979
SELECT ACOS (1.1)
Результат: NULL

Функция ATAN (x) вычисляет арктангенс числа x, т.е. возвращает величину, тангенс которой равен x. Пример:

SELECT ATAN (3);
Результат: 1.24904577239825

Для преобразования радиан в градусы и обратно используются функции DEGREES (x) и RADIANS (x) соответственно:

SELECT DEGREES (PI ())
Результат: 180
SELECT RADIANS (180)
Результат: 3.14

Случайные числа

Функция RAND (x) генерирует случайное значение в диапазоне от 0 до 1. Если указан аргумент x, то он используется как начальное значение этой величины. Пример:

SELECT RAND ();
Результат:0.472241415009636
SELECT RAND (0.5);
Результат: 0.943597390424144

На этом все. Вроде рассмотрел все часто используемые в SQL математические функции. Возможно вам будет интересно узнать и о функциях обработки строк в SQL.

До новых встреч!

Разбор на примерах: SUM, AVG, MIN, MAX, COUNT.

В статье поговорим про агрегирующие функции SQL: узнаем, зачем они нужны, и обсудим их основные понятия и возможности. Разберем на примерах функций: COUNT, SUM, MAX, MIN, AVG.

Что такое агрегатные функции SQL

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

Для сравнения — скалярные функции принимают на вход одно значение и возвращают одно значение. Пример скалярной функции: abs — функция, принимающая число и возвращающая абсолютное значение этого числа.

Свойства агрегатных функций:

• Игнорируют NULL-значения. Исключение — функция COUNT(*). Дальше детально рассмотрим, что это значит.
• Все агрегатные функции детерминированы. Это значит, что для одинакового набора данных функции возвращают одинаковое значение.

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

С агрегатными функциями можно использовать ключевые слова DISTINCT и ALL, которые мы рассмотрим ниже. Синтаксис выглядит так:

 aggregation_function(DISTINCT | ALL expression)

В примерах будем использовать таблицу Employees («Сотрудники»).

Создать ее можно с помощью команды:

CREATE TABLE IF NOT EXISTS employees (
name VARCHAR ( 50 ) NOT NULL,
office_id INT,
salary INT,
role VARCHAR ( 50 ) NOT NULL
);

INSERT INTO employees VALUES
('Ivan', 1, 500, 'SWE'), ('Misha', 2, 750, 'Manager'), ('Olya', 2, 600, 'QA'), ('Kolya', 1, 900, 'SWE'), ('Max', 2, NULL, 'Manager');

Протестировать команды ниже поможет сервис DB Fiddle. Используемый код совместим с программой PostgreSQL v14 — системой управления базами данных.

Функция COUNT

COUNT считает количество строк в таблице. Она может принимать в качестве параметров как числовые, так и нечисловые типы данных.

COUNT(*) — специальная форма функции COUNT, которая возвращает количество всех строк в указанной таблице. Обратите внимание: COUNT(*) считает дубликат и NULL.

Напишем запрос к таблице Employees, который будет считать количество сотрудников для каждой роли:

SELECT role, count(*) number_of_empoyee FROM employees GROUP BY role;

Посчитаем количество сотрудников уникальных офисов:

SELECT COUNT(DiSTINCT office_id) AS unique_offices FROM employees;

SUM

Функция SUM вычисляет суммы всех выбранных столбцов. Обратите внимание: она работает только с числовыми полями.

Посчитать суммарную зарплату для всех сотрудников можно так:

SELECT SUM(salary) AS total_salary FROM employees;

MAX и MIN

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

Например, минимальная зарплата среди всех сотрудников:

SELECT MIN(salary) AS min_salary FROM employees;

Напишем запрос сложнее, который использует оператор HAVING, — максимальная зарплата для каждого офиса, где больше двух сотрудников:

SELECT office_id, MAX(salary) AS max_salary FROM employees GROUP BY office_id HAVING COUNT(*) > 2;

AVG

Функция используется для вычисления среднего значения заданного столбца.
Например, рассчитаем среднюю зарплату по всем сотрудникам:

SELECT AVG(salary) AS avg_salary FROM employees;

Важно: в столбце salary у нас есть значение NULL, но функция AVG проигнорировала его и посчитала среднее значение как (500 + 750 + 600 + 900) / 4 = 687,5

Где применяют агрегирующие функции

Их активно используют в различных проектах и продуктах. Например, с помощью функций:

• строят модели в аналитике;
• строят графики в реальном времени с метриками сервисов;
• пишут отчеты.

Для некоторых продуктов критически важна производительность агрегирующих функций (например, для построения аналитики в реальном времени). Поэтому есть базы данных, которые оптимизированы для вычислений агрегатных функций: ClickHouse и Apache Druid. Еще есть хранилище данных, которое оптимизировано под выполнение запросов с агрегатными функциями, — firebolt.

Краткие итоги

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

Разобрали четыре функции:

• COUNT — считает количество строк в таблице;
• SUM — вычисляет суммы выбранных столбцов;
• MAX и MIN — находят максимальное и минимальное значение определенного столбца;
• AVG — вычисляет среднее значение столбца.

Агрегатные функции используют с операторами GROUP BY и HAVING:

• GROUP BY — группирует строки с одинаковыми значениями в одну строку;
• HAVING — используется как фильтр для запросов, в которых есть оператор GROUP BY.