Запросы

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

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’

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

  1. NOT
  2. AND
  3. 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

ON

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

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

Выполнено

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

  1. store_name — название магазина;
  2. manager_full_name — имя и фамилия руководителя, разделенные пробелом;
  3. 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

HAVING (10/13)

Выполнено

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

  1. product_id — идентификатор товара;
  2. price_min — минимальная стоимость товара;
  3. 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 поддерживает наборы группирования, реализованные через ROLLUPCUBEGROUPING 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)

Разберем другой встроенный набор группирования — CUBECUBE работает аналогично 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)

По умолчанию UNIONINTERSECT и 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 — модуль числа

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 #.## ##