Ответ 1
Если данные хранятся как INT
, вы можете попробовать
Average = (R1 + R2 + R3 + R4 + R5) / 5.0
У меня есть таблица под названием 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
Теперь я хочу отобразить среднее значение R1, R2, R3, R4 и R5
Итак, я написал запрос вроде:
Select Req_ID, Avg(R1+R2+R3+R4+R5) as Average
from Request
Group by Req_ID
Но я просто получаю сумму R1, R2, R3, R4 и R5 не в среднем? Где я поступаю неправильно.
Если данные хранятся как INT
, вы можете попробовать
Average = (R1 + R2 + R3 + R4 + R5) / 5.0
Вы не указываете, являются ли столбцы нулевыми. Если они есть, и вы хотите использовать ту же семантику, которую предоставляет агрегат AVG
, вы можете сделать (2008)
SELECT *,
(SELECT AVG(c)
FROM (VALUES(R1),
(R2),
(R3),
(R4),
(R5)) T (c)) AS [Average]
FROM Request
Версия 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
В PostgreSQL, чтобы получить среднее из нескольких (от 2 до 8) столбцов в одной строке, просто определите набор из семи функций, называемых average(). Выдает среднее значение столбцов, отличных от нуля.
А потом просто
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)
Вот так:
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;
Вы можете просто сделать:
Select Req_ID, (avg(R1)+avg(R2)+avg(R3)+avg(R4)+avg(R5))/5 as Average
from Request
Group by Req_ID
Right?
Я предполагаю, что у вас может быть несколько строк с одним и тем же Req_ID, и в этих случаях вы хотите рассчитать среднее значение по всем столбцам и строкам для этих строк с тем же Req_ID