Oracle の隠し関数 wmsys.wm_concat が非常に強力な件について

Oracle には非公開の隠し関数やパラメータが非常に多く存在します。そんな中でもつい最近知った wmsys.wm_concat という関数が非常に強力です。機能としては集約関数に相当するもので、正規化したテーブルを非正規化して出力するような要件にピッタリですが、Oracle 11g のマニュアルにも記載されていないので Undocumented な関数であることを理解して使用する必要があります。（※つまりは Oracle 保守でもサポート外ってわけです。）

さて、実際どのようなことができるかをみてみます。例えばポイントサイトのようなサービスにおけるポイント管理テーブルを考えてみます。たとえば、以下のような簡易なテーブル構造であったとします。※このエントリ用の仮想的なテーブルですので実用的ではないです。 

 名前              NULL?    型             備考
 ----------------- -------- -------------- -----------
 PID               NOT NULL NUMBER(38)     PRIMARY KEY
 USERID            NOT NULL VARCHAR2(40)   ユーザID
 POINT             NOT NULL NUMBER(38)     取得ポイント数
 GETDATE           NOT NULL DATE           取得日
 CODE              NOT NULL VARCHAR2(50)   取得理由コード

テーブルの使われ方として、ユーザが何らかの理由（会員登録や広告クリック等）によりポイントを獲得したら、このテーブルに獲得履歴を登録して管理するってイメージです。このような場合、当然ですが各ユーザ毎にレコード数もまちまちになります。このような正規化されたテーブルを以下のような非正規化した形に加工する必要があるとします。（※技術担当→営業担当にデータを渡すときとかでこんな要件は結構ありますよね。ｗ） 

ユーザID    合計ポイント数   獲得コード一覧（ , 区切りで羅列。CODE＋日付で）
----------- ---------------- ----------------------------------------------
hogehoge               14650 9909click13RC(99-09-20),9910reg06(99-10-15),9911click15RE(99-11-19),・・・
hoge2222               24820 0002click10EL(00-02-16),0003click07VG(00-03-19),0005click12CS(00-05-19)・・・
・・・以下他のユーザ続く・・・・

今までは、普通に select 文でデータをとってきてアプリ側（PL/SQL や Perl スクリプト）で加工して出力なんてことをやっていましたが、この wmsys.wm_concat を使うと SQL 一発で目的のデータを出すことができます。

- スポンサーリンク -

一発で出す SQL はこうなります。ポイントは wmsys.wm_concat 関数です。wmsys.wm_concat は group 化した各グループ内のデータに対して、あるフィールドをカンマ区切りで結合する役割をします。グループ化されたグループ内の任意のカラム名が指定可能です。あとはいつもの group by 句を書くときの流儀と何ら変わりありません。

select USERID,
       sum(GET_POINT) as POINT,
       wmsys.wm_concat(CODE || '(' ||GETDATE|| ')') as CODE
from USER_POINT
group by USERID
order by CODE;

上記の SQL を説明すると、USERID（ユーザID）で正規化されたデータをグループ化して、CODE（取得理由コード）でソートした後に、「ユーザID, ポイント合計値, CODE+日時の羅列」の３カラムを１レコードで出力しています。

この wmsys.wm_concat を使うことで、データマイニング分野では定石となるデータの非正規化の多くの部分も簡略化できます。例えば、アクセス履歴をユーザ毎のページ遷移として加工するのも、IPアドレスでグループ化して uri を日付でソートして wm_concat すればＯＫです。何しろ履歴系のデータ加工ならその適用範囲は非常に広いと思います。


ちなみに wm_concat 関数の所有者 wmsys ってのは、Oracle Workspace Manager用のメタデータ情報の格納に使用されるアカウントです。wm_concat 関数のソースは残念ながら暗号化されていて見ることができないのでどのような実装かは不明ですが、実行速度はネイティブ関数とおもわれ、大量データでテストしてみた場合でも非常に高速に動作しています。 

SQL> select TEXT from DBA_SOURCE where OWNER='WMSYS' and NAME='WM_CONCAT';

TEXT
------------------------------------------------------------------------------
function       wm_concat wrapped
a000000
1
abcd
・・・中略・・・
abcd
8
58 89
G2IOCTwNs2RlB9JzCCQPJq/UuH0wg8eZgcfLCNL+Xuf+8L8z/sefdLPnfMPnx3TAM7h0ZSXD
j57Asr2ym9ZtFldFmFu+RdpAU8VGORKSvh2BRIA5Hq8yxhKSOc4WVIKmpu8+Hw0=


最後に wmsys.wm_concat を体感するための簡単なサンプルを記載しておきます。
※http://blog.csdn.net/zhpsam109/archive/2007/12/04/1917011.aspx より拝借しました。 

create table IDTABLE (
  id  number,
  val varchar2(20)
);
 
insert into IDTABLE (ID, VAL) values (10, 'abc');
insert into IDTABLE (ID, VAL) values (10, 'abc');
insert into IDTABLE (ID, VAL) values (10, 'def');
insert into IDTABLE (ID, VAL) values (10, 'def');
insert into IDTABLE (ID, VAL) values (20, 'ghi');
insert into IDTABLE (ID, VAL) values (20, 'jkl');
insert into IDTABLE (ID, VAL) values (20, 'mno');
insert into IDTABLE (ID, VAL) values (20, 'mno');
commit;

column ENAMES format a50; 
select id,val from idtable;
select ID, wmsys.wm_concat(VAL) as ENAMES from IDTABLE group by ID;
select ID, wmsys.wm_concat(distinct VAL) as ENAMES from IDTABLE group by ID order by ID;
select ID, VAL, wmsys.wm_concat(VAL) over(partition by ID) as ENAMES from IDTABLE order by ID;
select ID, VAL, wmsys.wm_concat(VAL) over(order by ID, VAL) as ENAMES from IDTABLE order by ID;

sql の実行結果は以下のようになると思います。 


SQL> select id,val from idtable;

        ID VAL
---------- --------------------
        10 abc
        10 abc
        10 def
        10 def
        20 ghi
        20 jkl
        20 mno
        20 mno

8行が選択されました。

SQL> select ID, wmsys.wm_concat(VAL) as ENAMES from IDTABLE group by ID;

        ID ENAMES
---------- --------------------------------------------------
        10 abc,abc,def,def
        20 ghi,jkl,mno,mno

SQL> select ID, wmsys.wm_concat(distinct VAL) as ENAMES from IDTABLE group by ID order by ID;

        ID ENAMES
---------- --------------------------------------------------
        10 abc,def
        20 ghi,jkl,mno

SQL> select ID, VAL, wmsys.wm_concat(VAL) over(partition by ID) as ENAMES from IDTABLE order by ID;

        ID VAL                  ENAMES
---------- -------------------- --------------------------------------------------
        10 abc                  abc,abc,def,def
        10 abc                  abc,abc,def,def
        10 def                  abc,abc,def,def
        10 def                  abc,abc,def,def
        20 ghi                  ghi,jkl,mno,mno
        20 jkl                  ghi,jkl,mno,mno
        20 mno                  ghi,jkl,mno,mno
        20 mno                  ghi,jkl,mno,mno

8行が選択されました。

SQL> select ID, VAL, wmsys.wm_concat(VAL) over(order by ID, VAL) as ENAMES from IDTABLE order by ID;

        ID VAL                  ENAMES
---------- -------------------- --------------------------------------------------
        10 abc                  abc,abc
        10 abc                  abc,abc
        10 def                  abc,abc,def,def
        10 def                  abc,abc,def,def
        20 ghi                  abc,abc,def,def,ghi
        20 jkl                  abc,abc,def,def,ghi,jkl
        20 mno                  abc,abc,def,def,ghi,jkl,mno,mno
        20 mno                  abc,abc,def,def,ghi,jkl,mno,mno

8行が選択されました。


wmsys.wm_concat に関する他のサイトの情報はこんなところを見ると良いと思います。Oracle の隠し系で久々に感動を覚えました。以下のリンクの最後にご紹介した、Undocumented Oracle にはその他の隠し関数が紹介されています。
merge$actions　や　sys_op_distinct　が場合によっては使いどころがあると思いました。

- スポンサーリンク -

関連する記事＆スポンサーリンク