Amazon RedshiftのLISTAGGを使って集計内容をリスト化する

#Amazon Redshift

#AWS

横田あかり

2015.08.08

この記事は公開されてから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