この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
集計関数のLISTAGG
先日、Redshiftに新しくLISTAGG関数が追加されました。これは、グルーピングした値集合をリストで表示することができます。分析用とではわりとよく使いそうですね。Redshiftに接続して簡単なサンプルを試してみたいと思います。
Redshiftのバージョン確認
Redshiftの最小構成クラスタを立ち上げます。そして、接続したら以下のクエリーを投げます。ここで、Redshiftのバージョンが1.0.975以上であればOKです。新しい集計関数に対応しています。私はオレゴンリージョンで試したら使えました。東京リージョンは未だでしたが、すぐに対応するでしょう。
> select VERSION()
PostgreSQL 8.0.2 on i686-pc-linux-gnu,
compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3),
Redshift 1.0.975
サンプルデータの作成
次にクエリーを投げる対象となるテーブルなどを作成します。
> CREATE USER satoshi password '**********';
> CREATE SCHEMA AUTHORIZATION satoshi;
> CREATE TABLE satoshi.SALES (id int, name VARCHAR, age int,dept int, update_date date);
サンプルデータをインサートします。
> INSERT INTO satoshi.SALES (id,name,age,dept,update_date)VALUES(10,'satoshi',37,100,'2015-01-01');
> INSERT INTO satoshi.SALES (id,name,age,dept,update_date)VALUES(20,'akari',4,200,'2014-01-02');
> INSERT INTO satoshi.SALES (id,name,age,dept,update_date)VALUES(30,'yokota',10,200,'2013-01-02');
> INSERT INTO satoshi.SALES (id,name,age,dept,update_date)VALUES(40,'mama',1,200,'2012-01-03');
> INSERT INTO satoshi.SALES (id,name,age,dept,update_date)VALUES(50,'papa',40,100,'2011-01-03');
LISTAGG関数を使う
それではデータが揃いましたので、さっそく使ってみましょう。
まずは最も簡単な例です。idを並べました。
> SELECT LISTAGG(id,', ') WITHIN GROUP (ORDER BY id) FROM satoshi.SALES;
LISTAGG
----------------------
10, 20, 30, 40, 50
もう少し複雑にします。deptでグループ化してidリストとnameリストを並べました。
SELECT dept,
LISTAGG(name,', ') WITHIN GROUP (ORDER BY name) AS names,
LISTAGG(id,', ') WITHIN GROUP (ORDER BY name) AS ids
FROM satoshi.SALES
GROUP BY dept
ORDER BY dept;
dept names ids
------------------------------------
100 papa,satoshi 50, 10
200 akari,mama,yokota 20, 40, 30
ウィンドウ関数のLISTAGG
ウィンドウ関数にもLISTAGGが提供されていますので、こちらも試してみたいと思います。idでグループ化して、そのidが所属するLISTAGGの全体を表示しています。全部同じグループに属しているので同じものが行に並んでいます。
> SELECT LISTAGG(id,', ') WITHIN GROUP (ORDER BY id) OVER() FROM satoshi.SALES;
LISTAGG
----------------------
10, 20, 30, 40, 50
10, 20, 30, 40, 50
10, 20, 30, 40, 50
10, 20, 30, 40, 50
10, 20, 30, 40, 50
こちらは、idでグループ化して、同じdeptに所属しているidをLISTAGGで表示しています。
SELECT id, dept, LISTAGG(id,', ') WITHIN GROUP (ORDER BY id) OVER( PARTITION BY dept )
FROM satoshi.SALES
ORDER BY id;
id dept LISTAGG
----------------------
10 100 10, 50
20 200 20, 30, 40
30 200 20, 30, 40
40 200 20, 30, 40
50 100 10, 50
まとめ
Redshiftには様々な集計関数やウィンドウ関数があります。今回は、グループ化した結果セットをリスト取得するLISTAGGを学びました。Redshiftを使っている方は、集計して分析することが多々あると思います。集計関数やウィンドウ関数を押さえて、ビジネスに役立つ情報をたくさん収集しましょう。
参考資料
Amazon Redshift Database Developer Guide - LISTAGG Function
Amazon Redshift Database Developer Guide - LISTAGG Window Function