В таблице хранятся сведения о еженедельных продажах продукта.
Необходимо настроить автоматическое преобразование еженедельных значений в ежемесячные. Продажи в переходных неделях (часть недели в одном месяце, часть в другом) необходимо распределить по будним дням (исключая выходные дни — сб, вск).
Считаем, что продажи нужно разбить по будням равными долями и что продажи идут в будни.
В данном примере продажи за неделю, окончившуюся 05.03.2013, необходимо распределить следующим образом 2 дня на февраль, 3 дня на март.
Результатом решения задачи является SQL код процедуры, которая позволит автоматически получать преобразованные данные в формате «Таблица результат» (месяц;сумма) по введенному параметру MonthNumber (номер месяца). Если параметр не указан, выводятся вся таблица.
Тестовые данные:
CREATE TABLE "SALE" ("SALE_DATE" DATE, "SALE_AMOUNT" NUMBER ); COMMENT ON COLUMN "SALE"."SALE_DATE" IS 'Дата продажи'; COMMENT ON COLUMN "SALE"."SALE_AMOUNT" IS 'Сумма продажи'; COMMENT ON TABLE "SALE" IS 'Продажа'; / alter session set nls_territory = RUSSIA; insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('26.02.2013', 'dd.mm.yyyy'), to_number('312,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('05.03.2013', 'dd.mm.yyyy'), to_number('833,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('12.03.2013', 'dd.mm.yyyy'), to_number('225,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('19.03.2013', 'dd.mm.yyyy'), to_number('453,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('26.03.2013', 'dd.mm.yyyy'), to_number('774,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('02.04.2013', 'dd.mm.yyyy'), to_number('719,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('09.04.2013', 'dd.mm.yyyy'), to_number('136,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('16.04.2013', 'dd.mm.yyyy'), to_number('133,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('23.04.2013', 'dd.mm.yyyy'), to_number('157,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('30.04.2013', 'dd.mm.yyyy'), to_number('850,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('07.05.2013', 'dd.mm.yyyy'), to_number('940,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('14.05.2013', 'dd.mm.yyyy'), to_number('933,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('21.05.2013', 'dd.mm.yyyy'), to_number('422,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('28.05.2013', 'dd.mm.yyyy'), to_number('952,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('04.06.2013', 'dd.mm.yyyy'), to_number('136,00')); insert into SALE (SALE_DATE, SALE_AMOUNT) values (to_date('11.06.2013', 'dd.mm.yyyy'), to_number('701,00')); COMMIT WORK; /
Вариант решения:
CREATE TYPE O_SALE AS OBJECT ( SALE_MONTH INTEGER, SALE_AMOUNT NUMBER ) ; / CREATE TYPE T_SALE AS TABLE OF O_SALE; / CREATE OR REPLACE FUNCTION "GET_SALES_MONTH" (I_MONTH_NUMBER IN INTEGER) RETURN T_SALE PIPELINED AS L_O_SALE O_SALE := NULL; L_T_SALE T_SALE := T_SALE(); BEGIN FOR I IN (SELECT S.SALE_AMOUNT, S.SALE_DATE, EXTRACT(MONTH FROM S.SALE_DATE) AS DATE_MONTH_7, -- aoi?iee EXTRACT(MONTH FROM S.SALE_DATE - 1) AS DATE_MONTH_1, -- iiiaaaeuiee EXTRACT(MONTH FROM S.SALE_DATE - 4) AS DATE_MONTH_4, -- iyoieoa EXTRACT(MONTH FROM S.SALE_DATE - 5) AS DATE_MONTH_5, -- ?aoaa?a EXTRACT(MONTH FROM S.SALE_DATE - 6) AS DATE_MONTH_6, -- n?aaa S.SALE_AMOUNT / 5 AS AMOUNT_DAY FROM SALE S) LOOP L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_1, SALE_AMOUNT => I.AMOUNT_DAY); L_T_SALE.EXTEND; L_T_SALE(L_T_SALE.COUNT) := L_O_SALE; L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_4, SALE_AMOUNT => I.AMOUNT_DAY); L_T_SALE.EXTEND; L_T_SALE(L_T_SALE.COUNT) := L_O_SALE; L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_5, SALE_AMOUNT => I.AMOUNT_DAY); L_T_SALE.EXTEND; L_T_SALE(L_T_SALE.COUNT) := L_O_SALE; L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_6, SALE_AMOUNT => I.AMOUNT_DAY); L_T_SALE.EXTEND; L_T_SALE(L_T_SALE.COUNT) := L_O_SALE; L_O_SALE := O_SALE(SALE_MONTH => I.DATE_MONTH_7, SALE_AMOUNT => I.AMOUNT_DAY); L_T_SALE.EXTEND; L_T_SALE(L_T_SALE.COUNT) := L_O_SALE; END LOOP; FOR T IN (SELECT SUM(S.SALE_AMOUNT) AS SUM_SALE_AMOUNT, S.SALE_MONTH FROM (TABLE(L_T_SALE)) S WHERE S.SALE_MONTH = NVL(I_MONTH_NUMBER, S.SALE_MONTH) GROUP BY S.SALE_MONTH) LOOP L_O_SALE := O_SALE(SALE_MONTH => T.SALE_MONTH, SALE_AMOUNT => T.SUM_SALE_AMOUNT); L_T_SALE.EXTEND; L_T_SALE(L_T_SALE.COUNT) := L_O_SALE; PIPE ROW(L_O_SALE); END LOOP; RETURN; END GET_SALES_MONTH; /
Результат:
-- Продажи за 3 месяц (Март) SELECT * FROM (TABLE(GET_SALES_MONTH(I_MONTH_NUMBER => 3))) S; -- Продажи за все месяцы SELECT * FROM (TABLE(GET_SALES_MONTH(I_MONTH_NUMBER => NULL))) S; SELECT * FROM (TABLE(GET_SALES_MONTH(NULL))) S; /
Tags: Oracle; PL/SQL