データをグループ化すると、すべてのデータを論理セットに分割できるため、各グループで個別に統計計算を実行できるようになります。

1. グループの作成（GROUP BY）

グループは提案を使用して作成されます グループ化オペレーター 選択する。 例を見てみましょう。

選択する 製品、SUM(数量) AS Product_num FROM Sumproduct GROUP BY Product

このリクエストにより、各月に販売された製品の数に関する情報を取得しました。 オペレーター 選択する 2 つの列を出力するコマンド 製品- 製品名と 製品番号- 販売された製品の数量を表示するために作成した計算フィールド (フィールド式 SUM (数量))。 オファー グループ化 DBMS にデータを列ごとにグループ化するように指示します。 製品。 また、注目に値するのは、 グループ化文の後に来るべきです どこそしてその前に 注文方法.

2. フィルターグループ (HAVING)

テーブル内の行をフィルター処理したのと同じように、グループ化されたデータをフィルター処理できます。 この目的のために、 SQLオペレーターがいます 持っている。 前の例を使用して、グループによるフィルタリングを追加してみましょう。

選択する 製品、SUM(数量) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(数量)>4000

各製品の販売数量が計算された後、DBMS は販売数が 4000 個未満の製品を「切り捨て」たことがわかります。

ご覧のとおり、オペレーターは 持っているオペレーターによく似ています どこただし、それらの間には大きな違いがあります。 どこデータをグループ化する前にフィルタリングします。 持っている- グループ化後にフィルタリングを実行します。 したがって、提案によって削除された行は、 どこグループには含まれません。 そこで、オペレーターたちは、 どこそして 持っている同じ文の中で使えます。 例を見てみましょう:

選択する 製品、SUM(数量) AS Product_num FROM Sumproduct WHERE Product<>「スキー ロング」合計(数量)>4000 の製品ごとにグループ化

前の例に演算子を追加しました どこ製品が表示されていた場所 ロングスキー、それが演算子によるグループ化に影響を与えました。 持っている。 その結果、製品が ロングスキー販売数量が4000個を超えるグループのリストには含まれていませんでした。

3. グループ化と並べ替え

通常のデータ取得と同様に、演算子でグループ化した後にグループを並べ替えることができます。 持っている。 これを行うには、すでに使い慣れた演算子を使用できます。 注文方法。 この状況では、その適用は前の例と同様です。 例えば：

選択する 製品、SUM(数量) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(数量)>3000 ORDER BY SUM(数量)

または、単純に並べ替える順序でフィールド番号を指定します。

選択する 製品、SUM(数量) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(数量)>3000 ORDER BY 2

集計結果を並べ替えるには、次のような文を書くだけでよいことがわかります。 注文方法オペレーターの後に 持っている。 ただし、注意点が 1 つあります。 DBMSにアクセスする列の別名によるグループの並べ替えはサポートされていません。つまり、この例では、値を並べ替えるために、リクエストの最後に次のように記述することはできません。 Product_num ごとに注文.

この記事では、いくつかのクエリの例を使用して、データがどのようにグループ化されるか、group by および inside SQL クエリを正しく使用する方法を説明します。

データベース内のほとんどの情報は詳細な形式で保存されます。 ただし、多くの場合、レポートを取得する必要があります。 たとえば、ユーザーのコメントの総数や倉庫内の商品の数量を調べます。 似たようなタスクはたくさんあります。 したがって、SQL 言語は、そのような場合に特に、結果として得られるデータのグループをそれぞれグループ化およびフィルタリングできる group by および Hasting 構造を提供します。

ただし、これらを使用すると、ソフトウェア作成の初心者にとって多くの問題が発生します。 彼らは、得られた結果やデータのグループ化メカニズム自体を正確に解釈していません。 したがって、何がどのように起こるかを実際に理解してみましょう。

例の一部として、1 つのテーブルだけを考えてみましょう。 理由は簡単です。これらの演算子は、結果として得られるデータ サンプル (テーブルの行を結合してフィルタリングした後) にすでに適用されているからです。 したがって、where 演算子と join 演算子を追加しても本質は変わりません。

抽象的な例を想像してみましょう。 フォーラム ユーザーの概要テーブルがあるとします。 これを userstat と呼ぶと、次のようになります。 重要な点は、ユーザーは 1 つのグループにのみ所属できると考えていることです。

user_name - ユーザー名

forum_group - グループ名

mess_count - メッセージの数

is_have_social_profile - フォーラムのプロフィールにソーシャル ネットワーク上のページへのリンクが含まれているかどうか

ご覧のとおり、表は単純なので、電卓を使用して自分で多くのことを計算できます。 ただし、これは単なる例であり、エントリは 10 件しかありません。 実際のデータベースでは、レコードは数千単位で測定されることがあります。 それでは、クエリから始めましょう。

group by を使用した純粋なグループ化

各グループの価値、つまりグループ内のユーザーの平均評価とフォーラムに残されたメッセージの総数を知る必要があると想像してみましょう。

まず、SQL クエリを理解しやすくするための短い口頭説明です。 したがって、フォーラムグループごとに計算された値を見つける必要があります。 したがって、これら 10 行すべてを 3 つの異なるグループ (admin、moder、user) に分割する必要があります。 これを行うには、リクエストの最後に forum_group フィールドの値によるグループ化を追加する必要があります。 また、いわゆる集計関数を使用して選択する計算式も追加します。

フィールドと計算列を指定 select forum_group、avg(raiting) を avg_raiting、sum(mess_count) を total_mess_count -- userstat からテーブルを指定 -- forum_group でフィールド グループによるグループ化を指定

クエリで group by 構造を使用した後は、集計関数を使用せずに、group by の後に指定されたフィールドのみを select で使用できることに注意してください。 残りのフィールドは集計関数内で指定する必要があります。

2 つの集計関数も使用しました。 AVG - 平均値を計算します。 そして SUM - 合計を計算します。

フォーラムグループ	平均評価	total_mess_count
管理者	4	50
現代の	3	50
ユーザー	3	150

1. まず、ソーステーブルのすべての行が、forum_group フィールドの値に従って 3 つのグループに分割されました。 たとえば、管理者グループ内に 3 人のユーザーがいたとします。 モダー内にも3本のラインが入っています。 ユーザー グループ内には 4 行 (ユーザー 4 人) がありました。

2. 次に、集計関数が各グループに適用されました。 たとえば、管理者グループの場合、平均評価は次のように計算されました (2 + 5 + 5)/3 = 4。メッセージ数は (10 + 15 + 25) = 50 として計算されました。

ご覧のとおり、複雑なことは何もありません。 ただし、グループ化条件を 1 つだけ適用し、グループによるフィルター処理は行いませんでした。 それでは、次の例に進みましょう。

group by を使用したグループ化と、have を使用したグループのフィルタリング

次に、データのグループ化のより複雑な例を見てみましょう。 ユーザーをソーシャル活動に引き付けるためのアクションの有効性を評価する必要があるとします。 簡単に言うと、グループ内の何人のユーザーが自分のプロフィールへのリンクを残したか、何人のメールを無視したかなどを調べます。 ただし、現実にはそのようなグループが多数存在する可能性があるため、無視できるグループをフィルタリングする必要があります (たとえば、リンクを残さない人が少なすぎるため、完全なレポートが乱雑になるのはなぜですか)。 この例では、これらはユーザーが 1 人だけのグループです。

まず、SQL クエリで何を行う必要があるかを口頭で説明します。 元の userstat テーブルのすべての行を、グループ名とソーシャル プロファイルの存在という基準に従って分割する必要があります。 したがって、テーブルデータを forum_group および is_have_social_profile フィールドでグループ化する必要があります。 ただし、私たちは 1 人だけのグループには興味がありません。 したがって、そのようなグループはフィルタリングして除外する必要があります。

注記: この問題は 1 つのフィールドのみでグループ化することで解決できることを知っておく価値があります。 case コンストラクトを使用する場合。 ただし、この例の枠組みの中で、グループ化の可能性が示されています。

また、重要な点を 1 つすぐに明確にしておきたいと思います。 集計関数を使用する場合は、having を使用してのみフィルタリングでき、個々のフィールドではフィルタリングできません。 つまり、これは where 構造ではなく、個々のレコードではなく行のグループに対するフィルターです。 中の条件も「or」と「and」を使って同様に指定しますが。

SQL クエリは次のようになります

フィールドと計算列を指定 select forum_group、is_have_social_profile、count(*) を合計として指定 -- userstat からテーブルを指定 -- フィールドによるグループ化を指定 group by forum_group、is_have_social_profile -- count(*) > 1 を持つグループ フィルターを指定

group by 構造の後のフィールドはカンマで区切られていることに注意してください。 select でのフィールドの指定は、前の例と同じ方法で行われます。 また、グループ内の行数を計算する集計関数 count も使用しました。

結果は次のとおりです。

フォーラムグループ	is_have_social_profile	合計
管理者	1	2
現代の	1	2
ユーザー	0	3

この結果がどうなったかを段階的に見てみましょう。

1. 最初に 6 つのグループが得られました。 各 forum_group グループは、is_have_social_profile フィールドの値に基づいて 2 つのサブグループに分割されました。 つまり、グループ: 、 、 、 、 、 。

注記: ちなみに、必ずしも 6 つのグループがあるわけではありません。つまり、たとえば、すべての管理者がプロファイルを入力した場合、is_have_social_profile フィールドには管理者グループのユーザーの値が 1 つしかないため、5 つのグループが存在することになります。

2. 次に、各グループにフィルタ条件を適用します。 したがって、次のグループは除外されました: 、 、 。 このような各グループ内にはソース テーブルの行が 1 行しかないためです。

3. その後、必要なデータが計算され、結果が得られました。

ご覧のとおり、使用するのに難しいことは何もありません。

データベースによっては、これらの構成要素の機能が異なる場合があることを知っておく価値があります。 たとえば、さらに多くの集計関数が存在する場合や、個々のフィールドではなく計算列をグループとして指定することができます。 この情報は仕様書ですでに確認されている必要があります。

これで、group by を使用してデータをグループ化する方法と、have を使用してグループをフィルターする方法がわかりました。

最終更新日: 2017 年 7 月 19 日

T-SQL は、次の正式な構文を使用して、GROUP BY ステートメントと HAVING ステートメントを使用してデータをグループ化します。

テーブルから列を選択する

グループ化

GROUP BY 句は、行をグループ化する方法を決定します。

たとえば、メーカーごとに製品をグループ化してみましょう

メーカーを選択し、モデルとして COUNT(*) を選択します。製品グループからメーカー別にカウントします。

SELECT ステートメントの最初の列 - メーカーはグループの名前を表し、2 番目の列 - ModelsCount はグループ内の行数を計算する Count 関数の結果を表します。

SELECT ステートメントで使用される列 (集計関数の結果を格納する列は除きます) はすべて、GROUP BY 句の後に指定する必要があることを考慮する価値があります。 したがって、たとえば上記の場合、製造元列は SELECT 句と GROUP BY 句の両方で指定されています。

また、SELECT ステートメントで 1 つ以上の列を選択し、集計関数も使用する場合は、GROUP BY 句を使用する必要があります。 したがって、次の例はグループ化式が含まれていないため機能しません。

メーカーを選択、モデルとして COUNT(*) 製品から数える

別の例として、製品数によるグループ化を追加してみましょう。

メーカー、製品数、COUNT(*) AS モデル数を製品グループからメーカー、製品数ごとに選択します

GROUP BY 句は複数の列をグループ化できます。

グループ化している列に NULL 値が含まれている場合、NULL 値を持つ行は別のグループを形成します。

GROUP BY 句は、WHERE 句の後、ORDER BY 句の前に指定する必要があることに注意してください。

メーカーを選択、COUNT(*) AS ModelsCount FROM Products WHERE Price > 30000 GROUP BY Maker ORDER BY ModelsCount DESC

グループフィルタリング。 持っている

オペレーター 持っている 出力結果にどのグループが含まれるかを決定します。つまり、グループをフィルタリングします。

HAVING の使用は、多くの点で WHERE の使用と似ています。 行のフィルタリングには WHERE のみが使用され、グループのフィルタリングには HAVING が使用されます。

たとえば、複数のモデルが定義されているすべての製品グループをメーカー別に検索してみましょう。

メーカーを選択、モデルとして COUNT(*) を選択します COUNT(*) > 1 を持つメーカーごとに製品グループからカウントします

この場合、1 つのコマンドで WHERE 式と HAVING 式を使用できます。

メーカーを選択、COUNT(*) AS ModelsCount FROM Products WHERE Price * ProductCount > 80000 GROUP BY Maker HAVING COUNT(*) > 1

つまり、この場合、最初に行がフィルタリングされ、合計コストが 80,000 を超える製品が選択され、次に選択された製品がメーカー別にグループ化されます。 次に、グループ自体がフィルタリングされ、複数のモデルを含むグループが選択されます。

並べ替えが必要な場合は、HAVING 式の後に ORDER BY 式を指定します。

メーカー、COUNT(*) AS モデル、SUM(ProductCount) AS 単位 FROM 製品 WHERE 価格 * ProductCount > 80000 GROUP BY メーカー HAVING SUM(ProductCount) > 2 ORDER BY 単位 DESC

この場合、グループ化はメーカーごとに行われ、各メーカーのモデル数 (モデル) と、これらすべてのモデルの全製品の合計数 (ユニット) も選択されます。 最後に、グループは製品数の降順に並べ替えられます。

このチュートリアルでは、SQL ステートメントの使用方法を学習します。 グループ化構文と例を示します。

説明

SQL GROUP BY 句を SELECT ステートメントで使用すると、複数のレコードにわたるデータを収集し、結果を 1 つ以上の列にグループ化できます。

構文

SQL の GROUP BY ステートメントの構文は次のとおりです。

パラメータまたは引数

expression1 、expression2 、…expression_n 集計関数にカプセル化されていない式。SQL クエリの最後の GROUP BY に含める必要があります。 aggregate_function これは、SUM、COUNT、MIN、MAX、AVG などの集計関数です。 aggregate_expression これは、aggregate_function が使用される列または式です。 tables レコードを取得するテーブル。 FROM 句では、少なくとも 1 つのテーブルを指定する必要があります。 WHERE 条件 オプション。 これらは、レコードを選択するために満たさなければならない条件です。 ORDER BY 式 オプション。 結果セット内のレコードを並べ替えるために使用される式。 複数の式を指定する場合は、値をカンマで区切る必要があります。 ASC オプション。 ASC は、式によって結果セットを昇順に並べ替えます。 これは、修飾子が指定されていない場合のデフォルトの動作です。 DESC オプション。 DESC は、式によって結果セットを降順に並べ替えます。

例 - SUM 関数での GROUP BY の使用

SQL の SUM 関数で GROUP BY を使用する方法を見てみましょう。
この例では、次のデータを含む従業員テーブルがあります。

部門ID	total_salaries
500	119500
501	113000

この例では、SUM 関数を使用して各 dept_id のすべての給与を合計し、SUM(salary) の結果に「total_salaries」のエイリアスを与えました。 dept_id は SUM 関数内でカプセル化されないため、GROUP BY 句で指定する必要があります。

例 - GROUP BY と COUNT 関数の使用

SQL の COUNT 関数で GROUP BY 句を使用する方法を見てみましょう。

この例では、次のデータを含む製品テーブルがあります。

この例では、COUNT 関数を使用して各 category_id の total_products の数を計算し、COUNT 関数の結果としてエイリアス「total_products」を指定しました。 WHERE 句でフィルタリングすることにより、NULL であるすべての category_id 値を除外しました。 category_id は COUNT 関数でカプセル化されないため、GROUP BY 句で指定する必要があります。

例 - GROUP BY と MIN 関数の使用

SQL の MIN 関数で GROUP BY 句を使用する方法を見てみましょう。

この例では、次のデータを持つemployeesテーブルを再び使用します。

2作品が選出されます。 得られる結果は次のとおりです。

部門ID	最低給与
500	57500
501	42000

この例では、MIN 関数を使用して各 dept_id の最低給与値を返し、MIN 関数の結果に「lowest_salary」というエイリアスを付けました。 dept_id は MIN 関数でカプセル化されないため、GROUP BY 句で指定する必要があります。

例 - MAX 関数での GROUP BY の使用

最後に、MAX 関数で GROUP BY 句を使用する方法を見てみましょう。

再び従業員テーブルを使用してみましょう。ただし、今回は各 dept_id の最高給与を見つけます。

従業員番号	ファーストネーム	苗字	給料	部門ID
1001	ジャスティン	ビーバー	62000	500
1002	セレナ	ゴメス	57500	500
1003	ミラ	クニス	71000	501
1004	トム	クルーズ	42000	501

次の SQL ステートメントを入力します。

			姓	生年
			イワノビッチ
			ペトロヴィッチ
			ミハイロヴィチ
			ボリソビッチ
			ニコラエヴナ
	シドロワ	キャサリン	イワノヴナ
		バレンタイン	セルゲイビッチ
		アナトリー	ミハイロヴィチ
	米。 4.20。 LIKE "^[D-M]%" を使用する

特別に定義された SQL リレーションシップに関して述語を作成できるようになりました。 特定の範囲 (BETWEEN) または数値セット (IN) 内の値を検索したり、パラメーター内のテキストに一致する文字値 (LIKE) を検索したりできます。

4.4. GROUP BY および SQL 集計関数

クエリの結果は、単一フィールドの値と同様に、一般化されたフィールドのグループ値になる場合があります。 これは、以下に示す標準 SQL 集計関数を使用して行われます。

特殊なケースの COUNT(*) を除いて、これらの関数はそれぞれ、テーブルの列内の値のコレクションを操作し、値を 1 つだけ生成します。

COUNT(*) を除くすべての関数の引数の前にキーワード DISTINCT を付けることができ、これは列の値が重複していることを示します。

関数を適用する前に除外する必要があります。 特殊関数 COUNT(*) は、テーブル内のすべての行を例外なく (重複を含む) カウントするために使用されます。

集計関数は、SELECT クエリ句のフィールド名と同様に使用されますが、フィールド名を引数として受け取るという 1 つの例外があります。 SUM および AVG では数値フィールドのみを使用できます。

数値フィールドと文字フィールドの両方を COUNT、MAX、MIN とともに使用できます。 文字フィールドで使用すると、MAX と MIN はそれらを同等の ASCII コードに変換します。これにより、MIN はアルファベット順の最初の値を意味し、MAX は最後の値を意味することがわかります。

DEPARTMENT_EMPLOYEE テーブル (図 2.3) 内のすべての給与の合計を検索するには、次のクエリを入力する必要があります。

部門_従業員から;

そして、画面には結果 46800 が表示されます (テーブルには SUM という名前の列が 1 つあります)。

平均給与額の計算も簡単です。

平均を選択 ((給与))

部門_従業員から;

COUNT 関数は他の関数とは少し異なります。 指定された列の値の数、またはテーブルの行数をカウントします。 列の値をカウントするときは、DISTINCT とともに使用して、特定のフィールドの一意の値の数をカウントします。

このテーブルには、さまざまな給与値を含む 8 つの行があります。

最後の 3 つの例では、解雇された従業員に関する情報も考慮されていることに注意してください。

次の文では、1 つあたりの分割数を決定できます。

DISTINCT の後に適用されるフィールドの名前を括弧で囲み、COUNT を個々の列に適用します。

選択数 (*)

部門_従業員から;

答えは次のようになります。

COUNT(*) は、テーブル内のすべての行をカウントします。

DISTINCT は COUNT (*) と一緒には適用できません。

PAYMENT_STATE テーブル (図 2.4) に、ステートメントの各行で行われた控除額 (控除フィールド) を格納する列がもう 1 つあると仮定します。 次に、全額に興味がある場合は、「金額」列と「控除」列の内容を合計する必要があります。

明細書に含まれる控除を考慮した最大金額に興味がある場合は、次の文を使用して確認できます。

SELECT MAX (合計 + 減算)

給与明細から;

このクエリは、テーブル内の各行について、[金額] フィールドの値と [控除] フィールドの値を加算し、見つかった最大値を選択します。

GROUP BY CLAUSE (並べ替え、順序)

GROUP BY 句を使用すると、特定のフィールドの値のサブセットを別のフィールドに関して定義し、そのサブセットに集計関数を適用できます。 これにより、単一の SELECT 句でフィールドと集計関数を組み合わせることが可能になります。

たとえば、各部門の従業員の数を調べたいとします (答えは図 4.21 に示されています)。

SELECT 部門 ID, COUNT (DISTINCT 部門 ID) AS Number ofemployees

	部門_従業員
	解雇日
			就業者数

GROUP BY 句では、一意の列値のみが残り、デフォルトでは昇順に並べ替えられます。 この点で、GROUP BY 句は ORDER BY 句とは異なります。ORDER BY 句はレコードを昇順に並べ替えますが、重複する値は削除しません。 この例では、クエリは Dept_ID 列の値 (部門番号ごと) によってテーブルの行をグループ化します。 同じ部門番号を持つ行は最初にグループに結合されますが、グループごとに 1 行のみが表示されます。 2 番目の列には、各グループの行数が表示されます。 各部門の従業員の数。

GROUP BY が適用されるフィールドの値は、集計関数と同様に、定義上、出力グループごとに 1 つの値のみを持ちます。 その結果、この方法で集計とフィールドを組み合わせることができる互換性が得られます。

たとえば、PAYMENT_LIST テーブルが図のようになっているとします。 4.22 で、各従業員に支払われる給与の最高額を決定したいと考えています。

				支払い方法

その結果、私たちは得ます。

米。 4.23。 AS を使用した集計関数

グループ化は、いくつかの属性によって行うことができます。

シート1から

従業員 ID、日付によるグループ化。

結果：

米。 4.24。 複数の属性によるグループ化

GROUP BY の後に取得されるグループの数を制限する必要がある場合は、HAVING 句を使用してこれを実装できます。

4.5. HAVING 句の使用

HAVING 句は、WHERE 句が行に対して行うのと同じ役割をグループに対して果たします。HAVING 句は、WHERE が行を除外するのと同じように、グループを除外するために使用されます。 このフレーズは文中に含まれています

GROUP BY 句があり、HAVING の式がグループに対して 1 つの値を取る必要がある場合のみ。

たとえば、部門番号 3 を除くすべての部門の定量的構成 (図 2.3) を示す必要があるとします。

SELECT 部門 ID, COUNT (DISTINCT 部門 ID) AS _employees の数

	部門_従業員
	解雇日
			ID_Department を持っています< > 3;
				就業者数

テーブル式を評価するときの最後の要素は、HAVING セクション (存在する場合) です。 このセクションの構文は次のとおりです。

::=

持っている

このセクションの検索条件は、グループ化されたテーブル内の行のグループに対する条件を指定します。 正式には、HAVING セクションは、GROUP BY を含まないテーブル式にも存在できます。 この場合、前のパーティションの計算結果は、専用のグループ化列を持たない単一のグループで構成されるグループ化テーブルであると想定されます。

HAVING 句の検索条件は、WHERE 句の検索条件と同じ構文規則に従い、同じ述語を含めることができます。

ただし、特定のテーブル式の FROM 句からのテーブル列指定の検索条件での使用に関しては、特別な構文上の制限があります。 これらの制限は、HAVING セクションの検索条件が個々の行ではなくグループ全体の条件を設定するという事実に基づいています。

したがって、HAVING句の選択句に含まれる述語算術式には、GROUP BY句でグループ化列として指定した列の指定だけが直接使用できます。 残りの列は、集計関数 COUNT、SUM、AVG、MIN、および MAX の仕様内でのみ指定できます。この場合、行のグループ全体の集計値が計算されます。 状況は、HAVING セクションの選択条件の述語に含まれるサブクエリでも同様です。サブクエリが現在のグループの特性を使用する場合、グループ化列を参照することによってのみ指定できます。

クエリを次の形式にします (ベース テーブルについては、図 4.22 を参照)。

SELECT 従業員 ID、日付、MAX ((金額))

シート1から

従業員 ID、日付によるグループ化。

1000 を超える支払いのみが表示されるように明確にする必要があります。

ただし、標準では、述語は単一行として評価され、集計関数は行のグループとして評価されるため、WHERE 句で集計関数を使用することは (後述するサブクエリを使用しない限り) 違法です。 。

次の文は正しくありません。

SELECT Id_id、日付、MAX (金額)

シート1から

WHERE MAX ((合計)) > 1000 GROUP BY Comp_Id、日付;

正しい文は次のようになります。

SELECT 従業員 ID、日付、MAX ((金額))