図でイメージするOracle DatabaseのSQL全集

第3回 分析関数
Oracle SQLの各機能をイメージ図を交えて解説

Oracle ACE
山岸 賢治(やまぎし けんじ)ACE

SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。

目次

今回のテーマ

今回は、下記のOracleのSQL文の評価順序においての、9番目のselect句と11番目のorder by句で使用可能な、分析関数の典型的な使用例と、私のSQLのイメージを解説します。

1番目 from句
2番目 where句 (結合条件)
3番目 start with句
4番目 connect by句
5番目 where句 (行のフィルタ条件)
6番目 group by句
7番目 having句
8番目 model句
9番目 select句
10番目 union、minus、intersectなどの集合演算
11番目 order by句

動作確認環境

Oracle Database 11g Release 11.2.0.1.0 (windows 32ビット版)

分析関数のメリット

分析関数のメリットは、Oracle8iまでは、自己結合や相関サブクエリを使ったり、親言語(JavaやVB6など)やPL/SQLで求めていた結果を、SQLで容易に求めることができるようになることです。
帳票作成やデータ分析で特に使います。

さらに、分析関数のメリットについて
Tom Kyteさんの記事、Oracle Database 11g Release 2に関する10の重要なことから引用しておきます。
「分析関数は、SQLにSELECTというキーワードが加わって以来のすばらしい出来事だ」
分析関数はSQLでは、かつては実現できなかったことを可能にしてくれます。
分析関数によって、SQLはプロシージャ言語のようなものに変わり、前の行や次の行へのアクセスが可能となるのです。
これは、もともとのSQLにはなかった発想です。

▲ ページTOPに戻る

分析関数の使用頻度

使用頻度の高いものからおさえていくと学習効率が高いです。

最頻出
count  max  min  sum  Row_Number

頻出
Lag  Lead  Rank  dense_rank

たまに
avg  First_Value  Last_Value  nth_Value  ListAgg  wmsys.wm_concat

レア
Ratio_To_Report  median  NTile  regr_count

▲ ページTOPに戻る

count(*) over()

select文の件数取得

count(*) over()は、select文の結果の行数を列に持たせたい時に使います。

create table CountSample(Val) as
select 10 from dual union all
select 20 from dual union all
select 20 from dual union all
select 30 from dual union all
select 30 from dual;
-- count(*) over()を使ったSQL
select Val,count(*) over() as recordCount
  from CountSample;
出力結果
Val  recordCount
---  -----------
 10            5
 20            5
 20            5
 30            5
 30            5

select文の結果の行数である5がrecordCount列の値になってますね。
SQLのイメージは下記です。count(*) over()で黄緑線を引いてます。

count(*) over()のイメージ

distinct指定のあるselect文の件数を取得する場合は、下記のようにインラインビューを使うといいでしょう。
(select句の評価順序において、分析関数の評価後に、distinctによる重複排除があるため)

-- distinct指定のあるselect文の件数取得
select Val,count(*) over() as recordCount
from (select distinct Val
        from CountSample)
order by Val;
出力結果
Val  recordCount
---  -----------
 10            3
 20            3
 30            3

▲ ページTOPに戻る

Left Curve
図でイメージする
Oracle DatabaseのSQL全集
Right Curve