文字列のSUM
stenretniです。近所の梅の花が散って実が生りはじめました。
SQLで値を加工するとき、加工後の値を加工元のレコードと並べてにらめっこしたい事がときどきあります。GROUP BYが多いアプリなどで、DBの値が10000行ほどあるときなど、処理の粒度がうまくいっていることというのがぱっと見よくわかりません。考えた末、プライマリキーをグループ別に束ねて小分けして対照することにしました。
行をGROUP BYすると普通IDの類はなくなってしまうものですが、カラム一つに文字列を押し込んでしまえば一行になり、資料をまとめたりデータを眺めるために多少扱いやすくなります。ただ、ちょうどいい粒度でプライマリキーを束ねるという作業を手で10000行ぶんやるのは容易でありません。
この作業でほしいのは、文字列用のSUMです。
2016年版のSQL標準でLISTAGGという集計関数があるそうなのですが、まともに実装しているDBは今の所あまりないようです。ただ、似たような機能はそれぞれ持っています。
PostgreSQL
PostgreSQL 9.0では、STRING_AGGという名前です。
SELECT
STRING_AGG(foo, ',')
FROM
(SELECT 'fuga' UNION ALL SELECT 'moga') t (foo)
上のようにすると'fuga,moga'という文字列が一行だけ返ってきます。
MySQL
MySQLではGROUP_CONCATがあります。
SELECT
GROUP_CONCAT(foo SEPARATOR ',')
FROM
(SELECT 'fuga' AS FOO UNION ALL SELECT 'moga' AS FOO) t
Oracle
Oracleでこれに相当する関数はWM_CONCATやLIST_AGGがあります。
ただ、自分の使っているOracle(10g)では未実装だったり、varcharの上限(4000文字)にぶつかったりでうまくいきませんでした。プロシージャを自作せずにすます方法を探した末、次のような代替を見つけました。
SELECT
XMLAGG(XMLCDATA(hoge)).getClobVal()
FROM
(SELECT 'fuga' hoge FROM DUAL UNION ALL SELECT 'moga' hoge FROM DUAL) t;
この文は <![CDATA[fuga]]><![CDATA[moga]]> という文字列だけの結果1行を返します。使っているXMLAGGというのはXML集計というOracle特有の機能です。OracleにはXMLノードを集計するという機能があり、それを経由すると文字列にまとめて結果を取っています。
結果はXMLタグで返るので、見やすくするにはもう少しreplaceなどして手を加える必要があります。自分の場合はその後テキストエディタの置換でやっつけました。
リンク:
- PostgreSQLの集計関数
- 12.19.1 GROUP BY (集約) 関数 – MySQL 5.6 リファレンスマニュアル
- OracleのXMLAGG
- LISTAGG function: “result of string concatenation is too long”
- SQL LISTAGG concatenates values of multiple rows into an delimited string such as CSV – modern SQL
ほか、最近の個人的なニュースです。
- 基本情報技術者試験の午後試験でCOBOLが廃止され、Pythonが代わりに追加されるそうです。
ということで、基本情報技術者の参考書がPythonに対する教材になりそうです。 - 2018年11月、RedHatをIBMが買収しました。
もともとIBMはLinuxを支援していたので、その流れと思います。4ヶ月経ちますが、FedoraやGNOMEの類のソフトに看板取替えなどは起こっていないのでいまのところ平和な感じがします。 - 大分県美術館の喫茶店のハンバーグがすごくおいしかったです。