Группировка данных в SQLite

Введение

В этой лабораторной работе вы научитесь суммировать и анализировать данные в SQLite, используя агрегатные функции и предложения группировки (grouping clauses). Вы изучите COUNT и SUM для вычислений, сгруппируете данные по отдельным столбцам, отфильтруете группы с помощью HAVING и отсортируете сгруппированный вывод. Этот практический опыт предоставит вам необходимые навыки манипулирования данными в SQLite.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") subgraph Lab Skills sqlite/init_db -.-> lab-552547{{"Группировка данных в SQLite"}} sqlite/make_table -.-> lab-552547{{"Группировка данных в SQLite"}} sqlite/get_all -.-> lab-552547{{"Группировка данных в SQLite"}} sqlite/query_where -.-> lab-552547{{"Группировка данных в SQLite"}} sqlite/sort_data -.-> lab-552547{{"Группировка данных в SQLite"}} end

Создание таблицы Orders и вставка данных

На этом шаге вы создадите базу данных с именем sales.db и таблицу orders в ней. Затем вы вставите пример данных в таблицу. Эта таблица будет использоваться на протяжении всей лабораторной работы для отработки методов группировки данных.

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

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

sqlite3 sales.db

Эта команда откроет оболочку SQLite (SQLite shell), и вы увидите приглашение, похожее на sqlite>.

Далее создайте таблицу orders со столбцами для order_id, customer_id, product_name, quantity и price. Выполните следующую SQL-команду:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_name TEXT,
    quantity INTEGER,
    price REAL
);

Эта команда создает таблицу orders с указанными столбцами и типами данных. Столбец order_id установлен в качестве первичного ключа (primary key).

Теперь вставьте пример данных в таблицу orders. Выполните следующие операторы INSERT один за другим:

INSERT INTO orders (customer_id, product_name, quantity, price) VALUES
(1, 'Laptop', 1, 1200.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 75.00),
(2, 'Monitor', 1, 300.00),
(3, 'Laptop', 1, 1200.00),
(3, 'Headphones', 1, 100.00),
(1, 'Keyboard', 1, 75.00);

Эти команды вставляют семь строк данных в таблицу orders, представляющих различные заказы клиентов.

Чтобы убедиться, что данные были вставлены правильно, вы можете выполнить простой запрос SELECT:

SELECT * FROM orders;

Эта команда отобразит все строки и столбцы в таблице orders.

Агрегация с помощью COUNT и SUM

На этом шаге вы будете использовать агрегатные функции COUNT и SUM для выполнения вычислений над данными в таблице orders. Агрегатные функции позволяют суммировать данные из нескольких строк в один результат.

Вы должны быть по-прежнему подключены к базе данных sales.db с предыдущего шага. Если нет, переподключитесь, используя:

sqlite3 sales.db

Сначала давайте используем функцию COUNT, чтобы определить общее количество заказов в таблице. Выполните следующую SQL-команду:

SELECT COUNT(*) FROM orders;

Этот запрос вернет общее количество строк в таблице orders, что представляет собой общее количество заказов.

Функция COUNT(*) подсчитывает все строки в таблице, независимо от того, содержат ли какие-либо столбцы значения NULL.

Далее давайте используем функцию SUM, чтобы вычислить общее количество заказанных товаров. Выполните следующую SQL-команду:

SELECT SUM(quantity) FROM orders;

Этот запрос вернет сумму значений столбца quantity для всех строк в таблице orders.

Функция SUM суммирует значения в указанном столбце.

Наконец, давайте вычислим общий доход, полученный от всех заказов. Выполните следующую SQL-команду:

SELECT SUM(quantity * price) FROM orders;

Этот запрос умножает столбцы quantity и price для каждой строки, а затем суммирует результаты, давая вам общий доход.

Группировка по отдельным столбцам

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

sqlite3 sales.db

Давайте сгруппируем таблицу orders по customer_id и подсчитаем количество заказов для каждого клиента. Выполните следующую SQL-команду:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;

Этот запрос вернет customer_id и количество заказов (order_count) для каждого уникального клиента. Предложение GROUP BY customer_id указывает SQLite сгруппировать строки на основе значений в столбце customer_id. Функция COUNT(*) затем подсчитывает количество строк в каждой группе.

Далее давайте сгруппируем таблицу orders по product_name и вычислим общее количество заказанных товаров для каждого продукта. Выполните следующую SQL-команду:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;

Этот запрос вернет product_name и общее количество заказанных товаров (total_quantity) для каждого уникального продукта. Предложение GROUP BY product_name указывает SQLite сгруппировать строки на основе значений в столбце product_name. Функция SUM(quantity) затем вычисляет сумму значений столбца quantity для каждой группы.

Наконец, давайте сгруппируем таблицу orders по customer_id и вычислим общий доход, полученный от каждого клиента. Выполните следующую SQL-команду:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id;

Этот запрос вернет customer_id и общий доход (total_revenue), полученный от каждого клиента.

Применение HAVING к группам

На этом шаге вы узнаете, как использовать предложение HAVING для фильтрации групп после того, как они были созданы предложением GROUP BY. Предложение HAVING похоже на предложение WHERE, но оно работает с группами, а не с отдельными строками.

sqlite3 sales.db

Давайте сгруппируем таблицу orders по customer_id и подсчитаем количество заказов для каждого клиента. Затем мы используем предложение HAVING, чтобы отфильтровать результаты и включить только тех клиентов, которые разместили более одного заказа. Выполните следующую SQL-команду:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;

Этот запрос вернет customer_id и количество заказов (order_count) для каждого клиента, который разместил более одного заказа. Предложение GROUP BY customer_id группирует строки по customer_id, а предложение HAVING COUNT(*) > 1 фильтрует группы, чтобы включить только те, где количество заказов больше 1.

Далее давайте сгруппируем таблицу orders по product_name и вычислим общее количество заказанных товаров для каждого продукта. Затем мы используем предложение HAVING, чтобы отфильтровать результаты и включить только те продукты, общее количество заказанных которых больше 1. Выполните следующую SQL-команду:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name HAVING SUM(quantity) > 1;

Этот запрос вернет product_name и общее количество заказанных товаров (total_quantity) для каждого продукта, общее количество заказанных которых больше 1.

Наконец, давайте сгруппируем таблицу orders по customer_id и вычислим общий доход, полученный от каждого клиента. Затем мы используем предложение HAVING, чтобы отфильтровать результаты и включить только тех клиентов, которые принесли доход более 1000 долларов. Выполните следующую SQL-команду:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id HAVING SUM(quantity * price) > 1000;

Этот запрос вернет customer_id и общий доход (total_revenue), полученный от каждого клиента, который принес доход более 1000 долларов.

Сортировка сгруппированного вывода

На этом шаге вы узнаете, как использовать предложение ORDER BY для сортировки вывода запроса, который включает предложение GROUP BY. Сортировка сгруппированного вывода может упростить анализ и понимание данных.

sqlite3 sales.db

Давайте сгруппируем таблицу orders по customer_id и подсчитаем количество заказов для каждого клиента. Затем мы используем предложение ORDER BY, чтобы отсортировать результаты в порядке убывания на основе количества заказов. Выполните следующую SQL-команду:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC;

Этот запрос вернет customer_id и количество заказов (order_count) для каждого клиента, отсортированные в порядке убывания на основе order_count. Предложение GROUP BY customer_id группирует строки по customer_id, а предложение ORDER BY order_count DESC сортирует результаты в порядке убывания на основе псевдонима order_count.

Далее давайте сгруппируем таблицу orders по product_name и вычислим общее количество заказанных товаров для каждого продукта. Затем мы используем предложение ORDER BY, чтобы отсортировать результаты в порядке возрастания на основе названия продукта. Выполните следующую SQL-команду:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name ORDER BY product_name ASC;

Этот запрос вернет product_name и общее количество заказанных товаров (total_quantity) для каждого продукта, отсортированные в порядке возрастания на основе product_name.

Наконец, давайте сгруппируем таблицу orders по customer_id и вычислим общий доход, полученный от каждого клиента. Затем мы используем предложение ORDER BY, чтобы отсортировать результаты в порядке убывания на основе общего дохода. Выполните следующую SQL-команду:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id ORDER BY total_revenue DESC;

Этот запрос вернет customer_id и общий доход (total_revenue), полученный от каждого клиента, отсортированные в порядке убывания на основе total_revenue.

Чтобы выйти из оболочки SQLite (SQLite shell), выполните:

.exit

Итог

В этой лабораторной работе вы научились использовать агрегатные функции, такие как COUNT и SUM, для обобщения данных в SQLite. Вы создали базу данных sales.db с таблицей orders и вставили примеры данных. Затем вы использовали COUNT(*) для определения общего количества заказов и SUM(quantity * price) для расчета общего дохода. Вы также узнали, как группировать данные с помощью предложения GROUP BY, фильтровать группы с помощью предложения HAVING и сортировать вывод с помощью предложения ORDER BY. Эти навыки обеспечивают прочную основу для анализа данных в SQLite.