Skip to content

1 Віконні функції (window functions)

Віконні функції (window functions) — це потужний інструмент у SQL, який дозволяє виконувати агрегатні, аналітичні або ранжувальні обчислення над групами рядків, не об’єднуючи їх у одну групу, як це робить GROUP BY.

Інакше кажучи,

Віконна функція дає змогу бачити “вікно даних” навколо поточного рядка — і виконати розрахунки по ньому.

📘 Основна ідея

Звичайна агрегатна функція (SUM, AVG, COUNT):

  • об’єднує рядки (через GROUP BY),
  • повертає один результат на групу.

Віконна функція (SUM() OVER(...)):

  • не об’єднує рядки,
  • повертає результат для кожного рядка,
  • дозволяє “бачити” інші рядки у тому самому вікні (window).

🔹 Синтаксис

<функція>() OVER (
    [PARTITION BY <стовпець>]
    [ORDER BY <стовпець>]
    [ROWS BETWEEN ...]
)
  • <функція> — будь-яка агрегатна (SUM, AVG, COUNT) або аналітична (RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD) функція.
  • PARTITION BY — ділить дані на групи (як GROUP BY, але без злиття рядків).
  • ORDER BY — визначає порядок у межах вікна (наприклад, хронологічно).
  • ROWS BETWEEN — уточнює межі вікна (опціонально).

🧩 Приклад 1 — Сума продажів по продавцю (з бази BikeStores)

SELECT
    s.store_name,
    st.first_name + ' ' + st.last_name AS staff_name,
    SUM(oi.quantity * oi.list_price) OVER (PARTITION BY s.store_id) AS total_store_sales
FROM sales.stores AS s
JOIN sales.staffs AS st ON s.store_id = st.store_id
JOIN sales.orders AS o ON st.staff_id = o.staff_id
JOIN sales.order_items AS oi ON o.order_id = oi.order_id;

📍 Що робить запит:

  • PARTITION BY s.store_id — розбиває всі рядки по магазинах;
  • SUM(...) OVER (...) — рахує загальну суму продажів у магазині;
  • Повертає її для кожного рядка, не агрегуючи таблицю.

📈 Приклад 2 — Рейтинг продавців у межах магазину

SELECT
    s.store_name,
    st.first_name + ' ' + st.last_name AS staff_name,
    SUM(oi.quantity * oi.list_price) AS total_sales,
    RANK() OVER (
        PARTITION BY s.store_id
        ORDER BY SUM(oi.quantity * oi.list_price) DESC
    ) AS rank_in_store
FROM sales.stores AS s
JOIN sales.staffs AS st ON s.store_id = st.store_id
JOIN sales.orders AS o ON st.staff_id = o.staff_id
JOIN sales.order_items AS oi ON o.order_id = oi.order_id
GROUP BY s.store_name, s.store_id, st.first_name, st.last_name;

📍 Що відбувається:

  • RANK() OVER (...) створює рейтинг продавців у кожному магазині;
  • PARTITION BY s.store_id — для кожного магазину рахується свій рейтинг;
  • ORDER BY SUM(...) DESC — більший обсяг продажів = вищий рейтинг.

🧮 Популярні віконні функції

ФункціяОпис
ROW_NUMBER()Нумерація рядків (1, 2, 3, ...) у межах групи
RANK()Присвоює рейтинг (з пропусками при однакових значеннях)
DENSE_RANK()Рейтинг без пропусків
NTILE(n)Розбиває результати на n частин (квантілі)
LAG(expr, offset)Повертає значення з попереднього рядка
LEAD(expr, offset)Повертає значення з наступного рядка
SUM()/AVG()/COUNT()Може використовуватись як віконна, не лише агрегатна функція

📊 Приклад 3 — Зміна продажів між замовленнями

SELECT
    o.order_id,
    o.order_date,
    SUM(oi.quantity * oi.list_price) AS total,
    LAG(SUM(oi.quantity * oi.list_price)) OVER (ORDER BY o.order_date) AS prev_total,
    SUM(oi.quantity * oi.list_price)
        - LAG(SUM(oi.quantity * oi.list_price)) OVER (ORDER BY o.order_date) AS diff
FROM sales.orders AS o
JOIN sales.order_items AS oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_date
ORDER BY o.order_date;

📍 Пояснення:

  • LAG() дивиться на попередній рядок;
  • diff показує різницю між поточним і попереднім днем продажів.

🧠 Підсумок

Звичайна агрегаціяВіконна функція
Зменшує кількість рядківЗберігає всі рядки
Використовує GROUP BYВикористовує OVER()
Неможливо комбінувати кілька різних агрегатів в одному запитіМожна легко комбінувати

Як працюють віконні функції?

На прикладі продавців у магазині

візуальна схема, яка допоможе зрозуміти, як працюють віконні функції — на прикладі продавців у магазині 🏬

🧩 Приклад: Рейтинг продавців у межах кожного магазину

📋 Вихідні дані

store_idstaff_nametotal_sales
1Alice5000
1Bob4200
1Carol4200
2David6000
2Emma5800

🪟 Як працює RANK() OVER (PARTITION BY store_id ORDER BY total_sales DESC)

store_idstaff_nametotal_salesPARTITION (вікно)rank_in_store
1Alice5000🪟 Магазин 1 → [Alice, Bob, Carol]1
1Bob4200🪟 Магазин 1 → [Alice, Bob, Carol]2
1Carol4200🪟 Магазин 1 → [Alice, Bob, Carol]2
2David6000🪟 Магазин 2 → [David, Emma]1
2Emma5800🪟 Магазин 2 → [David, Emma]2

🔹 PARTITION BY store_id — ділить дані на "вікна" (по магазинах). 🔹 ORDER BY total_sales DESC — всередині кожного магазину сортує продавців. 🔹 RANK() — присвоює позиції (однакові суми → однаковий ранг, наступний пропускається).

🧮 Якби ми використали DENSE_RANK()

store_idstaff_nametotal_salesDENSE_RANK
1Alice50001
1Bob42002
1Carol42002
2David60001
2Emma58002

🔸 Різниця: DENSE_RANK() не пропускає значення (після двох "2" іде одразу "3", а не "4").

🧮 А як працює LAG() — “попередній рядок”

order_datetotal_salesLAG(total_sales)diff
2025-11-013000NULLNULL
2025-11-0235003000+500
2025-11-0328003500-700
2025-11-0440002800+1200

🪟 Тут вікно визначається ORDER BY order_date, і кожен рядок “бачить” попередній.

🧠 Висновок

ФункціяЩо робитьПриклад результату
RANK()Присвоює рейтинг із пропусками1, 2, 2, 4
DENSE_RANK()Присвоює рейтинг без пропусків1, 2, 2, 3
ROW_NUMBER()Простий лічильник рядків1, 2, 3, 4
LAG()Значення попереднього рядка3000 → 3500 → 2800...
LEAD()Значення наступного рядка3500 → 2800 → 4000...

Що таке SQL Rank?

RANK() — це вбудована аналітична (віконна) функція SQL, яка використовується для присвоєння порядкових номерів (рангів) рядкам у наборі результатів, з урахуванням певного порядку (ORDER BY) і можливих групувань (PARTITION BY).

Інакше кажучи, вона показує, яке місце займає кожен рядок у своєму "сегменті" даних (наприклад, 1-ше, 2-ге, 3-тє місце тощо).

🧠 Синтаксис

RANK() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3 [ASC|DESC], ...
)
  • RANK() — сама функція.
  • OVER — вказує, що це віконна функція.
  • PARTITION BY — ділить дані на групи (опціонально).
  • ORDER BY — визначає порядок, у якому призначається ранг.
Spinner
DataFrameas
df15
variable
SELECT
    s.store_name,
    st.first_name + ' ' + st.last_name AS staff_name,
    ROUND(SUM(oi.quantity * oi.list_price * (1 - oi.discount)), 2) AS total_sales,
    RANK() OVER (
        PARTITION BY s.store_id
        ORDER BY SUM(oi.quantity * oi.list_price * (1 - oi.discount)) DESC
    ) AS rank_in_store
FROM sales.stores AS s
JOIN sales.staffs AS st ON s.store_id = st.store_id
JOIN sales.orders AS o ON st.staff_id = o.staff_id
JOIN sales.order_items AS oi ON o.order_id = oi.order_id
GROUP BY s.store_name, s.store_id, st.first_name, st.last_name
ORDER BY s.store_name, rank_in_store;

📊 Пояснення

КомпонентОпис
sales.storesТаблиця магазинів
sales.staffsТаблиця продавців
sales.ordersТаблиця замовлень
sales.order_itemsТаблиця позицій замовлень
RANK() OVER (PARTITION BY s.store_id ORDER BY SUM(...) DESC)Ранжує продавців у межах кожного магазину за обсягом продажів
ROUND()Округлення загальної суми продажів
GROUP BYГрупує дані за кожним продавцем у магазині

SQL скрипт для створення бази даних Bike Store

Приклад структури бази даних для магазину велосипедів:

-- Створення бази даних
CREATE DATABASE bike_store;

-- Підключення до бази даних
\c bike_store;

-- ============================================
-- ТАБЛИЦІ ДОВІДНИКІВ
-- ============================================

-- Таблиця категорій продуктів
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    description TEXT
);

-- Таблиця брендів
CREATE TABLE brands (
    brand_id SERIAL PRIMARY KEY,
    brand_name VARCHAR(100) NOT NULL UNIQUE,
    country VARCHAR(50),
    website VARCHAR(200)
);

-- ============================================
-- ТАБЛИЦІ ПРОДУКТІВ
-- ============================================

-- Таблиця продуктів
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    brand_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    model_year INTEGER,
    list_price DECIMAL(10, 2) NOT NULL,
    description TEXT,
    stock_quantity INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(brand_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE,
    CHECK (list_price >= 0),
    CHECK (stock_quantity >= 0)
);

-- ============================================
-- ТАБЛИЦІ МАГАЗИНІВ ТА СКЛАДІВ
-- ============================================

-- Таблиця магазинів
CREATE TABLE stores (
    store_id SERIAL PRIMARY KEY,
    store_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    street VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    zip_code VARCHAR(10)
);

-- Таблиця складських запасів
CREATE TABLE stocks (
    store_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (store_id, product_id),
    FOREIGN KEY (store_id) REFERENCES stores(store_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    CHECK (quantity >= 0)
);

-- ============================================
-- ТАБЛИЦІ ПЕРСОНАЛУ
-- ============================================

-- Таблиця співробітників
CREATE TABLE staffs (
    staff_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    active BOOLEAN DEFAULT TRUE,
    store_id INTEGER NOT NULL,
    manager_id INTEGER,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10, 2),
    FOREIGN KEY (store_id) REFERENCES stores(store_id) ON DELETE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES staffs(staff_id) ON DELETE SET NULL
);

-- ============================================
-- ТАБЛИЦІ КЛІЄНТІВ
-- ============================================

-- Таблиця клієнтів
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100) UNIQUE NOT NULL,
    street VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    zip_code VARCHAR(10),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- ТАБЛИЦІ ЗАМОВЛЕНЬ
-- ============================================

-- Таблиця замовлень
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    required_date DATE,
    shipped_date DATE,
    store_id INTEGER NOT NULL,
    staff_id INTEGER NOT NULL,
    total_amount DECIMAL(10, 2) DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (store_id) REFERENCES stores(store_id) ON DELETE CASCADE,
    FOREIGN KEY (staff_id) REFERENCES staffs(staff_id) ON DELETE CASCADE,
    CHECK (order_status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'))
);

-- Таблиця деталей замовлення
CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    item_id SERIAL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    list_price DECIMAL(10, 2) NOT NULL,
    discount DECIMAL(4, 2) DEFAULT 0,
    PRIMARY KEY (order_id, item_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    CHECK (quantity > 0),
    CHECK (discount >= 0 AND discount <= 1)
);

-- ============================================
-- ІНДЕКСИ ДЛЯ ОПТИМІЗАЦІЇ
-- ============================================

CREATE INDEX idx_products_brand ON products(brand_id);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_name ON products(product_name);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(order_status);
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_staffs_store ON staffs(store_id);

-- ============================================
-- ТРИГЕРИ
-- ============================================

-- Функція для оновлення updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS 
$$

BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;

$$
 LANGUAGE plpgsql;

-- Тригер для products
CREATE TRIGGER update_products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Функція для розрахунку total_amount замовлення
CREATE OR REPLACE FUNCTION calculate_order_total()
RETURNS TRIGGER AS 
$$

BEGIN
    UPDATE orders
    SET total_amount = (
        SELECT SUM(quantity * list_price * (1 - discount))
        FROM order_items
        WHERE order_id = NEW.order_id
    )
    WHERE order_id = NEW.order_id;
    RETURN NEW;
END;

$$
 LANGUAGE plpgsql;

-- Тригер для автоматичного розрахунку суми замовлення
CREATE TRIGGER calculate_total_after_insert
    AFTER INSERT ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION calculate_order_total();

CREATE TRIGGER calculate_total_after_update
    AFTER UPDATE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION calculate_order_total();

-- ============================================
-- ПРЕДСТАВЛЕННЯ (VIEWS)
-- ============================================

-- Представлення продуктів з повною інформацією
CREATE VIEW v_products_full AS
SELECT 
    p.product_id,
    p.product_name,
    b.brand_name,
    c.category_name,
    p.model_year,
    p.list_price,
    p.stock_quantity
FROM products p
JOIN brands b ON p.brand_id = b.brand_id
JOIN categories c ON p.category_id = c.category_id;

-- Представлення замовлень з деталями
CREATE VIEW v_orders_details AS
SELECT 
    o.order_id,
    o.order_date,
    o.order_status,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    CONCAT(s.first_name, ' ', s.last_name) AS staff_name,
    st.store_name,
    o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN staffs s ON o.staff_id = s.staff_id
JOIN stores st ON o.store_id = st.store_id;

-- Представлення складських залишків
CREATE VIEW v_stock_levels AS
SELECT 
    st.store_name,
    p.product_name,
    b.brand_name,
    s.quantity,
    p.list_price,
    (s.quantity * p.list_price) AS stock_value
FROM stocks s
JOIN stores st ON s.store_id = st.store_id
JOIN products p ON s.product_id = p.product_id
JOIN brands b ON p.brand_id = b.brand_id;

-- ============================================
-- ВСТАВКА ТЕСТОВИХ ДАНИХ
-- ============================================

-- Категорії
INSERT INTO categories (category_name, description) VALUES
    ('Mountain Bikes', 'Bikes for off-road cycling'),
    ('Road Bikes', 'Bikes for road cycling'),
    ('Electric Bikes', 'Bikes with electric motor'),
    ('Kids Bikes', 'Bikes for children'),
    ('Accessories', 'Bike accessories and parts');

-- Бренди
INSERT INTO brands (brand_name, country, website) VALUES
    ('Trek', 'USA', 'www.trekbikes.com'),
    ('Giant', 'Taiwan', 'www.giant-bicycles.com'),
    ('Specialized', 'USA', 'www.specialized.com'),
    ('Cannondale', 'USA', 'www.cannondale.com'),
    ('Scott', 'Switzerland', 'www.scott-sports.com');

-- Магазини
INSERT INTO stores (store_name, phone, email, street, city, state, zip_code) VALUES
    ('Bike Store - Main', '+1-555-0101', '[email protected]', '123 Main St', 'New York', 'NY', '10001'),
    ('Bike Store - West', '+1-555-0102', '[email protected]', '456 West Ave', 'Los Angeles', 'CA', '90001'),
    ('Bike Store - East', '+1-555-0103', '[email protected]', '789 East Blvd', 'Miami', 'FL', '33101');

-- Продукти
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price, stock_quantity) VALUES
    ('Trek Marlin 7', 1, 1, 2024, 899.99, 15),
    ('Giant Defy Advanced 2', 2, 2, 2024, 2499.99, 8),
    ('Specialized Turbo Vado', 3, 3, 2024, 3499.99, 5),
    ('Cannondale Quick Kids 20', 4, 4, 2024, 349.99, 20),
    ('Trek Domane SL 5', 1, 2, 2023, 3299.99, 6);

-- Співробітники
INSERT INTO staffs (first_name, last_name, email, phone, store_id, manager_id, salary) VALUES
    ('John', 'Smith', '[email protected]', '+1-555-1001', 1, NULL, 5000.00),
    ('Mary', 'Johnson', '[email protected]', '+1-555-1002', 1, 1, 3500.00),
    ('Robert', 'Brown', '[email protected]', '+1-555-1003', 2, NULL, 4800.00),
    ('Sarah', 'Davis', '[email protected]', '+1-555-1004', 2, 3, 3200.00);

-- Клієнти
INSERT INTO customers (first_name, last_name, phone, email, street, city, state, zip_code) VALUES
    ('Michael', 'Wilson', '+1-555-2001', '[email protected]', '111 Oak St', 'New York', 'NY', '10002'),
    ('Jennifer', 'Taylor', '+1-555-2002', '[email protected]', '222 Pine St', 'Los Angeles', 'CA', '90002'),
    ('David', 'Anderson', '+1-555-2003', '[email protected]', '333 Maple Ave', 'Miami', 'FL', '33102');

-- Складські залишки
INSERT INTO stocks (store_id, product_id, quantity) VALUES
    (1, 1, 5),
    (1, 2, 3),
    (2, 1, 10),
    (2, 3, 5),
    (3, 4, 20);

-- Замовлення
INSERT INTO orders (customer_id, order_status, store_id, staff_id, required_date) VALUES
    (1, 'Delivered', 1, 2, CURRENT_DATE + INTERVAL '7 days'),
    (2, 'Processing', 2, 4, CURRENT_DATE + INTERVAL '5 days'),
    (3, 'Pending', 1, 2, CURRENT_DATE + INTERVAL '10 days');

-- Деталі замовлень
INSERT INTO order_items (order_id, product_id, quantity, list_price, discount) VALUES
    (1, 1, 1, 899.99, 0.10),
    (1, 5, 1, 3299.99, 0.05),
    (2, 3, 1, 3499.99, 0),
    (3, 4, 2, 349.99, 0.15);

💡 Можливості для аналізу датасету "Bicycle Sales Database" (База даних продажу велосипедів

1. Аналіз продажів

  • Загальний дохід по магазинах
  • Динаміка продажів за періодами
  • Топ-продукти за кількістю та доходом
  • Ефективність співробітників

2. Аналіз клієнтів

  • RFM-сегментація
  • Географічний розподіл
  • Поведінка покупців
  • Customer Lifetime Value (CLV)

3. Аналіз інвентарю

  • Товарні залишки по магазинах
  • Критичні запаси
  • Оборотність товарів
  • ABC-аналіз продуктів

4. Аналіз продуктів

  • Популярність брендів
  • Аналіз категорій
  • Цінова сегментація
  • Аналіз знижок

5. Операційна ефективність

  • Час виконання замовлень
  • Завантаженість магазинів
  • Продуктивність персоналу
  • Ефективність розміщення запасів

🎯 Ключові метрики для моніторингу

МетрикаОпис
Total RevenueЗагальний дохід від продажів
Average Order ValueСередня вартість замовлення
Units SoldКількість проданих одиниць
Inventory TurnoverОборотність запасів
Customer Retention RateПоказник утримання клієнтів
Stock CoverageПокриття запасів (в днях)
Sales per StoreПродажі на магазин
Sales per StaffПродажі на співробітника

🛠️ Рекомендовані типи аналізу

✅ Дослідницький аналіз даних (EDA)

  • Розуміння структури даних
  • Виявлення пропущених значень
  • Статистичний аналіз
  • Виявлення викидів та аномалій

✅ Бізнес-аналітика

  • Дашборди для керівництва
  • KPI моніторинг
  • Трендовий аналіз
  • Прогнозування продажів

✅ Маркетинговий аналіз

  • Сегментація клієнтів
  • Аналіз ефективності знижок
  • Крос-продажі (Market Basket Analysis)
  • Географічний таргетинг

✅ Оптимізація операцій

  • Управління запасами
  • Оптимізація штату
  • Ефективність логістики
  • Планування асортименту

📚 Приклади SQL запитів для початку

1. Топ-10 найприбутковіших продуктів

SELECT TOP 10
    p.product_name,
    b.brand_name,
    c.category_name,
    SUM(oi.quantity * oi.list_price * (1 - oi.discount)) AS revenue
FROM sales.order_items oi
INNER JOIN production.products p ON oi.product_id = p.product_id
INNER JOIN production.brands b ON p.brand_id = b.brand_id
INNER JOIN production.categories c ON p.category_id = c.category_id
GROUP BY p.product_name, b.brand_name, c.category_name
ORDER BY revenue DESC;

2. Продажі по місяцях

SELECT 
    FORMAT(order_date, 'yyyy-MM') AS month,
    COUNT(DISTINCT order_id) AS orders,
    SUM(oi.quantity * oi.list_price * (1 - oi.discount)) AS revenue
FROM sales.orders o
INNER JOIN sales.order_items oi ON o.order_id = oi.order_id
GROUP BY FORMAT(order_date, 'yyyy-MM')
ORDER BY month;

3. Топ-клієнти

SELECT TOP 10
    c.first_name + ' ' + c.last_name AS customer,
    COUNT(o.order_id) AS orders,
    SUM(oi.quantity * oi.list_price * (1 - oi.discount)) AS total_spent
FROM sales.customers c
INNER JOIN sales.orders o ON c.customer_id = o.customer_id
INNER JOIN sales.order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

🎨 Візуалізація даних

Датасет підтримує створення інтерактивних візуалізацій за допомогою:

  • Plotly - інтерактивні графіки
  • Pandas - обробка даних
  • Matplotlib/Seaborn - статичні графіки

Приклад візуалізації (з датасету):

  • Bar Chart - запаси велосипедів по роках та категоріях
  • Інтерактивність: наведення миші показує деталі
  • Dark theme для кращої читабельності

💼 Бізнес-кейси для аналізу

  1. Оптимізація асортименту - які продукти приносять найбільший прибуток?
  2. Управління запасами - які товари потребують поповнення?
  3. Маркетингові кампанії - які клієнти найбільш цінні?
  4. Розширення мережі - в яких регіонах відкрити нові магазини?
  5. Ціноутворення - як знижки впливають на продажі?
  6. Сезонність - коли найвищий попит на велосипеди?

🚀 Як почати роботу

  1. Перегляньте структуру таблиць - кнопка "Browse tables"
  2. Запустіть приклади запитів - вивчіть зразки SQL
  3. Створіть власні аналізи - комбінуйте таблиці для отримання інсайтів
  4. Візуалізуйте результати - використовуйте Plotly для графіків
  5. Експортуйте дані - для подальшого аналізу в Excel/Power BI

✨ Висновок

Bicycle Sales Database - це повноцінна база даних для:

  • 📊 Вивчення SQL та аналізу даних
  • 💼 Розробки бізнес-рішень
  • 📈 Створення дашбордів та звітів
  • 🎓 Навчання та практики data analysis

Датасет ідеально підходить для:

  • Data Analysts
  • Business Intelligence спеціалістів
  • SQL розробників
  • Студентів курсів з аналізу даних

🔥 Готові почати аналіз? Використовуйте SQL запити з цього Guide для глибокого дослідження даних!

1 Перевірка доступних таблиць у базі даних

Загальний опис

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

Детальний розбір кожного запиту

Запит 1: Використання INFORMATION_SCHEMA.TABLES

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE';

Пояснення:

  • INFORMATION_SCHEMA.TABLES — це стандартизоване системне представлення (view), яке є частиною стандарту SQL
  • table_name — назва таблиці
  • table_type = 'BASE TABLE' — фільтр, який виключає представлення (views) і повертає тільки реальні таблиці
  • Переваги: найбільш портативний метод, працює на різних СУБД (SQL Server, MySQL, PostgreSQL)

Запит 2: Використання sys.tables

SELECT name
FROM sys.tables;

Пояснення:

  • sys.tables — системний каталог SQL Server, який містить інформацію про всі таблиці
  • name — назва таблиці
  • Переваги: простий і швидкий, специфічний для SQL Server, автоматично виключає системні таблиці

Запит 3: Використання sysobjects

SELECT name
FROM sysobjects
WHERE xtype = 'U';

Пояснення:

  • sysobjects — застаріле системне представлення (legacy view), але все ще працює
  • xtype = 'U' — фільтр для об'єктів типу User Table (користувацькі таблиці)
  • Примітка: Microsoft рекомендує використовувати sys.tables замість sysobjects

Запит 4: Використання sys.objects

SELECT name
FROM sys.objects
WHERE type_desc = 'USER_TABLE';

Пояснення:

  • sys.objects — системний каталог, який містить інформацію про всі об'єкти бази даних
  • type_desc = 'USER_TABLE' — фільтр для користувацьких таблиць
  • Переваги: більш читабельний через використання описового типу замість коду

Який метод обрати?

МетодКоли використовувати
Запит 1Коли потрібна портативність коду між різними СУБД
Запит 2Рекомендований для SQL Server — найпростіший і найсучасніший
Запит 3Застарілий, краще уникати в новому коді
Запит 4Коли потрібно працювати з різними типами об'єктів БД

Практичне застосування

Ці запити корисні для:

  • Вивчення структури невідомої бази даних
  • Документування існуючих таблиць
  • Автоматичної генерації звітів про структуру БД
  • Перевірки наявності певних таблиць перед виконанням операцій
Spinner
DataFrameas
df
variable
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE';
Spinner
DataFrameas
bicycle_sales_1
variable
SELECT 
    model_year,
    category_name,
    SUM(quantity) AS total
FROM production.products AS pp
INNER JOIN production.categories AS pc
    ON pp.category_id = pc.category_id
INNER JOIN production.brands AS pb
    ON pp.brand_id = pb.brand_id
INNER JOIN production.stocks AS ps
    ON pp.product_id = ps.product_id
GROUP BY model_year, category_name
ORDER BY model_year, category_name;
# Import libraries
import pandas as pd
import plotly.express as px

# Create bar chart
fig = px.bar(
    bicycle_sales_1,
    x=bicycle_sales["model_year"].astype(str),
    y="total",
    color="category_name",
)

# Create labels and show plot
fig.update_layout(
    title="Bicycle Inventory<br><sup>By Quantity and Category of Product</sup>",
    title_x=0.5,
    xaxis_title="Year",
    yaxis_title="Quantity in Stock",
    legend_title="Bicycle Category",
    template="plotly_dark",
)
fig.show()
Spinner
DataFrameas
df1
variable
SELECT name
FROM sys.tables;
Spinner
DataFrameas
df2
variable
SELECT name
FROM sysobjects
WHERE xtype = 'U';
Spinner
DataFrameas
df3
variable
SELECT name
FROM sys.objects
WHERE type_desc = 'USER_TABLE';

2 Опис структури таблиці

Загальний опис

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

Детальний розбір запиту

Основний запит

EXEC sp_columns orders;

Пояснення:

  • EXEC — команда для виконання збереженої процедури (stored procedure)
  • sp_columns — системна збережена процедура SQL Server
  • orders — назва таблиці, структуру якої ми хочемо переглянути

Що повертає sp_columns?

Процедура повертає таблицю з наступними колонками:

КолонкаОпис
TABLE_QUALIFIERНазва бази даних
TABLE_OWNERВласник таблиці (схема)
TABLE_NAMEНазва таблиці
COLUMN_NAMEНазва стовпця
DATA_TYPEЧисловий код типу даних
TYPE_NAMEНазва типу даних (varchar, int, datetime тощо)
PRECISIONМаксимальна довжина або точність
LENGTHРозмір стовпця в байтах
SCALEКількість знаків після коми (для числових типів)
RADIXОснова системи числення
NULLABLEЧи може стовпець містити NULL (1 = так, 0 = ні)
REMARKSКоментарі або опис стовпця
‌
‌
‌