Archive for 3月 17th, 2019

文字列のSUM

日曜日, 3月 17th, 2019

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_CONCATLIST_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などして手を加える必要があります。自分の場合はその後テキストエディタの置換でやっつけました。

リンク:


ほか、最近の個人的なニュースです。