14 Базові запити

14.1 Відбір: `SELECT`

Давайте спочатку напишемо найпростіший запит і подивимося, як виглядає таблиця products.

Щоб вивести всі записи зі значеннями у всіх колонках, необхідно або перерахувати всі колонки в операторі SELECT або вказати після оператора спеціальний символ “*”:

SELECT column_1, column_2, ...
FROM table


SELECT *
FROM table

Примітка

Оператор FROM завжди вказується після оператора SELECT. У зворотному порядку їх записувати не можна – база даних поверне помилку.

Завдання 14.1
Виведіть усі записи з таблиці products.

Поля у результуючій таблиці: product_id, name, price

Рішення

%%sql
SELECT product_id,
       name,
       price
FROM   products

	product_id	name	price
0	1	sugar	150.0
1	2	green tea bags	50.0
2	3	still water	80.0
3	4	lollipops	46.0
4	5	coffee 3 in 1	15.0
...	...	...	...
82	83	waffles	55.0
83	84	tangerines	90.0
84	85	jam	200.0
85	86	cold coffee	70.0
86	87	veal	398.0

87 rows × 3 columns

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

14.2 Впорядкування: `ORDER BY`

Для сортування значень по будь-якій колонці SQL використовується оператор ORDER BY з ключовими словами ASC (за зростанням) або DESC (за спаданням).

При цьому за умовчанням сортування відбувається за зростанням, тобто ASC не обов’язково вказувати. Для сортування за спаданням після ORDER BY необхідно явно вказати DESC:

SELECT column_1, column_2
FROM table
ORDER BY column_1           -- сортування за зростанням


SELECT column_1, column_2
FROM table
ORDER BY column_1 ASC       -- сортування за зростанням


SELECT column_1, column_2
FROM table
ORDER BY column_1 DESC      -- сортування за спаданням

Завдання 14.2
Виведіть всі записи з таблиці products, відсортувавши їх за найменуванням товарів у алфавітному порядку, тобто за зростанням. Для сортування використовуйте оператор ORDER BY.

Поля у результуючій таблиці: product_id, name, price

Примітка

Сортування можна робити не тільки по полях зі значеннями, вираженими числами, але й по полях, значення яких представлені у вигляді тексту, як у нашому випадку.

Рішення

%%sql
SELECT product_id,
       name,
       price
FROM   products
ORDER BY name
LIMIT 5

	product_id	name	price
0	62	apple juice	120.0
1	71	apples	75.0
2	39	bagels	45.0
3	65	bananas	100.0
4	66	beef	370.0

Сортувати результат SQL-запиту можна відразу за декількома колонками, вказуючи їх після ORDER BY через кому разом із напрямком сортування (ASC або DESC):

SELECT column_1, column_2
FROM table
ORDER BY column_1 DESC, column_2    -- спочатку сортування по першій колонці (за спаданням),
                                    -- потім по другій (за зростанням)

Примітка

Важливо пам’ятати, що при роботі з великими таблицями потрібно по можливості обмежувати кількість виведених записів, щоб не створювати зайве навантаження на базу даних.

Оператори ORDER BY та LIMIT можна поєднувати в одному запиті, при цьому оператор LIMIT записується та виконується після оператора ORDER BY, обмежуючи кількість записів у вже відсортованому результаті:

SELECT column_1, column_2
FROM table
ORDER BY column_1 DESC, column_2
LIMIT 5

Завдання 14.3
Відсортуйте таблицю courier_actions спочатку по колонці courier_id за зростанням id кур’єра, потім по колонці action (знову за зростанням), а потім по колонці time, але вже за спаданням — від останньої дії до першої. Не забудьте включити колонку order_id.

Додайте в запит оператор LIMIT та виведіть лише перші 1000 рядків результуючої таблиці.

Поля у результуючій таблиці: courier_id, order_id, action, time.

Примітка

Як ви вже здогадалися, сортувати таблиці можна навіть по полях з датами та часом.

Рішення

%%sql
SELECT courier_id,
       order_id,
       action,
       time
FROM   courier_actions
ORDER BY courier_id, action, time DESC limit 5

	courier_id	order_id	action	time
0	1	23149	create_order	2022-09-02 02:36:00
1	1	22901	create_order	2022-09-02 00:58:00
2	1	4683	create_order	2022-08-27 20:56:00
3	1	1	create_order	2022-08-24 01:52:00
4	2	7114	create_order	2022-08-28 18:11:00

Зараз важливо запам’ятати порядок запису всіх відомих нам ключових слів:

SELECT
FROM
ORDER BY
LIMIT

Також важливо зрозуміти, що порядок їх виконання дещо відрізняється від того, в якій послідовності вони вказуються в SQL-запиті:

Спочатку виконується оператор FROM – відбувається вибір потрібної таблиці.
Потім SELECT - відбираються вказані стовпці.
Потім ORDER BY - проводиться сортування результуючої таблиці.
І наприкінці LIMIT — обмежується кількість записів, що виводяться.

Завдання 14.4
Використовуючи оператори SELECT, FROM, ORDER BY та LIMIT, визначте 5 найдорожчих товарів у таблиці products, які доставляє наш сервіс. Виведіть їх найменування та ціну.

Поля у результуючій таблиці: name, price.

Рішення

%%sql
SELECT name,
       price
FROM   products
ORDER BY price desc limit 5

	name	price
0	caviar	800.0
1	mutton	559.0
2	olive oil	450.0
3	pork	450.0
4	decaffeinated coffee	400.0

14.3 Зміна назви: `AS`

При складанні SQL-запитів колонкам у результуючій таблиці можна надавати будь-які інші імена (їх ще називають «аліасами»). Це можна робити за допомогою оператора AS:

SELECT name AS new_name
FROM table

Якщо раптом вам здасться, що на написання оператора AS йде занадто багато часу і сил, його можна опустити, вказавши нове ім’я колонки без нього. Наступний запис також спрацює:

SELECT name new_name
FROM table

Завдання 14.5
Повторіть запит із з попереднього прикладу (Завдання 14.3), але тепер колонки name і price перейменуйте відповідно на product_name і product_price.

Поля у результуючій таблиці: product_name, product_price

Рішення

%%sql

SELECT name AS product_name,
       price AS product_price
FROM   products
ORDER BY price desc limit 5

	product_name	product_price
0	caviar	800.0
1	mutton	559.0
2	olive oil	450.0
3	pork	450.0
4	decaffeinated coffee	400.0

14.4 Кількість символів: `LENGTH`

У SQL-запитах до колонок таблиць можна використовувати різні функції, наприклад, як в Excel.

У загальному вигляді синтаксис функцій виглядає приблизно так:

SELECT function(a, b, c, ...)
FROM table

Замість function вказується назва функції, а дужках — її аргументи. Як аргументи можуть виступати як колонки зі значеннями, так і окремі значення. Залежно від функції кількість аргументів може відрізнятися.

Якщо ви коли-небудь працювали в Excel, то вам, напевно, знайомі різні функції на кшталт SUM, MIN, MAX тощо, які роблять деякі обчислення по стовпцях. У SQL вони називаються агрегуючими функціями.

Зараз як приклад розглянемо функцію LENGTH. Вона виконує досить просте завдання — підраховує кількість символів у поданому їй на вхід значенні текстового типу (текстовий тип даних часто називають рядком — від англ. «string»). Іншими словами, функція LENGTH вимірює довжину деякого рядка у символах:

%%sql
SELECT LENGTH('I.love.Python') as py_love

	py_love
0	13

Функція LENGTH також може застосовуватись до всього стовпця. У такому випадку довжина символів буде порахована для кожного значення в стовпці. Відповідний запит виглядатиме так:

SELECT LENGTH(column) AS column_length
FROM table

Завдання 14.6
Використовуючи оператори SELECT, FROM, ORDER BY та LIMIT, а також функцію LENGTH, визначте товар із найдовшою назвою у таблиці products. Виведіть його найменування, довжину найменування у символах, а також ціну цього товару. Назвіть колонку з довжиною найменування в символах name_length.

Поля у результуючій таблиці: name, name_length, price.

Рішення

%%sql
SELECT name,
       length(name) as name_length,
       price
FROM   products
ORDER BY name_length desc limit 1

	name	name_length	price
0	fruit drink blueberry	21	170.0

Примітка

Зверніть увагу, що в цьому прикладі ви сортуєте результуючу таблицю по розрахунковій колонці name_length, якої спочатку не було таблиці products.

Таке сортування за новими колонками можливе, оскільки це узгоджується з порядком виконання операторів у запиті: спочатку виконується вибір колонок і всі перетворення над ними в операторі SELECT — і тільки потім проводиться сортування за вказаними в ORDER BY стовпчиками. Тобто на момент сортування всі розрахункові поля вже існують, а значить, їх можна вказувати в ORDER BY.

Також зверніть увагу, що ми двічі вказуємо колонку name в операторі SELECT: вперше просто звертаємося до неї за ім’ям, а вдруге - використовуємо її як аргумент функції LENGTH. У цьому випадку теж немає жодних протиріч - ми можемо вибирати колонку стільки разів, скільки необхідно, причому для цього навіть необов’язково надавати цим колонкам різні аліаси (база даних сама перейменує дублікат колонки).

У SQL, як й у багатьох інших мовах, функції можна застосовувати до інших функцій, тобто. послідовно:

SELECT function_one(function_two(a, b), c)

У наведеній вище конструкції a, b, c аргументи функцій. При цьому одним із аргументів функції function_one є результат виконання функції function_two.

Таким чином, спочатку буде виконана функція function_two, а потім її результат буде поданий на вхід функції function_one як один з аргументів.

Розглянемо такий випадок на прикладі функцій UPPER та LEFT. Функція UPPER наводить подане їй на вхід текстове значення до верхнього регістру:

SELECT 
    UPPER('I.love.Python') AS upper_all

Результат:
I.LOVE.PYTHON

Функція LEFT повертає перші n символів у рядку:

SELECT 
    LEFT('I.love.Python', 6) AS first_n

Результат:
I.love

Ми можемо застосувати ці функції послідовно і в результаті отримаємо перші три символів верхнього регістру:

SELECT 
    UPPER(LEFT('I.love.Python', 6))

Результат:
I.LOVE

Застереження

Зверніть увагу, що у блоці SELECT не можна виконати дві операції окремо, тобто. ми не можемо спочатку створити розрахункову колонку з першими шістьма символами, а потім відразу ж звернутися до цієї нової колонки, застосувавши до неї функцію UPPER.

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

SELECT 
    LEFT('I.love.Python', 3) AS first_n,
    UPPER(first_n) AS upper_first_n

Результат:
Error running query: column "new_name" does not exist

В межах одного запиту колонки створюються не по черзі, а разом, тому в межах одного запиту колонки upper_first_n не існує, поки блок SELECT не буде повністю виконаний. Дуже важливо враховувати цю особливість мови.

Однак на практиці до нових колонок доводиться звертатися досить часто — саме для цього є підзапити (запити до інших запитів). А поки що нам достатньо розуміти, що застосовувати функції до розрахункових колонок у тому ж запиті не можна.

14.5 Поділ та повернення: `SPLIT_PART`

Функція SPLIT_PART розбиває поданий їй на вхід рядок на кілька частин відповідно до зазначеного роздільника та повертає одну з частин.

Крім самого рядка (або колонки зі значеннями рядкового типу), функція приймає ще два аргументи — роздільник і порядковий номер частини, яку необхідно повернути. Подивіться наступний приклад:

SELECT SPLIT_PART('I.love.Python', '.', 3)

Результат:
Python

У прикладі вище рядок 'I.love.Python' було розбито на три частини ('I', 'love' та 'Python') по роздільнику “.”. Функція повернула 'Python', оскільки третім аргументом ми вказали частину із порядковим номером 3.

Примітка

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

Завдання 14.7
Застосуйте послідовно функції UPPER та SPLIT_PART до колонки name та перетворіть найменування товарів у таблиці products так, щоб від назв залишилося тільки перше слово, записане у верхньому регістрі. Колонку з новою назвою, що складається із першого слова, назвіть first_word.

У результаті виведіть вихідні найменування товарів, нові найменування з першого слова, а також ціну товарів. Результат відсортуйте за зростанням початкового найменування товару в колонці name.

Поля у результуючій таблиці: name, first_word, price

Рішення

%%sql
SELECT
    name,
    UPPER(SPLIT_PART(name, ' ', 1)) AS first_word,
    price
FROM
    products
ORDER BY
    name

	name	first_word	price
0	apple juice	APPLE	120.0
1	apples	APPLES	75.0
2	bagels	BAGELS	45.0
3	bananas	BANANAS	100.0
4	beef	BEEF	370.0
...	...	...	...
82	waffles	WAFFLES	55.0
83	watermelon	WATERMELON	120.0
84	white chocolate	WHITE	60.0
85	yogurt	YOGURT	45.0
86	сowberry juice	СOWBERRY	190.0

87 rows × 3 columns

14.6 Зміна типу: `CAST`

Іноді виникає необхідність змінити тип даних у якійсь колонці результуючої таблиці, не змінюючи при цьому властивості вихідної таблиці, наприклад, перетворити число на текст (тип даних VARCHAR). І тому існує функція CAST.

На вхід функції CAST необхідно подати ім’я колонки, вказавши через AS тип даних, до якого потрібно привести всі значення:

SELECT CAST(column AS VARCHAR)
FROM table

Також можна змінити тип даних за допомогою спеціального синтаксису з двома двокрапками («::»):

SELECT column::VARCHAR
FROM table

Наприклад, переведемо текст ‘100’ у число 100:

SELECT CAST('100' AS INTEGER)

Результат:
100

Зверніть увагу, що для успішної конвертації значення повинні бути конвертованими у вказаний тип. Наприклад, текст, який не виглядає як число, перевести до числового типу даних не вийде:

SELECT CAST('text' AS INTEGER)

Результат:
Error running query: invalid input syntax for type integer: "text"

Аналогічним чином можна, наприклад, перетворити текст на дату (якщо він виглядає як дата):

SELECT '2022-12-31'::DATE as date

Результат:
2022-12-31

Примітка

Докладніше з функцією CAST та перетворенням типів даних можна ознайомитись за цим посиланням.

Про самі типи даних можна почитати тут.

Завдання 14.8
Змініть тип колонки price з таблиці products на VARCHAR. Виведіть колонки з найменуванням товарів, ціною у вихідному форматі та ціною у форматі VARCHAR. Нову колонку з ціною у новому форматі назвіть price_char.

Результат відсортуйте за зростанням початкового найменування товару в колонці name. Кількість записів, що виводяться, не обмежуйте.

Поля в результуючій таблиці: name, price, price_char

Рішення

%%sql
SELECT 
    name,
    price,
    CAST(price as VARCHAR) as price_char
FROM
    products
ORDER BY
    name

	name	price	price_char
0	apple juice	120.0	120.0
1	apples	75.0	75.0
2	bagels	45.0	45.0
3	bananas	100.0	100.0
4	beef	370.0	370.0
...	...	...	...
82	waffles	55.0	55.0
83	watermelon	120.0	120.0
84	white chocolate	60.0	60.0
85	yogurt	45.0	45.0
86	сowberry juice	190.0	190.0

87 rows × 3 columns

14.7 Об’єднання: `CONCAT`

У цьому підрозділі ми ще трохи попрацюємо з текстовими даними та розглянемо функцію CONCAT, за допомогою якої можна з’єднувати в один рядок значення кількох стовпців.

Функція CONCAT приймає на вхід кілька аргументів і повертає результат їхньої послідовної складання один з одним. Хороша аналогія - складання речень з різних карток зі словами:

SELECT CONCAT('SQL', ' ', 'Python ', 2023)

Результат:
SQL Python 2023

При цьому аргументи не обов’язково мають бути виражені текстовими значеннями — головне, вони мають бути конвертованими в текст. У прикладі вище число 2023 можна конвертувати в текст ‘2023’, тому запит працює без помилок.

Примітка

Ознайомитись з іншими прикладами використання функції CONCAT можна за посиланням.

Завдання 14.9
Для перших 200 записів з таблиці orders виведіть інформацію у такому вигляді (зверніть увагу на пробіли):

Замовлення № [id_замовлення] створено [дата]

Отриману колонку назвіть order_info.

Приклад результату:

Замовлення № 65 створено 2022-09-01

Рішення

%%sql
SELECT 
    CONCAT(
        'Замовлення № ',
        order_id,
        ' створено ',
        creation_time::DATE
        ) as order_info
FROM   
    orders
LIMIT 200

	order_info
0	Замовлення № 1 створено 2022-08-24
1	Замовлення № 2 створено 2022-08-24
2	Замовлення № 3 створено 2022-08-24
3	Замовлення № 4 створено 2022-08-24
4	Замовлення № 5 створено 2022-08-24
...	...
195	Замовлення № 196 створено 2022-08-25
196	Замовлення № 197 створено 2022-08-25
197	Замовлення № 198 створено 2022-08-25
198	Замовлення № 199 створено 2022-08-25
199	Замовлення № 200 створено 2022-08-25

200 rows × 1 columns

14.8 Частина дати: `DATE_PART`

Як ви вже помітили, у наших таблицях значення деяких колонках представлені у форматі дати (DATE) і часу (TIMESTAMP). Давайте трохи попрацюємо з такими даними.

Насправді часто зустрічаються завдання, коли потрібна, наприклад, не вся дата, а якась її частина: рік, місяць, день, година тощо. Витягти цю частину вихідних даних дозволяє функція DATE_PART. Її синтаксис наступний:

SELECT DATE_PART(part, column)

На місці part необхідно в лапках вказати ту частину, яку потрібно витягти: 'year', 'month', 'day', 'hour' тощо. На місці column слід вказати потрібну колонку чи конкретну дату чи час. Наприклад:

SELECT DATE_PART('day', DATE '2022-01-12')

Результат:
12.00


SELECT DATE_PART('minute', TIMESTAMP '2022-01-12 20:31:05')

Результат:
31.00

Вище ми вказали конкретну дату. На її місці міг бути, наприклад, стовпчик з датами dates. Тоді запит виглядав би так:

SELECT DATE_PART('day', dates)

Примітка

Ознайомитись з іншими прикладами використання функції DATE_PART можна за посиланням.

Завдання 14.10
Виведіть id всіх кур’єрів та їхні роки народження з таблиці couriers.

Рік народження необхідно одержати з колонки birth_date. Нову колонку з роком назвіть birth_year. Результат відсортуйте спочатку за спаданням року народження кур’єра (тобто від наймолодших до найстарших), потім за зростанням id кур’єра.

Поля у результуючій таблиці: courier_id, birth_year

Рішення

%%sql
SELECT courier_id,
       date_part('year', birth_date) as birth_year
FROM   couriers
ORDER BY birth_year desc, courier_id

	courier_id	birth_year
0	1592	2007.0
1	2557	2007.0
2	1885	2006.0
3	946	2005.0
4	1741	2005.0
...	...	...
2818	2233	NaN
2819	2239	NaN
2820	2656	NaN
2821	2867	NaN
2822	2963	NaN

2823 rows × 2 columns

14.9 Перше ненульове значення: `COALESCE`

Ви могли помітити, що в минулому прикладі (Завдання 14.10) для окремих рядків функція DATE_PART не повернула рік народження кур’єра, а замість них утворилися порожні значення. Якщо не помітили, уважно подивіться на результат запиту.

Насправді це сталося тому, що в наших даних у колонці birth_date є перепустки — так звані значення NULL (у виводі результату це NA). Іншими словами, для окремих кур’єрів просто не вказано їхні дні народження. Є безліч варіантів, чому так могло статися, але ми зараз не маємо часу все це з’ясовувати — нам просто потрібно навчитися якось обробляти такі випадки.

Давайте зробимо так, щоб замість порожніх значень функція DATE_PART повертала якесь інше значення. У цьому нам допоможе функція COALESCE, яка повертає перше не NULL значення зі списку поданих на вхід аргументів.

Роботу COALESCE можна описати так: вона буквально читає список значень зліва направо і, як тільки бачить значення, яке не є NULL, відразу ж повертає його та припиняє читання списку. Подивіться уважно на такі приклади:

SELECT COALESCE(NULL, 'I am not NULL' , 'I.love.Python')

Результат:
I am not NULL


SELECT COALESCE(NULL, 25, 100, 150)

Результат:
25


SELECT COALESCE('NULL', 'I am not NULL', 'I.love.Python')

Результат:
NULL

Одним із аргументів функції COALESCE може бути результат виконання іншої функції:

SELECT COALESCE(NULL, LOWER('I am not NULL'), 'I.love.Python')

Результат:
i am not null

Щоб заповнити пропуски в колонці, достатньо застосувати функцію COALESCE до колонки з пропусками та вказати як другий аргумент те значення, яке ми хочемо бачити замість NULL:

SELECT COALESCE(column, 'filler value')
FROM table

Функція COALESCE застосовується до кожного значення колонки. Якщо це значення виявиться NULL, вона замінить його значення, вказане другим аргументом. Якщо значення колонці, навпаки, виявиться не NULL, то функція просто поверне це значення.

При цьому до колонки з перепустками можна заздалегідь застосовувати різні інші функції:

SELECT COALESCE(LEFT(column, 5), 'filler value')
FROM table

Примітка

З іншими прикладами використання функції COALESCE можна за посиланням.

Завдання 14.11
Як і в попередньому прикладі (Завдання 14.10), знову виведіть id десяти відсотків кур’єрів та їх роки народження, тільки тепер до вилученого року застосуйте функцію COALESCE. Вкажіть параметри функції так, щоб замість значення NULL в результат потрапляло текстове значення unknown. Назви полів залиште колишніми.

Поля у результуючій таблиці: courier_id, birth_year

Рішення

%%sql
SELECT
    courier_id,
    COALESCE(date_part('year', birth_date)::VARCHAR, 'unknown') birth_year
FROM
    couriers
USING SAMPLE 10 PERCENT (bernoulli, 123)

	courier_id	birth_year
0	17	1997
1	24	1986
2	40	1993
3	49	1996
4	52	1995
...	...	...
278	3103	1991
279	3124	1992
280	3128	1997
281	3162	1996
282	3166	1994

283 rows × 2 columns

14.10 Арифметичні оператори

Для роботи з числовими значеннями в DuckDB доступны різні арифметичні оператори. Нижче наведена таблиця з найбільш вживаними операторами:

Оператор	Опис	Приклад	Результат
+	Додавання	2 + 3	5
-	Віднімання	2 - 3	-1
*	Множення	2 * 3	6
/	Ділення	4 / 2	2
%	Залишок від ділення	5 % 4	1
^	Піднесення до степеня	2 ^ 3	8

Якби ми захотіли перевести 7600 гривен у долари за курсом 1 долар = 40 гривень, то операція виглядала б так:

SELECT 7500 / 40

Результат:
190

Якби нам потрібно було відняти з кожного значення в одній із колонок нашої таблиці якесь число (наприклад, 100), то запит виглядав би так:

SELECT column - 100
FROM table

Крім того, в арифметичних операціях можуть брати участь одразу кілька колонок. Наприклад, для кожного рядка таблиці можна обчислити середнє арифметичне двох чисел, які містяться у двох різних колонках:

SELECT (column_1 + column_2) / 2 AS average
FROM table

Примітка

Ознайомитись з усіма арифметичними операторами можна за посиланням.

Завдання 14.12
Давайте уявимо, що з якоїсь незрозумілої причини ми раптом вирішили відразу підвищити ціну всіх товарів у таблиці products на 5%.

Виведіть id та найменування всіх товарів, їх стару та нову ціну. Колонку зі старою ціною назвіть old_price, а колонку з новою – new_price.

Результат відсортуйте спочатку за спаданням нової ціни, потім за зростанням id товару.

Поля в результуючій таблиці: product_id, name, old_price, new_price.

Рішення

%%sql
SELECT product_id,
       name,
       price as old_price,
       price*1.05 as new_price
FROM   products
ORDER BY new_price desc, product_id

	product_id	name	old_price	new_price
0	13	caviar	800.0	840.00
1	37	mutton	559.0	586.95
2	15	olive oil	450.0	472.50
3	57	pork	450.0	472.50
4	43	decaffeinated coffee	400.0	420.00
...	...	...	...	...
82	6	crackers	25.0	26.25
83	5	coffee 3 in 1	15.0	15.75
84	73	cake	15.0	15.75
85	10	seeds	12.0	12.60
86	54	paper bag	1.0	1.05

87 rows × 4 columns

14.11 Математичні функції

Поряд з арифметичними операторами DuckDB також доступні різні математичні функції. Наприклад, для округлення чисел можна використовувати функцію ROUND:

SELECT ROUND(100.5454, 2)

Результат:
100.55


SELECT ROUND(100.551, 1)

Результат:
100.6


SELECT ROUND(100.5511)

Результат:
101.0

Першим аргументом вказується саме значення, яке хочемо округлити. Другим число знаків після точки, до якої хочемо округлити. Другий аргумент вказувати не обов’язково: якщо його не вказати, відбудеться округлення до цілого числа (проте тип даних при цьому не зміниться).

Примітка

Ознайомитись з усіма арифметичними операторами можна за посиланням.

Завдання 14.13
Знову, як і в минулому завданні (Завдання 14.12), підвищите ціну всіх товарів на 5%, тільки тепер до колонки з новою ціною застосуйте функцію ROUND. Виведіть id та найменування товарів, їх стару ціну, а також нову ціну із округленням. Нову ціну округліть до десятих, але тип даних не змінюйте.

Результат відсортуйте спочатку за спаданням нової ціни, потім за зростанням id товару.

Поля в результуючій таблиці: product_id, name, old_price, new_price.

Рішення

%%sql
SELECT product_id,
       name,
       price as old_price,
       round(price * 1.05, 1) as new_price
FROM   products
ORDER BY new_price desc, product_id

	product_id	name	old_price	new_price
0	13	caviar	800.0	840.0
1	37	mutton	559.0	587.0
2	15	olive oil	450.0	472.5
3	57	pork	450.0	472.5
4	43	decaffeinated coffee	400.0	420.0
...	...	...	...	...
82	6	crackers	25.0	26.3
83	5	coffee 3 in 1	15.0	15.8
84	73	cake	15.0	15.8
85	10	seeds	12.0	12.6
86	54	paper bag	1.0	1.1

87 rows × 4 columns

14.12 Умовна конструкція: `CASE` `WHEN`

А якби ми захотіли підвищити ціну не на всі товари, а, скажімо, тільки на дорогі?

Для цього нам потрібно було б створити деяку функцію, яка перевіряла б кожне значення в колонці price і порівнювала б його з якимось пороговим значенням. Якби ціна виявлялася вищою за цей поріг, то функція підвищувала б ціну, а якщо ні — залишала б без змін.

І таку функцію можна задати за допомогою умовної конструкції CASE. Вона має наступний синтаксис:

CASE  
WHEN logical_expression_1 THEN expression_1
WHEN logical_expression_2 THEN expression_2
...
ELSE expression_else
END AS case_example

Ця конструкція може бути громіздкою, але насправді в ній немає нічого складного. Логіка роботи оператора CASE досить проста:

На кожному етапі WHEN - THEN обчислюється деякий логічний вираз logical_expression, що стоїть після WHEN. Якщо воно виявляється істинним (TRUE), то в якості результату оператор повертає вираз expression, що стоїть після THEN, і закінчує свою роботу.
Якщо вираз виявляється помилковим (FALSE), то оператор продовжує роботу та перевіряє наступну умову.
Якщо жодна з умов не проходить перевірку на істинність, повертається вираз, вказаний після ELSE. При цьому ELSE вказувати не обов’язково - якщо його не вказати, то повернеться порожнє значення NULL (у разі, якщо всі перевірки після WHEN виявилися помилковими).
Насамкінець обов’язково вказується ключове слово END, яке говорить про закінчення конструкції CASE. Також після END новому розрахунковому полю за допомогою AS можна надати деяке ім’я, але це робити не обов’язково. Обов’язковими є лише ключові слова CASE, WHEN, THEN та END.

Наступна конструкція розіб’є весь список найменувань на три категорії: «м’ясо», «риба» та «інше». Відповідно, якщо у перших двох умовах ми не врахуємо якісь назви з колонки name (наприклад, «телятину»), то вони потраплять до категорії «інше».

SELECT name,
       CASE 
       WHEN name='свинина' OR name='баранина' OR name='курка' THEN "м'ясо"
       WHEN name='тріска' OR name='форель' OR name='окунь' THEN 'риба'
       ELSE 'інше'
       END AS сategory
FROM table

Тепер трохи про логічні вирази: SQL можуть включати оператори порівняння і логічні операції.

До операторів порівняння належать:

= («рівно»)
<> або != («не дорівнює»)
< («менше»)
> («більше»)
<= («менше або дорівнює»)
>= («більше чи дорівнює»)

Результатом роботи операторів порівняння можуть бути три стани:

TRUE («істина»)
FALSE (помилка)
NULL («невизначений стан» - коли одне з порівнюваних значень NULL)

З цими трьома станами можна проводити такі логічні операції:

AND («І»)
OR («АБО»)
NOT («НЕ»)

Результатом цих логічних операцій також можуть бути три вищевказані логічні стани (TRUE, FALSE або NULL):

`a`	`b`	`a AND b`	`a OR b`
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	NULL	NULL	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	NULL	FALSE	NULL
NULL	NULL	NULL	NULL

`a`	`NOT a`
TRUE	FALSE
FALSE	TRUE
NULL	NULL

Таким чином, оператори порівняння та логічні операції дозволяють писати комплексні логічні вирази.

Запам’ятати всі ці таблиці та комбінації не обов’язково. Головне, завжди тримайте цю пам’ятку під рукою.

Припустимо, відомо, що \(a=10\) і \(b=5\). Тоді наступний логічний вираз поверне TRUE:

a >= 10 OR b > 10

Також важливо розуміти, що є пріоритети виконання операцій:

множення та ділення (* та /)
додавання та віднімання (+ та -)
оператори порівняння (=, !=, >, <, >=, <=)
NOT
AND
OR

На порядок виконання операторів можна також впливати за допомогою дужок. Вирази, поміщені в дужки, матимуть найвищий пріоритет — як у математиці.

Завдання 14.14
Підвищіть ціну на 5% тільки на товари, вартість яких перевищує 100 одиниць. Ціну решти товарів залиште без змін. Також не підвищуйте ціну на ікру (caviar), яка й так коштує 800 одиниць. Виведіть id та найменування всіх товарів, їх стару та нову ціну. Ціну округляти не потрібно.

Результат відсортуйте спочатку за спаданням нової ціни, потім за зростанням id товару.

Поля в результуючій таблиці: product_id, name, old_price, new_price

Рішення

%%sql
SELECT product_id,
       name,
       price as old_price,
       case when price <= 100 or
                 name = 'caviar' then price
            else price * 1.05 end as new_price
FROM   products
ORDER BY new_price desc, product_id

	product_id	name	old_price	new_price
0	13	caviar	800.0	800.00
1	37	mutton	559.0	586.95
2	15	olive oil	450.0	472.50
3	57	pork	450.0	472.50
4	43	decaffeinated coffee	400.0	420.00
...	...	...	...	...
82	6	crackers	25.0	25.00
83	5	coffee 3 in 1	15.0	15.00
84	73	cake	15.0	15.00
85	10	seeds	12.0	12.00
86	54	paper bag	1.0	1.00

87 rows × 4 columns

Уявіть, що до вас звернувся менеджер із сусіднього відділу із проханням порахувати ПДВ кожного товару. Жодних додаткових даних він вам не надав, тому ви вирішили виконати завдання на власний розсуд, вважаючи, що ПДВ єдиний для всіх товарів і становить 20%.

Завдання 14.15
Обчисліть ПДВ кожного товару у таблиці products та розрахуйте ціну без урахування ПДВ. Виведіть всю інформацію про товари, включаючи суму податку та ціну без його врахування. Колонки із сумою податку та ціною без ПДВ назвіть відповідно tax та price_before_tax. Округліть значення у цих колонках до двох знаків після коми.

Результат відсортуйте спочатку за спаданням ціни товару без урахування ПДВ, потім за зростанням id товару.

Поля в результуючій таблиці: product_id, name, price, tax, price_before_tax.

Рішення

%%sql
SELECT product_id,
       name,
       price,
       round((price * 0.2) / 1.2, 2) as tax,
       round(price / 1.2, 2) as price_before_tax
FROM   products
ORDER BY price_before_tax desc, product_id

	product_id	name	price	tax	price_before_tax
0	13	caviar	800.0	133.33	666.67
1	37	mutton	559.0	93.17	465.83
2	15	olive oil	450.0	75.00	375.00
3	57	pork	450.0	75.00	375.00
4	43	decaffeinated coffee	400.0	66.67	333.33
...	...	...	...	...	...
82	6	crackers	25.0	4.17	20.83
83	5	coffee 3 in 1	15.0	2.50	12.50
84	73	cake	15.0	2.50	12.50
85	10	seeds	12.0	2.00	10.00
86	54	paper bag	1.0	0.17	0.83

87 rows × 5 columns

14.13 Типові помилки при написанні SQL-запитів.

Неправильний порядок або помилки у ключових словах. Правильний порядок операторів у запиті виглядає так:

SELECT      -- перерахування полів результуючої таблиці
FROM        -- вказівка джерела даних
WHERE       -- фільтрація даних
GROUP BY    -- угруповання даних
HAVING      -- фільтрація даних після угруповання
ORDER BY    -- сортування результуючої таблиці
LIMIT       -- обмеження кількості записів, що виводяться

Неправильно названі функції та оператори, що використовуються в запиті (наприклад, DATEPART, а не DATE_PART).
Неправильно вказано імена стовпців.
Неправильно виконано сортування записів.
Неправильно проведено розрахунки.
Пропущена кома при перерахуванні стовпців у SELECT
Зайва кома після імені останнього стовпця в SELECT
Не закриті дужки (перевірте, що кількість дужок дорівнює кількості закривають).
Допущено помилку в підзапиті (перед виконанням всього запиту перевірте, чи працюють окремі підзапити).
Запущено відразу кілька запитів, не розділених крапкою з комою.

14.1 Відбір: SELECT

14.2 Впорядкування: ORDER BY

14.3 Зміна назви: AS

14.4 Кількість символів: LENGTH

14.5 Поділ та повернення: SPLIT_PART

14.6 Зміна типу: CAST

14.7 Об’єднання: CONCAT

14.8 Частина дати: DATE_PART

14.9 Перше ненульове значення: COALESCE

14.10 Арифметичні оператори

14.11 Математичні функції

14.12 Умовна конструкція: CASE WHEN

14.13 Типові помилки при написанні SQL-запитів.

14.1 Відбір: `SELECT`

14.2 Впорядкування: `ORDER BY`

14.3 Зміна назви: `AS`

14.4 Кількість символів: `LENGTH`

14.5 Поділ та повернення: `SPLIT_PART`

14.6 Зміна типу: `CAST`

14.7 Об’єднання: `CONCAT`

14.8 Частина дати: `DATE_PART`

14.9 Перше ненульове значення: `COALESCE`

14.12 Умовна конструкція: `CASE` `WHEN`