SQLでできる！配列の「共通要素」だけを取り出すテクニック：PostgreSQL関数「intersect_arrays」のしくみ

PostgreSQL 関数: 2つの配列の交差集合を返す

このプログラミング記事では、PostgreSQL 関数を使用して、2つの配列の交差集合を返す方法について解説します。交差集合とは、2つの集合に共通する要素のみを含む集合です。

前提知識

この解説を理解するには、以下の知識が必要です。

• PostgreSQL データベース
• SQL言語
• 配列データ型

コード解説

CREATE FUNCTION intersect_arrays(array1 integer[], array2 integer[])
RETURNS integer[] AS $$
DECLARE
  result integer[];
  i integer;
BEGIN
  result := ARRAY[];

  FOR i IN 1..ARRAY_LENGTH(array1) LOOP
    IF array_contains(array2, array1[i]) THEN
      result := array_append(result, array1[i]);
    END IF;
  END LOOP;

  RETURN result;
END;
$$ LANGUAGE plpgsql;

コード説明

1. CREATE FUNCTION ステートメントを使用して、intersect_arrays という名前の関数を定義します。この関数は、2つの整数の配列を受け取り、整数の配列を返します。
2. DECLARE ステートメントを使用して、result という変数を宣言します。この変数は、交差集合を格納するために使用されます。
3. FOR ループを使用して、array1 の各要素を反復処理します。
4. array_contains 関数を使用して、array2 に array1[i] が含まれているかどうかを確認します。
5. array_contains 関数が true を返した場合、array_append 関数を使用して、array1[i] を result に追加します。
6. RETURN ステートメントを使用して、result を返します。

関数使用方法

SELECT intersect_arrays(array1, array2);

例

SELECT intersect_arrays(ARRAY[1, 2, 3], ARRAY[3, 4, 5]);

-- 結果:
-- {3}

この例では、array1 と array2 の交差集合は {3} であることが示されています。

関数 intersect_arrays

CREATE FUNCTION intersect_arrays(array1 anyarray, array2 anyarray)
RETURNS anyarray AS $$
SELECT ARRAY( SELECT UNNEST($1) INTERSECT SELECT UNNEST($2) );
$$ LANGUAGE sql IMMUTABLE STRICT;

関数 intersect_arrays の使用方法

SELECT intersect_arrays(array1, array2);

例

-- サンプルデータ
CREATE TABLE my_table (
  id serial PRIMARY KEY,
  array1 anyarray,
  array2 anyarray
);

INSERT INTO my_table (array1, array2)
VALUES
  (ARRAY['apple', 'banana', 'orange'], ARRAY['orange', 'grape', 'kiwi']),
  (ARRAY[1, 2, 3], ARRAY[3, 4, 5]);

-- 関数 `intersect_arrays` を使用して、2つの配列の交差集合を取得する
SELECT id, array1, array2, intersect_arrays(array1, array2) AS intersection
FROM my_table;

-- 結果:
-- id | array1           | array2           | intersection
-- -- | --------------- | --------------- | --------------
-- 1 | {'apple', 'banana', 'orange'} | {'orange', 'grape', 'kiwi'} | {'orange'}
-- 2 | {1, 2, 3}        | {3, 4, 5}        | {3}

この例では、my_table テーブルに 2 つのレコードが挿入されます。各レコードには、array1 と array2 という 2 つの配列が含まれています。

SELECT ステートメントでは、intersect_arrays 関数を使用して、各レコードの array1 と array2 の交差集合を取得します。結果は、intersection という新しい列に格納されます。

この例の結果は、以下のとおりです。

• レコード 1 の array1 と array2 の交差集合は {'orange'} です。

PostgreSQLで2つの配列の交差集合を求めるその他の方法

array_agg と unnest を使った方法

この方法は、unnest 関数で配列を要素に分解し、array_agg 関数で共通要素を集約することで、交差集合を求めます。

SELECT array_agg(DISTINCT a)
FROM (
  SELECT unnest(array1) AS a
  FROM my_table
  UNION ALL
  SELECT unnest(array2) AS a
  FROM my_table
) AS subquery;

WITH 句と再帰クエリを使った方法

この方法は、WITH 句で再帰クエリを定義し、共通要素を段階的に抽出することで、交差集合を求めます。

WITH RECURSIVE cte AS (
  SELECT array1 AS a, array2 AS b
  FROM my_table
  UNION ALL
  SELECT cte.b AS a, array_remove(cte.a, cte.b) AS b
  FROM cte
  WHERE array_length(cte.b) > 0
)
SELECT array_agg(DISTINCT a) AS intersection
FROM cte
WHERE array_length(a) = 1;

PostgreSQL拡張モジュールを使う方法

PostgreSQLには、intarray や array2 などの拡張モジュールが用意されており、これらのモジュールの中には、2つの配列の交差集合を求める関数を提供しているものがあります。

例えば、intarray モジュールには & 演算子が用意されており、2つの整数配列の交差集合を返すことができます。

SELECT array1 & array2 AS intersection
FROM my_table;

最適な方法の選択

使用する方法は、データ量、パフォーマンス要件、個人の好みによって異なります。

• データ量が少ない場合は、方法 1 または 2 がシンプルで分かりやすいのでおすすめです。
• データ量が多い場合は、方法 3 の方が高速に処理できる可能性があります。
• PostgreSQL拡張モジュールを使用している場合は、そのモジュールに用意されている関数を利用するのが効率的です。

どの方法を選択する場合も、事前にパフォーマンスを測定し、最適な方法を選択することをお勧めします。

補足

• 上記のコードは、PostgreSQL 9.5以降で動作します。
• 他のデータ型の場合は、適切な型変換関数を使用する必要があります。
• 性能が重要な場合は、インデックスを使用することを検討してください。