SQL 多列的平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7367750/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 12:03:56 来源:igfitidea点击:
Average of multiple columns
提问by Peter
I have a table called Request and the data looks like:
我有一个名为 Request 的表，数据如下所示：
Req_ID R1 R2 R3 R4 R5
R12673 2 5 3 7 10
R34721 3 5 2 1 8
R27835 1 3 8 5 6
Now I want to display the average of R1,R2,R3,R4 and R5
现在我想显示 R1、R2、R3、R4 和 R5 的平均值
So I wrote a query like:
所以我写了一个查询，如：
Select Req_ID, Avg(R1+R2+R3+R4+R5) as Average
from Request
Group by Req_ID
But I just get the sum of R1,R2,R3,R4 and R5 not the average? Where am I doing wrong.
但我只是得到 R1、R2、R3、R4 和 R5 的总和而不是平均值？我哪里做错了。
采纳答案by Aaron Bertrand
If the data is stored as INT, you may want to try
如果数据存储为INT，您可能需要尝试
Average = (R1 + R2 + R3 + R4 + R5) / 5.0
回答by Martin Smith
You don't mention if the columns are nullable. If they are and you want the same semantics that the AVGaggregate provides you can do (2008)
你没有提到列是否可以为空。如果它们是并且您想要AVG聚合提供的相同语义，您可以做（2008）
SELECT *,
(SELECT AVG(c)
FROM (VALUES(R1),
(R2),
(R3),
(R4),
(R5)) T (c)) AS [Average]
FROM Request
The 2005 version is a bit more tedious
2005版本有点繁琐
SELECT *,
(SELECT AVG(c)
FROM (SELECT R1
UNION ALL
SELECT R2
UNION ALL
SELECT R3
UNION ALL
SELECT R4
UNION ALL
SELECT R5) T (c)) AS [Average]
FROM Request
回答by beetree
You could simply do:
你可以简单地做：
Select Req_ID, (avg(R1)+avg(R2)+avg(R3)+avg(R4)+avg(R5))/5 as Average
from Request
Group by Req_ID
Right?
对？
I'm assuming that you may have multiple rows with the same Req_ID and in these cases you want to calculate the average across all columns and rows for those rows with the same Req_ID
我假设您可能有多行具有相同的 Req_ID，在这些情况下，您希望计算具有相同 Req_ID 的行的所有列和行的平均值
回答by Bob S
In PostgreSQL, to get the average of multiple (2 to 8) columns in one row just define a set of seven functions called average(). Will produce the average of the non-null columns.
在 PostgreSQL 中，要获得一行中多个（2 到 8）列的平均值，只需定义一组名为 average() 的七个函数。将产生非空列的平均值。
And then just
然后只是
select *,(r1+r2+r3+r4+r5)/5.0,average(r1,r2,r3,r4,r5) from request;
req_id | r1 | r2 | r3 | r4 | r5 | ?column? | average
--------+----+----+----+----+----+--------------------+--------------------
R12673 | 2 | 5 | 3 | 7 | 10 | 5.4000000000000000 | 5.4000000000000000
R34721 | 3 | 5 | 2 | 1 | 8 | 3.8000000000000000 | 3.8000000000000000
R27835 | 1 | 3 | 8 | 5 | 6 | 4.6000000000000000 | 4.6000000000000000
(3 rows)
update request set r4=NULL where req_id='R34721';
UPDATE 1
select *,(r1+r2+r3+r4+r5)/5.0,average(r1,r2,r3,r4,r5) from request;
req_id | r1 | r2 | r3 | r4 | r5 | ?column? | average
--------+----+----+----+----+----+--------------------+--------------------
R12673 | 2 | 5 | 3 | 7 | 10 | 5.4000000000000000 | 5.4000000000000000
R34721 | 3 | 5 | 2 | | 8 | | 4.5000000000000000
R27835 | 1 | 3 | 8 | 5 | 6 | 4.6000000000000000 | 4.6000000000000000
(3 rows)
select *,(r3+r4+r5)/3.0,average(r3,r4,r5) from request;
req_id | r1 | r2 | r3 | r4 | r5 | ?column? | average
--------+----+----+----+----+----+--------------------+--------------------
R12673 | 2 | 5 | 3 | 7 | 10 | 6.6666666666666667 | 6.6666666666666667
R34721 | 3 | 5 | 2 | | 8 | | 5.0000000000000000
R27835 | 1 | 3 | 8 | 5 | 6 | 6.3333333333333333 | 6.3333333333333333
(3 rows)
Like this:
像这样：
CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
COUNT NUMERIC;
TOTAL NUMERIC;
BEGIN
COUNT=0;
TOTAL=0;
IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
RETURN TOTAL/COUNT;
EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
COUNT NUMERIC;
TOTAL NUMERIC;
BEGIN
COUNT=0;
TOTAL=0;
IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
RETURN TOTAL/COUNT;
EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
COUNT NUMERIC;
TOTAL NUMERIC;
BEGIN
COUNT=0;
TOTAL=0;
IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
RETURN TOTAL/COUNT;
EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC,
V5 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
COUNT NUMERIC;
TOTAL NUMERIC;
BEGIN
COUNT=0;
TOTAL=0;
IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
IF V5 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V5; END IF;
RETURN TOTAL/COUNT;
EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC,
V5 NUMERIC,
V6 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
COUNT NUMERIC;
TOTAL NUMERIC;
BEGIN
COUNT=0;
TOTAL=0;
IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
IF V5 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V5; END IF;
IF V6 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V6; END IF;
RETURN TOTAL/COUNT;
EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC,
V5 NUMERIC,
V6 NUMERIC,
V7 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
COUNT NUMERIC;
TOTAL NUMERIC;
BEGIN
COUNT=0;
TOTAL=0;
IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
IF V5 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V5; END IF;
IF V6 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V6; END IF;
IF V7 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V7; END IF;
RETURN TOTAL/COUNT;
EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC,
V5 NUMERIC,
V6 NUMERIC,
V7 NUMERIC,
V8 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
COUNT NUMERIC;
TOTAL NUMERIC;
BEGIN
COUNT=0;
TOTAL=0;
IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
IF V5 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V5; END IF;
IF V6 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V6; END IF;
IF V7 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V7; END IF;
IF V8 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V8; END IF;
RETURN TOTAL/COUNT;
EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;
回答by user3192731
Select Req_ID, sum(R1+R2+R3+R4+R5)/5 as Average
from Request
Group by Req_ID;
