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

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

Oracle ACE
山岸 賢治（やまぎし けんじ）

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

目次

• 今回のテーマ
• 分析関数のメリット
• 分析関数の使用頻度
• count(*) over()
• count(*) over() と minus
• max(Val) over(partition by PID)
• count(distinct Val) over(partition by PID)
• Row_Numberとrankとdense_rank
• LagとLead
• sum(Val) over(order by SortKey)
• First_ValueとLast_Valueとnth_Value
• First_ValueとLast_Valueのignore nulls
• Rows 2 Preceding
• Range 2 Preceding
• 全称肯定,全称否定,存在肯定,存在否定
• ListAggとwmsys.wm_concat
• Range指定でInterVal型の使用

今回のテーマ

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

動作確認環境

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()で黄緑線を引いてます。

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に戻る

• 1
• 2
• 3
• 4
• 5
• 次のページへ ＞