查询查找平均加权价格

问题描述：

我在Oracle中有一个表，每个给定部分有多行。每一行都有与其相关的数量和价格。还有一个给定零件的行集合合计的总量。以下是数据的一个示例。我需要的是获得零件的平均加权价格。例如，如果的一部分100的量具有为1的价格和具有2的加权平均价的价格是1.33333333查询查找平均加权价格

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

思想50的量？

答

试试这个：

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

猜你不需要TOTAL_QTY字段了。它公然违反了唯一性数据原则 - 每一条数据只能出现一次。 – Josep 2013-05-29 13:05:14

答

创建用户定义的聚合函数来计算加权平均值：

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;

查询查找平均加权价格

相关推荐