Anti-Pattern Inc. Engineering Blog
TwitterFacebookRSS Feed

PostgreSQLで配列を扱う

Yu Takahashi2 min read

パフォーマンス改善のために、今までアプリでやっていた処理をDBに寄せることになりした。その一環でSQLで配列を使った処理を書いたので、まとめました。

詳しくは公式ドキュメントを参照ください。

9.18. 配列関数と演算子

環境

  • PostgreSQL 11.6

使用するテーブル

create table users(
id BIGSERIAL,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
first_name_kana VARCHAR(20),
last_name_kana VARCHAR(20),
version BIGINT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP,
PRIMARY KEY (id)
);
create table friends(
id BIGSERIAL,
user_id BIGINT NOT NULL,
friend_id BIGINT NOT NULL,
version BIGINT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users (id),
FOREIGN KEY (friend_id) REFERENCES users (id)
);
view raw go-xorm-table hosted with ❤ by GitHub

配列として集約する

SELECT  
  ARRAY_AGG(friend_id) AS friend_ids  
FROM  
  friends  
GROUP BY  
  user_id

ARRAY_AGG で配列を生成できます。

ソートして集約する

SELECT  
  ARRAY_AGG(friend_id ORDER BY created_at) AS friend_ids  
FROM  
  friends  
GROUP BY  
  user_id

friend_id を created_at でソートしています。

先頭要素だけ取得

SELECT  
  (ARRAY_AGG(friend_id ORDER BY created_at))[1] AS first_friend_id  
FROM  
  friends  
GROUP BY  
  user_id

1始まりの添字でアクセスできます。要素数を超えた添字を指定した場合、NULL になります。ちなみにARRAY_AGG の中にLIMIT 1 をつけるのは動きませんでした。

ARRAY_AGG(friend_id ORDER BY created_at LIMIT 1)

2番目以降だけ取得

SELECT  
  ARRAY_REMOVE(  
    ARRAY_AGG(friend_id),  
    (ARRAY_AGG(friend_id ORDER BY created_at))[1]  
  ) AS other_friend_ids  
FROM  
  friends  
GROUP BY  
  user_id

ARRAY_REMOVE で要素を削除できます。ソートした先頭要素を削除することで、2番目以降の要素のみを取得できます。重複要素がある場合は全て消えるので注意が必要です。

共通要素の確認

SELECT  
  *  
FROM (  
  SELECT  
    ARRAY_AGG(friend_id ORDER BY created_at) AS friend_ids  
  FROM  
    friends  
  GROUP BY  
    user_id  
) AS foo  
WHERE  
  friend_ids && ARRAY[1, 2]::bigint[]

&& は1要素でも一致していればtrueを返します。数値の型が違う場合は必要に応じてcastします。

配列をテーブルとして展開する

以下の記事を参考にしました。

Studio ODIN - blog�����l�^�W > PostgreSQL �� �z����e�[�u���̂悤�Ɉ���
blog�����l�^�W > PostgreSQL �� �z����e�[�u���̂悤�Ɉ���
SELECT  
  (fields::VARCHAR[])[1]::NUMERIC AS id,  
  (fields::VARCHAR[])[2]::NUMERIC AS value,  
FROM  
  UNNEST(ARRAY['{1,1}','{2,3}','{3,2}']) AS fields

アプリで持っている情報をSQLで参照したい場合に使用できます。

おわり

SQLで配列を使う方法についてまとめてみました。

配列だけでなくJSONを操作するSQLなども試しましたが、使いすぎると可読性が落ちるので、どれくらいのパフォーマンス向上に寄与するのかを考えて使った方が良さそうだなと思いました。（ここで挙げたものも、試すだけ試して使わなかった例があります）