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

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

-- 1. Таблица `seller_type` (Типы продавцов)
CREATE TABLE seller_type (
    seller_type_id SERIAL PRIMARY KEY,
    seller_type_name VARCHAR(100) NOT NULL
);

COMMENT ON TABLE seller_type IS 'Справочник, содержащий типы продавцов (например, ИП, ООО, Самозанятый). Используется для категоризации владельцев точек продаж.';
COMMENT ON COLUMN seller_type.seller_type_id IS 'Уникальный идентификатор типа продавца.';
COMMENT ON COLUMN seller_type.seller_type_name IS 'Наименование типа продавца (например, "Индивидуальный предприниматель").';

-- 2. Таблица `sellers` (Продавцы)
CREATE TABLE sellers (
    seller_id SERIAL PRIMARY KEY,
    seller_name VARCHAR(255) NOT NULL,
    seller_type_id INT NOT NULL REFERENCES seller_type(seller_type_id)
);

COMMENT ON TABLE sellers IS 'Основная таблица с информацией о продавцах или владельцах бизнеса, которым принадлежат точки продаж.';
COMMENT ON COLUMN sellers.seller_id IS 'Уникальный идентификатор продавца/владельца.';
COMMENT ON COLUMN sellers.seller_name IS 'Полное наименование или ФИО продавца.';
COMMENT ON COLUMN sellers.seller_type_id IS 'Внешний ключ к таблице seller_type, определяющий юридический тип продавца.';

-- 3. Таблица `locations` (Точки продаж)
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    seller_id INT NOT NULL REFERENCES sellers(seller_id),
    location_name VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    region VARCHAR(100) NOT NULL
);

COMMENT ON TABLE locations IS 'Таблица, содержащая информацию о физических или виртуальных точках продаж (магазинах, киосках, филиалах).';
COMMENT ON COLUMN locations.location_id IS 'Уникальный идентификатор точки продаж.';
COMMENT ON COLUMN locations.seller_id IS 'Внешний ключ к таблице sellers, указывающий владельца данной точки продаж.';
COMMENT ON COLUMN locations.location_name IS 'Рабочее наименование точки продаж (например, "Магазин на Ленина", "Онлайн-склад №1").';
COMMENT ON COLUMN locations.city IS 'Город, в котором расположена точка продаж. Используется для географической аналитики.';
COMMENT ON COLUMN locations.region IS 'Регион или область, в которой расположена точка продаж. Используется для региональной аналитики.';

-- 4. Таблица `sales_by_day_and_location` (Агрегированные продажи)
CREATE TABLE sales_by_day_and_location (
    sale_date DATE NOT NULL,
    location_id INT NOT NULL REFERENCES locations(location_id),
    total_amount NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    cash_amount NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    card_amount NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    tax_type_1_amount NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    tax_type_2_amount NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (sale_date, location_id) -- Составной первичный ключ
);

COMMENT ON TABLE sales_by_day_and_location IS 'Агрегированная таблица, хранящая ежедневные сводные данные о продажах по каждой точке продаж. Используется для построения отчетов и аналитики.';
COMMENT ON COLUMN sales_by_day_and_location.sale_date IS 'Дата, за которую агрегированы данные о продажах.';
COMMENT ON COLUMN sales_by_day_and_location.location_id IS 'Внешний ключ к таблице locations, указывающий точку продаж, к которой относятся данные.';
COMMENT ON COLUMN sales_by_day_and_location.total_amount IS 'Общая сумма продаж за день (наличные + безналичные).';
COMMENT ON COLUMN sales_by_day_and_location.cash_amount IS 'Сумма продаж, оплаченных наличными средствами.';
COMMENT ON COLUMN sales_by_day_and_location.card_amount IS 'Сумма продаж, оплаченных банковскими картами (безналичный расчет).';
COMMENT ON COLUMN sales_by_day_and_location.tax_type_1_amount IS 'Сумма налога 1-го типа (например, НДС 20%), включенного в общую сумму продаж.';
COMMENT ON COLUMN sales_by_day_and_location.tax_type_2_amount IS 'Сумма налога 2-го типа (например, НДС 10% или местный налог), включенного в общую сумму продаж.';

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

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

-- 2. Наполнение `sellers` (10 записей)
INSERT INTO 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);
SELECT setval('sellers_seller_id_seq', 10); -- Обновляем счетчик PK

-- 3. Наполнение `locations` (20 записей)
INSERT INTO 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, 'Киоск "Экспресс" В', 'Москва', 'Московская область');
SELECT setval('locations_location_id_seq', 20); -- Обновляем счетчик PK

-- 4. Наполнение `sales_by_day_and_location` (1000 записей)
-- Генерируем тестовые данные процедурно. 
DO $$
DECLARE
    -- Определяем временной диапазон (2025 и 2026 годы)
    start_date DATE := '2025-01-01'::DATE;
    end_date DATE := '2026-12-31'::DATE;
    
    -- Переменные для хранения случайных значений
    random_date DATE;
    random_location_id INT;
    v_total NUMERIC(10, 2);
    v_cash NUMERIC(10, 2);
    v_card NUMERIC(10, 2);
    v_tax1 NUMERIC(10, 2);
    v_tax2 NUMERIC(10, 2);
    
    -- Всего дней в диапазоне (731 день: 2025+2026)
    days_in_range INT := end_date - start_date + 1;
    -- Количество точек продаж (предполагаем, что их 20)
    locations_count INT := 20;

BEGIN
    -- Гарантированная очистка таблицы перед вставкой
    TRUNCATE TABLE sales_by_day_and_location RESTART IDENTITY;

    -- Генерируем 1000 случайных записей, чтобы покрыть 2 года неравномерно
    FOR i IN 1..1000 LOOP
        
        -- 1. Выбираем случайную дату в диапазоне 2025-2026 гг.
        -- RAND() * days_in_range дает случайное число дней от начала диапазона
        random_date := start_date + floor(random() * days_in_range)::int;

        -- 2. Выбираем случайную точку продаж (от 1 до 20)
        random_location_id := floor(random() * locations_count)::int + 1;

        -- 3. Генерируем случайные данные для сумм
        v_total := ROUND((RANDOM() * 50000)::numeric, 2); 
        v_cash := ROUND((RANDOM() * v_total)::numeric, 2);
        v_card := v_total - v_cash;
        v_tax1 := ROUND((v_total * 0.20)::numeric, 2);
        v_tax2 := ROUND((v_total * 0.05)::numeric, 2);

        -- Используем ON CONFLICT, так как случайный выбор может привести к дублированию пар (дата, локация)
        INSERT INTO sales_by_day_and_location (
            sale_date, 
            location_id, 
            total_amount, 
            cash_amount, 
            card_amount, 
            tax_type_1_amount, 
            tax_type_2_amount
        ) VALUES (
            random_date, 
            random_location_id, 
            v_total, 
            v_cash, 
            v_card, 
            v_tax1,
            v_tax2
        )
        ON CONFLICT (sale_date, location_id) DO UPDATE SET
            -- Если такая запись уже есть (случайно сгенерировали ту же дату/локацию), обновляем данные
            total_amount = EXCLUDED.total_amount,
            cash_amount = EXCLUDED.cash_amount,
            card_amount = EXCLUDED.card_amount;
            
    END LOOP;
END $$;