Запросы

Создание таблицы

SELECT 'Бим' AS dog_name, 'Бим' AS DOG_NAME, 'Бим' AS Dog_Name

Время на сервере

Капсом через Upper(переменная)

Select NOW() as server_date, name as name, upper(name) as name_upper
from city

Функции

Функция Concat

Работа со значениями в столбце и вывод в новом

SELECT product_id as product_id,
price as price,
price/2 as price_sale
FROM product_price

DISTINCT — убирает дубликаты

SELECT DISTINCT product_id
FROM purchase_item

SELECT DISTINCT pp.product_id AS product_id_price, pi.product_id AS product_id_purchase FROM product_price pp FULL JOIN purchase_item pi ON pp.product_id = pi.product_id WHERE pp.product_id IS NULL OR pi.product_id IS NULL

WHERE — для выборки строк из таблицы по условию

SELECT *
FROM store_address
WHERE city_id = 6
AND address = ‘ул. Чкалова, 42’

ПРИОРИТЕТЫ ОПЕРАЦИЙ

NOT
AND
OR

BETWEEN — диапазон между 2 числами ВКЛЮЧАЯ границы

SELECT *
FROM product_price
WHERE price BETWEEN 5000 AND 15000
OR price BETWEEN 30000 AND 40000

IN (‘ ‘,’ ‘) – проверка на наличие в массиве ( )

SELECT *
FROM employee
WHERE rank_id NOT IN (‘SELLER’, ‘MANAGER’)

LIKE — Поиск по шаблону
При проверке по шаблону LIKE всегда рассматривается вся строка. Поэтому, если нужно найти последовательность символов где-то в середине строки, шаблон должен начинаться и заканчиваться знаком %.

Чтобы найти в строке буквальное вхождение знака процента или подчёркивания, перед соответствующим символом в шаблоне нужно добавить спецсимвол. По умолчанию в качестве спецсимвола выбрана обратная косая черта \, но с помощью предложения ESCAPE можно выбрать и другой. Чтобы включить спецсимвол в шаблон поиска, продублируйте его. Синтаксис LIKE с указанием спецсимвола:
Вместо LIKE можно использовать ключевое слово ILIKE, чтобы поиск был регистр-независимым с учётом текущей языковой среды.

SELECT first_name,
last_name
FROM employee
WHERE last_name LIKE ‘М%в’

IS NULL — поиск нулевых ячеек

IN NOT NULL — поиск ненулевых ячеек

ORDER BY — Сортировка (писать в конце запроса)
Чтобы отсортировать в обратном порядке, необходимо после поля для сортировки указать ключевое слово DESC (по умолчанию ASC — по возрастанию). Например отсортируем магазины по названию по убыванию:

SELECT *
FROM city
ORDER BY name

LIMIT – Выводит ограниченное количество строк

OFFSET – Пропуск N строк

SELECT *
FROM city
ORDER BY name

Получи информацию о стоимости первых 5 товаров с максимальной ценой, пропустив первые 2.

Выведи все поля из таблицы product_price.

SELECT *

FROM product_price
ORDER BY price DESC
LIMIT 5
OFFSET 2

Сокращение имени таблицы

SELECT

FROM city c

Присоединение таблицы

JOIN

SELECT c.city_id,
       c.name,
       t.time_offset
  FROM city c
  JOIN timezone t
    ON t.timezone_id = c.timezone_id

Объединение 2х таблиц по 2м параметрам
Либо rank

Либо id

SELECT e.last_name,
e.first_name,
e.store_id as store_id_employee,
r.store_id as store_id_rank,
r.rank_id,
r.name as rank_name

FROM employee e
JOIN rank r
ON r.store_id = e.store_id
OR (r.rank_id = e.rank_id AND r.store_id != e.store_id)
ORDER BY e.last_name, e.first_name, r.store_id, r.rank_id

Использование одной таблицы 2 раза

Затем соединение и сравнение

SELECT pp.product_id,
       pp.store_id,
       pp.price,
       ppl.store_id AS store_id_less,
       ppl.price AS price_less
  FROM product_price pp
  JOIN product_price ppl
    ON ppl.product_id = pp.product_id
   AND ppl.store_id != pp.store_id
   AND ppl.price < pp.price
 ORDER BY pp.product_id,
          pp.store_id,
          ppl.store_id,
          ppl.price

INNER JOIN или просто JOIN — внутреннее соединение. В результате остаются только те строки, для которых нашлось соответствие. До сих пор мы использовали только этот тип соединений.
LEFT JOIN — левое внешнее соединение. Работает как JOIN, но если для строки таблицы, находящейся по левую сторону ключевого слова LEFT JOIN, не нашлось ни одной строки в таблице, находящейся по правую сторону LEFT JOIN, то строка все равно добавляется в результат, а значения столбцов правой таблицы равны null.
RIGHT JOIN — правое внешнее соединение. Работает как JOIN, но если для строки таблицы, находящейся по правую сторону ключевого слова RIGHT JOIN, не нашлось ни одной строки в таблице, находящейся по левую сторону RIGHT JOIN, то строка все равно добавляется в результат, а значения столбцов левой таблицы равны null.
FULL JOIN — полное внешнее соединение. Если для какой-либо из таблиц не нашлось строки в другой таблице, то строка все равно попадает в результат, а значения столбцов другой таблицы равны null.
CROSS JOIN — перекрестное (или декартово) произведение. Каждая строка одной таблицы соединяется с каждой строкой второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц. Аналогичного результата можно достичь просто перечислив таблицы в FROM через запятую.

CROSS JOIN

так же его можно заменить на

FROM таблица 1 , таблица 2

SELECT c.name as city_name,
c.timezone_id as timezone_id_city,
t2.timezone_id as timezone_id_timezone,
t2.time_offset as time_offset
FROM city c
CROSS JOIN
timezone t2
ORDER BY city_name, timezone_id_timezone

WHERE вместо JOIN

SELECT r.name AS rank_name,
       s.name AS store_name,
       c.name as city_name,
       sa.address,
       t.time_offset
  FROM rank r,
       store s,
       store_address sa,
       city c,
       timezone t
 WHERE s.store_id = r.store_id
   AND sa.store_id = s.store_id
   AND c.city_id = sa.city_id
   AND t.timezone_id = c.timezone_id

SELECT r.name AS rank_name,
s.name AS store_name,
c.name as city_name,
sa.address,
t.time_offset
FROM rank r
JOIN store s
ON s.store_id = r.store_id
JOIN store_address sa
ON sa.store_id = s.store_id
JOIN city c
ON c.city_id = sa.city_id
JOIN timezone t
ON t.timezone_id = c.timezone_id

Агрегатные Функции

avg(выражение) — арифметическое среднее;
min(выражение) — минимальное значение выражения;
max(выражение) — маскимальное значение выражения;
sum(выражение) — сумма значений выражения;
count(*) — количество строк в результате запроса;
count(выражение) — количество значений выражения, не равных NULL.

Работа с Таблицами

GROUP BY — группировка строк

SELECT category_id,
       count(*) AS count_products
  FROM product
 GROUP BY category_id

GROUP BY — группировка строк

SELECT category_id,
       count(*) AS count_products
  FROM product
 GROUP BY category_id

GROUP BY с соединением таблиц

category_id	name	count_products
3	Бытовая техника	2
5	Фотоаппараты	2
6	Игровые консоли	3
7	Аудиотехника	1

SELECT p.category_id,
       c.name,
       count(*) AS count_products
  FROM product p
  JOIN category c
    ON c.category_id = p.category_id
 GROUP BY p.category_id, c.name
 ORDER BY p.category_id

GROUP BY и WHERE

select
c.category_id,
p.name,
min(pp.price) as price_min,
max(pp.price) as price_max
FROM category c
JOIN product p
on c.category_id = p.category_id
JOIN product_price pp
on p.product_id = pp.product_id
WHERE c.category_id = 6 or c.category_id = 7
GROUP BY c.category_id, p.name
ORDER BY c.category_id, p.name

NULL значения в GROUP BY (9/13)

В условиях WHERE два NULL значения считаются различными. Но при группировке строк GROUP BY NULL значения считаются идентичными и объединяются в одну группу (как и при исключении повторяющихся строк DISTINCT).

Подсчитаем количество использований номеров телефонов в магазинах:

SELECT phone, count (*)
  FROM store_address
 GROUP BY phone

phone	count
NULL	3
7(347)668‒56‒66	1
7(385)777‒77‒07	1
7(495)312‒03‒08	3
7(812)700‒03‒03	1
…	…

В таблице store_address для трех адресов магазинов не указан номер телефона. Поэтому в результате мы видим строку

phone	count
NULL	3

Если бы NULL значения в GROUP BY считались различными, то мы бы получили вот такой результат:

phone	count
NULL	1
NULL	1
NULL	1

Что неудобно для проведения аналитики.

Пример сложного

GROUP BY

JOIN

Посчитай статистику по руководителям (employee.manager_id) в магазинах. Выведи следующие данные:

store_name — название магазина;
manager_full_name — имя и фамилия руководителя, разделенные пробелом;
amount_employees — количество человек в подчинении.

Если в магазине есть сотрудники, у которых нет руководителя (manager_id is null), в результате должна быть строка, в которой manager_full_name принимает значение NULL, а amount_employees равно количеству сотрудников без руководителя в магазине.

Отсортируй результат по названию магазина, затем по manager_full_name.

SELECT s.name AS store_name,
m.first_name || ‘ ‘ || m.last_name AS manager_full_name,
count(*) AS amount_employees
FROM employee e
LEFT JOIN
employee m
ON m.employee_id = e.manager_id
LEFT JOIN
store s
ON s.store_id = e.store_id
GROUP BY e.store_id,
s.name,
m.manager_id,
m.first_name,
m.last_name
ORDER BY s.name, manager_full_name

store_name	manager_full_name	amount_employees
1	Big	Ольга Вершинина	1
2	Big	Павел Авдеев	3
3	Big		1
4	Umi	Виктор Сухоруков	3
5	Umi	Вилен Кулаков	1
6	Umi	Николай Воробьёв	2
7	Umi		1
8	Адалин	Владислав Бирюков	1
9	Адалин		1
10	Адалин-family	Кристина Емельянова	1

HAVING — как WHERE только для сгруппированных строк

После выполнения группировки можно исключать строки из результата выполнения запроса. Для этого в SQL есть предложение HAVING, предназначение которого аналогично предложению WHERE, только работает с группами строк.

Так как HAVING работает с группами строк, то в условиях отсечения используют агрегатные функции вместо значений столбцов (можно использовать выражения из GROUP BY, но проще и эффективней это сделать в WHERE).

Оставим категории, в которых только один продукт:

SELECT category_id,
       count(*)
  FROM product
 GROUP BY category_id
HAVING count(*) = 1
 ORDER BY category_id

category_id	count
7	1

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

SELECT category_id
  FROM product
 GROUP BY category_id
HAVING count(*) = 1
 ORDER BY category_id

category_id
7

store_name	manager_full_name	amount_employees
1	Big	Ольга Вершинина	1
2	Big	Павел Авдеев	3
3	Big		1
4	Umi	Виктор Сухоруков	3
5	Umi	Вилен Кулаков	1
6	Umi	Николай Воробьёв	2
7	Umi		1
8	Адалин	Владислав Бирюков	1
9	Адалин		1
10	Адалин-family	Кристина Емельянова	1

Пример

HAVING

Для каждого товара получи минимальную и максимальную стоимость из таблицы product_price. Выведи столбцы:

product_id — идентификатор товара;
price_min — минимальная стоимость товара;
price_max — максимальная стоимость товара.

В результате оставь только те товары, для которых минимальная и максимальная стоимость отличается.

Отсортируй результат по идентификатору товара.

SELECT
pp.product_id,
min(price) AS price_min,
max(price) AS price_max
FROM product_price pp
GROUP BY pp.product_id
HAVING min(price) != max(price)
ORDER BY product_id

product_id	price_min	price_max
1	1	10500.00	12500.00
2	2	26100.00	27500.00
3	3	22000.00	22900.00
4	4	20000.00	22000.00
5	5	23500.00	24600.00
6	6	17800.00	17900.00
7	8	37000.00	38300.00
8	9	43800.00	44500.00
9	10	75600.00	76800.00

Срезы

ROLLUP (11/13)

При проведении аналитики часто требуется посмотреть на статистические показатели в разных разрезах. Например, для анализа средней зарплаты интересно посмотреть на среднюю зарплату по каждому подразделению и по всей организации в целом. Для решения этой задачи SQL поддерживает наборы группирования, реализованные через ROLLUP, CUBE, GROUPING SETS.

Данные, выбранные предложениями FROM и WHERE, группируются отдельно для каждого заданного набора группирования, затем для каждой группы вычисляются агрегатные функции как для простых предложений GROUP BY, и в конце результаты объединяются.

Конструкция ROLLUP в цикле выполняет группировку по подспискам переданного списка выражений, на каждой итерации убирая по одному выражению с конца списка, включая пустой список (вычисление агрегатных функций по всем строкам).

Например, для GROUP BY ROLLUP (выражение1, выражение2, выражение3) результатом запроса будет объединение результатов GROUP BY по

выражение1, выражение2, выражение3;
выражение1, выражение2;
выражение1;
все строки выборки.

Синтаксис ROLLUP:

GROUP BY ROLLUP (выражение1, выражение2, ... , выражениеN)

SELECT sa.city_id, 
       p.category_id,
       min(pp.price) AS price_min,
       max(pp.price) AS price_max
  FROM product_price pp
  JOIN product p
    ON p.product_id = pp.product_id
  JOIN store_address sa
    ON sa.store_id = pp.store_id
 WHERE sa.city_id IN (2, 3)
 GROUP BY ROLLUP (sa.city_id, p.category_id)
 ORDER BY sa.city_id NULLS LAST, 
          p.category_id NULLS LAST

city_id	category_id	price_min	price_max
1	2	6	21500.00	23500.00
2	2	7	17800.00	17800.00
3	2	NULL	17800.00	23500.00
4	3	3	10500.00	10500.00
5	3	NULL	10500.00	10500.00
6	NULL	NULL	10500.00	23500.00

SELECT
store_id,
rank_id,
count(*) as count_employees
FROM employee e
GROUP BY ROLLUP (e.store_id, e.rank_id)
ORDER BY store_id NULLS LAST,
rank_id NULLS LAST

store_id	rank_id	count_employees
1	100	CHIEF	1
2	100	MANAGER	1
3	100	SELLER	1
4	100		3
5	200	CHIEF	1
6	200	MANAGER	1
7	200	SELLER	2
8	200		4

CUBE (12/13)

Разберем другой встроенный набор группирования — CUBE. CUBE работает аналогично ROLLUP, только выполняет группировку по всевозможным подмножествам переданного списка выражений.

Например, для GROUP BY CUBE (a, b, c) результатом запроса будет объединение результатов GROUP BY по

a, b, c;
a, b;
a, c;
b, c;
a;
b;
c;
по всем строкам.

Синтаксис CUBE:

GROUP BY CUBE (выражение1, выражение2, ... , выражениеN)

Рассмотрим пример. Получим минимальную и максимальную цены на товары в городах с идентификаторами 2 и 4 по:

городу и категории товара;
только по городу;
только по категории товара;
по всем строкам.

SELECT sa.city_id, 
       p.category_id,
       min(pp.price) AS price_min,
       max(pp.price) AS price_max
  FROM product_price pp
  JOIN product p
    ON p.product_id = pp.product_id
  JOIN store_address sa
    ON sa.store_id = pp.store_id
 WHERE sa.city_id IN (2, 4)
 GROUP BY CUBE (sa.city_id, p.category_id)
 ORDER BY sa.city_id NULLS LAST,
          p.category_id NULLS LAST

#	city_id	category_id	price_min	price_max
1	2	6	21500.00	23500.00
2	2	7	17800.00	17800.00
3	2	NULL	17800.00	23500.00
4	4	6	20000.00	20000.00
5	4	8	37000.00	37000.00
6	4	9	75600.00	75600.00
7	4	NULL	20000.00	75600.00
8	NULL	6	20000.00	23500.00
9	NULL	7	17800.00	17800.00
10	NULL	8	37000.00	37000.00
11	NULL	9	75600.00	75600.00
12	NULL	NULL	17800.00	75600.00

SELECT store_id,
rank_id,
count(*) as count_employees
FROM employee
GROUP BY CUBE (store_id, rank_id)
ORDER BY store_id NULLS LAST,
rank_id NULLS LAST

store_id	rank_id	count_employees
1	100	CHIEF	1
2	100	MANAGER	1
3	100	SELLER	1
4	100		3
5	200	CHIEF	1
6	200	MANAGER	1
7	200	SELLER	2
8	200		4

GROUPING SETS (13/13)

Если возможностей ROLLUP и CUBE не хватает, то можно наборы группирования задать самому через GROUPING SETS.

Синтаксис:

GROUP BY GROUPING SETS ((выражение_11, выражение_12, ... выражение_1n), ... (выражение_m1, выражение_m2, ... выражение_mk))

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

Значения полей из GROUP BY, по которым не производилась группировка, в результате выполнения запроса будут равны NULL.

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

только по городу;
только по категории товара;
по всем строкам.

SELECT sa.city_id, 
       p.category_id,
       min(pp.price) AS price_min,
       max(pp.price) AS price_max
  FROM product_price pp
  JOIN product p
    ON p.product_id = pp.product_id
  JOIN store_address sa
    ON sa.store_id = pp.store_id
 WHERE sa.city_id IN (2, 4)
 GROUP BY GROUPING SETS ((sa.city_id), (p.category_id), ())
 ORDER BY sa.city_id NULLS LAST,
          p.category_id NULLS LAST

#	city_id	category_id	price_min	price_max
1	2	NULL	17800.00	23500.00
2	4	NULL	20000.00	75600.00
3	NULL	6	20000.00	23500.00
4	NULL	7	17800.00	17800.00
5	NULL	8	37000.00	37000.00
6	NULL	9	75600.00	75600.00
7	NULL	NULL	17800.00	75600.00

SELECT store_id,
rank_id,
count(*) as count_employees
FROM employee
GROUP BY GROUPING SETS ((store_id, rank_id), ())
ORDER BY store_id NULLS LAST,
rank_id NULLS LAST

store_id	rank_id	count_employees
1	100	CHIEF	1
2	100	MANAGER	1
3	100	SELLER	1
4	200	CHIEF	1
5	200	MANAGER	1
6	200	SELLER	2
7	201	CEO	1
8	201	MANAGER	2
9	201	SELLER	6

Операции над множествами

Объединение результатов (1/8)

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

UNION — объединение строк (дополнение результатов первого запроса результатами второго запроса);
INTERSECT — пересечение строк (остаются строки, присутствующие в результатах обоих запросов);
EXCEPT — исключение строк (из строк первого запроса исключаются строки второго запроса).

SELECT value
  FROM table1
 UNION
SELECT value
  FROM table2

value value
1 2
3 3
5 4

Результат объединения
value
1
2
3
4
5

SELECT c.name
FROM category c
WHERE c.name ilike ‘%но%’
UNION
SELECT p.name
FROM product p
WHERE p.name ilike ‘%но%’

name
1	Ноутбуки
2	Моноблок C4
3	Ноутбуки и аксессуары

Из какого запроса строка? (2/8)

В предыдущем задании ты нашел категории товаров и товары по подстроке. У результата есть один недочет — непонятно, какие строки являются категориями, а какие — товарами.

Чтобы различать строки результата, можно добавить в список выборки SELECT дополнительный столбец с константой. Сделаем это для наших таблиц table1 и table2, а категориями и товарами ты займешься в задании 🙂

SELECT value, 
       'from_table_1' AS source
  FROM table1
 UNION
SELECT value, 
       'from_table_2' AS source
  FROM table2

value value
1 2
3 3
5 4

Результат объединения

#	value	source
1	1	from_table_1
2	3	from_table_1
3	5	from_table_1
4	2	from_table_2
5	3	from_table_2
6	4	from_table_2

SELECT c.name,
‘Категория’ as type
FROM category c
WHERE c.name ilike ‘%но%’
UNION
SELECT p.name,
‘Товар’ as type
FROM product p
WHERE p.name ilike ‘%но%’

name	type
1	Ноутбуки и аксессуары	Категория
2	Ноутбуки	Категория
3	Моноблок C4	Товар

Дубликаты строк (5/8)

По умолчанию UNION, INTERSECT и EXCEPT исключают дубликаты строк. Это работает так же, как и добавление DISTINCT после ключевого слова SELECT.

Чтобы СУБД не исключала из результата задублированные строки, нужно к ключевому слову операции над множествами добавить ALL. Перепишем наши запросы и посмотрим на результат:

SELECT value
  FROM table1
 UNION ALL
SELECT value
  FROM table2

#	value
1	1
2	1
3	2
4	3
5	3
6	3

SELECT c.name,
‘Категория’ as type
FROM category c
WHERE c.name ilike ‘%но%’

UNION ALL

SELECT p.name,
‘Товар’ as type
FROM product p
WHERE p.name ilike ‘%но%’

name	type
1	Ноутбуки и аксессуары	Категория
2	Ноутбуки	Категория
3	Моноблок C4	Товар

Совпадение типов данных столбцов (6/8)

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

количество полей во всех запросах должно совпадать;
типы данных соответствующих полей должны совпадать.

Что делать, если очень хочется объединить столбцы с разными типами данных? Нужно их преобразовать к одному типу данных.

SELECT value::text
  FROM table1
 UNION
SELECT name
  FROM table1

#	value
1	1
2	3
3	5
4	one
5	three
6	five

Соблюдаем порядок вызова столбцов

SELECT
pp.product_id,
pp.price,
‘отсутствует’ as count
FROM product_price pp

UNION ALL

SELECT
pi.product_id,
pi.price,
pi.count::text
FROM purchase_item pi

#	product_id	price	count
1,00	1,00	10500.00	отсутствует
2	10	75600.00	отсутствует
3	8	37000.00	отсутствует
4	4	20000.00	отсутствует
28	6	17800.00	1
29	2	26100.00	30
30	11	1321800	1
31	8	38300.00	1
32	9	44500.00	1

Сортировка (7/8)

Сортировка строк производится после выполнения операции над множествами. Следственно, в конструкции ORDER BY могут быть использованы только названия столбцов результата. Обращение к столбцу таблицы запрещено.

Не верно

SELECT *
  FROM table1
 ORDER BY name
 UNION ALL
SELECT *
  FROM table2

Верно

SELECT *
  FROM table1
 UNION
SELECT *
  FROM table2
 ORDER BY value

#	value
1	1
2	3
3	5
4	one
5	three
6	five

Подзапросы

Подзапрос одиночной строки (1/9)

Подзапрос — это запрос в запросе. Выглядит как обычный запрос, которые мы рассматривали ранее, и так же возвращает набор строк.

Подзапросы могут использоваться в любой части основного запроса. Его можно использовать для определения значения столбца в списке выборки SELECT. Его можно использовать вместо таблицы в предложении FROM, определять значение для условий отсечения строк в WHERE и HAVING, проверять наличие строк в другой таблице. Еще много чего можно… Подзапросы — мощный инструмент языка SQL.

Обо всем по порядку. Начнем с подзапросов, возвращающих одно значение (single-row subquery или подзапрос одиночной строки). Такой запрос должен возвращать не более одной строки и один единственный столбец.

Подзапрос одиночной строки можно использовать везде, где может быть использована константа или значение столбца таблицы.

SELECT *
  FROM product_price pp
 WHERE pp.price = (SELECT min(ppm.price)
                     FROM product_price ppm)

#	product_id	store_id	price
1	7	301	4900.00

SELECT p.name as product_name,
pi.count,
pi.price
from purchase_item pi
join product p
on p.product_id = pi.product_id
where pi.price = (select max(pi2.price)
from purchase_item pi2)

product_name	count	price
1	Lord Nikon 95	1	1321800000.00

Коррелированный подзапрос (2/9)

Коррелированным подзапросом называется подзапрос, который ссылается на значения столбцов внешнего запроса.

Коррелированный подзапрос выполняется для каждой строки основного запроса. В момент выполнения подзапроса значения столбцов внешнего запроса являются константами.

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

SELECT *
  FROM product_price pp
 WHERE pp.price = (SELECT min(ppm.price)
                     FROM product_price ppm
                    WHERE ppm.product_id = pp.product_id)
 ORDER BY pp.product_id, pp.price, pp.store_id

#	product_id	store_id	price
1	1	300	10500.00
2	2	500	26100.00
3	3	500	22000.00
4	4	400	20000.00
5	5	201	23500.00
6	5	500	23500.0

SELECT c.name AS category_name,
p.name as product_name,
pp.price
FROM product_price pp
JOIN product p
ON p.product_id = pp.product_id
JOIN category c
ON c.category_id = p.category_id
WHERE pp.price = (SELECT max (ppm.price)
FROM product pm
JOIN product_price ppm
ON ppm.product_id = pm.product_id
WHERE pm.category_id = p.category_id)
ORDER BY c.name, p.name

category_name	product_name	price
1	Аудиотехника	Наушники S3	17900.00
2	Бытовая техника	Холодильник A2	27500.00
3	Игровые консоли	Xbox	24600.00
4	Ноутбуки	Ультрабук X5	76800.00
5	Рюкзаки	Deepbox	4900.00
6	Сотовые телефоны	Моноблок C4	44500.00
7	Фотоаппараты	Lord Nikon 95	1321800000.00

Строковые функции

CONCAT — конкатенация строк (1/9)

Конкатенация строк — это объединение нескольких строк в одну.
Если хотя бы одна из строк при конкатенации через || будет равна NULL, то результат конкатенации будет NULL.
Функция concat игнорирует NULL значения.

SELECT 'Раз, ' || 'два, ' || NULL AS result

#	result
1	NULL

SELECT concat('Раз, ', 'два, ', NULL) AS result

#	result
1	Раз, два,

SELECT concat(last_name,’ ‘,first_name,’ ‘,middle_name, ‘;’) as full_name
from employee
ORDER BY full_name

#	full_name
1	Авдеев Павел Яковович;
2	Агафьев Мирон Иванович;
3	Антонов Сергей Кириллович;
4	Белозёров Зиновий Семёнович;
5	Бирюков Владислав Яковович;

Преобразование регистра букв (2/9)

В PostgreSQL есть три функции для изменения регистра символов строки:

lower — преобразование символов в нижний регистр;
upper — преобразование символов в верхний регистр;
initcap — преобразование первого символа каждого слова в верхний регистр, а остальных в нижний.

SELECT lower('Мама мыла РАМУ')

#	lower
1	мама мыла раму

SELECT upper('Мама мыла РАМУ')

#	upper
1	МАМА МЫЛА РАМУ

SELECT initcap('Мама мыла РАМУ')

#	initcap
1	Мама Мыла Раму

SELECT lower(concat(last_name,’ ‘,first_name)) as lower,
upper(concat(last_name,’ ‘,first_name)) as upper,
initcap(concat(last_name,’ ‘,first_name)) as initcap
FROM employee
ORDER BY last_name, first_name

#	lower	upper	initcap
1	авдеев павел	АВДЕЕВ ПАВЕЛ	Авдеев Павел
2	агафьев мирон	АГАФЬЕВ МИРОН	Агафьев Мирон
3	антонов сергей	АНТОНОВ СЕРГЕЙ	Антонов Сергей
4	белозёров зиновий	БЕЛОЗЁРОВ ЗИНОВИЙ	Белозёров Зиновий
5	бирюков владислав	БИРЮКОВ ВЛАДИСЛАВ	Бирюков Владислав

LENGTH — определение длины строки (3/9)

Функция length возвращает количество символов в строке:

SELECT length('Мама мыла раму')

#	length
1	14

SELECT last_name,
length(last_name)
FROM employee
ORDER BY length DESC, last_name

#	last_name	length
1	Кондратьева	11
2	Кондратьева	11
3	Емельянова	10
4	Калашников	10
5	Белозёров	9

Извлечение подстроки (4/9)

Разберем функции для получения части строки.

`left`

left(строка, количество_символов) — возвращает первые символы в строке.

SELECT left('мама мыла раму', 3)

left
мам

Если вторым параметром передать отрицательное число, то с конца строки будет откинуто это количество символов:

SELECT left('мама мыла раму', -3)

left
мама мыла р

`right`

right(строка, количество_символов) — возвращает переданное количество символов с конца строки.

SELECT right('мама мыла раму', 4)

right
раму

Если вторым параметром передать отрицательное число, то с начала строки будет откинуто это количество символов:

SELECT right('мама мыла раму', -5)

right
мыла раму

`substring`

substring(строка from с_какого_символа for сколько_символов) — получение определенного количества символов из строки.

SELECT substring('мама мыла раму' from 3 for 5)

substring
ма мы

Если запросить символов больше, чем есть в строке, то будут возвращены все символы до конца строки:

SELECT substring('мама мыла раму' from 3 for 50)

substring
ма мыла раму

С какого символа брать подстроку можно не передавать. В этом случае символы будут браться с начала строки (аналог left):

SELECT substring('мама мыла раму' for 3)

substring
мам

Количество символов можно не передавать. В таком случае будут возвращены все символы до конца строки:

SELECT substring('мама мыла раму' from 5)

substring
мыла раму

`substr`

substr(строка, с_какого_символа, сколько_символов) — аналог substring для тех, кому не нравится писать from и for.

SELECT substr('мама мыла раму', 3, 5)

substr
ма мы

SELECT substr('мама мыла раму', 5)

substr
мыла раму

SELECT substr('мама мыла раму', 5)

substr
мыла раму

`split_part`

split_part(строка, разделитель, какой_элемент_вернуть) — разбивает строку на подстроки по разделителю и возвращает подстроку с заданным номером.

SELECT split_part('мама мыла раму', ' ', 1)

split_part
мама

SELECT split_part('мама мыла раму', ' ', 2)

split_part
мыла

SELECT split_part('мама мыла раму', ' ', 3)

split_part
раму

Если указать индекс несуществующего элемента, то будет возвращена пустая строка ''.

SELECT split_part('мама мыла раму', ' ', 4)

split_part

POSITION — поиск подстроки (5/9)

Функция position возвращает номер символа, с которого начинается подстрока в другой строке. Нумерация символов начинается с 1.

Синтаксис

position(что_искать in в_какой_строке)

Функция чувствительна к регистру символов

SELECT position('мам' in 'мама мыла раму')

#	position
1	1

Из фамилии сотрудника получи подстроку с начала и до первой буквы 'а' включительно. Буква 'а' может быть как заглавной, так и строчной.

SELECT last_name, 
left(last_name,position('а' in lower(last_name))) as substring 
FROM employee 
ORDER BY last_name

#	last_name	substring
1	Авдеев	А
2	Агафьев	А
3	Антонов	А
4	Белозёров
5	Бирюков
6	Блинова	Блинова

Функции `lpad` и `rpad`

В SQL есть две функции, дополняющих строку до определенной длины.

`lpad(строка, до_какой_длины, какими_символами)`

Дополняет строку слева до длины переданными в третьем параметре символами.

SELECT lpad('123', 5, '*')

Дополняет строку слева до длины переданными в третьем параметре символами.

SELECT lpad('123', 5, '*')

#	lpad
1	**123

Если третий параметр не передан, то строка дополняется пробелами.

SELECT lpad('123', 5)

#	lpad
1	123

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

SELECT lpad('12345', 2)

#	lpad
1	12

Например, из 'Иванов Иван' должно получиться 'Иванов И***'

SELECT 
concat(last_name,' ',rpad(left(first_name,1),length(first_name),'*')) as mask
FROM employee
ORDER BY mask

#	mask
1	Авдеев П****
2	Агафьев М****
3	Антонов С*****
4	Белозёров З******
5	Бирюков В********

TRIM — удаление символов с начала и конца строки (7/9)

`ltrim` — удаляет символы с начала строки.

Синтаксис:

ltrim(откуда, какие_символы)

Порядок символов не важен:

SELECT ltrim('мама_мыла_раму', 'ам')
> _мыла_раму

Регистр важен:

SELECT ltrim('мама_мыла_раму', 'мА')
> ама_мыла_раму

Если не передать второй параметр, то удаляются пробелы:

SELECT '#' || ltrim('   мама_мыла_раму') AS result
> #мама_мыла_раму

`rtrim` — удаляет символы с конца строки.

Работает аналогично ltrim, только символы удаляются с конца строки.

SELECT rtrim('мама_мыла_раму', 'муа')
> мама_мыла_р

`btrim`, или просто `trim` — удаляет символы с начала и конца строки.

Работает аналогично ltrim и rtrim, только символы удаляются с начала и конца строки.

SELECT trim('мама_мыла_раму', 'муа')
> _мыла_р

`trim (из_какой_части какие_символы from откуда)`

У trim есть еще одна форма записи:

trim (из_какой_части какие_символы from откуда)

из_какой_части принимает одно из значений:

leading — с начала строки;
trailing — с конца строки;
both — с обеих сторон.

SELECT trim(both 'муа' from 'мама_мыла_раму')
> _мыла_р

SELECT trim(trailing 'муа' from 'мама_мыла_раму')
> мама_мыла_р

SELECT 
trim(concat(last_name,' ',first_name,' ',middle_name)) as full_name
FROM employee
ORDER BY full_name

#	full_name
1	Авдеев Павел Яковович
2	Агафьев Мирон Иванович
3	Антонов Сергей Кириллович
4	Белозёров Зиновий Семёнович

REPLACE — замена подстроки (8/9)

`replace(где, что_заменить, на_что_заменить)`

replace заменяет все вхождения одной подстроки на другую подстроку.

Регистр важен:

SELECT replace ('Иванов Иван Иванович', 'Иван', 'Петр')

#	replace
1	Петров Петр Петрович

В поле address таблицы store_address замени сокращения типа улицы на полное название:

ул. на улица;
пр. на проспект.

SELECT

replace(replace(address,'пр.', 'проспект'),'ул.','улица') as address_full
FROM store_address
ORDER BY address_full

#	address_full
1	проспект Ленина, 42
2	улица Арбат, 20
3	улица Казанская, 43

TRANSLATE — замена набора символов (9/9)

`translate(в_какой_строке, какие_символы, на_какие_символы)`

translate заменяет символы из второго параметра на символы с такой же позицией из третьего параметра.

SELECT translate('абвгд', 'бд', 'bd')

#	translate
1	аbвгd

Первый символ 2-го параметра заменяется на первый символ 3-го параметра. Второй на второй и т.д. В запросе выше

б -> b;
д -> d.

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

SELECT translate('абвгд', 'бдавг', 'DB')

#	translate
1	DB

SELECT
DISTINCT first_name,
translate(first_name,’АБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЫЭЮЯабвгдеёжзийклмнопрстуфхцчшщыэюяЬЪьъ’,
‘ABVGDEEJZIJKLMNOPRSTUFHCHSSYEYAabvgdeejzijklmnoprstufhchssyeya’) as first_name_translated
FROM employee
ORDER BY first_name

#	first_name	first_name_translated
1	Алексей	Aleksej
2	Анна	Anna
3	Арнольд	Arnold
4	Валерий	Valerij
5	Виктор	Viktor

Математические функции

Математические операции (2/13)

+ — / * – классические

% – остаток от деления

^ – возведение в степень
! – факториал

ABS — модуль числа

GREATEST(число1,число2…) — определение большего числа (NULL игнорируются)
LEAST(число1,число2…) — определение наименьшего числа (NULL игнорируются)

CEIL(число) – следующее целое число

FLOOR(число) – предыдущее целое число
round(число, до скольки знаков) – округление числа

trunc(число, до скольки знаков) – усечение числа

Если вторым параметром передать отрицательное число, то округление будет производиться до десятков, сотен, тысяч, десятков тысяч…

SELECT trunc(12345.6, -1) as "trunc -1",
       round(12345.6, -1) as "round -1",
       trunc(12345.6, -2) as "trunc -2",
       round(12345.6, -2) as "round -2",

#	trunc -1	round -1	trunc -2	round -2	trunc -3	round -3
1	12340	12350	12300	12300	12000	12000

Скидка от разницы цены и минимальной цены

SELECT
p.product_id,
p.store_id,
p.price,
(price-(SELECT min(pp.price)
FROM product_price pp
WHERE pp.product_id = p.product_id))*0.5 as discount
FROM product_price p
ORDER BY p.product_id, p.price

#	product_id	store_id	price	discount
1	1	300	10500.00	0.000
2	1	800	12000.00	750.000
3	1	301	12500.00	1000.000
4	2	500	26100.00	0.000
5	2	600	27500.00	700.000

При использовании АГРЕГАТНЫХ функций, ОБЯЗАТЕЛЬНА ГРУППИРОВКА

SELECT pp.store_id,
avg(pp.price) as average_price,
round(avg(pp.price), 2) AS average_price_rounded
FROM product_price pp
GROUP BY pp.store_id
ORDER BY average_price

На сколько цена на товар в магазине отличается от средней цены на этот товар по всем магазинам.
Округлим это число до копеек (2 знака после запятой) и возьмем от него модуль.

SELECT pp.product_id,
pp.store_id,
pp.price,
(SELECT round (avg (ppa.price), 2)
FROM product_price ppa
WHERE ppa.product_id = pp.product_id ) as price_avg,
abs ( round ( pp.price — (SELECT avg (ppa.price)
FROM product_price ppa
WHERE ppa.product_id = pp.product_id) , 2) ) AS price_difference
FROM product_price pp
ORDER BY pp.product_id, pp.price

Деление целых чисел (3/13)

В PostgreSQL числа могут быть разных типов данных:

Имя	Размер	Описание	Диапазон
smallint	2 байта	целое в небольшом диапазоне	-32768 .. +32767
integer	4 байта	типичный выбор для целых чисел	-2147483648 .. +2147483647
bigint	8 байт	целое в большом диапазоне	-9223372036854775808 .. 9223372036854775807
decimal	переменный	вещественное число с указанной точностью	до 131072 цифр до десятичной точки и до 16383 — после
numeric	переменный	вещественное число с указанной точностью	до 131072 цифр до десятичной точки и до 16383 — после
real	4 байта	вещественное число с переменной точностью	точность в пределах 6 десятичных цифр
double precision	8 байт	вещественное число с переменной точностью	точность в пределах 15 десятичных цифр
smallserial	2 байта	небольшое целое с автоувеличением	1 .. 32767
serial	4 байта	целое с автоувеличением	1 .. 2147483647
bigserial	8 байт	большое целое с автоувеличением	1 .. 9223372036854775807

При делении двух целочисленных чисел остаток от деления отбрасывается.

SELECT 1 / 2 AS res1,
       5 / 3 AS res2,
       24 / 5 AS res3,
       -24 / 5 AS res4

#	res1	res2	res3	res4
1	0	1	4	-4

В выражении есть константа

Если в выражении есть константа, то можно дописать .0 к константе.

SELECT 1.0 / 2 AS res1,
       5.0 / 3 AS res2,
       24.0 / 5 AS res3,
       -24.0 / 5 AS res4

#	res1	res2	res3	res4
1	0.5	1.67	4.8	-4.8

Деление на 2. Результат целое и десятичное число

SELECT
purchase_item_id,
count,
count/2 as whole,
count::numeric/2 as fractional
FROM purchase_item
ORDER BY count DESC

#	purchase_item_id	count	whole	fractional
1	2	30	15	15.0000000000000000
2	14	5	2	2.5000000000000000
3	11	3	1	1.5000000000000000
4	6	2	1	1.00000000000000000000
5	7	1	0	0.50000000000000000000
6	8	1	0	0.50000000000000000000

Функция `cast`

Функция cast делает ровно то же самое, что и ::

cast (значение AS тип данных)

Например:

SELECT cast('123' AS integer)
или
SELECT '123'::integer)

#	int4
1	123

SELECT t.timezone_id,
       t.time_offset,
       cast(right(t.time_offset, -4) AS integer) AS hours
  FROM timezone t
 WHERE cast(right(t.time_offset, -4) AS integer) <= 5
 ORDER BY hours

#	timezone_id	time_offset	hours
1	9	UTC+1	1
2	1	UTC+2	2
3	2	UTC+3	3
4	3	UTC+4	4
5	4	UTC+5	5

TO_CHAR — форматирование числа (13/13)

Для преобразования числа в строку с заданным форматом используют функцию TO_CHAR:

to_char (число, формат)

Например:

SELECT to_char (12345, '99999990.00') AS "99999990.00",
       to_char (12345, 'S99999990.00') AS "S99999990.00",
       to_char (-12345, 'S99999990.00') AS "S99999990.00",
       to_char (12345, 'S99G999G990D00') AS "S99G999G990D00"

#	99999990.00	S99999990.00	S99999990.00	S99G999G990D00
1	12345.00	+12345.00	-12345.00	+12,345.00

Формат числа задается кодами форматирования

Код	Описание
9	позиция цифры (может отсутствовать, если цифра незначащая)
0	позиция цифры (присутствует всегда, даже если цифра незначащая)
. (точка)	десятичная точка
, (запятая)	разделитель групп (тысяч)
PR	отрицательное значение в угловых скобках
S	знак, добавляемый к числу (с учётом локали)
L	символ денежной единицы (с учётом локали)
D	разделитель целой и дробной части числа (с учётом локали)
G	разделитель групп (с учётом локали)
MI	знак минус в заданной позиции (если число < 0)
PL	знак плюс в заданной позиции (если число > 0)
SG	знак плюс или минус в заданной позиции
RN	число римскими цифрами (в диапазоне от 1 до 3999)
TH или th	окончание порядкового числительного
V	сдвиг на заданное количество цифр (см. замечания)
EEEE	экспоненциальная запись числа

Из этого многообразия обычно используют только

Код	Описание
9	позиция цифры (может отсутствовать, если цифра незначащая)
0	позиция цифры (присутствует всегда, даже если цифра незначащая)
D	разделитель целой и дробной части числа (с учётом локали)
G	разделитель групп (с учётом локали)

Пройдемся по ним.

9 — позиция цифры. Может отсутствовать, если цифра незначащая.

Если на позиции 9-ки в числе есть значащая цифра, то она отображается, если нет, то не отображается.

На примерах будет понятнее.

SELECT to_char (12, '999')

#	to_char
1	12

Если дробную часть не указали в формате, то она не отображается:

SELECT to_char (12.345, '999')

#	to_char
1	12

Дробная часть в формате начинается после точки . или символа D:

SELECT to_char (12.345, '999.9')

#	to_char
1	12.3

Мы указали одну 9-ку после запятой, поэтому в отформатированном числе видем после запятой только десятки.

SELECT to_char (0.12345, '999.99')

#	to_char
1	.12

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

SELECT to_char (12.457, '999.99')

#	to_char
1	12.46

Можно было ожидать, что в результате получим 12.45. Но так как на позиции тысячных стоит 7, то после округления получаем 12.46

0 — позиция цифры. Отображается всегда.

Если в формате числа встретился 0, то цифра всегда отображается:

SELECT to_char (12, '00') AS "00", 
       to_char (12, '000000') AS "000000"

#	00	000000
1	12	000012

SELECT to_char (0.12345, '999.99') as "999.99",
       to_char (0.12345, '990.99') as "990.99"

#	999.99	990.99
1	.12	0.12

D — разделитель целой и дробной части

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

SELECT to_char (123.4567, '999D0') AS "999D0", 
       to_char (123.4567, '999D00') AS "999D00"

#	999D0	999D00
1	123.5	123.46

`G` — разделитель групп

На месте G в формате числа вставляется символ разделителя групп. Обычно его используют для отделения тысяч, миллионов, миллиардов…

SELECT to_char (1234567.89, '9G999G999D00')

#	to_char
1	1,234,567.89

Но ни что не мешает использовать его в произвольном месте:

SELECT to_char (12345, '999G9G9')

#	to_char
1	123,4,5

Если число не входит в формат

Если в формате числа указать мешьше цифр, чем значащих цифр в самом числе, то в результате получим строку из # по формату:

SELECT to_char (123, '9D99'),
       to_char (123, '90')

#	to_char	to_char
1	#.##	##

Математические функции

Математические операции (2/13)

+ — / * – классические

% – остаток от деления

^ – возведение в степень
! – факториал

ABS — модуль числа

CEIL(число) – следующее целое число

FLOOR(число) – предыдущее целое число
round(число, до скольки знаков) – округление числа

trunc(число, до скольки знаков) – усечение числа

SELECT trunc(12345.6, -1) as "trunc -1",
       round(12345.6, -1) as "round -1",
       trunc(12345.6, -2) as "trunc -2",
       round(12345.6, -2) as "round -2",

#	trunc -1	round -1	trunc -2	round -2	trunc -3	round -3
1	12340	12350	12300	12300	12000	12000

Скидка от разницы цены и минимальной цены

SELECT
p.product_id,
p.store_id,
p.price,
(price-(SELECT min(pp.price)
FROM product_price pp
WHERE pp.product_id = p.product_id))*0.5 as discount
FROM product_price p
ORDER BY p.product_id, p.price

#	product_id	store_id	price	discount
1	1	300	10500.00	0.000
2	1	800	12000.00	750.000
3	1	301	12500.00	1000.000
4	2	500	26100.00	0.000
5	2	600	27500.00	700.000

При использовании АГРЕГАТНЫХ функций, ОБЯЗАТЕЛЬНА ГРУППИРОВКА

SELECT pp.store_id,
avg(pp.price) as average_price,
round(avg(pp.price), 2) AS average_price_rounded
FROM product_price pp
GROUP BY pp.store_id
ORDER BY average_price

Деление целых чисел (3/13)

В PostgreSQL числа могут быть разных типов данных:

Имя	Размер	Описание	Диапазон
smallint	2 байта	целое в небольшом диапазоне	-32768 .. +32767
integer	4 байта	типичный выбор для целых чисел	-2147483648 .. +2147483647
bigint	8 байт	целое в большом диапазоне	-9223372036854775808 .. 9223372036854775807
decimal	переменный	вещественное число с указанной точностью	до 131072 цифр до десятичной точки и до 16383 — после
numeric	переменный	вещественное число с указанной точностью	до 131072 цифр до десятичной точки и до 16383 — после
real	4 байта	вещественное число с переменной точностью	точность в пределах 6 десятичных цифр
double precision	8 байт	вещественное число с переменной точностью	точность в пределах 15 десятичных цифр
smallserial	2 байта	небольшое целое с автоувеличением	1 .. 32767
serial	4 байта	целое с автоувеличением	1 .. 2147483647
bigserial	8 байт	большое целое с автоувеличением	1 .. 9223372036854775807

При делении двух целочисленных чисел остаток от деления отбрасывается.

SELECT 1 / 2 AS res1,
       5 / 3 AS res2,
       24 / 5 AS res3,
       -24 / 5 AS res4

#	res1	res2	res3	res4
1	0	1	4	-4

В выражении есть константа

Если в выражении есть константа, то можно дописать .0 к константе.

SELECT 1.0 / 2 AS res1,
       5.0 / 3 AS res2,
       24.0 / 5 AS res3,
       -24.0 / 5 AS res4

#	res1	res2	res3	res4
1	0.5	1.67	4.8	-4.8

Деление на 2. Результат целое и десятичное число

SELECT
purchase_item_id,
count,
count/2 as whole,
count::numeric/2 as fractional
FROM purchase_item
ORDER BY count DESC

#	purchase_item_id	count	whole	fractional
1	2	30	15	15.0000000000000000
2	14	5	2	2.5000000000000000
3	11	3	1	1.5000000000000000
4	6	2	1	1.00000000000000000000
5	7	1	0	0.50000000000000000000
6	8	1	0	0.50000000000000000000

Функция `cast`

Функция cast делает ровно то же самое, что и ::

cast (значение AS тип данных)

Например:

SELECT cast('123' AS integer)
или
SELECT '123'::integer)

#	int4
1	123

SELECT t.timezone_id,
       t.time_offset,
       cast(right(t.time_offset, -4) AS integer) AS hours
  FROM timezone t
 WHERE cast(right(t.time_offset, -4) AS integer) <= 5
 ORDER BY hours

#	timezone_id	time_offset	hours
1	9	UTC+1	1
2	1	UTC+2	2
3	2	UTC+3	3
4	3	UTC+4	4
5	4	UTC+5	5

TO_CHAR — форматирование числа (13/13)

Для преобразования числа в строку с заданным форматом используют функцию TO_CHAR:

to_char (число, формат)

Например:

SELECT to_char (12345, '99999990.00') AS "99999990.00",
       to_char (12345, 'S99999990.00') AS "S99999990.00",
       to_char (-12345, 'S99999990.00') AS "S99999990.00",
       to_char (12345, 'S99G999G990D00') AS "S99G999G990D00"

#	99999990.00	S99999990.00	S99999990.00	S99G999G990D00
1	12345.00	+12345.00	-12345.00	+12,345.00

Формат числа задается кодами форматирования

Код	Описание
9	позиция цифры (может отсутствовать, если цифра незначащая)
0	позиция цифры (присутствует всегда, даже если цифра незначащая)
. (точка)	десятичная точка
, (запятая)	разделитель групп (тысяч)
PR	отрицательное значение в угловых скобках
S	знак, добавляемый к числу (с учётом локали)
L	символ денежной единицы (с учётом локали)
D	разделитель целой и дробной части числа (с учётом локали)
G	разделитель групп (с учётом локали)
MI	знак минус в заданной позиции (если число < 0)
PL	знак плюс в заданной позиции (если число > 0)
SG	знак плюс или минус в заданной позиции
RN	число римскими цифрами (в диапазоне от 1 до 3999)
TH или th	окончание порядкового числительного
V	сдвиг на заданное количество цифр (см. замечания)
EEEE	экспоненциальная запись числа

Из этого многообразия обычно используют только

Код	Описание
9	позиция цифры (может отсутствовать, если цифра незначащая)
0	позиция цифры (присутствует всегда, даже если цифра незначащая)
D	разделитель целой и дробной части числа (с учётом локали)
G	разделитель групп (с учётом локали)

Пройдемся по ним.

9 — позиция цифры. Может отсутствовать, если цифра незначащая.

Если на позиции 9-ки в числе есть значащая цифра, то она отображается, если нет, то не отображается.

На примерах будет понятнее.

SELECT to_char (12, '999')

#	to_char
1	12

Если дробную часть не указали в формате, то она не отображается:

SELECT to_char (12.345, '999')

#	to_char
1	12

Дробная часть в формате начинается после точки . или символа D:

SELECT to_char (12.345, '999.9')

#	to_char
1	12.3

Мы указали одну 9-ку после запятой, поэтому в отформатированном числе видем после запятой только десятки.

SELECT to_char (0.12345, '999.99')

#	to_char
1	.12

SELECT to_char (12.457, '999.99')

#	to_char
1	12.46

0 — позиция цифры. Отображается всегда.

Если в формате числа встретился 0, то цифра всегда отображается:

SELECT to_char (12, '00') AS "00", 
       to_char (12, '000000') AS "000000"

#	00	000000
1	12	000012

SELECT to_char (0.12345, '999.99') as "999.99",
       to_char (0.12345, '990.99') as "990.99"

#	999.99	990.99
1	.12	0.12

D — разделитель целой и дробной части

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

SELECT to_char (123.4567, '999D0') AS "999D0", 
       to_char (123.4567, '999D00') AS "999D00"

#	999D0	999D00
1	123.5	123.46

`G` — разделитель групп

SELECT to_char (1234567.89, '9G999G999D00')

#	to_char
1	1,234,567.89

Но ни что не мешает использовать его в произвольном месте:

SELECT to_char (12345, '999G9G9')

#	to_char
1	123,4,5

Если число не входит в формат

SELECT to_char (123, '9D99'),
       to_char (123, '90')

#	to_char	to_char
1	#.##	##

SQL

Запросы

Создание таблицы

Время на сервере

Функции

Агрегатные Функции

Работа с Таблицами

NULL значения в GROUP BY (9/13)

NULL значения в GROUP BY (9/13)

HAVING (10/13)

Срезы

ROLLUP (11/13)

CUBE (12/13)

GROUPING SETS (13/13)

Операции над множествами

Объединение результатов (1/8)

Из какого запроса строка? (2/8)

Дубликаты строк (5/8)

Совпадение типов данных столбцов (6/8)

Сортировка (7/8)

Подзапросы

Подзапрос одиночной строки (1/9)

Коррелированный подзапрос (2/9)

Строковые функции

CONCAT — конкатенация строк (1/9)

Преобразование регистра букв (2/9)

LENGTH — определение длины строки (3/9)

Извлечение подстроки (4/9)

left

right

substring

substr

split_part

POSITION — поиск подстроки (5/9)

Функции lpad и rpad

lpad(строка, до_какой_длины, какими_символами)

TRIM — удаление символов с начала и конца строки (7/9)

ltrim — удаляет символы с начала строки.

rtrim — удаляет символы с конца строки.

btrim, или просто trim — удаляет символы с начала и конца строки.

trim (из_какой_части какие_символы from откуда)

REPLACE — замена подстроки (8/9)

replace(где, что_заменить, на_что_заменить)

TRANSLATE — замена набора символов (9/9)

translate(в_какой_строке, какие_символы, на_какие_символы)

Математические функции

Математические операции (2/13)

Деление целых чисел (3/13)

В выражении есть константа

Функция cast

TO_CHAR — форматирование числа (13/13)

9 — позиция цифры. Может отсутствовать, если цифра незначащая.

0 — позиция цифры. Отображается всегда.

D — разделитель целой и дробной части

G — разделитель групп

Если число не входит в формат

Математические функции

Математические операции (2/13)

Деление целых чисел (3/13)

В выражении есть константа

Функция cast

TO_CHAR — форматирование числа (13/13)

9 — позиция цифры. Может отсутствовать, если цифра незначащая.

0 — позиция цифры. Отображается всегда.

D — разделитель целой и дробной части

G — разделитель групп

Если число не входит в формат

`left`

`right`

`substring`

`substr`

`split_part`

Функции `lpad` и `rpad`

`lpad(строка, до_какой_длины, какими_символами)`

`ltrim` — удаляет символы с начала строки.

`rtrim` — удаляет символы с конца строки.

`btrim`, или просто `trim` — удаляет символы с начала и конца строки.

`trim (из_какой_части какие_символы from откуда)`

`replace(где, что_заменить, на_что_заменить)`

`translate(в_какой_строке, какие_символы, на_какие_символы)`

Функция `cast`

`G` — разделитель групп

Функция `cast`

`G` — разделитель групп