Имеется таблица в данными по платежным документам. Необходимо написать запрос, который выведет все документы того типа, которого за все время было по сумме больше всего. Если таких типов несколько, то вывести все такие типы. Для каждой строки результата вывести промежуточную сумму платежей данного типа от самого раннего до текущего платежа включительно.
Структура таблицы:
CREATE TABLE "PAYMENT_DOCUMENT"
("ID" INTEGER,
"PAY_TYPE" INTEGER,
"PAY_DATE" DATE,
"PAY_SUM" NUMBER
) TABLESPACE MIRAVILD_DATA;
COMMENT ON TABLE "PAYMENT_DOCUMENT" IS 'Платёжный документ';
COMMENT ON COLUMN "PAYMENT_DOCUMENT"."ID" IS 'Идентификатор платёжного документа';
COMMENT ON COLUMN "PAYMENT_DOCUMENT"."PAY_TYPE" IS 'Тип платёжного документа';
COMMENT ON COLUMN "PAYMENT_DOCUMENT"."PAY_DATE" IS 'Дата платежа';
COMMENT ON COLUMN "PAYMENT_DOCUMENT"."PAY_SUM" IS 'Сумма платежа';
CREATE UNIQUE INDEX "U_IDX_PAYMENT_DOCUMENT" ON "PAYMENT_DOCUMENT" ("ID") TABLESPACE MIRAVILD_INDEX;
ALTER TABLE "PAYMENT_DOCUMENT" ADD CONSTRAINT "PK_PAYMENT_DOCUMENT" PRIMARY KEY ("ID")
USING INDEX "U_IDX_PAYMENT_DOCUMENT" ENABLE VALIDATE;
/
Пример данных:
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(1, 1, to_date('01.01.2012', 'dd.mm.yyyy'), 100);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(2, 1, to_date('02.01.2012', 'dd.mm.yyyy'), 200);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(3, 1, to_date('03.01.2012', 'dd.mm.yyyy'), 300);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(4, 1, to_date('01.02.2012', 'dd.mm.yyyy'), 400);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(5, 1, to_date('01.02.2012', 'dd.mm.yyyy'), 500);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(6, 2, to_date('01.01.2012', 'dd.mm.yyyy'), 600);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(7, 2, to_date('01.02.2012', 'dd.mm.yyyy'), 700);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(8, 2, to_date('01.04.2012', 'dd.mm.yyyy'), 800);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(9, 2, to_date('01.05.2012', 'dd.mm.yyyy'), 900);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(10, 2, to_date('01.06.2012', 'dd.mm.yyyy'), 1000);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(11, 3, to_date('10.01.2012', 'dd.mm.yyyy'), 1100);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(12, 3, to_date('01.03.2012', 'dd.mm.yyyy'), 1200);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(13, 3, to_date('01.05.2012', 'dd.mm.yyyy'), 1300);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(14, 3, to_date('05.05.2012', 'dd.mm.yyyy'), 1400);
INSERT INTO payment_document
(id, pay_type, pay_date, pay_sum)
VALUES
(15, 3, to_date('01.06.2012', 'dd.mm.yyyy'), 1500);
COMMIT WORK;
/
Варианты запроса:
SELECT pd.*,
SUM(pd.pay_sum) over(PARTITION BY pd.pay_type ORDER BY pd.pay_date ASC) as SM
FROM payment_document pd
JOIN (SELECT pd.pay_type
FROM (SELECT pd.pay_type,
SUM(pd.pay_sum) AS sm,
MAX(SUM(pd.pay_sum)) over() AS maxsm
FROM payment_document pd
GROUP BY pd.pay_type) pd
WHERE pd.sm = pd.maxsm) t
ON t.pay_type = pd.pay_type;
/
SELECT pd.*,
SUM(pd.pay_sum) over(PARTITION BY pd.pay_type ORDER BY pd.pay_date ASC) as SM
FROM payment_document pd
JOIN (SELECT pd.pay_type
FROM (SELECT pd.pay_type,
SUM(pd.pay_sum) AS sm,
rank() over(ORDER BY SUM(pd.pay_sum) DESC) AS rn
FROM payment_document pd
GROUP BY pd.pay_type) pd
WHERE pd.rn = 1) t
ON t.pay_type = pd.pay_type;
/
Результат:
ID PAY_TYPE PAY_DATE PAY_SUM SM 11 3 10.01.2012 1100 1100 12 3 01.03.2012 1200 2300 13 3 01.05.2012 1300 3600 14 3 05.05.2012 1400 5000 15 3 01.06.2012 1500 6500
P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776