Oracle SQL-使用函数获取平均值 [英] Oracle SQL - Using Function to get Average of values

问题描述

我正在尝试使用方法"来获取表中分数的平均值. 我认为这很简单，但显然不是. 当前在下面

I'm trying to use a Method to get the average of a score within a table. I thought it would be simple but obviously not.. It's currently the below

CREATE OR REPLACE TYPE Team_Type AS OBJECT
(TeamReviewed REF Game_Type,
Team VARCHAR2(30),
TeamScore NUMBER(1),
MAP MEMBER FUNCTION team_rating RETURN NUMBER)
/
CREATE OR REPLACE TYPE BODY Team_Type
AS
MAP MEMBER FUNCTION team_rating
  RETURN NUMBER
IS 
avg_score NUMBER;
BEGIN
SELECT AVG(TeamScore)
INTO avg_score FROM DUAL; 
RETURN avg_score;
END;
END;
/
CREATE TABLE Team_Table of Team_Type
/
INSERT INTO Team_Table 
VALUES((SELECT REF(a) FROM Game_Table a WHERE a.gameid = 1000000), 'Team A', 2)
/
INSERT INTO Team_Table
VALUES((SELECT REF(a) FROM Game_Table a WHERE a.gameid = 1000000), 'Team B', 6)

我可以使用

select avg(teamscore)
from team_table
where teamreviewed = (select ref(a) from game_table a where a.gameid=1000000)

但是下面的代码只返回分数(在本例中为2和6)，而不是将其平均.

But the below just returns the score (in this case 2 and 6) rather than averaging it out.

SELECT p.team_rating() from team_table p where p.teamreviewed = (select 
ref(a) from game_table a where a.gameid=1000000)

我要去哪里了?

推荐答案

对象表示表中的单行.

成员函数:

MAP MEMBER FUNCTION team_rating RETURN NUMBER
IS 
  avg_score NUMBER;
BEGIN
  SELECT AVG(TeamScore)
  INTO avg_score FROM DUAL; 
  RETURN avg_score;
END;

使用DUAL表-该表具有一行，并获取该对象的TeamScore属性的平均值-该属性是单个值-因此您的函数可以重写为:

Uses the DUAL table - which has a single row and takes the average of the Object's TeamScore attribute - which is a single value - so your function could just be rewritten as:

MAP MEMBER FUNCTION team_rating RETURN NUMBER
IS 
  avg_score NUMBER;
BEGIN
  RETURN TeamScore;
END;

目前尚不清楚您想要平均什么，但类似:

It is unclear what you want to average but something like:

MAP MEMBER FUNCTION team_rating RETURN NUMBER
IS 
  avg_score NUMBER;
BEGIN
  SELECT AVG(TeamScore)
  INTO   avg_score
  FROM   Team_Table t
  WHERE  t.team = self.team;

  RETURN avg_score;
END;

SQL小提琴

查询1 :

select avg(teamscore)
from team_table
where teamreviewed = (select ref(a) from game_table a where a.gameid=1000000)

结果 :

Results:

| AVG(TEAMSCORE) |
|----------------|
|              4 |

这篇关于Oracle SQL-使用函数获取平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！