Aggregation

• Overview
• 温度測定値を数える
• 解答
• NULLでの平均化
• ソリューション
• このクエリは何をするのですか？
• 解決策
• Ordering When Concatenating
• Key Points

Overview

Teaching: 10 min
Esercises（演習）。 10分

問題

• 総和、平均、その他の集計値を計算するには？

目的

• 集計の定義とその使用例。

• 集約値を計算するクエリを書く。

• 集約を実行するクエリの実行を追跡する。

• 集約中に欠損データがどう処理されるかを説明できる。

ここで、データの範囲と平均を計算したいと思います。我々は、Visitedテーブルからすべての日付を選択する方法を知っています。

SELECT dated FROM Visited;

-null-

しかしそれらを結合するには、min または max などの集計関数を使用しなければいけません。これらの関数はそれぞれ入力としてレコードの集合を受け取り、出力として1つのレコードを生成する。

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

SELECT count(reading) FROM Survey WHERE quant = 'sal';

SELECT sum(reading) FROM Survey WHERE quant = 'sal';

ここでcount(reading)を使いました。しかし、quantやテーブルの他のフィールドを数えたり、count(*)を使ったりすることも簡単にできます。

SQL では、一度に複数の集計を行うことができます。たとえば、塩分濃度の測定値の範囲を見つけることができます：

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

0.21

また、出力に驚くかもしれませんが、集計結果を生の結果と組み合わせることも可能です。

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

なぜ、ローリッチやダイアーではなく、レイクの名前が出てくるのでしょうか。その答えは、あるフィールドを集約しなければならないが、その方法が指示されていない場合、データベース管理者は入力セットから実際の値を選択するからである。最初に処理されたもの、最後に処理されたもの、あるいは全く別のものが使われるかもしれません。

もう1つの重要な事実は、集約する値がない場合、例えばWHERE句を満たす行がない場合、集約の結果はゼロや他の任意の値ではなく「わからない」であるということです。

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';

集約関数の最後の重要な特徴は、非常に有用な方法でSQLの残りの部分と矛盾していることです。その延長として、sum を使用してセット内のすべての値を追加し、それらの値のいずれかが NULL の場合、結果も NULL になるはずです。しかし、集約関数が NULL 値を無視して NULL でないものだけを結合する方がはるかに便利です。この動作により、常に明示的にフィルタリングする代わりに、

SELECT min(dated) FROM Visited;

のように問い合わせを記述することができます。

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

すべてのレコードを一度に集約することは常に意味があるわけではありません。例えば、データに系統的な偏りがあり、ある科学者の放射線測定値が他の科学者よりも高いのではないかと疑ったとします。これはうまくいかないことが分かっています。

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';

これは、データベース管理者が科学者ごとに集計するのではなく、任意の科学者の名前を1つ選択するためである。科学者は5人しかいないので、次のような5つのクエリを書けばよい。

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';

しかし、これは面倒で、もし50人とか500人の科学者がいるデータセットがあったとしても、これらのクエリをすべて正しく実行できる可能性は低いでしょう。

そこで必要なことは、GROUP BY 節を使用して、各科学者の時間を個別に集約するようにデータベース マネージャーに指示することです。

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;

1.6 31.725

GROUP BY は、その名前が示すとおり、指定したフィールドに同じ値を持つすべてのレコードをグループ化して、集計で各バッチを個別に処理できるようにするものです。各バッチのすべてのレコードが person に対して同じ値を持っているので、データベース マネージャーが集約された reading 値と一緒に表示するために任意の値を選択することはもはや重要ではありません。たとえば、科学者と測定された量による平均値を取得するには、GROUP BY節に別のフィールドを追加するだけです。

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;

1.2.82

Lake

65390

表示されるフィールドのリストに quant を追加したことに注意してください。

さらに一歩進んで、誰が測定を行ったかわからないエントリをすべて削除してみましょう。

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;

もっと詳しく見てみると、このクエリ。

1. Survey テーブルから person フィールドが NULL でないレコードを選択し、

2. それらのレコードをサブセットにグループ化して、それぞれのサブセットの person および quant 値が同じであるようにしました。

3. それらのサブセットをまず person で、次に各サブグループ内で quant で並べ替えました。

温度測定値を数える

Frank Pabodieはいくつの温度測定値を記録し、その平均値は何だったか？

解答

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';

count(reading)	avg(reading)
2	-20.0

NULLでの平均化

値の集合の平均は、値の合計を値の数で割った値です。これは、avg関数が値1.0、null、5.0を与えられたときに2.0または3.0を返すことを意味しますか？

ソリューション

答えは3.0です。

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

このクエリは何をするのですか？

個々の放射線測定値とすべての放射線測定値の平均の差を計算したいと思います。

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

このクエリは実際に何を生成し、その理由を考え出すことができますか。

解決策

私たちが本当に欲しいのは測定値ごとの結果なのに、クエリは結果の1行のみを生成しています。7955>

SELECT avg(reading) FROM Survey WHERE quant='rad';

欲しいものを得るには、2つのクエリを実行する必要があります。5625)を生成し、これを2番目のクエリに挿入します。

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

これは欲しいものを生成しますが、これをサブクエリを使用して1つのクエリに結合することができます。

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

この方法では、2 つのクエリを実行する必要はありません。

要約すると、私たちが行ったことは、元のクエリの avg(reading) を (SELECT avg(reading) FROM Survey WHERE quant='rad') で置き換えることです。

Ordering When Concatenating

The function group_concat(field, separator)concatenates all the values in a field using the specified separator character (or ‘,’ if the separator is not specified).これは、指定された区切り文字を使用して、フィールド内のすべての値を連結するものです。これを使用して、次のような科学者の名前の一行リストを作成します：

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

名字でリストを並べる方法はありますか？

Key Points

• 複数の値を結合するには集約関数を使用します。

• 集約関数は null 値を無視します。

• 集約はフィルタリングの後に行われます。

• GROUP BY を使用してサブセットを別々に結合します。

• フィールドに対して集約関数が指定されていない場合、クエリーはそのフィールドに対して任意の値を返す可能性があります。