Запросы

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

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  с соединением таблиц

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)

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

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

GROUP BY

JOIN

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

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

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

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

Пример

HAVING

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

ROLLUP (11/13)

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

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

CUBE (12/13)

GROUP BY CUBE (выражение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, 4)
 GROUP BY CUBE (sa.city_id, p.category_id)
 ORDER BY sa.city_id NULLS LAST,
          p.category_id NULLS LAST

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

GROUPING SETS (13/13)

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

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

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

Объединение результатов (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 ‘%но%’

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

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

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

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 ‘%но%’

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

SELECT value
  FROM table1
 UNION ALL
SELECT value
  FROM table2

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 ‘%но%’

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

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

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

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

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

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

Не верно

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

Верно

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

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

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

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)

Коррелированный подзапрос (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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

substring

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

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

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

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

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

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

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

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

substr

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

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

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

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

split_part

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

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

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

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

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

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

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

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

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

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

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

Функции lpad и rpad

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

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

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

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

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

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

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

SELECT lpad('123', 5)

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

SELECT lpad('12345', 2)

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

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

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

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

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

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

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

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

SELECT 

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

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

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

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

• б -> b;
• д -> d.

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

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

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

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

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

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

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

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

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

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

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

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",

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

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

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

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)

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

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

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

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

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

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

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

Функция cast

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

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

Например:

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

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

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

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"

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

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

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

SELECT to_char (12, '999')

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

SELECT to_char (12.345, '999')

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

SELECT to_char (12.345, '999.9')

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

SELECT to_char (0.12345, '999.99')

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

SELECT to_char (12.457, '999.99')

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

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

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

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

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

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

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

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

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

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

SELECT to_char (1234567.89, '9G999G999D00')

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

SELECT to_char (12345, '999G9G9')

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

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

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

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

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

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

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

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

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

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

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

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",

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

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

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

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)

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

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

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

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

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

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

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

Функция cast

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

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

Например:

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

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

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

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"

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

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

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

SELECT to_char (12, '999')

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

SELECT to_char (12.345, '999')

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

SELECT to_char (12.345, '999.9')

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

SELECT to_char (0.12345, '999.99')

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

SELECT to_char (12.457, '999.99')

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

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

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

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

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

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

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

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

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

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

SELECT to_char (1234567.89, '9G999G999D00')

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

SELECT to_char (12345, '999G9G9')

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

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

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