Запрос, чтобы найти средневзвешенную цену

У меня есть таблица в Oracle с несколькими строками в данной части. Каждая строка имеет количество и цену, связанные с ней. Существует также общее количество, которое добавляет набор строк для данной части. Ниже приведен образец данных. Мне нужно получить средневзвешенную цену за деталь. Например, если количество 100 части имеет цену 1 и количество 50 имеет цену 2, то средневзвешенная цена составляет 1.33333333

PART   TOTAL_QTY  QTY   PRICE_PER
----------------------------------
part1  317        244   27
part1  317        40    53.85
part1  317        33    24.15

Идеи?

Ответ 1

Попробуйте следующее:

SELECT part, SUM(qty*price_per)/SUM(qty)
  FROM <YOUR_TABLE>
GROUP BY part

Ответ 2

создать пользовательскую агрегатную функцию для вычисления средневзвешенного значения:

CREATE OR REPLACE TYPE WEIGHTED_AVG_O AS OBJECT ( 
  sum_of_weights NUMBER, 
  sum_of_weights_times_value NUMBER, 

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(cs_ctx IN OUT WEIGHTED_AVG_O) RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATEITERATE   (self IN OUT WEIGHTED_AVG_O, value IN WEIGHTED_AVG_O) RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATEMERGE     (self IN OUT WEIGHTED_AVG_O, ctx2 IN OUT WEIGHTED_AVG_O) RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATETERMINATE (self IN OUT WEIGHTED_AVG_O, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER 
);
/



CREATE OR REPLACE TYPE BODY WEIGHTED_AVG_O
AS

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(cs_ctx IN OUT WEIGHTED_AVG_O) RETURN NUMBER
  IS
  BEGIN
    cs_ctx := WEIGHTED_AVG_O(0, 0);
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE   (self IN OUT WEIGHTED_AVG_O, value IN WEIGHTED_AVG_O) RETURN NUMBER
  IS
  BEGIN
    self.sum_of_weights := self.sum_of_weights + value.sum_of_weights;
    self.sum_of_weights_times_value := self.sum_of_weights_times_value + value.sum_of_weights * value.sum_of_weights_times_value;
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE     (self IN OUT WEIGHTED_AVG_O, ctx2 IN OUT WEIGHTED_AVG_O) RETURN NUMBER
  IS
  BEGIN
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE (self IN OUT WEIGHTED_AVG_O, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER 
  IS
  BEGIN    
    IF sum_of_weights = 0 THEN
      returnvalue := NULL;
    ELSE
      returnvalue := sum_of_weights_times_value / sum_of_weights;
    END IF;
    RETURN odciconst.success;
  END;

END;
/


CREATE OR REPLACE FUNCTION WEIGHTED_AVG (input WEIGHTED_AVG_O)
   RETURN NUMBER PARALLEL_ENABLE
   AGGREGATE USING WEIGHTED_AVG_O;
/

запрос с вашими данными:

SELECT part, WEIGHTED_AVG(WEIGHTED_AVG_O(qty, price_per))
  FROM <YOUR_TABLE>
 GROUP BY part;