Моделирование. Инцидент и финансовый эффект

Логическая модель

Логическая модель

Физическая модель

Физическая модель

В таблице «ИНЦИДЕНТ»/«INCIDENT» содержится информация по произошедшим в банке инцидентам с 2003 по 2006 года.

  • Поле «INCIDENT_NUMBER» — уникальный номер инцидента.
  • Поле «INCIDENT_DATE» — дата, когда инцидент был обнаружен сотрудником банка.
  • Поле «INCIDENT_EMPLOYEE» — сотрудник банка, обнаруживший инцидент.

В таблице «ФИНАНСОВЫЙ ЭФФЕКТ»/«FINANCIAL_EFFECT» представлена информация по потерям по каждому из инцидентов. Поле «INCIDENT_NUMBER» таблицы «INCIDENT» является primary key поля «INCIDENT_NUMBER» таблицы «FINANCIAL_EFFECT».

Создадим необходимые таблицы:

CREATE TABLE "INCIDENT"
("INCIDENT_NUMBER" VARCHAR2(64),
"INCIDENT_DATE" DATE,
"INCIDENT_EMPLOYEE" VARCHAR2(256)) TABLESPACE MIRAVILD_DATA;

COMMENT ON TABLE "INCIDENT" IS 'Информация по произошедшим в банке инцидентам с 2003 по 2006 года';
COMMENT ON COLUMN "INCIDENT"."INCIDENT_NUMBER" IS 'Уникальный номер инцидента';
COMMENT ON COLUMN "INCIDENT"."INCIDENT_DATE" IS 'Дата, когда инцидент был обнаружен сотрудником банка';
COMMENT ON COLUMN "INCIDENT"."INCIDENT_EMPLOYEE" IS 'Cотрудник банка, обнаруживший инцидент';

CREATE UNIQUE INDEX "INCIDENT_U_IDX" ON "INCIDENT" ("INCIDENT_NUMBER") TABLESPACE MIRAVILD_INDEX;

ALTER TABLE "INCIDENT" ADD CONSTRAINT "INCIDENT_PK" PRIMARY KEY ("INCIDENT_NUMBER")
  USING INDEX "INCIDENT_U_IDX" ENABLE VALIDATE;
  
ALTER TABLE "INCIDENT" MODIFY ("INCIDENT_DATE" CONSTRAINT INCIDENT_DATE_NN NOT NULL ENABLE);
ALTER TABLE "INCIDENT" MODIFY ("INCIDENT_EMPLOYEE" CONSTRAINT INCIDENT_EMPLOYEE_NN NOT NULL ENABLE);
/
CREATE TABLE "FINANCIAL_EFFECT"
("INCIDENT_NUMBER" VARCHAR2(64),
"LOSS" NUMERIC(11)) TABLESPACE MIRAVILD_DATA;

COMMENT ON TABLE "FINANCIAL_EFFECT" IS 'Финансовый эффект';
COMMENT ON COLUMN "FINANCIAL_EFFECT"."LOSS" IS 'Информация о потери по каждому из инцидентов';

ALTER TABLE "FINANCIAL_EFFECT" ADD CONSTRAINT "INCIDENT_EFFECT_FK" FOREIGN KEY ("INCIDENT_NUMBER") 
REFERENCES "INCIDENT" ("INCIDENT_NUMBER") ENABLE;

ALTER TABLE "FINANCIAL_EFFECT" MODIFY ("INCIDENT_NUMBER" CONSTRAINT EFFECT_INCIDENT_NUMBER_NN NOT NULL ENABLE);
ALTER TABLE "FINANCIAL_EFFECT" MODIFY ("LOSS" CONSTRAINT FINANCIAL_EFFECT_LOSS_NN NOT NULL ENABLE);
/

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

INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)
VALUES
  ('EVE-01215', to_date('07.12.2003', 'dd.mm.yyyy'), 'Heather Ainley');

INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)
VALUES
  ('EVE-01216', to_date('14.02.2005', 'dd.mm.yyyy'), 'Sacha Galloway');
INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)

VALUES
  ('EVE-01217', to_date('29.06.2004', 'dd.mm.yyyy'), 'Sacha Galloway');
INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)

VALUES
  ('EVE-01218', to_date('19.01.2003', 'dd.mm.yyyy'), 'Patricia Pittaway');
INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)

VALUES
  ('EVE-01219', to_date('22.09.2003', 'dd.mm.yyyy'), 'Peyman Mestchian');
INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)

VALUES
  ('EVE-01220', to_date('29.01.2004', 'dd.mm.yyyy'), 'Patricia Pittaway');
INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)

VALUES
  ('EVE-01221', to_date('11.10.2004', 'dd.mm.yyyy'), 'Raj Ray');
INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)

VALUES
  ('EVE-01222', to_date('07.08.2005', 'dd.mm.yyyy'), 'Raj Ray');
INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)

VALUES
  ('EVE-01223', to_date('21.07.2003', 'dd.mm.yyyy'), 'Peyman Mestchian');
INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)

VALUES
  ('EVE-01224', to_date('16.05.2004', 'dd.mm.yyyy'), 'Peyman Mestchian');

INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)
VALUES
  ('EVE-01225', to_date('18.11.2003', 'dd.mm.yyyy'), 'Peyman Mestchian');

INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)
VALUES
  ('EVE-01226', to_date('20.02.2004', 'dd.mm.yyyy'), 'Peyman Mestchian');

INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)
VALUES
  ('EVE-01227', to_date('18.10.2005', 'dd.mm.yyyy'), 'Heather Ainley');

INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)
VALUES
  ('EVE-01228', to_date('01.03.2005', 'dd.mm.yyyy'), 'Patricia Pittaway');

INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)
VALUES
  ('EVE-01229', to_date('22.12.2002', 'dd.mm.yyyy'), 'Patricia Pittaway');

INSERT INTO incident i
  (i.incident_number, i.incident_date, i.incident_employee)
VALUES
  ('EVE-01230', to_date('28.03.2004', 'dd.mm.yyyy'), 'Raj Ray');

COMMIT WORK;
/
INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01215', to_number('208690'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01216', to_number('235270'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01217', to_number('25960'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01215', to_number('22280'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01218', to_number('588570'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01218', to_number('29970'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01218', to_number('27570'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01221', to_number('38760'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01215', to_number('8980'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01222', to_number('15030'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01225', to_number('6090'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01224', to_number('16400'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01221', to_number('319740'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01227', to_number('78380'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01227', to_number('19430'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01228', to_number('979190'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01228', to_number('11760'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01229', to_number('20960'));

INSERT INTO financial_effect fe
  (fe.incident_number, fe.loss)
VALUES
  ('EVE-01230', to_number('11960'));

COMMIT WORK;
/

Задание №1
Вывести всех сотрудников («INCIDENT_NUMBER» | «INCIDENT_NUMBER» | «INCIDENT_EMPLOYEE»), которые обнаружили инциденты после 01.01.2003.

SELECT i.*
  FROM incident i
 WHERE i.incident_date > to_date('01.01.2003', 'dd.mm.yyyy');
/

Задание №2
Вывести список сотрудников, которые чаще других встречаются в таблице «INCIDENT».

SELECT i1.incident_employee AS "Сотрудник",
       COUNT(*) AS "Количество"
  FROM incident i1
 GROUP BY i1.incident_employee
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                     FROM incident i2
                    GROUP BY i2.incident_employee);
/

Вариант исполнения предыдущего запрос

SELECT i.incident_employee AS "Сотрудник",
       i.cnt_employee      AS "Количество"
  FROM (SELECT i.incident_employee,
               i.cnt_employee,
               rank() over(ORDER BY i.cnt_employee DESC) AS rank,
               dense_rank() over(ORDER BY i.cnt_employee DESC) AS dense_rank
          FROM (SELECT i.incident_employee, COUNT(*) AS cnt_employee
                  FROM incident i
                 GROUP BY i.incident_employee) i) i
 WHERE i.rank = 1;
/

Задание №3
Вывести список уникальных идентификаторов инцидентов и суммы потерь по каждому из них.

SELECT DISTINCT fe.incident_number AS "Номер инцидента",
                SUM(fe.loss) over(PARTITION BY fe.incident_number) AS "Сумма потерь"
  FROM financial_effect fe
 ORDER BY "Сумма потерь" DESC;
/

Задание №4
Вывести сотрудника, обнаружившего наибольшую сумму потерь с 2003 по 2006 год.

SELECT i.incident_employee AS "Сотрудник"
  FROM (SELECT fe.incident_number, SUM(fe.loss) AS sum_loss
          FROM financial_effect fe
         GROUP BY fe.incident_number
        HAVING SUM(fe.loss) = (SELECT MAX(SUM(fe.loss)) AS max_sum_loss
                                FROM financial_effect fe
                               GROUP BY fe.incident_number)) fe
  JOIN incident i
    ON i.incident_number = fe.incident_number;
/
SELECT i.incident_employee AS "Сотрудник"
  FROM (SELECT fe.incident_number,
               rank() over(ORDER BY fe.sum_loss DESC) AS rank,
               dense_rank() over(ORDER BY fe.sum_loss DESC) AS dense_rank
          FROM (SELECT DISTINCT fe.incident_number,
                                SUM(fe.loss) over(PARTITION BY fe.incident_number) AS sum_loss
                  FROM financial_effect fe) fe) fe
  JOIN incident i
    ON i.incident_number = fe.incident_number
 WHERE fe.rank = 1;
/

Tags: ,

Leave a Reply

You must be logged in to post a comment.