SQL 查询以找到平均加权价格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4740489/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to find an average weighted price
提问by MikeTWebb
I have a table in Oracle with multiple rows per a given part. Each row has a quantity and a price associated with it. There is also a total quantity that the set of rows for a given part adds up to. Below is a sample of the data. What I need is to get the average weighted price for the part. For example if a quantity of 100 of a part has a price of 1 and a quantity of 50 has a price of 2 the weighted average price is 1.33333333
我在 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
Ideas?
想法？
回答by Chandu
Try this:
尝试这个：
SELECT part, SUM(qty*price_per)/SUM(qty)
FROM <YOUR_TABLE>
GROUP BY part
回答by patserat
create a user defined aggregate function to calculate the weighted average:
创建一个用户定义的聚合函数来计算加权平均值：
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;
/
query with your data:
查询您的数据：
SELECT part, WEIGHTED_AVG(WEIGHTED_AVG_O(qty, price_per))
FROM <YOUR_TABLE>
GROUP BY part;
