この記事では、T-SQLでAVG()関数として知られているSQL平均関数を学びます。 AVG()関数は、SELECT文から返される数値データセットの平均値を計算する集約関数です。
はじめに
数字の集まりがあるとします。 まず、コレクションのすべてのメンバーを加算し、コレクションの合計メンバー数を割ります。 その結果、得られた数は平均値となります。
ジョンは大学に通う学生で、毎日の支出を記録することにしました。
さて、ジョンさんの先週の平均支出を計算してみましょう。
まず、前述の週の支出をすべて合計します。
($20+$60+$20+$42+$10+$15+$8)=$175がその週の支出の合計額です。
第二段階として、このコレクションは7人のメンバーで構成されているので、支出の合計額を7で割ります。
$175 / 7 = $25 がその週の平均支出額です。
平均という数学的な概念を説明した後、引き続き SQL の AVG() 関数の基本を学びましょう。
Data Preparation
以下のクエリの助けを借りて、WeekExpense テーブルを作成し、John の 3 週間分の支出を挿入しましょう。 この記事のすべての例で、このテーブルを使用します。
|
1
2
3div
4
5
6
7
8
9
10
11
12
13
14
15
です。 16
17
18
19
20
21
22
23
24
25
26
です。 27
28
29
30
|
CREATE TABLE WeekExpense
( WeekNumber VARCHAR(20),WeekDayName VARCHAR(50), Expense MONEY)
INSERT INTO WeekExpense
VALUES
(‘Week05′,’Monday’, 20 ),
( ‘Week05′,’Tuesday’, 60 ),
( ‘Week05′,’Wednesday’, 20 ),
( ‘Week05′,’Thurusday’, 42 ),
( ‘Week05′,’Friday’, 10 ),
( ‘Week05′,’Saturday’, 15 ) ,
( ‘Week05′,’Sunday’, 8 ),
( ‘Week04′,’Monday’, 29 ),
( ‘Week04′,’Tuesday’, 17 ) 。
( ‘Week04′,’Weden’, 42 ),
( ‘Week04′,’Thurusday’, 11 ),
( ‘Week04′,’Friday’, 43 ),
(‘Week04′,’Saturday’, 10 ) ,
(‘Week04′,’Sunday’, 15 ),
(‘Week03′,’Monday’, 10 ),
( ‘Week03′,’Tuesday’, 32 ),
( ‘Week03′,’Weden’, 35 ),
( ‘Week03′,’Thurusday’, 19 ),
( ‘Week03′,’Friday’, 30 ),
( ‘Week03′,’Saturday’, 10 ) ,
( ‘Week03′,’Sunday’, 15 )
GO
SELECT *
FROM WeekExpense
|
SQL平均関数の構文
AVG()の構文関数は、単純な形では以下のようになります。
|
1
2
3
|
SELECT AVG ( columname )
FROM TABLENAME
WHERE CONDITION
|
ALLキーワードは、結果セットのすべての値の平均を計算することができ、デフォルトで使用されます。 DISTINCT キーワードは、ユニークな値に対してのみ AVG() 関数を実装します。
AVG() 関数の例
以下のクエリは、AVG() 関数の助けを借りて John の平均支出を計算します。
|
1
2
3
|
SELECT AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber = ‘Week05’
div |
以下の画像は、デフォルトの使い方によるAVG()関数の計算方法を示しています。
見てわかるように、AVG()は平日と週末のすべての値を考慮して計算しています。 また、構文にALLキーワードを追加しても同じ結果を得ることができます。
|
1 div
2
3
|
SELECT AVG(ALL Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
div |
AVG()関数の計算中に重複した値を無視したい場合は、DISTINCTを使用することができます。 DISTINCT キーワードを使用することができます。 以下のクエリを実行した後、その結果を分析してみましょう。
|
1
2
3div
|
SELECT AVG(DISTINCT Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
DISTINCTキーワードは、重複する値を排除します。 DISTINCT キーワードは、重複する値を排除するため、値が $20 である経費のうち 1 つだけを考慮して計算を行います。 次の画像は、DISTINCT キーワードの動作メカニズムを基本的に示しています。
div 関数の説明SQL平均関数とNULL値
AVG()関数は、計算中にNULL値を考慮しません。 それでは、この問題の例を見てみましょう。 まず、WeekExpenseテーブルで日曜日の支出をNULLとして更新します。
|
1
2
3
4
5
|
UPDATE WeekExpense
SET
Expense = NULL
WHERE WeekDayName = ‘Sunday’ AND
WeekNumber = ‘Week05’
|
さて。 平均値を計算するために、以下のクエリを実行します。
|
1
2
3
|
SELECT AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
見ての通り。 AVG()関数の計算では、NULL値が考慮されていません。 次の画像は、計算方法を示したものです。
NULL値を計算に入れたい場合、ISNULL関数を使用することができます。 ISNULL関数は、NULL値を定義された値に変更するために使用されます。 そこで、NULL式を計算に含めるために、以下のように実行します。
|
1
2
3
|
SELECT AVG(ISNULL(Expense,0)) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
次の画像は、先ほどのクエリの計算方法を示したものです。
ここで注目すべきは、NULL式を0として計算に入れたことです。
GROUP BY文でのSQL平均関数の使い方
GROUP BY文は、データをグループ化するために使用され、主に集約関数で使用されます。 この問題を解決するには、GROUP BY文とAVG()関数を同時に使用する必要があります。 次のクエリは、個々の週ごとに平均費用を計算します。
|
1
2
3
4
|
SELECT WeekNumber, AVG(Expense) AS
FROM WeekExpense
GROUP BY WeekNumber
ORDER BY WeekNumber DESC
|
クエリの結果セットを見てみると、平均値が表示されています。 平均値がすべての週について別々に計算されていることがわかります。
ボーナス チップ。 SQL Average 関数の実行計画の詳細
実行計画は、クエリの実行の詳細を理解するのに役立ちます。
さて、AVG()関数の舞台裏で何が起こっているかを明確に理解するために、以下のクエリの実際の実行計画をApexSQL Planで分析してみましょう。
|
1
2
|
div
SELECT AVG(Expense) AS
FROM WeekExpense
|
クエリの視覚的な実行プランは以下のようになります。
このテーブルにはインデックスを作成していないため、テーブルスキャンではテーブル内のすべての行を読み取ります。
Compute scalarは、Stream aggregateからこれらの式を受け取り、次の式で平均値を計算します。
|
1
2
3
4
5
|
CASE
WHEN = (0)
THEN NULL
ELSE / CONVERT_IMPLICIT(money, , 0)
END
|
この式では、1つの点に注意しなければなりません。 式の総数が0になると、平均計算はNULLを返します。 式の総数が0を返す場合、平均値はNULLになります。
結論
この記事では、SQL平均関数を学び、基本的な例やイラストで学習内容を補強しました。 その際、AVG()関数の実行プランの詳細についても説明しました。
- Author
- Recent Posts
彼のキャリアのほとんどは、SQL Serverのデータベース管理と開発に集中しています。 現在は、データベース管理とビジネスインテリジェンスに興味があります。 リンクトインで彼を見つけることができます。
Esat Erkecのすべての投稿を見る
- クエリ最適化の神話 – 3月23日。 2021年
- SQL Serverにおけるパラメータスニフィングの症状 – 2021年3月17日
- クエリチューニングのための自動プラン補正の使用 – 2021年3月4日