分析関数の衝撃

MySQLで分析関数を模倣5（応用編）

MySQLで、Oracleの分析関数と同じ結果を取得する5

山岸賢治[著]

2009/03/19 14:00

ポスト

ダウンロード SourceCode (4.9 KB)

4. その行までの文字列を連結

　最後に、その行までの文字列を連結した値を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。

StrTable

SortKey	Str
1	AAA
2	BBB
3	AAA
4	CCC
5	AAA
6	DDD
7	AAA
8	EEE

　SortKeyの昇順で、各行までの文字列Strをカンマ区切りで連結した値を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

select SortKey,Str,
wmsys.wm_concat(Str) over(order by SortKey) as strAgg
  from StrTable
order by SortKey;

出力結果

SortKey	Str	strAgg
1	AAA	AAA
2	BBB	AAA,BBB
3	AAA	AAA,BBB,AAA
4	CCC	AAA,BBB,AAA,CCC
5	AAA	AAA,BBB,AAA,CCC,AAA
6	DDD	AAA,BBB,AAA,CCC,AAA,DDD
7	AAA	AAA,BBB,AAA,CCC,AAA,DDD,AAA
8	EEE	AAA,BBB,AAA,CCC,AAA,DDD,AAA,EEE

注意

wmsys.wm_concatは、Oracle11gのマニュアルにすら載ってません。

　MySQLには、group_concatという関数がありますので、答えは下記となります。

group_concatを使うSQL

select SortKey,Str,
(select group_concat(b.Str order by b.SortKey)
   from StrTable b
  where b.SortKey <= a.SortKey) as strAgg
from StrTable a
order by SortKey;

　相関サブクエリのwhere句でSortKeyが自分以下であることを条件とし、group_concat関数で、SortKeyの昇順でStrをカンマ区切りで連結した値を求めています。

　SQLのイメージは下記です。

SQLのイメージ

　ちなみに、PostgreSQLでは、下記のSQLとなります。

arrayを使うSQL

select SortKey,Str,
array_to_string
(array(select b.Str
         from StrTable b
        where b.SortKey <= a.SortKey
       order by b.SortKey),',') as strAgg
  from StrTable a
order by SortKey;

最後に

　連載記事、分析関数の衝撃の番外編である、MySQLで分析関数を模倣シリーズは、前編,中編,後編,完結編,応用編ときて一段落となりました。

　次回のMySQLの記事は、Oracleのminus,intersect,完全外部結合,パーティション化された外部結合をMySQLで模倣する記事となる予定です。

参考資料

OracleSQLパズル『標準SQLでメジアン（中央値）を取得』
本稿の「2. メジアン（中央値）を求める」の元ネタを置いています。
OracleSQLパズル『wmsys.wm_concat』
本稿の「4. その行までの文字列を連結」で使用した、Oracleのwmsys.wm_concatに関する実験結果を置いています。
MySQL 5.1 リファレンスマニュアル『11.11.1. GROUP BY（集約）関数』
MySQLのマニュアルです。本稿の「4. その行までの文字列を連結」で使用したgroup_concatについて記述されています。
PostgreSQL 8.3.4文書『4.2.10. 配列コンストラクタ』
PostgreSQLのマニュアルです。本稿の「4. その行までの文字列を連結」で使用した配列コンストラクタについて記述されています。
PostgreSQL 8.3.4文書『9.17. 配列関数と演算子』
PostgreSQLのマニュアルです。本稿の「4. その行までの文字列を連結」で使用したarray_to_stringについて記述されています。
ONLamp.com Stephane Faroult 『Emulating Analytic （AKA Ranking） Functions with MySQL』 Stephane Faroultt著、2007年3月
本連載の原案となった記事です。
ONLamp.com Stephane Faroult 『Emulating Analytic （AKA Ranking） Functions with MySQL: Part 2』 Stephane Faroult著、2007年4月
本連載の原案となった記事です。