SQLize Online / PHPize Online  /  SQLtest Online

create table providers (id serial primary key, name varchar(100)); create table manufacturers (id serial primary key, name varchar(100)); create table countries (id serial primary key, name varchar(100)); create table products (id serial primary key, type varchar(100), name varchar(100), year_of_issue date, date_of_sale date, providers_id int references providers(id), manufacturers_id int references manufacturers(id), country_id int references countries(id), price int, target_audience varchar(100)); create table customers (id serial primary key, lastname varchar(100), firstname varchar(100), city varchar(100), products_id int references products(id)); insert into providers (name) values ('ООО "ТехноМаркет"'), ('ЗАО "Энергия Плюс"'), ('ООО "Глобал Трейд"'), ('ПАО "ТрансСнаб"'), ('ЗАО "Альфа Логистик"'), ('ООО "ИнтерСнаб"'), ('ПАО "ЮниТранс"'), ('ООО "Мегаполис-Сервис"'), ('ЗАО "СеверТорг"'), ('ПАО "Логистик-Системс"'); insert into manufacturers (name) values ('ОАО "АвтоМаш"'), ('ЗАО "Электроника-Про"'), ('ООО "Кварц-Тех"'), ('ПАО "ПромСтрой"'), ('ОАО "ГидроСистемы"'), ('ООО "МеталлИндустрия"'), ('ЗАО "ТехноТранс"'), ('ПАО "Энергетика-М"'), ('ООО "Кристалл-Инжиниринг"'), ('ОАО "СтройПроект"'); insert into countries (name) values ('Россия'), ('США'), ('Германия'), ('Франция'), ('Италия'), ('Китай'), ('Япония'), ('Бразилия'), ('Индия'), ('Австралия'); insert into products (id, type, name, year_of_issue, date_of_sale, providers_id, manufacturers_id, country_id, price, target_audience) VALUES (1, 'духи', 'Chanel No. 5', '1921-01-01', '2024-12-13', 1, 1, 1, 15000, 'женщины'), (2, 'духи', 'Dior Sauvage', '2015-01-01', '2020-11-13', 2, 2, 2, 12000, 'мужчины'), (3, 'туалетная вода', 'Acqua di Gio', '1996-01-01', '2021-10-01', 3, 3, 3, 8000, 'мужчины'), (4, 'парфюмированная вода', 'Guerlain La Petite Robe Noire', '2012-01-01', '2002-05-20', 4, 4, 4, 9500, 'женщины'), (5, 'духи', 'Yves Saint Laurent Black Opium', '2014-01-01', '2018-03-14', 5, 5, 5, 11000, 'женщины'), (6, 'туалетная вода', 'Hugo Boss Bottled', '1998-01-01', '2000-11-13', 6, 6, 6, 9000, 'мужчины'), (7, 'парфюмированная вода', 'Tom Ford Black Orchid', '2006-01-01', '2024-01-17', 7, 7, 7, 14000, 'женщины'), (8, 'духи', 'Chanel Bleu de Chanel', '2010-01-01', '2024-12-13', 1, 1, 1, 13500, 'мужчины'), (9, 'туалетная вода', 'CK One', '1994-01-01', '2023-08-15', 8, 8, 8, 5000, 'дети'), (10, 'парфюмированная вода', 'Lancome La Vie Est Belle', '2012-01-01', '2013-08-30', 9, 9, 9, 10500, 'женщины'), (11, 'духи', 'Creed Aventus', '2010-01-01', '2019-12-19', 10, 10, 10, 25000, 'мужчины'), (12, 'туалетная вода', 'Davidoff Cool Water', '1988-01-01', '2024-12-13', 1, 2, 3, 4500, 'мужчины'), (13, 'парфюмированная вода', 'Chloe Eau de Parfum', '2008-01-01', '2012-09-29', 4, 3, 4, 11500, 'женщины'), (14, 'духи', 'Tom Ford Tobacco Vanille', '2007-01-01', '2007-12-31', 5, 6, 7, 22000, 'дети'), (15, 'туалетная вода', 'Viktor and Rolf Flowerbomb', '2005-01-01', '2024-12-20', 6, 8, 8, 13000, 'женщины'), (16, 'парфюмированная вода', 'Bvlgari Omnia Crystalline', '2005-01-01', '2007-11-22', 7, 9, 9, 9500, 'женщины'), (17, 'духи', 'Hermes Terre', '2006-01-01', '2009-01-25', 8, 10, 1, 17000, 'мужчины'), (18, 'туалетная вода', 'Jean Paul Gaultier Le Male', '1995-01-01', '2000-02-27', 9, 1, 2, 8000, 'мужчины'), (19, 'парфюмированная вода', 'Prada Candy', '2011-01-01', '2024-06-06', 10, 4, 6, 12000, 'женщины'), (20, 'духи', 'Baby Dior', '2013-01-01', '2023-04-11', 2, 5, 3, 5500, 'дети'); insert into customers (id, lastname, firstname, city, products_id) VALUES (1, 'Иванов', 'Алексей', 'Москва', 1), (2, 'Петров', 'Иван', 'Санкт-Петербург', 2), (3, 'Смирнова', 'Мария', 'Новосибирск', 3), (4, 'Кузнецова', 'Екатерина', 'Екатеринбург', 4), (5, 'Попова', 'Анна', 'Нижний Новгород', 5), (6, 'Васильев', 'Михаил', 'Краснодар', 6), (7, 'Михайлова', 'Ольга', 'Ростов-на-Дону', 7), (8, 'Новиков', 'Дмитрий', 'Казань', 8), (9, 'Ковалев', 'Юрий', 'Челябинск', 9), (10, 'Соловьева', 'Ирина', 'Воронеж', 10), (11, 'Григорьев', 'Петр', 'Уфа', 11), (12, 'Дмитриева', 'Светлана', 'Пермь', 12), (13, 'Федорова', 'Татьяна', 'Красноярск', 13), (14, 'Сергеев', 'Андрей', 'Тюмень', 14), (15, 'Шевченко', 'Наталья', 'Омск', 15), (16, 'Беляев', 'Владимир', 'Саратов', 16), (17, 'Захарова', 'Елена', 'Волгоград', 17), (18, 'Яковлев', 'Константин', 'Тверь', 18), (19, 'Крылова', 'Марина', 'Ярославль', 19), (20, 'Романов', 'Григорий', 'Арзамас', 20); ALTER TABLE products ADD COLUMN shipment_date DATE, -- Дата отгрузки товара ADD COLUMN regular_supply BOOLEAN; -- Флаг регулярной поставки UPDATE products SET shipment_date = '2024-01-15', regular_supply = TRUE WHERE id = 1; UPDATE products SET shipment_date = '2020-11-10', regular_supply = FALSE WHERE id = 2; UPDATE products SET shipment_date = '2021-10-01', regular_supply = TRUE WHERE id = 3; UPDATE products SET shipment_date = '2022-05-20', regular_supply = FALSE WHERE id = 4; UPDATE products SET shipment_date = '2018-03-14', regular_supply = TRUE WHERE id = 5; UPDATE products SET shipment_date = '2000-11-13', regular_supply = FALSE WHERE id = 6; UPDATE products SET shipment_date = '2024-01-17', regular_supply = TRUE WHERE id = 7; UPDATE products SET shipment_date = '2024-12-13', regular_supply = TRUE WHERE id = 8; UPDATE products SET shipment_date = '2023-08-15', regular_supply = FALSE WHERE id = 9; UPDATE products SET shipment_date = '2013-08-30', regular_supply = TRUE WHERE id = 10; UPDATE products SET shipment_date = '2019-12-19', regular_supply = FALSE WHERE id = 11; UPDATE products SET shipment_date = '2024-12-13', regular_supply = TRUE WHERE id = 12; UPDATE products SET shipment_date = '2012-09-29', regular_supply = FALSE WHERE id = 13; UPDATE products SET shipment_date = '2007-12-31', regular_supply = TRUE WHERE id = 14; UPDATE products SET shipment_date = '2024-12-20', regular_supply = FALSE WHERE id = 15; UPDATE products SET shipment_date = '2007-11-22', regular_supply = TRUE WHERE id = 16; UPDATE products SET shipment_date = '2009-01-25', regular_supply = FALSE WHERE id = 17; UPDATE products SET shipment_date = '2000-02-27', regular_supply = TRUE WHERE id = 18; UPDATE products SET shipment_date = '2024-06-06', regular_supply = FALSE WHERE id = 19; UPDATE products SET shipment_date = '2023-04-11', regular_supply = TRUE WHERE id = 20; CREATE OR REPLACE FUNCTION calculate_regular_supply_share_by_name( product_name VARCHAR, -- Название парфюмерии input_shipment_date DATE -- Заданный день отгрузки ) RETURNS NUMERIC AS $$ DECLARE total_count INT; -- Общее количество поставок для данной парфюмерии regular_count INT; -- Количество регулярных поставок для данной парфюмерии share NUMERIC; -- Доля регулярных поставок BEGIN -- Вычисление общего количества поставок для данной парфюмерии по указанной дате SELECT COUNT(*) INTO total_count FROM products WHERE name = product_name -- Фильтруем по названию парфюмерии AND shipment_date <= input_shipment_date; -- Поставки до или на указанную дату -- Если общее количество поставок равно 0, возвращаем 0 IF total_count = 0 THEN RETURN 0; END IF; -- Вычисление количества регулярных поставок для данной парфюмерии по указанной дате SELECT COUNT(*) INTO regular_count FROM products WHERE name = product_name -- Фильтруем по названию парфюмерии AND shipment_date <= input_shipment_date AND regular_supply = TRUE; -- Поставки, которые являются регулярными -- Расчёт доли регулярных поставок share := regular_count::NUMERIC / total_count::NUMERIC; -- Возвращаем долю RETURN share; END; $$ LANGUAGE plpgsql; SELECT calculate_regular_supply_share_by_name('Chanel No. 5', '2024-10-01');