配列化したカラムから条件に一致した要素を取得(PostgreSQL)

業務で必要になってグループ化したテーブルのカラムを配列化し、
条件によって取得する要素を変えて取得してみたので備忘録として記事にしました。

実施環境

OS: Windows 11
DBMS: PostgreSQL16

実現したいこと

以下のようなテーブル「process」があるとして

processテーブル

|ID   |flowNo  |processor  |operation  |create_date  |
|1    |1       |tanaka     |apply      |2000/07/17   |
|1    |2       |tanaka     |apply      |2000/07/18   |
|1    |3       |yamada     |apply      |2000/07/19   |
|2    |1       |yamada     |apply      |2000/07/17   |
|2    |3       |yamada     |apply      |2000/07/19   |
|2    |3       |yamada     |cancel     |2000/07/20   |
|3    |1       |tanaka     |apply      |2000/07/17   |
|3    |2       |tanaka     |apply      |2000/07/18   |
|3    |2       |suzuki     |cancel     |2000/07/20   |
|3    |2       |sasaki     |apply      |2000/07/21   |

IDとflowNoごとにグループ化し、
flowNoごとのprocessorを1レコードで取得したい。

条件

・最新operationがcancelの場合はNonを表示
・flowNoにレコードが存在しない場合はNonを表示

欲しい取得結果

|ID   |folow1_processor  |folow2_processor  |folow3_processor  |
|1    |tanaka            |tanaka            |yamada            |
|2    |yamada            |Non               |Non               |
|3    |tanaka            |sasaki            |Non               |

実現方法(結果)

結果として以下のようなSQLで実現しました

SELECT 
    p.ID
,   CASE
      WHEN '1' = ANY(flowNo_list) AND
           operation_list[array_position(flowNo_list,'1')] != 'cancel'
      THEN processor_list[array_position(flowNo_list,'1')]
      ELSE 'Non'
    END AS folow1_processor
,   CASE
      WHEN '2' = ANY(flowNo_list) AND
           operation_list[array_position(flowNo_list,'2')] != 'cancel'
      THEN processor_list[array_position(flowNo_list,'2')]
      ELSE 'Non'
    END AS folow2_processor
,   CASE
      WHEN '3' = ANY(flowNo_list) AND
           operation_list[array_position(flowNo_list,'3')] != 'cancel'
      THEN processor_list[array_position(flowNo_list,'3')]
      ELSE 'Non'
    END AS folow3_processor
FROM 
    (
     SELECT
         list_p.ID
     ,   array_agg(list_p.flowNo) AS flowNo_list
     ,   array_agg(list_p.processor) AS processor_list
     ,   array_agg(list_p.operation) AS operation_list
     FROM process AS list_p
     INNER JOIN 
         (
          SELECT 
              ID
          ,   flowNo
          ,   MAX(create_date) AS end_date
          FROM process
          GROUP ID,flowNo
         ) AS end_p
     ON end_p.ID = list_p.ID AND
        end_p.flowNo = list_p.flowNo AND
        end_p.end_date = create_date
     GROUP BY list_p.ID
    ) AS p
ORDER BY p.ID;

実現方法(手法)

基本の考え方

processテーブルのprocessorをarray_agg関数で、
IDとflowNoごとに配列化。
flowNoごとに添え字で取得。

問題点

flowNoが'2'のレコードはあったりなかったりや
operationが'cancel'後に再度'apply'のものがあったりする性で、
添え字が固定では取得できない

対応策

①最新レコードのみ取得

先に最新のレコードだけ取得し、
取得した日付でマッチングしたレコードのみを配列化

SELECT
    list_p.ID
     ,   array_agg(list_p.flowNo) AS flowNo_list
     ,   array_agg(list_p.processor) AS processor_list
     ,   array_agg(list_p.operation) AS operation_list
FROM process AS list_p
INNER JOIN 
     (
      SELECT 
            ID
        ,   flowNo
        ,   MAX(create_date) AS end_date
        FROM process
        GROUP ID,flowNo
    ) AS end_p
ON end_p.ID = list_p.ID AND
   end_p.flowNo = list_p.flowNo AND
   end_p.end_date = create_date
GROUP BY list_p.ID

②flowNo_listを使って添え字を取得

①でflowNoも配列化しておいたので、
array_position関数で添え字を取得することで、
動的に配列から該当データを取得しました。

processor_list[array_position(flowNo_list,'1')]

array_position関数について

array_positionは、第一引数の配列を第二引数の値で検索し、
最初にヒットした要素の要素番号を取得してくれます。

array_position関数の構文
array_position(対象の配列,検索したい値)

今回の課題には渡りに船でした。

参考：https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/functions/array_position

まとめ

今回は配列化を活用して、
条件に一致するデータをグループごとに取得してみました。
DBの配列を扱ったのが初めてで調べるのに苦労したので、
誰かの助けになれば幸いです。

本来ならこんなことしないと取得できないようなテーブル設計にすること自体よくないのですが。
設計後にマスターデータから取得しないといけない場合など色々あると思うので、そこら辺は目をつぶってくださいw