Еженедельные продажи продукта

В таблице хранятся сведения о еженедельных продажах продукта.

Необходимо настроить автоматическое преобразование еженедельных значений в ежемесячные. Продажи в переходных неделях (часть недели в одном месяце, часть в другом) необходимо распределить по будним дням (исключая выходные дни — сб, вск).

Считаем, что продажи нужно разбить по будням равными долями и что продажи идут в будни.

В данном примере продажи за неделю, окончившуюся 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:

Comments are closed.