今日のレッスンでは、SQL GROUP BY句を使用したクエリから返された結果のグループ化について学びます。
今日のレッスンの目的は次のとおりです。
- GROUP BYを使用して結果をグループ化する方法を学ぶ
- 集計関数を使用して計算を実行する
- HAVING句を使用してグループをフィルタリングする方法を理解する
重要！ に従って、データベース内の例を行ってください。 まだ行っていない場合は、SQL Serverの使用を開始するための私のガイドにサインアップしてください。, まだインストール方法は無料のツールやサンプルデータベースです。
SQL GROUP BY句
SQL GROUP BY句は、指定された列値にまたがる行を出力するために使用されます。 これは、通常、SUMやCountなどの集計関数と組み合わせて値を要約するために使用されます。 アプリケーショングループが独自に組み合わせます。 返すのではなく、各列テーブル、価値観をグループ化のみが独自に組み合わせが返されます。
GROUP BY句は、WHERE句の後にSQL文に追加されます。, ここでは、100より大きい数量を除いてSalesOrderIDをリストしている例を示します。
ただし、重要な違いがあります。, DISTINCT修飾子は、一意の行の組み合わせの出力を停止しますが、GROUP BYステートメントでは、一意の組み合わせごとに基になるフィルター処理された行に基づいて値を計算できます。つまり、この例をGROUP BYで使用すると、注文ごとの数またはOrderDetailsを次のように計算できます。
COUNTは集計関数の例であり、これらは実際にGROUP BYステートメントに特別な値を与えるものです。
SQL集計関数
SUMなどの一部の関数は、行のグループに対して計算を実行するために使用されます。 ほとんどの場合、これらの関数はGROUP BY句を使用して定義された値のグループで動作します。, GROUP BY句がない場合、一般に、集計関数はすべてのフィルタリングされた結果に適用されると理解されます。
最も一般的な集計関数のいくつかは次のとおりです。
| AVG(expression) | 式の平均を計算します。 |
| COUNT(expression) | 式によって返されるnull以外の値の出現回数をカウントします。 |
| COUNT(*) | 指定されたテーブル内のすべての行をカウントします。, |
| MIN(expression) | 式の最小値を求めます。 |
| MAX(expression) | 式の最大値を求めます。 |
| SUM(expression) | 式の合計を計算します。 |
これらの関数は、GROUP BY句と組み合わせて単独で使用できます。 ただし、GROUP BYと一緒に使用すると、グループ化が変更されるたびに計算が”リセット”されます。, このようにして、彼らは小計として機能します。
単純集計関数
集計関数を使用する場合、すべての値または個別の値の結果を計算できます。 たとえば、すべてのSalesOrderDetailsレコードをカウントするには、次の式を使用できます。
SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
これにより、121317のカウントが返されます。
詳細を構成する注文の個別をカウントするには、次のようにします。
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
現在のカウントは31465です。,
SQL GROUP BYおよび集計関数
集計するには、個々の部分から全体を作成することを意味します。 集計関数は、結果を返すために複数の行に対して機能する関数です。
AVGおよびSUM
SUM関数は返された値を合計し、同様の方法でAVGが平均を計算します。
OrderDetailsから合計注文金額を計算できるかどうかを見てみましょう。,
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
式に集計関数を適用できるので、OrderIDにグループ化を設定して注文あたりの合計価格を
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
合計でソートして上位の注文を最初に取得することもできます
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC
同様の方法で、avgを使用して平均注文の詳細を計算できます。 なぜてみました！ 道に迷った場合はお気軽切り替えたと言えるのでしょうか。
好奇心のために、平均はサンプルの合計をサンプル数で割ったものとして計算されるので、上記のステートメントでAVGを使用することは同じです。
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
このセクションでは多くのことを説明しました—–, ここでは覚えておくべきいくつかの重要なポイントがあります：
- 集計関数はSUM(A+B)などの式を評価することができます
- 集計関数の別名を付ける必要がありますので、列名は意味があります
- 集計関数とGROUP BYで作業するときは、最初に詳細について考える方が簡単なことがあります。 私はこれをより良いクエリを書く方法で説明します。
SQL COUNT
COUNT関数は、テーブルまたはグループ内に存在するレコードの数を知る必要がある場合に使用されます。, COUNT(*)はグループ化のすべてのレコードをカウントしますが、COUNT(expression)はexpressionの結果がnullでないすべてのレコードをカウントします。 また、DistinctをCOUNTと共に使用して、グループ内の一意の値の数を検索することもできます。
注文あたりのSalesOrderDetail行の数を検索するには
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
製品あたりの一意の販売注文の数を検索するには
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
MINおよびMAX
MINおよびMAXを使用して、テーブルまたはグループ内の最小値および最大値をそれぞれ検索します。,
たとえば、注文内で注文された最小および最大の製品数量を見つけるには、
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
計算の最小値または最大値を見つけることもできます。 ここでは、製品内で注文された最高の製品量を見つけます。
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
HAVING句
SQL HAVING句は、集計関数の結果に応じてグループをフィルタリングする これにより、複数の注文詳細明細行を持つすべての注文を選択するなどの問題を解決できます。,
この例は次のようになります
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
$1000より大きいすべての注文を見つけたい場合は、
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
ORDER BY句でエイリアスTotalPriceを使用できますが、句を持つ場合は式を使用する必要があります。
ハンマーホーム持つために、私は最後の例を示したいと思います。 ここでは、HAVINGステートメントにSELECTリストにない集計関数が含まれていることがわかります。,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
上記のクエリでは、平均SalesOrderDetail金額が$500.00より大きい注文の合計価格を取得しています。
HAVINGについての最後のステートメント
同様の機能を実行しますが、WHERE句とHAVINGの間には重要な違いがあります。 WHERE句は、個々のレコードをフィルタリングします。
私の頭の中でまっすぐに保つために、グループ化が行われる前にWHERE句がその作業を行い、グループが形成された後にHAVING句が引き継がれることを考え,
エクササイズ
練習することが重要です！ 用のサンプルデータベースに答えられていると思います。
- HRは、職位別にアクティブな従業員数のレポートを求めています。 どのSQLを使用しますか？
- SalesOrderDetailsに各製品の注文数量の最小値、最大値、および平均を表示します。
- すべての従業員の役職、および病気休暇時間の平均数が四十以下である従業員の数をリストします。
- 上記の＃3で返された役職の場合、対応する役職の回答＃1の結果のカウントは同じですか？li>
答えはここにあります！,
Leave a Reply