Oracle SQL 関数その３(集計関数:COUNT,SUM,MAX,MIN,GROUP BY)

Oracle SQL 関数その３(集計関数)

このページはページの再構築に伴い移転しました。3秒後にジャンプします。
ジャンプしない場合は、以下のURLをクリックしてください。

COUNT
データの存在する行数を計数
SUM
データの合計値を返す
MAX，MIN
データ内の最大値及び最小値を返す

COUNT　：　データの存在する行数を計数

　numberRet := COUNT( [ * , [ALL , DISTINCT] <expr> ] [ OVER <分析句> ] )

COUNTは集計関数、または分析関数として使用します。
COUNT(*)は行の項目にNULLがあっても、また重複行があっても全ての行の数を返します。
<expr>が指定された場合は、NULLの項目は計数されません。また、DISTINCT指定の場合は重複行は計数されません。

COUNTの説明をする為に、以下のTT_売上明細の表を例にします。

SQL> SELECT * FROM TT_売上明細 ORDER BY 売上番号,明細番号;
 
  売上番号   明細番号 商品コード   売上数量 備考                                       売上単価
---------- ---------- ---------- ---------- ---------------------------------------- ----------
         1          1          1          2 *****
         1          2          4          1 NOTE-1010                                    188000
         2          1          2          1 PC-9002                                      120000
         2          2          5          2 NOTE-1020                                    200000
         2          3         11          1 HUB-A001                                      40000
         3          1          3          3 PC-9003                                      190000
         4          1          6          1 NOTE-1030                                    220000
         4          2          7          2 PRT-3001                                      88000
         5          1          8          3 PRT-4001                                     180000
         5          2          9          2 CRT-1001                                      78000
         6          1          8          1 PRT-4001                                     180000
         6          2          9          1 CRT-1001                                      78000
         6          3         10          2 CRT-2001                                      98000
         8          1          8          3 PRT-4001                                     180000
         8          2          9          1 CRT-1001                                      78000
         9          1          7          2 PRT-3001                                      88000
         9          2          9          1 CRT-1001                                      78000
 
17行が選択されました。

以下に、COUNT(*)、COUNT(<expr>)、及びCOUNT(DISTINCT <expr>)の例を示します。

SQL> SELECT COUNT(*),COUNT(商品コード),COUNT(DISTINCT 商品コード) FROM TT_売上明細;
 
  COUNT(*) COUNT(商品コード) COUNT(DISTINCT商品コード)
---------- ----------------- -------------------------
        17                17                        11

確かに、DISTINCT指定は商品コードの重複分を除き、TT_売上明細での商品コードの使用種類を表しています。
さらに、COUNTとGROUP BY句の使用例を以下に示します。各商品毎の売上件数を計数しています。

SQL> SELECT 商品コード,COUNT(商品コード) FROM TT_売上明細
  2  GROUP BY 商品コード
  3  ORDER BY 商品コード;
 
商品コード COUNT(商品コード)
---------- -----------------
         1                 1
         2                 1
         3                 1
         4                 1
         5                 1
         6                 1
         7                 2
         8                 3
         9                 4
        10                 1
        11                 1
 
11行が選択されました。

分析の例として以下に示します。ORDER BY句で計数の対象となるカラムを指定し、RANGE指定で現在値からみて前後の値の範囲を指定します。PRECEDINGからFOLLOWINGまでの範囲に入るデータの件数を計数することになります。以下の例でいえば、COUNT1のカラムは売上数量が現在値より1個少なくかつ現在値までの明細件数を計数し、COUNT2のカラムは現在の商品コードと同じものが存在する明細件数を計数します。

SQL> SELECT
  2   売上番号,明細番号,商品コード,売上数量
  3  ,COUNT(*) OVER (ORDER BY 売上数量
  4    RANGE BETWEEN 1 PRECEDING AND 0 FOLLOWING) AS COUNT1
  5  ,COUNT(*) OVER (ORDER BY 商品コード
  6    RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING) AS COUNT2
  7  FROM TT_売上明細
  8  ORDER BY 売上番号,明細番号;
 
  売上番号   明細番号 商品コード   売上数量     COUNT1     COUNT2
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          2         14          1
         2          1          2          1          8          1
         3          1          3          3          9          1
         1          2          4          1          8          1
         2          2          5          2         14          1
         4          1          6          1          8          1
         9          1          7          2         14          2
         4          2          7          2         14          2
         6          1          8          1          8          3
         8          1          8          3          9          3
         5          1          8          3          9          3
         6          2          9          1          8          4
         8          2          9          1          8          4
         9          2          9          1          8          4
         5          2          9          2         14          4
         6          3         10          2         14          1
         2          3         11          1          8          1
 
17行が選択されました。

その他、COUNTの例としてGROUP　BY句のテーブルカラム値に加工を用いる場合と、FROM句内の副問合せでの利用を示します。

SQL> SELECT SUBSTR(備考,1,3) AS 分類 , COUNT(売上数量) FROM TT_売上明細
  2  GROUP BY SUBSTR(備考,1,3)
  3  ORDER BY SUBSTR(備考,1,3);
 
分類   COUNT(売上数量)
------ ---------------
***                  1
CRT                  5
HUB                  1
NOT                  3
PC-                  2
PRT                  5
 
6行が選択されました。
 
 
SQL> SELECT TT.商品コード,TT.商品件数,TC.全件数,(TT.商品件数 / TC.全件数) * 100 AS 構成比
  2  FROM (
  3   SELECT 商品コード,COUNT(商品コード) AS 商品件数 FROM TT_売上明細
  4   GROUP BY 商品コード
  5  ) TT,
  6  (
  7   SELECT COUNT(商品コード) AS 全件数 FROM TT_売上明細
  8  ) TC;
 
商品コード   商品件数     全件数     構成比
---------- ---------- ---------- ----------
         1          1         17 5.88235294
         2          1         17 5.88235294
         3          1         17 5.88235294
         4          1         17 5.88235294
         5          1         17 5.88235294
         6          1         17 5.88235294
         7          2         17 11.7647059
         8          3         17 17.6470588
         9          4         17 23.5294118
        10          1         17 5.88235294
        11          1         17 5.88235294
 
11行が選択されました。

ページのトップへ戻る

SUM　：　データの合計値を返す

　numberRet := SUM( [ALL , DISTINCT] <expr> [ OVER <分析句> ] )

SUMは集計関数、または分析関数として使用します。
<expr>がNULLの項目は計算されません。また、DISTINCT指定の場合は重複行は計算されません。

簡単な例として、単なる合計を求めるSQL文と、GROUP　BY句を使用したSQL文を以下に示します。最初の例は、売上明細の全ての売上金額を合計しています。条件付けのWHERE句がありませんのでTT_売上明細に存在する全ての行が対象になります。
　2番目の例は、売上番号毎の売上金額の合計を計算しています。共にSUM関数の中で数量と単価の乗算を行っていますが、括弧の中は<式>が許されますので、<式>として問題の無いものはすべて許されます。

SQL> SELECT SUM(売上数量 * 売上単価) AS 売上金額
  2  FROM TT_売上明細;
 
  売上金額
----------
   3736000
 
SQL> SELECT 売上番号, SUM(売上数量 * 売上単価) AS 売上金額
  2  FROM TT_売上明細
  3  GROUP BY 売上番号
  4  ORDER BY 売上番号;
 
  売上番号   売上金額
---------- ----------
         1     188000
         2     560000
         3     570000
         4     396000
         5     696000
         6     454000
         8     618000
         9     254000
 
8行が選択されました。

次に2個のテーブルを利用して別の項目でグループ化を行ってSUM関数を利用する例を示します。最初のSQL文は、TT_売上とTT_売上明細から得意先毎の売上金額の合計を求めます。WHERE句で売上伝票のヘッダーデータであるTT_売上の売上番号とTT_売上明細の売上番号でリンクを張り、TT_売上の得意先コードでグループ化を行います。（売上先を示す得意先コードはヘッダーにしか持たない為、当然この様になります）　2番目のSQL文は最初とは視点を変えて、担当者ごとの売上金額の合計を求めます。

SQL> SELECT TH.得意先コード,SUM(TD.売上数量 * TD.売上単価) AS 売上金額
  2  FROM TT_売上 TH,TT_売上明細 TD
  3  WHERE TH.売上番号 = TD.売上番号
  4  GROUP BY TH.得意先コード
  5  ORDER BY TH.得意先コード;
 
得意先コード   売上金額
------------ ----------
           1    1514000
           2     560000
           3     396000
           4     570000
           5     696000
 
SQL> SELECT TH.担当者コード,SUM(TD.売上数量 * TD.売上単価) AS 売上金額
  2  FROM TT_売上 TH,TT_売上明細 TD
  3  WHERE TH.売上番号 = TD.売上番号
  4  GROUP BY TH.担当者コード
  5  ORDER BY TH.担当者コード;
 
担当者コード   売上金額
------------ ----------
           1    1060000
           2     396000
           4     560000
           5     570000
           7     696000
           9     454000
 
6行が選択されました。

ページのトップへ戻る

MAX，MIN　：　データ内の最大値及び最小値を返す

　numberRet := MAX( [ALL , DISTINCT] <expr> [ OVER <分析句> ] )

MAX,MINは集計関数、または分析関数として使用します。
<expr>がNULLの項目は計算されません。また、DISTINCT指定の場合は重複行は計算されません。

例として、SUM関数で示した得意先コード、担当者コードでのグループ化をMAX,MIN関数に適応してみます。

SQL> SELECT
  2   TH.得意先コード
  3  ,SUM(TD.売上数量 * TD.売上単価) AS 売上金額
  4  ,MAX(TD.売上数量 * TD.売上単価) AS MAX売上金額
  5  ,MIN(TD.売上数量 * TD.売上単価) AS MIN売上金額
  6  FROM TT_売上 TH,TT_売上明細 TD
  7  WHERE TH.売上番号 = TD.売上番号
  8  GROUP BY TH.得意先コード
  9  ORDER BY TH.得意先コード;
 
得意先コード   売上金額 MAX売上金額 MIN売上金額
------------ ---------- ----------- -----------
           1    1514000      540000       78000
           2     560000      400000       40000
           3     396000      220000      176000
           4     570000      570000      570000
           5     696000      540000      156000
 
SQL> SELECT
  2   TH.担当者コード
  3  ,SUM(TD.売上数量 * TD.売上単価) AS 売上金額
  4  ,MAX(TD.売上数量 * TD.売上単価) AS MAX売上金額
  5  ,MIN(TD.売上数量 * TD.売上単価) AS MIN売上金額
  6  FROM TT_売上 TH,TT_売上明細 TD
  7  WHERE TH.売上番号 = TD.売上番号
  8  GROUP BY TH.担当者コード
  9  ORDER BY TH.担当者コード;
 
担当者コード   売上金額 MAX売上金額 MIN売上金額
------------ ---------- ----------- -----------
           1    1060000      540000       78000
           2     396000      220000      176000
           4     560000      400000       40000
           5     570000      570000      570000
           7     696000      540000      156000
           9     454000      196000       78000
 
6行が選択されました。

分析の例として以下に示します。以下の例は、売上明細を商品コード毎で見て、それぞれの売上数量のMAX,MINを抽出します。最初のSQL文は商品コードの重複表示がされますが、これは全ての売上明細の行ごとに処理されているためです。同一商品コードは一行しか表示されないようにしたのが2番目のSQL文で、DISTINCTを商品コードに付加しています。

SQL> SELECT
  2   商品コード
  3  ,MAX(売上数量) OVER(PARTITION BY 商品コード) AS 最大売上数量
  4  ,MIN(売上数量) OVER(PARTITION BY 商品コード) AS 最少売上数量
  5  FROM TT_売上明細
  6  ORDER BY 商品コード;
 
商品コード 最大売上数量 最少売上数量
---------- ------------ ------------
         1            2            2
         2            1            1
         3            3            3
         4            1            1
         5            2            2
         6            1            1
         7            2            2
         7            2            2
         8            3            1
         8            3            1
         8            3            1
         9            2            1
         9            2            1
         9            2            1
         9            2            1
        10            2            2
        11            1            1
 
17行が選択されました。
 
SQL> SELECT
  2   DISTINCT 商品コード
  3  ,MAX(売上数量) OVER(PARTITION BY 商品コード) AS 最大売上数量
  4  ,MIN(売上数量) OVER(PARTITION BY 商品コード) AS 最少売上数量
  5  FROM TT_売上明細
  6  ORDER BY 商品コード;
 
商品コード 最大売上数量 最少売上数量
---------- ------------ ------------
         1            2            2
         2            1            1
         3            3            3
         4            1            1
         5            2            2
         6            1            1
         7            2            2
         8            3            1
         9            2            1
        10            2            2
        11            1            1
 
11行が選択されました。