SQL

Data Miorsh Ihor Miroshnychenko Youtube Monobank

SQL (анг. Structured query language) - мова запитів на якому ми спілкуємось з базами даних.

База даних - це сховище даних, де зберігаються наші “таблиці”.

Схема бази даних

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

Для запуску бази даних, я використовую DuckDB. Самі файли з даними можна знайти в репозиторії. Для локального запуску бази даних необхідно виконати наступний код:

# Встановлення та імпорт необхідних пакетів
!pip install --quiet duckdb
!pip install --quiet jupysql 
!pip install --quiet duckdb-engine
!pip install --quiet pandas
!pip install --quiet matplotlib

import duckdb
import pandas as pd

# Імпорт Jupyter-розширення jupysql для створення SQL комірок
%load_ext sql

# Налаштування jupysql, щоб дані поверталися у вигляді DataFrame Pandas з меншим виводом
%config SqlMagic.autopandas = True
%config SqlMagic.feedback = False
%config SqlMagic.displaycon = False

# Підключення до DuckDB в режимі "in-memory"
conn = duckdb.connect()
conn.execute("INSTALL 'httpfs';")
%sql conn --alias duckdb-native

# Завантаження даних з файлів
%sql CREATE OR REPLACE TABLE user_actions AS SELECT * FROM read_csv('https://raw.githubusercontent.com/Aranaur/py4ds/main/00_data/sql/user_actions.csv', header=True, columns={'user_id': 'INT', 'order_id': 'INT', 'action': 'VARCHAR', 'time': 'TIMESTAMP'}, timestampformat='%d/%m/%y %H:%M');
%sql CREATE OR REPLACE TABLE courier_actions AS SELECT * FROM read_csv('https://raw.githubusercontent.com/Aranaur/py4ds/main/00_data/sql/courier_actions.csv', header=True, columns={'courier_id': 'INT', 'order_id': 'INT', 'action': 'VARCHAR', 'time': 'TIMESTAMP'}, timestampformat='%d/%m/%y %H:%M');
%sql CREATE OR REPLACE TABLE orders AS SELECT * FROM read_csv('https://raw.githubusercontent.com/Aranaur/py4ds/main/00_data/sql/orders.csv', header=True, columns={'order_id': 'INT', 'creation_time': 'TIMESTAMP', 'product_ids': 'INT[]'}, timestampformat='%d/%m/%y %H:%M');
%sql CREATE OR REPLACE TABLE users AS SELECT * FROM read_csv('https://raw.githubusercontent.com/Aranaur/py4ds/main/00_data/sql/users.csv', header=True, columns={'user_id': 'INT', 'birth_date': 'DATE', 'sex': 'VARCHAR'}, dateformat='%d/%m/%y');
%sql CREATE OR REPLACE TABLE couriers AS SELECT * FROM read_csv('https://raw.githubusercontent.com/Aranaur/py4ds/main/00_data/sql/couriers.csv', header=True, columns={'courier_id': 'INT', 'birth_date': 'DATE', 'sex': 'VARCHAR'}, dateformat='%d/%m/%y');
%sql CREATE OR REPLACE TABLE products AS SELECT * FROM read_csv('https://raw.githubusercontent.com/Aranaur/py4ds/main/00_data/sql/products.csv', header=True, columns={'product_id': 'INT', 'name': 'VARCHAR', 'price': 'DOUBLE'});

На схемі продемонстровані зв’язки між таблицями, а також опис даних:

erDiagram
    orders }|..|{ products : product_ids-product_id
    orders }|..|{ courier_actions : order_id
    users }|..|{ user_actions : user_id
    user_actions }|..|{ orders : order_id
    user_actions }|..|{ courier_actions : time
    courier_actions }|..|{ courier : product
    
    users {
        DATE birth_date
        VARCHAR sex
        INT user_id
    }
    user_actions {
        INT user_id
        VARCHAR actions
        INT order_id
        TIMESTAMP time
    }
    orders {
        INT order_id
        ARRAY product_ids
        TIMESTAMP creation_time
    }
    products {
        INT product_id
        NUMERIC price
        VARCHAR name
    }
    courier_actions {
        INT courier_id
        VARCHAR action
        INT order_id
        TIMESTAMP time
    }
    courier {
        INT courier_id
        VARCHAR sex
        DATE birth_date
    }

Типи даних

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

Типи даних Опис Приклад
0 INT Ціле число id користувача: 123
1 NUMERIC / DECIMAL Дійсне число Вартість товару: 120.55
2 VARCHAR Текст Дія із замовленням: «create_order»
3 DATE Дата з точністю до дня Дата народження користувача: 25/03/91
4 TIMESTAMP Дата з точністю до секунди Час реєстрації у додатку: 24/08/22 01:52:24
5 [] Масив Список id товаров у замовленні: [1, 13, 22]

Більш детально почитати про типи даних можна за посиланням

Структура та наповнення таблиць

user_actions – дії користувачів із замовленнями.

Стовпчик Тип даних Опис
0 user_id INT id користувача
1 order_id INT id замовлення
2 action VARCHAR(50) дія користувача із замовленням; "create_order"...
3 time TIMESTAMP час дії

courier_actions – дії кур’єрів із замовленнями.

Стовпчик Тип даних Опис
0 courier_id INT id кур'єра
1 order_id INT id замовлення
2 action VARCHAR(50) дія кур'єра із замовленням; 'accept order' - п...
3 time TIMESTAMP час дії

orders - інформація про замовлення.

Стовпчик Тип даних Опис
0 order_id INT id замовлення
1 creation_time TIMESTAMP час створення замовлення
2 product_ids integer[] список id товарів у замовленні

users - інформація про користувачів.

Стовпчик Тип даних Опис
0 user_id INT id користувача
1 birth_date DATE дата народження
2 sex VARCHAR(50) стать

couriers - інформація про кур’єрів.

Стовпчик Тип даних Опис
0 courier_id INT id кур'єра
1 birth_date DATE дата народження
2 sex VARCHAR(50) стать

products - інформація про товари, які доставляє сервіс.

Стовпчик Тип даних Опис
0 courier_id INT id кур'єра
1 birth_date DATE дата народження
2 sex VARCHAR(50) стать
Примітка

У дужках типу VARCHAR вказано максимально допустиму кількість символів у тексті. У типу даних NUMERIC у дужках вказано загальну кількість символів.

Data Miorsh Ihor Miroshnychenko Youtube Monobank