集計関数を使用する

3 分

T-SQL には、複数の値を受け取って 1 つの結果を返す計算を実行するための、SUM、MAX、AVG などの集計関数が用意されています。

集計関数を使用する

ここまで見てきたクエリの大部分は、WHERE 句を使用して行をフィルター処理し、一度に 1 つの行を操作しています。返される各行は、元のデータセット内の 1 つの行に対応します。

SQL Server には多くの集計関数が用意されています。このセクションでは、SUM、MIN、MAX、AVG、COUNT などの最も一般的な関数について説明します。

集計関数を使用する場合は、次の点を考慮する必要があります。

集計関数は 1 つの (スカラー) 値を返し、1 つの値を使用できるほぼすべての箇所において SELECT ステートメントで使用できます。たとえば、これらの関数は SELECT、HAVING、および ORDER BY 句で使用できます。ただし、WHERE 句では使用できません。
COUNT(*) を使用する場合を除き、集計関数で NULL は無視されます。
AS を使用して別名を指定しない限り、SELECT リスト内の集計関数には列ヘッダーがありません。
SELECT リスト内の集計関数により、SELECT 操作に渡したすべての行に対して操作が行われます。 GROUP BY 句がない場合は、WHERE 句にある任意のフィルターを満たすすべての行が集計されます。 GROUP BY については、次のトピックで詳しく説明します。
GROUP BY を使用している場合を除き、集計関数と、同じ SELECT リスト内の関数に含まれていない列を組み合わせることはできません。

組み込み関数を超えて拡張するために、SQL Server には、.NET 共通言語ランタイム (CLR) を介したユーザー定義の集計関数のメカニズムが用意されています。そのトピックについては、このモジュールでは扱いません。

組み込み集計関数

前述のように、Transact-SQL には多数の組み込み集計関数が用意されています。よく使用する関数には次のものがあります。

関数名

構文

説明

[SUM]

SUM(expression)

列にある NULL でないすべての数値を合計します。

AVG

AVG(expression)

列にある NULL でないすべての数値の平均値を計算します (sum/count)。

[MIN]

MIN(expression)

照合順序の並べ替え規則に従って、最も小さい数値、最も古い日付/時刻、または最初に出現する文字列を返します。

[MAX]

MAX(expression)

照合順序の並べ替え規則に従って、最も大きな数値、最も新しい日付/時刻、または最後に出現する文字列を返します。

COUNT または COUNT_BIG

COUNT(*) または COUNT(expression)

(*) が使用されている場合は、NULL である行を含め、すべての行をカウントします。 expression として列が指定されている場合は、その列の NULL でない行の数を返します。 COUNT は int を返します。COUNT_BIG は、big_int を返します。

SELECT 句で組み込みの集計を使用するには、MyStore サンプルデータベースの次の例を参考にしてください。

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

このクエリの結果は次のようになります。

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

上記の例では、Production.Product テーブルのすべての行が集計されています。次のように WHERE 句を追加することで、特定のカテゴリの製品の平均、最小、最大の価格を返すようにクエリを簡単に変更できます。

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

SELECT 句で集計を使用する場合、SELECT リストで参照される列はすべて、集計関数の入力として使用するか、GROUP BY 句で参照する必要があります。

次のクエリについて考えてみましょう。これは、集計された結果に ProductCategoryID フィールドを含めようとするものです。

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

このクエリを実行すると、次のエラーが発生します

メッセージ 8120、レベル 16、状態 1、行 1

選択リストの中の列 'Production.ProductCategoryID' が無効です。その列は集計関数にも GROUP BY 句にも含まれていないためです。

このクエリにおいては、すべての行が 1 つの集計グループとして扱われます。そのため、すべての列を集計関数の入力として使用する必要があります。

これまでの例として、前の例では価格や数量などの数値データを集計しました。集計関数の中には、日付、時刻、および文字データを集計するために使用できるものもあります。以下の例は、日付と文字での集計の使い方を示しています。

このクエリには MIN と MAX が使用されていて、名前で最初と最後の会社が返されます。

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

このクエリにより、データベースの照合順序 (この例ではアルファベット順) で最初と最後の CompanyName の値が返されます。

MinCustomer

MaxCustomer

A Bike Store

Yellow Bicycle Company

集計関数に他の関数を入れ子にすることができます。

たとえば、次の例では、MIN と MAX を評価する前に、注文日の年の部分のみを返すために YEAR スカラー関数を使用しています。

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

最も早い

最も遅い

2008

2021

MIN および MAX 関数は、日付データと共に使用して、最も古い、および最も新しい時系列の値を返すこともできます。ただし、AVG および SUM は数値データに対してのみ使用できます。これには、int、money、float、decimal の各データ型が含まれます。

DISTINCT を集計関数と共に使用する

重複する行を削除するために、SELECT 句で DISTINCT を使用することに注意する必要があります。 DISTINCT を集計関数と共に使用すると、集計値を計算する前に、重複する値が入力列から削除されます。 DISTINCT は、orders テーブル内の顧客など、値の一意の出現を集計するときに便利です。

次の例では、何回注文したかに関係なく、注文した顧客の数が返されます。

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<some_column>) の場合、ある値を列に持つ行の個数をカウントするだけです。 NULL 値がない場合、COUNT(<some_column>) は COUNT(*) と同じです。 COUNT (DISTINCT <some_column>) は、列に含まれる異なる値の個数をカウントします。

集計関数と NULL を使用する

データに NULL が存在する可能性と、集計関数を含む T-SQL クエリコンポーネントで NULL がどのように処理されるかについて注意することが重要です。注意すべきいくつかの考慮事項があります。

(*) オプションを付けて使用する COUNT を除き、T-SQL 集計関数で NULL は無視されます。たとえば、SUM 関数では、NULL でない値だけが追加されます。 NULL は 0 と評価されません。 COUNT(*) は、列の値に関係なく、また値がない場合でも、すべての行をカウントします。
AVG は、値が設定されている行だけを合計し、その合計を NULL でない行の数で除算するため、列に NULL が存在すると、計算が不正確になる可能性があります。 AVG(<column>) と (SUM(<column>)/COUNT(*)) の結果は異なる場合があります。

たとえば、t1 という名前の次のテーブルについて考えてみます。

NULL

このクエリは、AVG で NULL を処理する方法と、SUM/COUNT(*) の計算列を使用して平均を計算する方法の違いを示しています。

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

結果は次のようになります。

sum_nonnulls

count_all_rows

count_nonnulls

average

arith_average

150

この結果セットにおいて、average という名前の列は、内部的に 150 という合計を取得し、列 c2 の null でない値の個数で除算する集計です。計算は 150/5 つまり 30 になります。 arith_average という列は、合計をすべての行の個数で明示的に除算するため、計算は 150/6 つまり 25 です。

NULL かどうかに関係なくすべての行を集計する必要がある場合は、集計関数で無視されない別の値に NULL を置き換えることを検討してください。この目的には、COALESCE 関数を使用できます。

集計関数を使用する