PostgreSQL array_agg 関数と ORDER BY 句による配列の順序制御
PostgreSQL array_agg 関数と ORDER BY 句による配列の順序制御
array_agg 関数は、複数の行の値を1つの配列に集約します。このとき、ORDER BY 句を併用することで、配列の要素の順序を制御できます。
基本的な使い方
SELECT array_agg(value)
FROM table
ORDER BY value;
上記のように記述すると、table テーブルの value 列の値を、昇順に並べた配列として取得できます。
降順で並べる場合は、ORDER BY 句に DESC キーワードを指定します。
SELECT array_agg(value)
FROM table
ORDER BY value DESC;
複数の列で並べ替える場合は、複数の ORDER BY 句を記述します。
SELECT array_agg(value)
FROM table
ORDER BY value, another_column;
上記のように記述すると、まず value 列で昇順に、次に another_column 列で昇順に並べた配列として取得できます。
NULL 値は、デフォルトでは配列の末尾に配置されます。
SELECT array_agg(value)
FROM table;
NULL 値を配列の先頭に配置したい場合は、ORDER BY 句に NULLS FIRST キーワードを指定します。
SELECT array_agg(value)
FROM table
ORDER BY value NULLS FIRST;
SELECT array_agg(value)
FROM table
ORDER BY value NULLS LAST;
例
以下の例では、products テーブルの name 列の値を、価格が安い順に並べた配列として取得しています。
SELECT array_agg(name)
FROM products
ORDER BY price ASC;
array_agg関数は、さまざまなデータ型に対して使用できます。ORDER BY句は、集約関数だけでなく、SELECT ステートメントの他の部分でも使用できます。
-- テーブル作成
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price INT
);
-- データ挿入
INSERT INTO products (name, price) VALUES ('商品1', 100);
INSERT INTO products (name, price) VALUES ('商品2', 200);
INSERT INTO products (name, price) VALUES ('商品3', 300);
-- 価格が安い順に名前を配列に格納
SELECT array_agg(name)
FROM products
ORDER BY price ASC;
-- 結果
-- ['商品1', '商品2', '商品3']
-- 価格が高い順に名前を配列に格納
SELECT array_agg(name)
FROM products
ORDER BY price DESC;
-- 結果
-- ['商品3', '商品2', '商品1']
-- 在庫数が多い順に名前を配列に格納 (在庫数は存在しないため、デフォルトの順序で取得)
SELECT array_agg(name)
FROM products
ORDER BY stock DESC;
-- 結果
-- ['商品1', '商品2', '商品3']
-- NULL を配列の先頭に配置
SELECT array_agg(name)
FROM products
ORDER BY name NULLS FIRST;
-- 結果
-- [NULL, '商品1', '商品2', '商品3']
-- NULL を配列の末尾に配置
SELECT array_agg(name)
FROM products
ORDER BY name NULLS LAST;
-- 結果
-- ['商品1', '商品2', '商品3', NULL]
実行方法
- PostgreSQL に接続する。
- 上記のコードをテキストエディタに記述する。
- コードを保存する。
- PostgreSQL クライアントで保存したファイルを
PostgreSQL array_agg 関数と ORDER BY 句による配列の順序制御のその他の方法
サブクエリを使用して、事前に並べ替えた結果を配列に格納することができます。
SELECT array_agg(name)
FROM (
SELECT name
FROM products
ORDER BY price ASC
);
WINDOW 関数を使用して、各行の値に基づいて順序を決定することができます。
SELECT array_agg(name)
FROM (
SELECT name,
ROW_NUMBER() OVER (ORDER BY price ASC) AS rn
FROM products
) AS t
ORDER BY rn;
PL/pgSQL を使用して、独自の順序制御ロジックを実装することができます。
CREATE FUNCTION array_agg_by_price(
INOUT array_agg_state state,
IN name text
) RETURNS array_agg_state
AS
BEGIN
IF state IS NULL THEN
state := array_agg_create();
END IF;
state := array_agg_add(state, name);
RETURN state;
END;
SELECT array_agg_final(array_agg_by_price(NULL, name))
FROM products
ORDER BY price ASC;
外部ライブラリ
pgroonga などの外部ライブラリを使用して、より高度な順序制御機能を利用することができます。
これらの方法は、それぞれ異なる利点と欠点があります。状況に応じて適切な方法を選択する必要があります。
上記の各方法をサンプルコードで示します。
サブクエリ
-- 価格が安い順に名前を配列に格納
SELECT array_agg(name)
FROM (
SELECT name
FROM products
ORDER BY price ASC
);
-- 結果
-- ['商品1', '商品2', '商品3']
WINDOW 関数
-- 価格が安い順に名前を配列に格納
SELECT array_agg(name)
FROM (
SELECT name,
ROW_NUMBER() OVER (ORDER BY price ASC) AS rn
FROM products
) AS t
ORDER BY rn;
-- 結果
-- ['商品1', '商品2', '商品3']
PL/pgSQL
-- 価格が安い順に名前を配列に格納
CREATE FUNCTION array_agg_by_price(
INOUT array_agg_state state,
IN name text
) RETURNS array_agg_state
AS
BEGIN
IF state IS NULL THEN
state := array_agg_create();
END IF;
state := array_agg_add(state, name);
RETURN state;
END;
SELECT array_agg_final(array_agg_by_price(NULL, name))
FROM products
ORDER BY price ASC;
-- 結果
-- ['商品1', '商品2', '商品3']
外部ライブラリ (pgroonga)
-- pgroonga をインストールする
-- 価格が安い順に名前を配列に格納
SELECT array_agg(name)
FROM products
ORDER BY pgroonga_rank(price) ASC;
-- 結果
-- ['商品1', '商品2', '商品3']
- それぞれの方法に対応したコードを PostgreSQL クライアントで実行する。
- 結果を確認する。
PostgreSQL には、array_agg 関数と ORDER BY 句以外にも、配列の順序を制御するいくつかの方法があります。状況に応じて適切な方法を選択する必要があります。
postgresql array-agg
