並び替え後の連続した同一値を変換、または、レコードを選択しない
並び替え後の連続した同一値を別の値に変換したり省略したい例として多いのは
帳票出力において連続する重複する列を印字を省略して欲しいという要件などが該当する。
具体的な例としては、社員一覧を部門別、社員番号順に並び替え、部門名、社員番号、社員名、･･･の一覧表を帳票出力する際に 「同一の部門が連続する場合には部門名の重複表記はしないようにする。」 などである。
プログラム的に言れば、同一カラムの直前行の値が現在行と同値であれば表示しない。
LAG 分析関数と LEAD 分析関数
Oracle 8i エンタープライズ版、または、Oracle 9i スタンダード版以上であれば、複雑な自己結合や PL/SQL などの手続き言語による カーソルループ処理 を用いて重複データを検出して置換する必要がない。
LAG / LEAD 分析関数を使用するだけで先読みやバッファ処理と同じことが SQL のみで利用することが可能になる。
LAG 分析関数
LAG 分析関数は、結果セットにおいてカレント行から相対的に n レコード前の行にアクセスできる関数である。
書式は
LAG ( expr [,offest [,defalut]] )
OVER ( [partition_cluase] order_by_cluase )
- offset は相対位置の行数（デフォルト 1）
- default は offest による操作がウィンドウ外になった場合のデフォルト値を任意に指定する（デフォルト NULL）
LEAD 分析関数も LAG と書式が同じで相対レコードの参照先が n レコード後の行にアクセスできる関数
書式は
LEAD ( expr [,offest [,defalut]] )
OVER ( [partition_cluase] order_by_cluase )
並び替え後の連続した同一値を "上に同じ" に変換する
ここでは、シンプルな例を示す。id と code 列があり、id を昇順に並べ替えて code 値が直前の値と同じ値なら "上に同じ" とする。
SQL> -- NULL を見えるように編集
SQL> column code_prev null '<null>'
--
select id, decode(code, prev_code, '上に同じ', code) print_code, code, prev_code
from (
select id, code, lag(code) over (order by id) prev_code from lag_test
-- ↑ 直前のレコードの code を参照（id 列で昇順の行セット）
)
order by id;
ID PRINT_CODE CODE PREV_CODE
---------- ------------ ------------ ------------
10 a a <null>
11 b b a
26 a a b
38 上に同じ a a
59 上に同じ a a
96 b b a
⇒ SQL*Plus の NULL 表示
テーブル定義とサンプルデータ
drop table rivus.lag_test;
create table rivus.lag_test
(
id number(5,0) primary key,
code varchar2(6)
)
/
insert into lag_test values (10,'a');
insert into lag_test values (11,'b');
insert into lag_test values (26,'a');
insert into lag_test values (38,'a');
insert into lag_test values (59,'a');
insert into lag_test values (96,'b');
並び替え後の連続した同一値をもつレコードを削除する
上の 「並び替え後の連続した同一値を "上に同じ" に変換する」例を応用することで、
並び替え後の連続した同一値をもつレコードを削除する（選択しない）ようにすることも可能になる。
抽出する DECODE(...) の部分を編集して条件述語に移動することで全く同じ要領で処理することができる。
select id, code, prev_code
from (
select id, code, lag(code) over (order by id) prev_code from lag_test
)
where decode(code, prev_code, 1, 0) = 0
order by id ;
ID CODE PREV_CODE
---------- ------------ ------------
10 a <null>
11 b a
26 a b
96 b a
条件述語で DECODE 関数を使用しているのは NULL の扱いを簡略化させるため。
DECODE と NULL における関連事項
関連事項
ライセンス
分析関数を使用するには Oracle 8i エンタープライズ版、または、Oracle 9i スタンダード版以上が必要
サイト統合にともない代替情報の URL は不明
印刷表示