init_scripts/clickhouse/init.sql - master - igormayer/sql_agent_sample_db

init_scripts/clickhouse/init.sql
106 строк9 KB
Igor Mayer
postgres and clickhouse dbs added
27 янв 2026, 19:17
27 янв 2026, 19:176051787
-- DDL СКРИПТЫ (СОЗДАНИЕ СТРУКТУРЫ БД И КОММЕНТАРИИ)

-- 1. Таблица `seller_type` (Типы продавцов)
CREATE TABLE IF NOT EXISTS test_db.seller_type (
    seller_type_id UInt8 COMMENT 'Уникальный идентификатор типа продавца.',
    seller_type_name String COMMENT 'Наименование типа продавца (например, "Индивидуальный предприниматель").'
) ENGINE = MergeTree()
ORDER BY seller_type_id
COMMENT 'Справочник, содержащий типы продавцов (например, ИП, ООО, Самозанятый). Используется для категоризации владельцев точек продаж.';

-- 2. Таблица `sellers` (Продавцы/Владельцы)
CREATE TABLE IF NOT EXISTS test_db.sellers (
    seller_id UInt16 COMMENT 'Уникальный идентификатор продавца/владельца.',
    seller_name String COMMENT 'Полное наименование или ФИО продавца.',
    seller_type_id UInt8 COMMENT 'Внешний ключ к таблице seller_type, определяющий юридический тип продавца.'
) ENGINE = MergeTree()
ORDER BY seller_id
COMMENT 'Основная таблица с информацией о продавцах или владельцах бизнеса, которым принадлежат точки продаж.';

-- 3. Таблица `locations` (Точки продаж)
CREATE TABLE IF NOT EXISTS test_db.locations (
    location_id UInt16 COMMENT 'Уникальный идентификатор точки продаж.',
    seller_id UInt16 COMMENT 'Внешний ключ к таблице sellers, указывающий владельца данной точки продаж.',
    location_name String COMMENT 'Рабочее наименование точки продаж (например, "Магазин на Ленина", "Онлайн-склад №1").',
    city String COMMENT 'Город, в котором расположена точка продаж. Используется для географической аналитики.',
    region String COMMENT 'Регион или область, в которой расположена точка продаж. Используется для региональной аналитики.'
) ENGINE = MergeTree()
ORDER BY (location_id, region, city) -- Оптимизируем сортировку для аналитики по регионам
COMMENT 'Таблица, содержащая информацию о физических или виртуальных точках продаж (магазинах, киосках, филиалах).';

-- 4. Таблица `sales_by_day_and_location` (Агрегированные продажи)
CREATE TABLE IF NOT EXISTS test_db.sales_by_day_and_location (
    sale_date Date COMMENT 'Дата, за которую агрегированы данные о продажах.',
    location_id UInt16 COMMENT 'Внешний ключ к таблице locations, указывающий точку продаж, к которой относятся данные.',
    total_amount Decimal(10, 2) COMMENT 'Общая сумма продаж за день (наличные + безналичные).',
    cash_amount Decimal(10, 2) COMMENT 'Сумма продаж, оплаченных наличными средствами.',
    card_amount Decimal(10, 2) COMMENT 'Сумма продаж, оплаченных банковскими картами (безналичный расчет).',
    tax_type_1_amount Decimal(10, 2) COMMENT 'Сумма налога 1-го типа (например, НДС 20%), включенного в общую сумму продаж.',
    tax_type_2_amount Decimal(10, 2) COMMENT 'Сумма налога 2-го типа (например, НДС 10% или местный налог), включенного в общую сумму продаж.'
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(sale_date) -- Партиционирование по месяцам для ускорения запросов по датам
ORDER BY (sale_date, location_id) -- Ключ сортировки по дате и локации
COMMENT 'Агрегированная таблица, хранящая ежедневные сводные данные о продажах по каждой точке продаж. Используется для построения отчетов и аналитики.';

-- DML СКРИПТЫ (НАПОЛНЕНИЕ ТЕСТОВЫМИ ДАННЫМИ)

-- Очистка таблиц перед заполнением (если необходимо)
TRUNCATE TABLE test_db.seller_type;
TRUNCATE TABLE test_db.sellers;
TRUNCATE TABLE test_db.locations;
TRUNCATE TABLE test_db.sales_by_day_and_location;

-- 1. Наполнение `seller_type` (3 записи)
INSERT INTO test_db.seller_type (seller_type_id, seller_type_name) VALUES
(1, 'ИП (Индивидуальный предприниматель)'),
(2, 'ООО (Общество с ограниченной ответственностью)'),
(3, 'Самозанятый');

-- 2. Наполнение `sellers` (10 записей)
INSERT INTO test_db.sellers (seller_id, seller_name, seller_type_id) VALUES
(1, 'Иванов Иван Иванович', 1),
(2, 'ООО "Ромашка"', 2),
(3, 'Петров Петр Петрович', 3),
(4, 'Сидоров Сидор Сидорович', 1),
(5, 'ООО "Василек"', 2),
(6, 'Кузнецов К.К.', 1),
(7, 'ООО "Заря"', 2),
(8, 'Алексеев А.А.', 3),
(9, 'Михайлов М.М.', 1),
(10, 'ООО "Альфа"', 2);

-- 3. Наполнение `locations` (20 записей)
INSERT INTO test_db.locations (location_id, seller_id, location_name, city, region) VALUES
(1, 1, 'Магазин "У дома" A', 'Москва', 'Московская область'),
(2, 1, 'Киоск "Экспресс" Б', 'Москва', 'Московская область'),
(3, 2, 'Центральный офис продаж', 'Санкт-Петербург', 'Ленинградская область'),
(4, 3, 'Онлайн-склад (МСК)', 'Москва', 'Московская область'),
(5, 4, 'Точка продаж 1', 'Казань', 'Татарстан'),
(6, 4, 'Точка продаж 2', 'Казань', 'Татарстан'),
(7, 5, 'Филиал Юг', 'Краснодар', 'Краснодарский край'),
(8, 6, 'Пункт выдачи №1', 'Новосибирск', 'Новосибирская область'),
(9, 7, 'Офис на Невском', 'Санкт-Петербург', 'Ленинградская область'),
(10, 8, 'Услуги на дому', 'Екатеринбург', 'Свердловская область'),
(11, 9, 'Склад-магазин №3', 'Москва', 'Московская область'),
(12, 10, 'Офис продаж Восток', 'Владивосток', 'Приморский край'),
(13, 1, 'Магазин "У дома" В', 'Подольск', 'Московская область'),
(14, 2, 'Склад временного хранения', 'Санкт-Петербург', 'Ленинградская область'),
(15, 5, 'Филиал Север', 'Мурманск', 'Мурманская область'),
(16, 7, 'Офис на Садовой', 'Санкт-Петербург', 'Ленинградская область'),
(17, 9, 'Пункт выдачи №2', 'Новосибирск', 'Новосибирская область'),
(18, 10, 'Офис продаж Запад', 'Калининград', 'Калининградская область'),
(19, 1, 'Магазин "У дома" Г', 'Москва', 'Московская область'),
(20, 2, 'Киоск "Экспресс" В', 'Москва', 'Московская область');


-- 4. Наполнение `sales_by_day_and_location` (Генерация данных за 2025 и 2026 годы)
INSERT INTO test_db.sales_by_day_and_location SELECT
    toDate('2025-01-01') + floor(rand() % (toDate('2026-12-31') - toDate('2025-01-01') + 1)) AS sale_date,
    floor(rand() % 20) + 1 AS location_id,
    round(rand() / 1000 * 50000, 2) AS total_amount,
    round(rand() / 1000 * 50000 * 0.6, 2) AS cash_amount, -- Примерно 60% наличкой
    round(rand() / 1000 * 50000 * 0.4, 2) AS card_amount, -- Примерно 40% картой
    round(total_amount * 0.20, 2) AS tax_type_1_amount,
    round(total_amount * 0.05, 2) AS tax_type_2_amount
FROM system.numbers
LIMIT 1000; -- Генерируем 1000 случайных записей