AVG

Synopsis

AVG([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])

概要

AVG 集約関数は、expression の値の平均値を返します。一般的に expression は、クエリで返される複数行の中のフィールド名 (または、フィールド名を 1 つ以上含む式) です。

AVG は、テーブルまたはビューを参照する SELECT クエリまたは SELECT サブクエリで使用できます。AVG は、一般のフィールド値と共に、SELECT リストや HAVING 節で使用できます。

AVG は、WHERE 節では使用できません。SELECT がサブクエリの場合を除いて、JOIN の ON 節では、AVG を使用できません。

AVG は、すべての集約関数と同様に、オプションの DISTINCT 節を取ることができます。AVG(DISTINCT col1) は、これらの個別 (一意) の col1 フィールド値のみの平均値を計算します。AVG(DISTINCT BY(col2) col1) は、col2 値が個別 (一意) であるレコードのこれらの col1 フィールド値のみの平均値を計算します。ただし、個別の col2 値には、個別値として NULL が 1 つ含まれる場合があります。

単一値の平均

AVG に指定される expression の値がすべて同じ場合、計算結果の平均値はアクセスされるテーブルの行数 (除数) によって異なります。例えば、テーブルの特定の列ですべての行の値が同じ場合、その列の平均値の計算結果が個々の列の値とわずかに異なる場合があります。この矛盾を回避するには、DISTINCT キーワードを使用します。

次の例では、平均値の計算結果でわずかな差異が生じる理由について説明します。最初のクエリではテーブル行が参照されていないため、AVG は 1 で除算されます。2 番目のクエリではテーブル行が参照されているため、AVG はテーブル内の行数で除算されます。3 番目のクエリではテーブル行を参照していますが、単一値の DISTINCT 値を平均しています。この場合、AVG は 1 で除算して計算します。

  SET pi=$ZPI
  &sql(SELECT :pi,AVG(:pi) INTO :p,:av FROM Sample.Person)
  WRITE p," the value of pi",!
  WRITE av," avg of pi/1",!
  &sql(SELECT Name,:pi,AVG(:pi) INTO :n,:p,:av FROM Sample.Person)
  WRITE av," avg calculated using numrows",!
  &sql(SELECT Name,:pi,AVG(DISTINCT :pi) INTO :n,:p,:av FROM Sample.Person)
  WRITE av," avg of pi/1"

例

以下のクエリは、Sample.Employee データベースに記録されている全従業員の平均給与額をリストにします。クエリから返されたすべての行には同じ平均値が入っているので、このクエリは、平均給与額から成る 1 行のみを返します。結果の見やすさを考慮し、|| 演算子を使用して数値にドル記号を付加し、AS 節を使用して列にラベルを表示しています。

SELECT '$' || AVG(Salary) AS AverageSalary
     FROM Sample.Employee

以下のクエリは、従業員の平均給与額を州別にまとめてリストにします。

SELECT Home_State,'$' || AVG(Salary) AS AverageSalary
     FROM Sample.Employee
GROUP BY Home_State

以下のクエリは、平均給与額よりも給与額が多い従業員の名前と給与額をリストにします。また、全従業員の平均給与額も表示します。この値は、このクエリで返されるすべての行で同じ値になります。

SELECT Name,Salary,
       '$' || AVG(Salary) AS AverageAllSalary
FROM Sample.Employee
HAVING Salary>AVG(Salary)
ORDER BY Salary

以下のクエリは、平均給与額よりも給与額が多い従業員の名前と給与額をリストにします。また、平均給与額よりも給与が多い従業員の平均給与額も表示します。この値は、このクエリで返されるすべての行で同じ値になります。

SELECT Name,Salary,
       '$' || AVG(Salary %AFTERHAVING) AS AverageHighSalary
FROM Sample.Employee
HAVING Salary>AVG(Salary)
ORDER BY Salary

以下のクエリは、従業員が 4 人以上いる州とその州の従業員の平均給与額、およびその州で給与額が $20,000 を超える従業員の平均給与額をリストにします。

SELECT Home_State,
       '$' || AVG(Salary) AS AvgStateSalary,
       '$' || AVG(Salary %AFTERHAVING) AS AvgLargerSalaries
FROM Sample.Employee
GROUP BY Home_State
HAVING COUNT(*) > 3 AND Salary > 20000
ORDER BY Home_State

以下のクエリは、複数の形式の DISTINCT 節を使用します。Home_City に NULL が 1 つ以上含まれる場合、BY 節に個別値として NULL が 1 つ含まれる可能性があるため、AVG(DISTINCT BY col-list の例では追加の Age 値が平均に含まれることがあります。

SELECT AVG(Age) AS AveAge,AVG(ALL Age) AS Synonym,
       AVG(DISTINCT Age) AS AveDistAge,
       AVG(DISTINCT BY(Home_City) Age) AS AvgAgeDistCity,
       AVG(DISTINCT BY(Home_City,Home_State) Age) AS AvgAgeDistCityState
     FROM Sample.Person

以下のクエリは、%FOREACH キーワードと %AFTERHAVING キーワードの両方を使用します。名前が “A”、“M”、または “W” で始まる人がいる州の行を返します。(HAVING 節と GROUP BY 節)。各州の行には以下の値があります。

• LIST(Age %FOREACH(Home_State)) ： 州内のすべての人の年齢のリスト。

• AVG(Age %FOREACH(Home_State)) ： 州内のすべての人の平均年齢。

• AVG(Age %AFTERHAVING) ： データベースに記録されている人のうち、HAVING 節の条件を満たすすべての人の平均年齢 (この数はすべての行で同じ値になります)。

• LIST(Age %FOREACH(Home_State) %AFTERHAVING) ： 州内の人のうち、HAVING 節の条件を満たすすべての人の年齢のリスト。

• AVG(Age %FOREACH(Home_State) %AFTERHAVING) ： 州内の人のうち、HAVING 節の条件を満たすすべての人の平均年齢。

SELECT Home_State,
       LIST(Age %FOREACH(Home_State)) AS StateAgeList,
       AVG(Age %FOREACH(Home_State)) AS StateAgeAvg,
       AVG(Age %AFTERHAVING ) AS AgeAvgHaving,
       LIST(Age %FOREACH(Home_State)%AFTERHAVING ) AS StateAgeListHaving,
       AVG(Age %FOREACH(Home_State)%AFTERHAVING ) AS StateAgeAvgHaving
FROM Sample.Person
GROUP BY Home_State
HAVING Name LIKE 'A%' OR Name LIKE 'M%' OR Name LIKE 'W%'
ORDER BY Home_State

関連項目

• 集約関数の概要

• COUNT 集約関数

• SUM 集約関数