Фільтрація даних дозволяє включати в результуючий запит не всі рядки вихідної таблиці, а лише ті, що відповідають заздалегідь заданій умові.
WHEREВ SQL для фільтрації даних використовується оператор WHERE. Після оператора WHERE вказується логічний вираз, результат якого визначає, чи буде рядок включений до результуючої таблиці. Якщо умова виявляється істинною (TRUE), то рядок включається до результату, якщо хибним (FALSE) — рядок виключається.
Таким чином, кожен рядок у таблиці проходить перевірку на відповідність певній умові, і в результаті цих перевірок формується таблиця, над якою проводяться операції, зазначені в блоці SELECT.
Оператор WHERE та логічний вираз вказуються після блоку FROM:
SELECT column_1, column_2
FROM table
WHERE column_2 >= 0Наприклад, у результаті зазначеного вище запиту у вибірці попадуть лише записи з невід’ємними значеннями в колонці column_2.
У свою чергу оператори ORDER BY і LIMIT записуються вже після оператора WHERE. Якщо додати їх у приклад вище, запит виглядатиме так:
SELECT column_1, column_2
FROM table
WHERE column_2 >= 0
ORDER BY column_1
LIMIT 100У результаті ми спочатку відфільтруємо необхідні нам рядки, потім виберемо стовпчики, виділені в SELECT, а потім сортуємо результуючу таблицю, обмеживши число виведених записів.
Таким чином порядок запису відомих нам на поточний момент ключових слів виглядає так:
SELECTFROMWHEREORDER BYLIMITЗнову звернемо увагу, що порядок виконання відрізняється від того, в якій послідовності вони вказуються в запиті:
FROM – відбувається вибір потрібної таблиці.WHERE - відфільтровуються рядки, що відповідають умові.SELECT - відбираються зазначені стовпці та застосовуються функції.ORDER BY - проводиться сортування результуючої таблиці.LIMIT — обмежується кількість записів, що виводяться.Іншими словами, в результаті виконання запиту спочатку відбувається підготовка таблиці до роботи, а потім над нею виконуються різні операції.
Докладніше про оператор WHERE можна за посиланням.
Завдання 15.1
Напишіть SQL-запит до таблиці products та виведіть всю інформацію про товари, ціна яких не перевищує 100 одиниць. Результат відсортуйте за зростанням id товару.
Поля у результуючій таблиці: product_id, name, price
%%sql
SELECT
product_id,
name,
price
FROM products
WHERE price <= 100
ORDER BY product_id
LIMIT 10;| product_id | name | price | |
|---|---|---|---|
| 0 | 2 | green tea bags | 50.0 |
| 1 | 3 | still water | 80.0 |
| 2 | 4 | lollipops | 46.0 |
| 3 | 5 | coffee 3 in 1 | 15.0 |
| 4 | 6 | crackers | 25.0 |
| 5 | 8 | drying | 30.0 |
| 6 | 9 | black leaf tea | 84.0 |
| 7 | 10 | seeds | 12.0 |
| 8 | 14 | mayonnaise | 60.0 |
| 9 | 18 | ketchup | 58.0 |
Фільтрувати дані в таблицях можна не тільки по полях з числовими значеннями, але і по полях зі значеннями, представленими у вигляді тексту:
SELECT column_1, column_2
FROM table
WHERE column_2 = 'text'У прикладі вище в результуючу таблицю потраплять лише рядки, значення в яких повністю збігаються із зазначеним у WHERE рядком 'text'.
При порівнянні рядків також допускається використовувати нерівності:
SELECT column_1, column_2
FROM table
WHERE column_2 > 'text'Втім, така операція використовується рідше, оскільки не цілком очевидно, що означає «один рядок більше за інший».
Насправді порядок сортування даних рядкового типу зазвичай визначається заздалегідь встановленими правилами сортування, у яких значення мають довжина рядка в символах, порядок букв відповідно до алфавіту, наявність особливих символів, регістр тощо. Ми не будемо докладно зупинятись на цій темі — за бажання про це можна додатково прочитати у документації.
Докладніше про правила сортування даних рядкового типу можна за посиланням.
Завдання 15.2
Виберіть користувачів жіночої статі з таблиці users. Виведіть лише id цих користувачів. Результат відсортуйте за зростанням id.
Додайте в запит оператор LIMIT і виведіть лише 10 перших ID з відсортованого списку.
Поле у результуючій таблиці: user_id
%%sql
SELECT
user_id
FROM
users
WHERE
sex = 'female'
ORDER BY
user_id
LIMIT 10| user_id | |
|---|---|
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 7 |
| 6 | 8 |
| 7 | 11 |
| 8 | 12 |
| 9 | 13 |
Як значення для фільтрації можна також використовувати дати та час:
SELECT column_1
FROM table
WHERE column_2 <= '2022-12-31'
SELECT column_1
FROM table
WHERE column_2 <= '2022-12-31 00:00:00'Однак при цьому важливо заздалегідь переконатися, що в колонці, за якою фільтруватимуться дані, знаходяться саме дати або мітки часу, а не дані рядкового типу, зовні схожі на дати. Якщо в колонці виявляться рядки, то як такої помилки не відбудеться, проте результат сильно відрізнятиметься від очікуваного.
Також в операціях порівняння дат важливо враховувати, що дата завжди інтерпретується опівночі (початок дня), тобто '2022-12-31' насправді означає '2022-12-31 00:00:00'.
До речі, ніщо не забороняє нам комбінувати в блоці WHERE різні умови з різними типами даних і створювати складніші логічні вирази:
SELECT column_1, column_2, column_3
FROM table
WHERE column_1 >= 0
AND column_2 = 'some text'
AND column_3 = '2022-12-31'Давайте якраз вирішимо завдання одразу з кількома умовами!
Завдання 15.3
Виберіть з таблиці user_actions усі дії користувачів зі створення замовлень, які були зроблені ними після опівночі 6 вересня 2022 року. Виведіть колонки з id користувачів, id створених замовлень та часом їх створення.
Результат має бути відсортований за зростанням id замовлення.
Поля в результуючій таблиці: user_id, order_id, time
Пояснення: зверніть увагу, що в таблиці user_actions у кожного користувача можуть бути записи не тільки з часом створення замовлення, а й часом його скасування. Нам необхідно отримати лише записи із створенням замовлень.
%%sql
SELECT user_id,
order_id,
time
FROM user_actions
WHERE action = 'create_order' and time > '2022-09-06'
ORDER BY order_id| user_id | order_id | time | |
|---|---|---|---|
| 0 | 16721 | 45201 | 2022-09-06 00:01:00 |
| 1 | 17299 | 45202 | 2022-09-06 00:01:00 |
| 2 | 7957 | 45203 | 2022-09-06 00:01:00 |
| 3 | 17300 | 45204 | 2022-09-06 00:02:00 |
| 4 | 14723 | 45205 | 2022-09-06 00:03:00 |
| ... | ... | ... | ... |
| 14390 | 21402 | 59591 | 2022-09-08 23:58:00 |
| 14391 | 20859 | 59592 | 2022-09-08 23:58:00 |
| 14392 | 15131 | 59593 | 2022-09-08 23:59:00 |
| 14393 | 10881 | 59594 | 2022-09-08 23:59:00 |
| 14394 | 19774 | 59595 | 2022-09-08 23:59:00 |
14395 rows × 3 columns
В операторі WHERE можна використовувати не тільки колонки, які вже є в таблиці, але й розрахункові колонки. При цьому вказувати їх у SELECT знову ж таки не обов’язково:
SELECT column_1, column_2, (column_1 + column_2) * 0.5 AS average
FROM table
WHERE (column_1 + column_2) * 0.5 = 10
SELECT column_1, column_2
FROM table
WHERE (column_1 + column_2) * 0.5 = 10 У прикладі вище ми відфільтруємо тільки ті записи, для яких середнє арифметичне двох чисел, що містяться в колонках column_1 і column_2, дорівнюватиме 10.
Також важливо враховувати, що використовувати в блоці WHERE аліаси, які присвоєно новим розрахунковим колонкам, не можна. Зверніть увагу на приклади наступних запитів, які вважають частку значень колонки column_1 у відповідних їм значеннях колонки column_2. Перший запит спрацює, а другий поверне помилку:
-- Цей запит спрацює:
SELECT column_1, column_2,
column_1 / column_2 AS share
FROM table
WHERE column_1 / column_2 > 50
-- Цей запит поверне помилку:
SELECT column_1, column_2,
column_1 / column_2 AS share
FROM table
WHERE share > 50Це пов’язано всі з тією ж послідовністю виконання операторів у запиті: на момент виконання оператора WHERE аліас колонці ще не присвоєно, і тому база даних поки не знає про існування колонки з новим ім’ям.
Можна розглянути ще більш тривіальний приклад: навіть якщо ми просто зробимо дублікат колонки з іншим ім’ям і спробуємо використати це ім’я в WHERE, все одно отримаємо помилку:
-- Цей запит поверне помилку:
SELECT column, column AS same_column
FROM table
WHERE same_column > 0При вказівці фільтрів у блоці WHERE важливо враховувати розглянуті особливості. Можете керуватися наступним правилом: якщо збираєтеся фільтрувати дані по розрахунковій колонці, то дублюйте розрахунки WHERE і не використовуйте аліаси, присвоєні новим колонкам.
Завдання 15.4
Призначте знижку 20% на всі товари з таблиці products та відберіть ті, ціна на які з урахуванням знижки перевищує 100. Виведіть id товарів, їх найменування, колишню ціну та нову ціну з урахуванням знижки. Колонку зі старою ціною назвіть old_price, новою — new_price.
Результат має бути відсортований за зростанням id товару.
Поля в результуючій таблиці: product_id, name, old_price, new_price
Пояснення: будьте уважні, коли вказуватимете фільтр після оператора WHERE.
%%sql
SELECT product_id,
name,
price as old_price,
price * 0.8 as new_price
FROM products
WHERE price * 0.8 > 100
ORDER BY product_id
LIMIT 10 -- для скорочення виводу| product_id | name | old_price | new_price | |
|---|---|---|---|---|
| 0 | 1 | sugar | 150.0 | 120.0 |
| 1 | 7 | grain coffee | 351.0 | 280.8 |
| 2 | 11 | linseed oil | 200.0 | 160.0 |
| 3 | 12 | grape | 278.0 | 222.4 |
| 4 | 13 | caviar | 800.0 | 640.0 |
| 5 | 15 | olive oil | 450.0 | 360.0 |
| 6 | 17 | сowberry juice | 190.0 | 152.0 |
| 7 | 19 | salted fish | 180.0 | 144.0 |
| 8 | 21 | ground coffee | 283.0 | 226.4 |
| 9 | 25 | instant coffee | 150.0 | 120.0 |
Після оператора WHERE до колонок можна також застосовувати різні функції:
SELECT column_1, column_2
FROM table
WHERE LOWER(column_1) = 'i.love.python'У прикладі вище кожне значення колонки column_1 спочатку буде приведено до нижнього регістру, а потім зіставлено зі значенням ‘i.love.python’. При цьому результат буде включено вихідні значення в колонці column_1. Таким чином, функція послужить тільки для фільтрації записів, але самі значення в колонках ніяк не вплине.
Зверніть увагу, що використовувати аліаси, присвоєні колонкам в операторі SELECT, так само не можна. Наступний запит поверне помилку:
-- Цей запит поверне помилку:
SELECT column_1 AS new_column_1, column_2
FROM table
WHERE LOWER(new_column_1) = 'i.love.python'Завдання 15.5
Відберіть із таблиці products всі товари, назви яких або починаються зі слова «tea», або складаються з п'яти символів. Виведіть дві колонки: id товарів та їх найменування.
Результат має бути відсортований за зростанням id товару.
Поля у результуючій таблиці: product_id, name
Пояснення: для вирішення завдання вам знадобляться функції SPLIT_PART та LENGTH, які ми розглядали раніше.
Для вирішення завдання в операторі WHERE до колонки з назвами товарів необхідно застосувати функцію SPLIT_PART, розділивши по пробілу на окремі слова і відібравши тільки ті, в яких перше слово - «tea». Також через OR необхідно додати другу умову з функцією LENGTH для перевірки, що найменування складається з 5 символів.
%%sql
SELECT product_id,
name
FROM products
WHERE split_part(name, ' ', 1) = 'tea'
or length(name) = 5
ORDER BY product_id| product_id | name | |
|---|---|---|
| 0 | 1 | sugar |
| 1 | 10 | seeds |
| 2 | 12 | grape |
| 3 | 26 | chips |
| 4 | 28 | cream |
| 5 | 34 | pasta |
| 6 | 36 | tea mushroom |
| 7 | 40 | bread |
| 8 | 52 | kvass |
| 9 | 53 | flour |
| 10 | 61 | honey |
| 11 | 72 | pears |
LIKEУявіть, що із загального списку найменувань товарів нам раптом знадобилося відібрати певні позиції, які містять певні слова і навіть символи. Але при цьому ми точно не знаємо, в якій частині назви їх можна зустріти.
Наприклад, нам потрібні всі “tea”, але вони необов’язково повинні починатися зі слова “tea”.
Але як це зробити? Невже нам доведеться переглядати всю таблицю, вручну складати список товарів і писати довгий логічний вираз, що враховує всі можливі сценарії?
Зрозуміло, що ні. Для фільтрації по колонках з текстовими значеннями в SQL передбачений оператор LIKE.
Оператор LIKE не просто порівнює рядки на повний збіг (або розбіжність), а перевіряє їх на відповідність заданому шаблону: якщо рядок йому відповідає, то повертається TRUE, інакше — FALSE. Як можна здогадатися, конструкція NOT LIKE працює з точністю навпаки.
Шаблони можуть містити як звичайні символи, так і символи-шаблони: знак відсотка (%) та підкреслення (_). Підкреслення підміняє будь-який одиночний символ, а знак відсотка - будь-яку (у тому числі порожню) послідовність символів:
SELECT 'love.python' LIKE 'love%'
Результат:
true
SELECT 'love.python' LIKE 'love_'
Результат:
false
SELECT 'love.python' LIKE '%love%'
Результат:
true
SELECT 'love.python' LIKE '_love%'
Результат:
false
SELECT 'love.python' LIKE '%.%'
Результат:
true
SELECT 'love.python' LIKE '_._'
Результат:
false
SELECT 'love.python' LIKE 'Love%'
Результат:
falseЗверніть увагу на останній приклад: оператор LIKE чутливий до регістру.
Якщо шаблон не містить знаків відсотка та підкреслень, тоді шаблон є рядком і LIKE працює як оператор порівняння, перевіряючи рядки на точний збіг.
SELECT 'love.python' LIKE 'love.python'
Результат:
true
SELECT 'love.python' LIKE 'love'
Результат:
falseДокладніше про оператор LIKE та шаблони можна почитати тут.
Завдання 15.6
Відберіть із таблиці products всі товари, що містять у своїй назві послідовність символів «tea» (без лапок). Виведіть дві колонки: id продукту та його назву.
Результат має бути відсортований за зростанням id товару.
Поля у результуючій таблиці: product_id, name
%%sql
SELECT product_id,
name
FROM products
WHERE name LIKE '%tea%'
ORDER BY product_id| product_id | name | |
|---|---|---|
| 0 | 2 | green tea bags |
| 1 | 9 | black leaf tea |
| 2 | 27 | herbal leaf tea |
| 3 | 36 | tea mushroom |
| 4 | 45 | leaf green tea |
| 5 | 48 | ivan-tea in bags |
| 6 | 50 | cold tea |
| 7 | 76 | black tea bags |
| 8 | 79 | herbal tea bags |
Тепер зробимо наш фільтр трохи цікавішим.
Завдання 15.7
Виберіть із таблиці products id та найменування лише тих товарів, назви яких починаються на букву “c” і містять лише одне слово.
Результат має бути відсортований за зростанням id товару.
Поля у результуючій таблиці: product_id, name
Якщо з буквою «c» все зрозуміло, то з фільтром на одне слово ситуація трохи складніша: подумайте, яким символом принципово відрізняються рядки з одним і двома словами. Складіть шаблон для пошуку рядка, що складається з двох слів і виключіть такі рядки з результату.
Також будьте уважні мовою введення: латинська літера “c” та кирилична “с” виглядають однаково, але для бази даних це різні символи.
%%sql
SELECT product_id,
name
FROM products
WHERE name like 'c%'
and name not like '% %'
ORDER BY product_id| product_id | name | |
|---|---|---|
| 0 | 6 | crackers |
| 1 | 13 | caviar |
| 2 | 26 | chips |
| 3 | 28 | cream |
| 4 | 46 | cookie |
| 5 | 73 | cake |
| 6 | 77 | chicken |
| 7 | 78 | cupcakes |
Уявіть, що напередодні Нового року до вас звернувся менеджер, якому на думку спала ідея зробити знижку на найдорожчий чай. Він попросив вас надати йому вивантаження з бази даних з усіма чаями та новими цінами на них з урахуванням знижки.
Що він далі робитиме з цими даними, вам невідомо, але завдання є завдання, до того ж не таке вже й складне. Спробуймо його вирішити!
Завдання 15.8
Складіть SQL-запит, який вибирає з таблиці products всі чаї вартістю більше 60 і нараховує їм знижку 25%.
Знижку в % менеджер попросив вказати в окремому стовпці у форматі тексту, тобто так: «25%» (без лапок). Стовпці зі знижкою та новою ціною назвіть відповідно discount та new_price.
Також необхідно будь-яким відомим способом позбавитися «tea mushroom»: навряд чи менеджер мав на увазі і його, коли ставив нам завдання.
Результат має бути відсортований за зростанням id товару.
Поля в результуючій таблиці: product_id, name, price, discount, new_price
Пояснення:
Щоб вказати стовпець з одним значенням для всіх записів (у нашому випадку це стовпець зі знижкою), достатньо просто ввести потрібне значення і дати стовпцю якусь назву - вказане значення автоматично проставиться у всіх рядках результуючої таблиці:
SELECT column_1, 'text' AS column_2
FROM table%%sql
SELECT product_id,
name,
price,
'25%' as discount,
price * 0.75 as new_price
FROM products
WHERE price > 60
and name != 'tea mushroom'
and name like '%tea%'
ORDER BY product_id| product_id | name | price | discount | new_price | |
|---|---|---|---|---|---|
| 0 | 9 | black leaf tea | 84.0 | 25% | 63.00 |
| 1 | 27 | herbal leaf tea | 121.0 | 25% | 90.75 |
| 2 | 45 | leaf green tea | 78.0 | 25% | 58.50 |
| 3 | 79 | herbal tea bags | 65.0 | 25% | 48.75 |
IN та BETWEEN для фільтраціїАле якщо ми все-таки не хочемо перевіряти наші дані на відповідність якомусь шаблону, а просто хочемо відібрати значення з деякого списку або навіть діапазону? У цьому випадку у логічному виразі після ключового слова WHERE можна використовувати оператори IN та BETWEEN.
Оператор IN перевіряє, чи відповідає значення в колонці одному із значень із заданого списку. Іншими словами, він перевіряє, чи входить значення до цього списку:
SELECT column_1, column_2
FROM table
WHERE column_1 IN ('product_1', 'product_2', 'product_3')У свою чергу, оператор BETWEEN дозволяє відібрати дані, що відносяться до деякого інтервалу. При цьому межі інтервалу включаються:
SELECT column_1, column_2
FROM table
WHERE column_2 BETWEEN 5 AND 10Запит вище відповідає наступному запиту:
SELECT column_1, column_2
FROM table
WHERE column_2 >= 5 AND column_2 <= 10Оператор BETWEEN можна використовувати і для фільтрації по колонках з датами та часом:
SELECT column_1, column_2, column_3
FROM table
WHERE column_3 BETWEEN '2022-11-20' AND '2022-12-31'Однак, в такому випадку, треба враховувати, що дата завжди інтерпретується опівночі (початок дня), тобто. ‘2022-12-31’ насправді означає ‘2022-12-31 00:00:00’. Тож заданий вище інтервал не потраплять записи пізніше опівночі ‘2022-12-31’, тобто цей день практично не буде враховано в інтервалі.
При цьому, запит вище буде рівносильний наступному запиту:
SELECT column_1, column_2, column_3
FROM table
WHERE column_3 >= '2022-11-20' AND column_3 <= '2022-12-31'Для отримання зворотного результату у поєднанні з операторами IN та BETWEEN можна використовувати оператор NOT:
SELECT column_1, column_2
FROM table
WHERE column_1 NOT IN ('product_1', 'product_2', 'product_3')
SELECT column_1, column_2
FROM table
WHERE column_2 NOT BETWEEN 5 AND 10Завдання 15.9
З таблиці user_actions виведіть всю інформацію про дії користувачів з id 170, 200 та 230 за період з 25 серпня до 4 вересня 2022 року включно. Результат відсортуйте за зменшенням id замовлення - тобто від останніх до найпізніших.
Поля у результуючій таблиці: user_id, order_id, action, time
%%sql
SELECT user_id,
order_id,
action,
time
FROM user_actions
WHERE user_id in (170, 200, 230)
and time >= '2022-08-25'
and time < '2022-09-05' -- уважно з датами!
ORDER BY order_id desc| user_id | order_id | action | time | |
|---|---|---|---|---|
| 0 | 170 | 34428 | create_order | 2022-09-04 01:43:00 |
| 1 | 170 | 34424 | create_order | 2022-09-04 01:42:00 |
| 2 | 230 | 24675 | create_order | 2022-09-02 12:21:00 |
| 3 | 200 | 24538 | create_order | 2022-09-02 11:50:00 |
| 4 | 230 | 22887 | create_order | 2022-09-02 00:54:00 |
| 5 | 230 | 22878 | create_order | 2022-09-02 00:50:00 |
| 6 | 230 | 19543 | create_order | 2022-09-01 07:21:00 |
| 7 | 170 | 14894 | create_order | 2022-08-30 22:04:00 |
| 8 | 230 | 14466 | create_order | 2022-08-30 19:51:00 |
| 9 | 170 | 4194 | create_order | 2022-08-27 17:28:00 |
| 10 | 170 | 4190 | create_order | 2022-08-27 17:27:00 |
| 11 | 200 | 3416 | create_order | 2022-08-27 09:36:00 |
| 12 | 200 | 3413 | create_order | 2022-08-27 09:34:00 |
| 13 | 170 | 1234 | create_order | 2022-08-25 23:35:00 |
| 14 | 230 | 263 | create_order | 2022-08-25 02:54:00 |
| 15 | 200 | 229 | create_order | 2022-08-25 01:52:00 |
| 16 | 200 | 226 | create_order | 2022-08-25 01:50:00 |
| 17 | 170 | 188 | create_order | 2022-08-25 00:49:00 |
NULLРаніше Завдання 14.11 ми зіткнулися з пропущеними значеннями в таблиці couriers — у деяких кур’єрів не було зазначено їхні дні народження.
Для перевірки на NULL значення SQL є оператор IS NULL. У поєднанні з WHERE записується він так:
SELECT column_1, column_2
FROM table
WHERE column_1 IS NULLЯкщо ж, навпаки, необхідно відібрати значення не NULL, то додатково використовується оператор NOT:
SELECT column_1, column_2
FROM table
WHERE column_1 IS NOT NULLЯк ми знаємо, у світі даних NULL означає відсутність інформації. NULL - це не якась величина і тому її не можна порівняти з чимось ще. Результатом порівняння NULL з будь-яким іншим значенням буде той самий NULL. Більше того, NULL не вдасться порівняти і з іншим NULL, тому що в такому випадку порівнюватимуться дві невизначеності і не можна напевно сказати, рівні вони чи ні.
Спробуйте виконати наступні запити:
SELECT NULL = NULL
Результат:
NULL
SELECT NULL IS NULL
Результат:
true
SELECT 100 = NULL
Результат:
NULL
SELECT 100 IS NULL
Результат:
falseДетальніше про значення NULL можна прочитати тут.
Завдання 15.10
Напишіть SQL-запит до таблиці couriers та виведіть всю інформацію про кур’єрів, у яких не вказано їх день народження.
Результат має бути відсортований за зростанням id кур’єра.
Поля в результуючій таблиці: birth_date, courier_id, sex
%%sql
SELECT birth_date,
courier_id,
sex
FROM couriers
WHERE birth_date is null
ORDER BY courier_id
LIMIT 10 -- для скорочення виводу| birth_date | courier_id | sex | |
|---|---|---|---|
| 0 | NaT | 83 | male |
| 1 | NaT | 187 | male |
| 2 | NaT | 200 | male |
| 3 | NaT | 232 | male |
| 4 | NaT | 411 | male |
| 5 | NaT | 450 | male |
| 6 | NaT | 467 | male |
| 7 | NaT | 481 | male |
| 8 | NaT | 726 | male |
| 9 | NaT | 790 | male |
Давайте вирішимо ще кілька завдань, щоб переконатися, що ми розібралися з фільтрацією даних.
Завдання 15.11
Визначте id та дати народження 10 наймолодших користувачів чоловічої статі з таблиці users. Не враховуйте користувачів, у яких не вказано дату народження.
Поле в результуючій таблиці: user_id, birth_date
Пояснення: будьте уважні і пам’ятайте про значення NULL.
%%sql
SELECT user_id,
birth_date
FROM users
WHERE birth_date IS NOT NULL
AND sex = 'male'
ORDER BY birth_date DESC
LIMIT 10| user_id | birth_date | |
|---|---|---|
| 0 | 11419 | 2003-09-02 |
| 1 | 11461 | 2002-11-05 |
| 2 | 9257 | 2002-07-11 |
| 3 | 20804 | 2002-01-09 |
| 4 | 14956 | 2001-08-09 |
| 5 | 7047 | 2001-06-11 |
| 6 | 11725 | 2001-05-27 |
| 7 | 4374 | 2001-04-29 |
| 8 | 15102 | 2001-04-21 |
| 9 | 14571 | 2001-03-31 |
Завдання 15.12
Напишіть SQL-запит до таблиці courier_actions, щоб дізнатися ID та час доставки останніх 10 замовлень, доставлених кур’єром з ID 100.
Поля у результуючій таблиці: order_id, time
Пояснення: Зверніть увагу, що у вихідній таблиці є записи не лише з часом доставки, а й з часом прийняття замовлення.
%%sql
SELECT order_id,
time
FROM courier_actions
WHERE courier_id = 100
AND action = 'deliver_order'
ORDER BY order_id desc limit 10| order_id | time | |
|---|---|---|
| 0 | 57791 | 2022-09-08 18:18:00 |
| 1 | 55095 | 2022-09-08 07:51:00 |
| 2 | 53500 | 2022-09-07 23:05:00 |
| 3 | 49779 | 2022-09-07 07:34:00 |
| 4 | 48406 | 2022-09-06 22:13:00 |
| 5 | 44546 | 2022-09-05 21:13:00 |
| 6 | 42258 | 2022-09-05 11:49:00 |
| 7 | 41606 | 2022-09-05 09:00:00 |
| 8 | 41440 | 2022-09-05 08:14:00 |
| 9 | 38536 | 2022-09-04 18:57:00 |
І ще кілька завдань на роботу з датами та часом.
Завдання 15.13
З таблиці user_actions отримаєте id всіх замовлень, зроблених користувачами сервісу у серпні 2022 року.
Результат відсортуйте за зростанням id замовлення.
Поле у результуючій таблиці: order_id
Пояснення: зверніть увагу, що у вихідній таблиці є записи не лише з часом оформлення, а й часом скасування замовлення.У цій задачі може стати в нагоді функція DATE_PART. Ми розглядали її в Завдання 14.10.
%%sql
SELECT order_id
FROM user_actions
WHERE action = 'create_order'
AND DATE_PART('month', time) = 8
AND DATE_PART('year', time) = 2022
ORDER BY order_id| order_id | |
|---|---|
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| ... | ... |
| 18794 | 18795 |
| 18795 | 18796 |
| 18796 | 18797 |
| 18797 | 18798 |
| 18798 | 18799 |
18799 rows × 1 columns
Завдання 15.14
З таблиці couriers відберіть id всіх кур’єрів, що народилися в період з 1990 по 1995 включно.
Результат відсортуйте за зростанням id кур’єра.
Поле у результуючій таблиці: courier_id
Пояснення: у цьому завдання знову може стати в нагоді функція DATE_PART.
%%sql
SELECT courier_id
FROM couriers
WHERE date_part('year', birth_date) BETWEEN 1990
AND 1995
ORDER BY courier_id| courier_id | |
|---|---|
| 0 | 2 |
| 1 | 3 |
| 2 | 8 |
| 3 | 9 |
| 4 | 10 |
| ... | ... |
| 1522 | 3153 |
| 1523 | 3160 |
| 1524 | 3161 |
| 1525 | 3166 |
| 1526 | 3167 |
1527 rows × 1 columns
Завдання 15.15
З таблиці user_actions отримайте інформацію про всі скасування замовлень, які користувачі здійснювали протягом серпня 2022 по середах з 12:00 до 15:59.
Результат відсортуйте за зменшенням id скасованих замовлень.
Поля у результуючій таблиці: user_id, order_id, action, time
Пояснення: будьте уважні при роботі з датами та часом. Для вирішення задачі вам знадобиться функція DATE_PART. Для отримання дня тижня можна вказати аргумент 'dow' (“day of week”):
SELECT DATE_PART('dow', DATE '2022-12-31')
Результат:
6.00В DuckDB дні тижня рахуються з неділі (0) до суботи (6).
%%sql
SELECT user_id,
order_id,
action,
time
FROM user_actions
WHERE action = 'cancel_order'
AND DATE_PART('dow', time) = 03
AND DATE_PART('month', time) = 08
AND DATE_PART('year', time) = 2022
AND DATE_PART('h', time) BETWEEN 12
AND 15
ORDER BY order_id DESC
LIMIT 10| user_id | order_id | action | time | |
|---|---|---|---|---|
| 0 | 8117 | 17307 | cancel_order | 2022-08-31 15:59:00 |
| 1 | 8107 | 17282 | cancel_order | 2022-08-31 15:50:00 |
| 2 | 1495 | 17272 | cancel_order | 2022-08-31 15:46:00 |
| 3 | 7427 | 17252 | cancel_order | 2022-08-31 15:38:00 |
| 4 | 3207 | 17231 | cancel_order | 2022-08-31 15:33:00 |
| 5 | 8077 | 17218 | cancel_order | 2022-08-31 15:30:00 |
| 6 | 3446 | 17216 | cancel_order | 2022-08-31 15:28:00 |
| 7 | 8076 | 17214 | cancel_order | 2022-08-31 15:26:00 |
| 8 | 6116 | 17190 | cancel_order | 2022-08-31 15:17:00 |
| 9 | 8057 | 17151 | cancel_order | 2022-08-31 15:02:00 |
CASE WHENІ насамкінець давайте повернемося до наших податків.
Минулого разу ми вирішили завдання для одного з менеджерів та порахували ПДВ кожного товару. Ви довго дивилися на розрахунки, що вийшли, і вас все-таки замучила совість: адже ви точно знаєте, що на окремі групи товарів ПДВ становить не 20%, а 10%.
Оскільки менеджер раптово перестав відповідати на ваші повідомлення, ви вирішили написати безпосередньо бухгалтеру та запросили список товарів, на які поширюється ПДВ 10%.
Ось який список ви отримали:
'sugar', 'crackers', 'drying', 'seeds',
'linseed oil', 'grapes', 'olive oil',
'watermelon', 'baton', 'yogurt', 'cream', 'buckwheat',
'oatmeal', 'pasta', 'mutton', 'oranges',
'bagels', 'bread', 'peas', 'sour cream', 'smoked fish',
'flour', 'sprats', 'sausages', 'pork', 'rice',
'sesame oil', 'condensed milk', 'pineapple', 'beef',
'salt', 'dried fish', 'sunflower oil', 'apples',
'pears', 'flatbread', 'milk', 'chicken', 'lavash', 'waffles', 'tangerines'Завдання 15.16
Як і у минулому завданні (Завдання 14.15), обчисліть ПДВ кожного товару в таблиці products і розрахуйте ціну без урахування ПДВ. Однак тепер врахуйте, що для товарів зі списку податок становить 10%. Для решти товарів ПДВ той самий — 20%.
Виведіть всю інформацію про товари, включаючи суму податку та ціну без його врахування. Колонки із сумою податку та ціною без ПДВ назвіть відповідно tax та price_before_tax. Округліть значення у цих колонках до двох знаків після коми.
Результат відсортуйте спочатку за спаданням ціни товару без урахування ПДВ, потім за зростанням id товару.
Поля в результуючій таблиці: product_id, name, price, tax, price_before_tax
Пояснення: порядок розрахунку податку той самий, що й у Завдання 14.15. Для вирішення завдання вам можуть стати в нагоді конструкція CASE та оператор IN. Конструкцію CASE ми розглядали у Завдання 14.14.
%%sql
SELECT product_id,
name,
price,
ROUND(price * (CASE WHEN name IN ('sugar', 'crackers', 'drying', 'seeds',
'linseed oil', 'grapes', 'olive oil',
'watermelon', 'baton', 'yogurt', 'cream',
'buckwheat', 'oatmeal', 'pasta', 'mutton',
'oranges', 'bagels', 'bread', 'peas',
'sour cream', 'smoked fish', 'flour', 'sprats',
'sausages', 'pork', 'rice', 'sesame oil',
'condensed milk', 'pineapple', 'beef', 'salt',
'dried fish', 'sunflower oil', 'apples',
'pears', 'flatbread', 'milk', 'chicken',
'lavash', 'waffles', 'tangerines')
THEN 0.1 / 1.1
ELSE 0.2 / 1.2 end), 2) AS tax,
ROUND(price / (CASE WHEN name IN ('sugar', 'crackers', 'drying', 'seeds',
'linseed oil', 'grapes', 'olive oil',
'watermelon', 'baton', 'yogurt', 'cream',
'buckwheat', 'oatmeal', 'pasta', 'mutton',
'oranges', 'bagels', 'bread', 'peas',
'sour cream', 'smoked fish', 'flour', 'sprats',
'sausages', 'pork', 'rice', 'sesame oil',
'condensed milk', 'pineapple', 'beef', 'salt',
'dried fish', 'sunflower oil', 'apples',
'pears', 'flatbread', 'milk', 'chicken',
'lavash', 'waffles', 'tangerines')
THEN 1.1
ELSE 1.2 END), 2) AS price_before_tax
FROM products
ORDER BY price_before_tax DESC, product_id asc;| product_id | name | price | tax | price_before_tax | |
|---|---|---|---|---|---|
| 0 | 13 | caviar | 800.0 | 133.33 | 666.67 |
| 1 | 37 | mutton | 559.0 | 50.82 | 508.18 |
| 2 | 15 | olive oil | 450.0 | 40.91 | 409.09 |
| 3 | 57 | pork | 450.0 | 40.91 | 409.09 |
| 4 | 66 | beef | 370.0 | 33.64 | 336.36 |
| ... | ... | ... | ... | ... | ... |
| 82 | 6 | crackers | 25.0 | 2.27 | 22.73 |
| 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 | 1.09 | 10.91 |
| 86 | 54 | paper bag | 1.0 | 0.17 | 0.83 |
87 rows × 5 columns