Моделирование. Документ и валюта

Существует таблица документов с суммой в определённой валюте и таблица валют по отношению к рублю.

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

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

Необходимо получить:
1. Список всех документов (ид документа, наименование документа, сумма в рублях или эквивалент в рублях на текущую дату).
2. Список всех валют, используемых в документах (код валюты, сумма по этой валюте по всем документам, эквивалент суммы в рублях по этой валюте на указанную дату).

Необходимо учесть, что в таблице валют может не быть значения на какую-то дату. В этом случае, нужно брать значение на ближайшую меньшую дату. Для валюты «Рубль» нет записей в таблице CURRENCY.

Создадим необходимые объекты (таблицы, ограничения, индексы):

CREATE TABLE "CURRENCY" 
("CURRENCY_CODE" CHAR(3),
 "CURRENCY_DATE" DATE,
 "CURRENCY_NAME" VARCHAR2(256),
 "CURRENCY_RATE" NUMERIC(18, 4)
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON COLUMN "CURRENCY"."CURRENCY_CODE" IS 'Код валюты';
COMMENT ON COLUMN "CURRENCY"."CURRENCY_DATE" IS 'Дата курса валюты';
COMMENT ON COLUMN "CURRENCY"."CURRENCY_NAME" IS 'Наименование валюты';
COMMENT ON COLUMN "CURRENCY"."CURRENCY_RATE" IS 'Курс валюты';
COMMENT ON TABLE "CURRENCY" IS 'Валюта';

CREATE UNIQUE INDEX "U_IDX_CURRENCY" ON "CURRENCY" ("CURRENCY_CODE", "CURRENCY_DATE") TABLESPACE MIRAVILD_INDEX;

ALTER TABLE "CURRENCY" ADD CONSTRAINT "PK_CURRENCY" PRIMARY KEY ("CURRENCY_CODE", "CURRENCY_DATE")
  USING INDEX "U_IDX_CURRENCY" ENABLE VALIDATE;
  
ALTER TABLE "CURRENCY" MODIFY ("CURRENCY_DATE" CONSTRAINT NN_CURRENCY_DATE NOT NULL ENABLE);
ALTER TABLE "CURRENCY" MODIFY ("CURRENCY_NAME" CONSTRAINT NN_CURRENCY_NAME NOT NULL ENABLE);
ALTER TABLE "CURRENCY" MODIFY ("CURRENCY_RATE" CONSTRAINT NN_CURRENCY_RATE NOT NULL ENABLE);
/
CREATE TABLE "DOCUMENT" 
("DOCUMENT_ID" NUMERIC(11),
 "DOCUMENT_NAME" VARCHAR2(256),
 "DOCUMENT_DATE" DATE,
 "CURRENCY_CODE" CHAR(3),
 "DOCUMENT_AMOUNT" NUMERIC(18, 2)
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON COLUMN "DOCUMENT"."DOCUMENT_ID" IS 'Идентификатор документа';
COMMENT ON COLUMN "DOCUMENT"."DOCUMENT_NAME" IS 'Наименование документа';
COMMENT ON COLUMN "DOCUMENT"."DOCUMENT_DATE" IS 'Дата документа';
COMMENT ON COLUMN "DOCUMENT"."CURRENCY_CODE" IS 'Код валюты';
COMMENT ON COLUMN "DOCUMENT"."DOCUMENT_AMOUNT" IS 'Сумма по документу';
COMMENT ON TABLE "DOCUMENT" IS 'Документ';

CREATE UNIQUE INDEX "U_IDX_DOCUMENT" ON "DOCUMENT" ("DOCUMENT_ID") TABLESPACE MIRAVILD_INDEX;

ALTER TABLE "DOCUMENT" ADD CONSTRAINT "PK_DOCUMENT" PRIMARY KEY ("DOCUMENT_ID") 
USING INDEX "U_IDX_DOCUMENT" ENABLE VALIDATE;
  
ALTER TABLE "DOCUMENT" MODIFY ("DOCUMENT_NAME" CONSTRAINT NN_DOCUMENT_NAME NOT NULL ENABLE);
ALTER TABLE "DOCUMENT" MODIFY ("DOCUMENT_DATE" CONSTRAINT NN_DOCUMENT_DATE NOT NULL ENABLE);
ALTER TABLE "DOCUMENT" MODIFY ("DOCUMENT_AMOUNT" CONSTRAINT NN_DOCUMENT_AMOUNT NOT NULL ENABLE);
ALTER TABLE "DOCUMENT" MODIFY ("CURRENCY_CODE" CONSTRAINT NN_CURRENCY_CODE NOT NULL ENABLE);
/

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

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('USD',
   to_date('28.04.2016', 'dd.mm.yyyy'),
   'Доллар США',
   to_number('65.1618'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('USD',
   to_date('29.04.2016', 'dd.mm.yyyy'),
   'Доллар США',
   to_number('65.1133'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('USD',
   to_date('30.04.2016', 'dd.mm.yyyy'),
   'Доллар США',
   to_number('64.3334'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('USD',
   to_date('05.05.2016', 'dd.mm.yyyy'),
   'Доллар США',
   to_number('66.1718'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('USD',
   to_date('06.05.2016', 'dd.mm.yyyy'),
   'Доллар США',
   to_number('65.8918'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('USD',
   to_date('07.05.2016', 'dd.mm.yyyy'),
   'Доллар США',
   to_number('66.1928'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('USD',
   to_date('11.05.2016', 'dd.mm.yyyy'),
   'Доллар США',
   to_number('66.3277'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('USD',
   to_date('12.05.2016', 'dd.mm.yyyy'),
   'Доллар США',
   to_number('66.2428'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('EUR',
   to_date('28.04.2016', 'dd.mm.yyyy'),
   'ЕВРО',
   to_number('73.8023'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('EUR',
   to_date('29.04.2016', 'dd.mm.yyyy'),
   'ЕВРО',
   to_number('73.9166'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('EUR',
   to_date('30.04.2016', 'dd.mm.yyyy'),
   'ЕВРО',
   to_number('73.3015'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('EUR',
   to_date('05.05.2016', 'dd.mm.yyyy'),
   'ЕВРО',
   to_number('76.0512'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('EUR',
   to_date('06.05.2016', 'dd.mm.yyyy'),
   'ЕВРО',
   to_number('75.5713'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('EUR',
   to_date('07.05.2016', 'dd.mm.yyyy'),
   'ЕВРО',
   to_number('75.5789'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('EUR',
   to_date('11.05.2016', 'dd.mm.yyyy'),
   'ЕВРО',
   to_number('75.5406'));

INSERT INTO currency c
  (c.currency_code, c.currency_date, c.currency_name, c.currency_rate)
VALUES
  ('EUR',
   to_date('12.05.2016', 'dd.mm.yyyy'),
   'ЕВРО',
   to_number('75.4505'));

COMMIT WORK;
/

INSERT INTO document d
  (d.document_id,
   d.document_name,
   d.document_date,
   d.currency_code,
   d.document_amount)
VALUES
  (1, 'Документ №1', trunc(SYSDATE) - 1, 'USD', 10);

INSERT INTO document d
  (d.document_id,
   d.document_name,
   d.document_date,
   d.currency_code,
   d.document_amount)
VALUES
  (2, 'Документ №2', trunc(SYSDATE) - 2, 'USD', 20);

INSERT INTO document d
  (d.document_id,
   d.document_name,
   d.document_date,
   d.currency_code,
   d.document_amount)
VALUES
  (3, 'Документ №3', trunc(SYSDATE) - 3, 'EUR', 30);

INSERT INTO document d
  (d.document_id,
   d.document_name,
   d.document_date,
   d.currency_code,
   d.document_amount)
VALUES
  (4, 'Документ №4', trunc(SYSDATE) - 4, 'EUR', 40);

INSERT INTO document d
  (d.document_id,
   d.document_name,
   d.document_date,
   d.currency_code,
   d.document_amount)
VALUES
  (5, 'Документ №5', trunc(SYSDATE) - 5, 'EUR', 50);

INSERT INTO document d
  (d.document_id,
   d.document_name,
   d.document_date,
   d.currency_code,
   d.document_amount)
VALUES
  (6, 'Документ №6', trunc(SYSDATE) - 5, 'RUB', 50);

INSERT INTO document d
  (d.document_id,
   d.document_name,
   d.document_date,
   d.currency_code,
   d.document_amount)
VALUES
  (7, 'Документ №7', trunc(SYSDATE) - 5, 'RUB', 50);

COMMIT WORK;
/

1. Список всех документов:

SELECT d.document_id AS "Ид документа",
       d.document_name AS "Наименование",
       CASE d.currency_code
         WHEN 'RUB' THEN
          d.document_amount
         ELSE
          (d.document_amount * c.currency_rate)
       END AS "Сумма в рублях"
  FROM (SELECT d.document_id,
               d.document_name,
               d.currency_code,
               d.document_amount,
               (SELECT MAX(c.currency_date)
                  FROM currency c
                 WHERE c.currency_code = d.currency_code
                   AND c.currency_date <= d.document_date) AS max_currency_date
          FROM document d) d
  LEFT JOIN currency c
    ON c.currency_code = d.currency_code
   AND c.currency_date = d.max_currency_date;
/

Вариант запроса с конструкцией WITH

WITH t AS
 (SELECT d.document_id,
         d.document_name,
         d.currency_code,
         d.document_amount,
         (SELECT MAX(c.currency_date)
            FROM currency c
           WHERE c.currency_code = d.currency_code
             AND c.currency_date <= d.document_date) AS max_currency_date
    FROM document d)
SELECT t.document_id,
       t.document_name,
       CASE t.currency_code
         WHEN 'RUB' THEN
          t.document_amount
         ELSE
          (t.document_amount * c.currency_rate)
       END AS "Сумма в рублях"
  FROM t
  LEFT JOIN currency c
    ON c.currency_code = t.currency_code
   AND c.currency_date = t.max_currency_date;
/

Результат запроса
Ид документа Наименование Сумма в рублях
1                         Документ №1   663,277
2                        Документ №2   1323,856
3                        Документ №3   2267,367
4                        Документ №4   3023,156
5                        Документ №5   3778,945

2. Список всех валют:

SELECT d.currency_code AS "Список валют",
       SUM(d.document_amount) AS "Сумма в валюте",
       CASE d.currency_code
         WHEN 'RUR' THEN
          SUM(d.document_amount)
         ELSE
          SUM(d.document_amount * c.currency_rate)
       END AS "Сумма в рублях"
  FROM (SELECT d.document_id,
               d.document_name,
               d.currency_code,
               d.document_amount,
               (SELECT MAX(c.currency_date)
                  FROM currency c
                 WHERE c.currency_code = d.currency_code
                   AND c.currency_date <= d.document_date) AS document_date
          FROM document d) d
  LEFT JOIN currency c
    ON c.currency_code = d.currency_code
   AND c.currency_date = d.document_date
 GROUP BY d.currency_code;
/

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

Tags: ERwin, ERwin Data Modeler, Oracle, SQL

You must be logged in to post a comment.

Miravild

Моделирование. Документ и валюта

Leave a Reply