Лучший контакт по долгу

Показать лучший контакт (лучший контакт определяется как минимум по полю CONTACT_IMP.IMP, то есть, если должник «Подтвердил обещание», то это лучше, чем когда должник говорит нам «Перезвоните») на конец работы каждого месяца по долгу.

Имеется таблица важностей контактов по долгу CONTACT_IMP (связывается с таблицей контактов: CONTACT.RESULT_CODE = CONTACT_IMP.RESULT_CODE and CONTACT. CONTACT_TYPE = CONTACT_IMP.CONTACT_TYPE).
Создадим необходимые объекты (таблицы, ограничения, индексы):

CREATE TABLE "CONTACT" 
("ID_CONTACT" INTEGER, 
  "PERSON_TYPE" INTEGER, 
  "ID_DEBT" INTEGER,
  "DATE_CONTACT" DATE,
  "BOUND_TYPE" VARCHAR2(64),
  "RESULT_CODE" VARCHAR2(64),
  "CONTACT_TYPE" VARCHAR2(64),
  "PROMISE_SUM" NUMBER
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON COLUMN "CONTACT"."ID_CONTACT" IS 'Идентификатор контакта';
COMMENT ON COLUMN "CONTACT"."PERSON_TYPE" IS 'Тип лица (физическое/юридическое)';
COMMENT ON COLUMN "CONTACT"."ID_DEBT" IS 'Идентификатор долга';
COMMENT ON COLUMN "CONTACT"."DATE_CONTACT" IS 'Дата контакта';
COMMENT ON COLUMN "CONTACT"."BOUND_TYPE" IS 'Тип соединения (входящее/исходящее)';
COMMENT ON COLUMN "CONTACT"."RESULT_CODE" IS 'Результат контакта';
COMMENT ON COLUMN "CONTACT"."CONTACT_TYPE" IS 'Тип контакта';
COMMENT ON COLUMN "CONTACT"."PROMISE_SUM" IS 'Сумма обещания';
COMMENT ON TABLE "CONTACT" IS 'Контакт по долгу';
 
CREATE UNIQUE INDEX "CONTACT_U_IDX" ON "CONTACT" ("ID_CONTACT") TABLESPACE MIRAVILD_INDEX;

ALTER TABLE "CONTACT" ADD CONSTRAINT "CONTACT_PK" PRIMARY KEY ("ID_CONTACT")
  USING INDEX "CONTACT_U_IDX" ENABLE VALIDATE;
  
ALTER TABLE "CONTACT" MODIFY ("PERSON_TYPE" CONSTRAINT CONTACT_PERSON_TYPE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT" MODIFY ("ID_DEBT" CONSTRAINT CONTACT_ID_DEBT_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT" MODIFY ("DATE_CONTACT" CONSTRAINT CONTACT_DATE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT" MODIFY ("BOUND_TYPE" CONSTRAINT CONTACT_BOUND_TYPE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT" MODIFY ("RESULT_CODE" CONSTRAINT CONTACT_RESULT_CODE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT" MODIFY ("CONTACT_TYPE" CONSTRAINT CONTACT_TYPE_NN NOT NULL ENABLE);
/

CREATE TABLE "CONTACT_IMP" 
("CONTACT_TYPE" VARCHAR2(64),
 "RPC_TYPE" CHAR(3),
 "RESULT_CODE" VARCHAR2(64), 
  "IMP" INTEGER, 
  "RESULT_ID" NUMBER
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON COLUMN "CONTACT_IMP"."CONTACT_TYPE" IS 'Тип контакта';
COMMENT ON COLUMN "CONTACT_IMP"."RPC_TYPE" IS 'Тип';
COMMENT ON COLUMN "CONTACT_IMP"."RESULT_CODE" IS 'Результакт контакта';
COMMENT ON COLUMN "CONTACT_IMP"."IMP" IS 'Идентификатор важности';
COMMENT ON COLUMN "CONTACT_IMP"."RESULT_ID" IS 'Идентификатор результата';
COMMENT ON TABLE "CONTACT_IMP" IS 'Важность контакта по долгу';
 
CREATE UNIQUE INDEX "CONTACT_IMP_U_IDX" ON "CONTACT_IMP" ("IMP") TABLESPACE MIRAVILD_INDEX;

ALTER TABLE "CONTACT_IMP" ADD CONSTRAINT "CONTACT_IMP_PK" PRIMARY KEY ("IMP")
  USING INDEX "CONTACT_IMP_U_IDX" ENABLE VALIDATE;
  
ALTER TABLE "CONTACT_IMP" MODIFY ("CONTACT_TYPE" CONSTRAINT CONTACT_IMP_CONTACT_TYPE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT_IMP" MODIFY ("RPC_TYPE" CONSTRAINT CONTACT_IMP_RPC_TYPE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT_IMP" MODIFY ("RESULT_CODE" CONSTRAINT CONTACT_IMP_RESULT_CODE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT_IMP" MODIFY ("RESULT_ID" CONSTRAINT CONTACT_IMP_RESULT_ID_NN NOT NULL ENABLE);
/

Добавим тестовые данные:

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (1,
   1,
   123,
   to_date('02.05.2014', 'DD.MM.YYYY'),
   'INBOUND',
   'Необходимо перезвонить',
   'Контакт с должником',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (2,
   1,
   123,
   to_date('06.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Обещание оплаты',
   'Контакт с 3-м лицом',
   1000);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (3,
   1,
   123,
   to_date('12.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Бросили трубку',
   'Контакт с должником',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (4,
   2,
   123,
   to_date('17.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Необходимо перезвонить',
   'Контакт с 3-м лицом',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (5,
   1,
   123,
   to_date('21.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Бросили трубку',
   'Контакт с должником',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (6,
   1,
   123,
   to_date('31.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Бросили трубку',
   'Контакт с 3-м лицом',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (7,
   1,
   345,
   to_date('02.05.2014', 'DD.MM.YYYY'),
   'INBOUND',
   'Необходимо перезвонить',
   'Контакт с 3-м лицом',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (8,
   1,
   345,
   to_date('06.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Необходимо перезвонить',
   'Контакт с 3-м лицом',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (9,
   1,
   345,
   to_date('17.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Необходимо перезвонить',
   'Контакт с 3-м лицом',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (10,
   2,
   345,
   to_date('17.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Обещание оплаты',
   'Контакт с должником',
   3000);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (11,
   1,
   345,
   to_date('21.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Необходимо перезвонить',
   'Контакт с должником',
   NULL);

INSERT INTO contact c
  (c.id_contact,
   c.person_type,
   c.id_debt,
   c.date_contact,
   c.bound_type,
   c.result_code,
   c.contact_type,
   c.promise_sum)
VALUES
  (12,
   1,
   345,
   to_date('31.05.2014', 'DD.MM.YYYY'),
   'OUTBOUND',
   'Необходимо перезвонить',
   'Контакт с должником',
   NULL);

COMMIT WORK;
/

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с должником',
   'RPC',
   'Подтверждение оплаты',
   1,
   -9999);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'TPC',
   'Подтверждение оплаты',
   2,
   -9999);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с должником',
   'RPC',
   'Подтвердил обещание',
   3,
   34);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'RPC',
   'Подтвердил обещание',
   4,
   34);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с должником',
   'RPC',
   'Отсрочка платежа',
   5,
   87);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с должником',
   'RPC',
   'Обещание оплаты',
   6,
   1);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'TPC',
   'Обещание оплаты',
   7,
   1);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с должником',
   'RPC',
   'Необходимо перезвонить',
   8,
   12);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'TPC',
   'Отсрочка платежа',
   9,
   87);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'TPC',
   'Hеобходимо перезвонить',
   10,
   12);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'TPC',
   'Обещали передать информацию',
   11,
   8);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с должником',
   'RPC',
   'Бросили трубку',
   12,
   14);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'TPC',
   'Отказ передавать информацию',
   13,
   9);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'TPC',
   'Бросили трубку',
   14,
   14);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с 3-м лицом',
   'TPC',
   'Должник умер',
   15,
   -9999);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с должником',
   'RPC',
   'Отказывается платить',
   16,
   2);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с неизвестным лицом',
   'RPC',
   'Необходимо перезвонить',
   17,
   12);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с неизвестным лицом',
   'RPC',
   'Должника не знают',
   18,
   7);

INSERT INTO contact_imp i
  (i.contact_type, i.rpc_type, i.result_code, i.imp, i.result_id)
VALUES
  ('Контакт с неизвестным лицом',
   'RPC',
   'Бросил трубку',
   19,
   14);

COMMIT WORK;
/

Варианты запроса:

SELECT c.id_debt, c.last_day, MIN(c.imp) AS best_contact
  FROM (SELECT c.id_debt, last_day(c.date_contact) AS last_day, i.imp
          FROM contact_imp i
          JOIN contact c
            ON c.result_code = i.result_code
           AND c.contact_type = i.contact_type) c
 GROUP BY c.id_debt, c.last_day
 ORDER BY c.id_debt;
/

SELECT DISTINCT c.id_debt,
                  c.last_day,
                  MIN(c.imp) over(PARTITION BY c.id_debt, c.last_day) AS best_contact
    FROM (SELECT c.id_debt, last_day(c.date_contact) AS last_day, i.imp
            FROM contact_imp i
            JOIN contact c
              ON c.result_code = i.result_code
             AND c.contact_type = i.contact_type) c
   ORDER BY c.id_debt;
/

P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776

Tags: Oracle, SQL

You must be logged in to post a comment.

Miravild

Лучший контакт по долгу

Leave a Reply