カラム・レコード単位で連続する値を省略・削除する

JavaScriptが無効になっています。

この状態では一部の情報が表示されず、すべての機能を利用することができません。

並び替え後の連続した同一値を変換、または、レコードを選択しない

並び替え後の連続した同一値を別の値に変換したり省略したい例として多いのは

帳票出力において連続する重複する列を印字を省略して欲しいという要件などが該当する。具体的な例としては、社員一覧を部門別、社員番号順に並び替え、部門名、社員番号、社員名、・・・の一覧表を帳票出力する際に「同一の部門が連続する場合には部門名の重複表記はしないようにする。」などである。

プログラム的に言れば、同一カラムの直前行の値が現在行と同値であれば表示しない。

LAG 分析関数と LEAD 分析関数

Oracle 8i エンタープライズ版、または、Oracle 9i スタンダード版以上であれば、複雑な自己結合や PL/SQL などの手続き言語によるカーソルループ処理を用いて重複データを検出して置換する必要がない。

LAG / LEAD 分析関数を使用するだけで先読みやバッファ処理と同じことが SQL のみで利用することが可能になる。

LAG 分析関数

LAG 分析関数は、結果セットにおいてカレント行から相対的に n レコード前の行にアクセスできる関数である。

書式は
LAG ( expr [,offest [,defalut]] )
  OVER ( [partition_clause] order_by_clause )
offset は相対位置の行数（デフォルト 1）

default は offest による操作がウィンドウ外になった場合のデフォルト値を任意に指定する（デフォルト NULL）

LEAD 分析関数も LAG と書式が同じで相対レコードの参照先が n レコード後の行にアクセスできる関数

書式は

LEAD ( expr [,offest [,defalut]] )
  OVER ( [partition_clause] order_by_clause )

並び替え後の連続した同一値を "上に同じ" に変換する

ここでは、シンプルな例を示す。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 の扱いを簡略化させるため。

Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

並び替え後の連続した同一値を変換、または、レコードを選択しない

LAG 分析関数と LEAD 分析関数

LAG 分析関数

並び替え後の連続した同一値を "上に同じ" に変換する

並び替え後の連続した同一値をもつレコードを削除する

DECODE と NULL における関連事項

関連事項

ライセンス

Table of Contents

カラム・レコード単位で連続する値を省略・削除するの関連トピックス

SHIFT the Oracle 最近のトピックス