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月
本連載の原案となった記事です。
