Last active
November 14, 2019 18:21
-
-
Save aykononov/0620121abb08d037c72593acaac3bd23 to your computer and use it in GitHub Desktop.
Отчёт для отдела маркетинга
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Создаем тестовые данные | |
CREATE TABLE clients (ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, NAME VARCHAR2(256)); | |
INSERT INTO clients (NAME) VALUES ('ООО Экотранс'); | |
INSERT INTO clients (NAME) VALUES ('ООО Аптека'); | |
INSERT INTO clients (NAME) VALUES ('ИП Колесников'); | |
INSERT INTO clients (NAME) VALUES ('ИП Яшин'); | |
INSERT INTO clients (NAME) VALUES ('ИП Иванов'); | |
INSERT INTO clients (NAME) VALUES ('ИП Нулевой'); | |
CREATE TABLE kontakts ( ID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 100, | |
CLIENT_ID NUMBER, -- FK CLIENT | |
C_TYPE NUMBER, -- Тип контакта 1-телефон 2-email | |
C_INFO VARCHAR2(128), -- Контакт – телефон либо адрес email | |
CREATED DATE, -- Дата внесения в базу | |
ACTIVE CHAR(1), -- Y/N активный или архив | |
FOREIGN KEY (CLIENT_ID) | |
REFERENCES clients (ID)); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,1,'+79110110101', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,1,'+79110110102', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,1,'+79110110103', SYSDATE-1,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,2,'ekotrans@mail.com', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,2,'eko-trans@mail.com', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,1,'0000000000000', SYSDATE,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,2,'ekotrans@', SYSDATE,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(2,1,'+79010110111', SYSDATE-1,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(2,2,'apteka1@mail.com', SYSDATE-2,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(2,2,'apteka@mail.com', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(2,1,'790101101112', SYSDATE-2,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(3,1,'+79111111101', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(3,1,'+79110110101', SYSDATE-1,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(3,1,'0000000000000', SYSDATE-2,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(3,2,'ip_kolesnikov@mail.ru', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(4,1,'+79211121101', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(4,1,'+79220110101', SYSDATE-2,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(4,1,'0000000000000', SYSDATE-3,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(4,2,'ip_ya@mail.ru', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(5,1,'+79110000001', SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(5,1,'+79110110101', SYSDATE-1,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(5,1,'0000000000000', SYSDATE-2,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(5,2, NULL, SYSDATE-2,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(6,1,'0000000000000', SYSDATE,'n'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(6,1,NULL, SYSDATE,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(6,2, NULL, SYSDATE-2,'y'); | |
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(6,2, NULL, SYSDATE,'y'); | |
CREATE TABLE address ( ID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 200, | |
CLIENT_ID NUMBER, | |
A_TYPE NUMBER, -- Тип адреса 1-домашний 2-регистрации 3- фактический | |
CITY VARCHAR2(128), -- Город | |
STREET VARCHAR2(128), -- Улица | |
HOUSE VARCHAR2(8), -- Дом | |
FLAT VARCHAR2(8), -- Квартира | |
CREATED DATE, | |
ACTIVE CHAR(1), -- Y/N активный или архив | |
FOREIGN KEY (CLIENT_ID) | |
REFERENCES clients (ID)); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,1,'Москва','Октября','12','8',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,1,'Москва','Октября','12','8',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,2,'Москва','Октября','12','8',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,'Москва','Октября','12','8',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,'Москва','Октября','12','8',SYSDATE-1,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,1,NULL,NULL,'12','8',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,2,'Москва',NULL,'12','8',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,NULL,NULL,'12',NULL,SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,'Москва','Октября','12','8',SYSDATE,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,'Москва','Октября','12','8',SYSDATE-1,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,1,'Уфа','Ленина','1','56',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,2,'Санкт-Питербург','Авиаторов','15','732',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,3,'Москва','Новая','23','115',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,3,'Москва','Октября','23','115',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,3,'Москва','Новая','23','115',SYSDATE-1,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,3,'Москва','Новая','0','0',SYSDATE,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,1,'Уфа','Октября','40','73',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,2,'Москва','Ленина','3','603',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,2,'Москва','Октября','12','8',SYSDATE-1,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,1,'Уфа',NULL,'40','73',SYSDATE,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,2,NULL,'Ленина','3',NULL,SYSDATE,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,2,NULL,'Октября','12','8',SYSDATE-1,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,3,'Москва','Ленина',NULL,'603',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,1,'Стерлитамак','Октября','27','98',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,2,'Стерлитамак','Октября','27','98',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,2,'Стерлитамак','Октября','27','98',SYSDATE-1,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,1,'Уфа',NULL,'40','73',SYSDATE,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,2,NULL,'Октября','3',NULL,SYSDATE,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,2,NULL,NULL,NULL,'8',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,1,'Стерлитамак','Октября','27','98',SYSDATE-2,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,'Стерлитамак','Октября','27','98',SYSDATE-2,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,'Стерлитамак','Октября','27','98',SYSDATE-2,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,1,'Уфа',NULL,'40','73',SYSDATE-3,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,'Стерлитамак','Октября','30','100',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,NULL,'Октября','100',NULL,SYSDATE-2,'n'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,NULL,NULL,NULL,'100',SYSDATE-2,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,3,NULL,NULL,NULL,'100',SYSDATE-2,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,3,'Санкт-Питербург','Ленина','40','700',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,1,NULL,NULL,NULL,'100',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,1,NULL,NULL,NULL,NULL,SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,2,NULL,NULL,NULL,NULL,SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,3,'Санкт-Питербург',NULL,NULL,NULL,SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,1,'Санкт-Питербург',NULL,NULL,'100',SYSDATE,'y'); | |
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,1,'Санкт-Питербург',NULL,'50','100',SYSDATE-1,'y'); | |
-- Отчет для отела маркетинга | |
WITH adr AS ( | |
SELECT a.client_id,a.city, a.street, a.house, a.flat | |
,row_number() OVER(PARTITION BY a.client_id | |
ORDER BY a.a_type DESC | |
,(NVL2(a.city,1,0)+NVL2(a.street,1,0)+NVL2(a.house,1,0)+NVL2(a.flat,1,0))DESC | |
,a.created DESC) AS rn | |
FROM address a | |
WHERE a.active = 'y' | |
), | |
tel AS ( | |
SELECT k.client_id,k.c_info | |
,row_number() OVER(PARTITION BY k.client_id ORDER BY k.created DESC) AS rn | |
FROM kontakts k | |
WHERE k.c_type = 1 | |
AND k.active = 'y' | |
), | |
eml AS ( | |
SELECT k.client_id,k.c_info, | |
row_number() OVER(PARTITION BY k.client_id ORDER BY k.created) AS rn | |
FROM kontakts k | |
WHERE k.c_type = 2 | |
AND k.active = 'y' | |
) | |
SELECT c.name AS "Клиент" | |
,adr.city||','||adr.street||','||adr.house||','||adr.flat AS "Адрес" | |
,tel.c_info AS "Телфон" | |
,eml.c_info AS "email" | |
FROM clients c | |
JOIN adr ON adr.client_id = c.id | |
JOIN tel ON tel.client_id = c.id | |
JOIN eml ON eml.client_id = c.id | |
WHERE adr.rn = 1 | |
AND tel.rn = 1 | |
AND eml.rn = 1 | |
ORDER BY c.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Имеется база данных по клиентам, имеющая структуру: