Аналитика оконных функций в SQLite

Введение

В этой лабораторной работе вы изучите оконную аналитику SQLite, сосредоточив внимание на ранжировании строк и вычислении нарастающих итогов (running totals). Вы узнаете, как использовать оконные функции (window functions) для выполнения вычислений над наборами строк, связанных с текущей строкой.

В частности, вы будете использовать функцию ROW_NUMBER() для присвоения уникального ранга каждой строке на основе суммы продаж. Вы также узнаете, как вычислять нарастающие итоги и разделять (partition) данные для более продвинутого анализа. Эта лабораторная работа представляет собой практическое введение в оконные функции в SQLite.

Создание базы данных и таблицы продаж (Sales)

На этом первом шаге вы создадите базу данных SQLite с именем sales.db и таблицу с именем sales для хранения данных о продажах. Эта таблица будет включать столбцы для идентификатора продукта (product ID), названия продукта (product name) и суммы продаж (sales amount).

Откройте свой терминал в LabEx VM. Ваш путь по умолчанию - /home/labex/project.

Для начала создайте базу данных sales.db и откройте инструмент командной строки SQLite, выполнив следующую команду:

sqlite3 sales.db

Эта команда создает файл базы данных и открывает оболочку SQLite, где вы можете выполнять SQL-команды. Вы увидите приглашение, подобное этому:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Далее создайте таблицу sales со следующими столбцами: product_id, product_name и sales_amount. Введите следующую SQL-команду в командной строке sqlite> и нажмите Enter:

CREATE TABLE sales (
    product_id INTEGER,
    product_name TEXT,
    sales_amount INTEGER
);

Эта команда настраивает таблицу sales, где:

• product_id - это целое число, представляющее уникальный идентификатор для каждого продукта.
• product_name - это текстовое поле, хранящее название продукта.
• sales_amount - это целое число, представляющее сумму продаж для продукта.

Вы не увидите никакого вывода, если команда выполнится успешно.

Вставка образцов данных в таблицу продаж (Sales)

Теперь, когда вы создали таблицу sales, давайте добавим в нее несколько образцов данных. Мы вставим шесть записей, представляющих различные продукты и их суммы продаж.

Вставьте следующие записи в таблицу sales, выполняя эти команды одну за другой в командной строке sqlite>:

INSERT INTO sales (product_id, product_name, sales_amount) VALUES
(1, 'Laptop', 1200),
(2, 'Keyboard', 75),
(3, 'Mouse', 25),
(4, 'Monitor', 300),
(5, 'Headphones', 100),
(6, 'Webcam', 50);

Эти команды добавляют шесть строк в таблицу sales. Каждая строка представляет продукт с его ID, названием и суммой продаж.

• INSERT INTO sales (product_id, product_name, sales_amount) указывает, что вы вставляете данные в столбцы product_id, product_name и sales_amount таблицы sales.
• VALUES (1, 'Laptop', 1200) предоставляет значения для вставки для каждой записи.

Чтобы убедиться, что данные добавлены правильно, выполните эту команду, чтобы просмотреть все записи в таблице:

SELECT * FROM sales;

Ожидаемый результат:

1|Laptop|1200
2|Keyboard|75
3|Mouse|25
4|Monitor|300
5|Headphones|100
6|Webcam|50

Этот вывод показывает product_id, product_name и sales_amount для каждой записи. Команда SELECT * извлекает все столбцы из указанной таблицы.

Ранжирование строк с помощью ROW_NUMBER()

На этом шаге вы узнаете, как использовать оконную функцию ROW_NUMBER() для присвоения уникального ранга каждой строке на основе sales_amount (суммы продаж). Это полезно для определения самых продаваемых продуктов.

Функция ROW_NUMBER() присваивает уникальное целое число каждой строке в разделе результирующего набора. Ранг определяется порядком, указанным в предложении ORDER BY.

Выполните следующий запрос в командной строке sqlite>:

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

Этот запрос вычисляет ранг каждого продукта на основе его sales_amount в порядке убывания.

• ROW_NUMBER() OVER (ORDER BY sales_amount DESC) присваивает ранг каждой строке на основе sales_amount, при этом наибольшая сумма продаж получает ранг 1.
• ORDER BY sales_amount DESC указывает, что ранжирование должно основываться на sales_amount в порядке убывания (descending order).

Ожидаемый результат:

Laptop|1200|1
Monitor|300|2
Headphones|100|3
Keyboard|75|4
Webcam|50|5
Mouse|25|6

Как видите, столбец sales_rank теперь содержит ранг каждого продукта на основе его sales_amount, при этом наибольшая сумма продаж (Laptop) получает ранг 1.

Вычисление нарастающих итогов

На этом шаге вы узнаете, как вычислять нарастающие итоги (кумулятивные суммы) с помощью оконных функций. Нарастающие итоги полезны для отслеживания суммы значений за период времени или по набору строк.

Чтобы вычислить нарастающие итоги, вы используете функцию SUM() с предложением OVER() и предложением ORDER BY, чтобы указать порядок, в котором вычисляется сумма.

Сначала давайте добавим столбец sale_date (дата продажи) в таблицу sales и заполним его некоторыми образцами дат. Выполните следующие команды в командной строке sqlite>:

ALTER TABLE sales ADD COLUMN sale_date DATE;

UPDATE sales SET sale_date = '2023-01-01' WHERE product_name = 'Laptop';
UPDATE sales SET sale_date = '2023-01-05' WHERE product_name = 'Keyboard';
UPDATE sales SET sale_date = '2023-01-10' WHERE product_name = 'Mouse';
UPDATE sales SET sale_date = '2023-01-15' WHERE product_name = 'Monitor';
UPDATE sales SET sale_date = '2023-01-20' WHERE product_name = 'Headphones';
UPDATE sales SET sale_date = '2023-01-25' WHERE product_name = 'Webcam';

Эти команды добавляют столбец sale_date в таблицу sales и обновляют таблицу образцами дат для каждого продукта.

Теперь давайте вычислим нарастающий итог sales_amount с течением времени, упорядоченный по sale_date. Выполните следующий запрос:

SELECT
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM
    sales;

Этот запрос вычисляет нарастающий итог sales_amount с течением времени, упорядоченный по sale_date.

• SUM(sales_amount) OVER (ORDER BY sale_date) вычисляет кумулятивную сумму sales_amount до каждой sale_date.
• ORDER BY sale_date указывает, что нарастающий итог должен вычисляться на основе sale_date в порядке возрастания (ascending order).

Ожидаемый результат:

2023-01-01|Laptop|1200|1200
2023-01-05|Keyboard|75|1275
2023-01-10|Mouse|25|1300
2023-01-15|Monitor|300|1600
2023-01-20|Headphones|100|1700
2023-01-25|Webcam|50|1750

Столбец running_total показывает кумулятивную сумму sales_amount до каждой sale_date. Например, нарастающий итог на '2023-01-15' составляет 1600, что является суммой продаж с '2023-01-01', '2023-01-05', '2023-01-10' и '2023-01-15'.

Разделение данных для анализа

На этом шаге вы узнаете, как разделять данные с помощью предложения PARTITION BY внутри оконных функций. Разделение (partitioning) позволяет разделить данные на логические группы, а затем выполнять вычисления внутри каждой группы независимо.

Давайте добавим столбец product_category (категория продукта) в нашу таблицу sales. Выполните следующие команды в командной строке sqlite>:

ALTER TABLE sales ADD COLUMN product_category TEXT;

UPDATE sales SET product_category = 'Electronics' WHERE product_name IN ('Laptop', 'Monitor', 'Headphones', 'Webcam');
UPDATE sales SET product_category = 'Accessories' WHERE product_name IN ('Keyboard', 'Mouse');

Эти команды добавляют столбец product_category в таблицу sales и обновляют таблицу категориями для каждого продукта.

Теперь давайте используем PARTITION BY, чтобы вычислить нарастающий итог sales_amount внутри каждой product_category. Выполните следующий запрос:

SELECT
    product_category,
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total_by_category
FROM
    sales;

Этот запрос вычисляет нарастающий итог sales_amount внутри каждой product_category, упорядоченный по sale_date.

• PARTITION BY product_category разделяет данные на разделы (partitions) на основе product_category.
• SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) вычисляет кумулятивную сумму sales_amount внутри каждой product_category, упорядоченную по sale_date.

Ожидаемый результат:

Accessories|2023-01-05|Keyboard|75|75
Accessories|2023-01-10|Mouse|25|100
Electronics|2023-01-01|Laptop|1200|1200
Electronics|2023-01-15|Monitor|300|1500
Electronics|2023-01-20|Headphones|100|1600
Electronics|2023-01-25|Webcam|50|1650

Столбец running_total_by_category показывает кумулятивную сумму sales_amount внутри каждой product_category, упорядоченную по sale_date. Обратите внимание, что нарастающий итог перезапускается для каждой категории.

Резюме

В этой лабораторной работе вы узнали, как использовать оконные функции (window functions) в SQLite для выполнения расширенного анализа данных. Вы начали с создания таблицы sales и вставки образцов данных. Затем вы использовали функцию ROW_NUMBER(), чтобы ранжировать продукты на основе их суммы продаж. Вы также узнали, как вычислять нарастающие итоги (running totals), используя функцию SUM() с предложением OVER(), и как разделять данные (partition data), используя предложение PARTITION BY, чтобы выполнять вычисления внутри логических групп. Эти навыки обеспечивают основу для более сложных задач анализа данных в SQLite.