Oracle SQL 関数その3(集計関数)
このページはページの再構築に伴い移転しました。3秒後にジャンプします。
ジャンプしない場合は、以下のURLをクリックしてください。
- データの存在する行数を計数
- データの合計値を返す
- データ内の最大値及び最小値を返す
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行が選択されました。