SQL Базовый №6. Простые математические операции

Курс профессиональной Power BI аналитики	Подробнее
Курс SQL-анализа данных с нуля	Подробнее

Файлы к уроку:

Математические операторы

Ниже перечислены математические операторы PostgreSQL. Некоторые математические операторы являются стандартными и присутствуют в любой СУБД. Некоторых операторов может не быть в других СУБД или их синтаксис может отличаться.

Оператор	Описание
+	Сложение
—	Вычитание
*	Умножение
/	Деление
%	Остаток от деления
^	Возведение в степень
\|/	Квадратный корень
\|\|/	Кубический корень
factorial(x)	Факториал

Математика и типы данных

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

Два integer вернут integer
Если присутствует хотя бы 1 numeric, то вернется numeric
Если присутствует хотя бы 1 floating, то вернется floating
Возведение в степень, корень, факториал возвращают numeric или floating

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

Чтобы узнать типы данных столбцов таблицы можно воспользоваться запросом:

select
  column_name,
  data_type
from information_schema.columns
where table_name = 'bike_sales';

Если нужно узнать тип данных одного столбца, то можно воспользоваться функцией pg_typeof:

select
  pg_typeof(order_date)
from bike_sales

Практика

-- Сложение, вычитание, умножение, деление
select
  5 + 5,
  pg_typeof(5 + 5);

select
  4 / 3,
  pg_typeof(4 / 3);

select
  4.0 / 3.0,
  pg_typeof(4.0 / 3.0);

select
  4.0 / 3,
  pg_typeof(4.0 / 3);

select
  5 - 2.5,
  pg_typeof(5 * 2.5);
  
select
  5 * 3.14,
  pg_typeof(5 * 3.14);
  
select
  3.14 / 18,
  pg_typeof(3.14 / 18);

-- Остаток от деления
select
  12 % 7,
  pg_typeof(12 % 7);

-- Возведение в степень
select
  3 ^ 4,
 pg_typeof(3 ^ 4);

-- Квадратный корень
select
  |/  16,
  pg_typeof(|/  16);

select
  sqrt(16),
  pg_typeof(sqrt(16));

-- Корень третьей степени
select
  ||/ 27,
  pg_typeof(||/ 27);

-- Факториал = 1 * 2 * 3 * 4
select
  factorial(4),
  pg_typeof(factorial(4));

Порядок выполнения математических операций

Возведение в степень и корень
Умножение, деление и остаток от деления
Сложение и вычитание

select
  -- =-3
  5 - 1 * 8,
  -- =32
  (5 - 1) * 8;

select
  -- =23
  4 ^ 2 + 7,
  -- =262'144
  4 ^ (2 + 7);

Практика на таблице bike_sales

-- Добавить столбец margin
select
  *,
  profit / revenue as margin
from bikes.bike_sales

-- Добавить столбец profit_per_unit
select
  *,
  profit / order_quantity as profit_per_unit
from bikes.bike_sales

-- Добавить столбец profit_after_tax
select
  *,
  profit * 0.85 as profit_after_tax
from bikes.bike_sales

Найти декаду для каждого года, посчитать количество строк в каждой декаде

Для этого упражнения воспользуемся таблицей kinopoisk. Сначала создадим эту таблицу и импортируем данные в базу.

-- Создать таблицу
create table kinopoisk (
  rating smallint,
  movie text,
  year smallint,
  country text,
  rating_balls numeric(4, 3),
  overview text,
  director text,
  screenwriter text,
  actors text,
  url_logo text
);

-- Импорт данных в таблицу
copy kinopoisk
from 'c:\Users\user\Desktop\sql_training\kp.csv'
with (format CSV,
	  header,
	  delimiter ';',
	  encoding 'WIN1251');

Чтобы найти декаду для каждого года можно из года вычесть остаток от деления года на 10.

-- Получить декаду для года
select
  year as release_year,
  year - year % 10 as release_decade_mod
from kinopoisk

Посчитаем количество фильмов в каждой декаде:

-- Посчитать сколько фильмов каждой декады находится в топе
select
  year - year % 10 as release_decade_mod,
  count(*) as films_cnt
from kinopoisk
group by
  year - year % 10 as release_decade_mod
order by
  release_decade_rnd

SQL Базовый

Номер урока	Урок	Описание
1	SQL Базовый №1. Установка PostgreSQL, создание таблицы, импорт из CSV	Начинаем изучать SQL. Установим СУБД, создадим схему, таблицу и выполним импорт из CSV.
1.1	SQL Базовый №1.1. UI создание схемы, таблицы, импорт из CSV	Дополнение к уроку 1. Создадим схему, таблицу и выполним импорт данных из CSV с помощью пользовательского интерфейса.
2	SQL Базовый №2. Простые операции, SELECT	Научимся отбирать нужные нам столбцы и строки таблицы и делать сортировку.
3	SQL Базовый №3. Типы данных	Знакомство с основными типами данных в PostgreSQL: строковые, числовые, дата и время.
4	SQL Базовый №4. Импорт и экспорт данных	Научимся импортировать данные из CSV и экспортировать в CSV.
5	SQL Базовый №5. Группировка	Изучаем операцию группировки и основные агрегатные функции.
6	SQL Базовый №6. Математические операции	Сложение, вычитание, умножение, деление, остаток от деления, факториал, возведение в степень, корень.
7	SQL Базовый №7. CASE	CASE — это условный оператор, который позволяет осуществлять проверку и возвращать результат, который зависит от того, какое условие выполнено.
8	SQL Базовый №8. Дополнения к урокам 1-7	Значение NULL, запросы к таблицам из других схем, рекомендации по форматированию кода, порядок выполнения операций в SQL.
9	SQL Базовый №9. Повторение изученного	Повторим все, что изучили в уроках 1-8. Спонсоры также смогут получить домашнее задание.
10	SQL Базовый №10. Подзапросы	Подзапросы нужны, когда для решения задачи нужно сделать несколько действий. Подзапросы внутри WHERE, FROM, SELECT, HAVING.
11	SQL Базовый №11. CTE	CTE также используются для решения задач в несколько этапов.
12	SQL Базовый №12. Оконные функции. Ранг, ранжирование (ranking)	Как посчитать ранг для всей таблицы или для конкретной категории.
13	SQL Базовый №13. Оконные функции. Агрегаты (SUM, AVG, MIN, MAX, COUNT)	Получить агрегированную итоговую сумму для категории.
14	SQL Базовый №14. Смещение	Приемы из этого урока позволяют получить значение из другой строки таблицы.
15	SQL Базовый №15. Оконные функции. Скользящие агрегаты	Научимся вычислять скользящее среднее и нарастающий итог.
16	SQL Базовый №16. Операция JOIN	Какие бывают виды объединения по горизонтали. Чем они отличаются друг от друга. Практика и домашнее задание.
17	SQL Базовый №17. Практика	Повторим все изученное в предыдущих уроках. Создадим схему, несколько таблиц и выполним несколько запросов на закрепление изученного материала.
18	SQL Базовый №18. Объединение по вертикали (UNION, UNION ALL, INTERSECT, EXCEPT)	Научимся объединять таблицы по вертикали.
19	SQL Базовый №19. Устройство на работу 1	Тестовые задания при приеме на работу.
20	SQL Базовый №20. Сравнение с Excel, важные функции DBeaver	Важные функции DBeaver. Сравниваем SQL и Excel.
21	SQL Базовый №21. Устройство на работу 2	Решим еще один тест при устройстве на работу.
22	SQL Базовый №22. Краткий обзор основных DDL и DML команд	Познакомимся с командами, которые позволяют создавать таблицы, наполнять их данными, редактировать, удалять и т. д.
23	SQL Базовый №23. Устройство на работу 3	Еще одно тестовое задание по SQL при приеме на работу на позицию BI-аналитика.

предыдущая запись

Power Query Язык М №13. Ключевое слово each

следующая запись