[PostgreSQL] 特殊なケースにおける配列抽出の実現方法

Question

前提・実現したいこと

array_agg()関数で集計して得られた配列に対して以下の条件のもと、値を抽出したいと考えています。
（array_agg()で得られた配列[0, 1, 2]は、配列の定義ALLAY[0, 1, 2]と同じ）

• 昇順にソートした配列の中から、値が欠損する前までの最大値を取得したい。

例） [0, 1, 2, 4, 5]という配列の場合、「3」が欠損しているため、欠損する前までの最大値である「2」を出力する。

例） [0, 1, 4]の場合も上と同様、「2」、「3」が欠損しているため、欠損する前の最大値である「1」を出力

例） [0, 1, 2, 3, 4, 5]である場合は、欠損している箇所がないため、出力は最大値の「5」となる。

※欠損の基準は、整数値を0から順に並べたときに欠けている整数のことを指します。（1つめの例の「3」）

追記

また、配列長は現状最大30個まであります。
つまり、[0,1,...,30]までの長さの配列が存在します。

ですが、これから先その最大長が更新される可能性があるため、可変長であることを仮定したプログラムを作成できたらな、と考えております。

該当の配列の例

--	aggregate
--	[0,1,2,4,5]
--	[0,1,2,3]
--	[0,1,5]
--	[0,1]
--	[0,1,2,5]

最終的に欲しい結果の例

--	aggregate	result
--	[0,1,2,4,5]	2
--	[0,1,2,3]	3
--	[0,1,5]	1
--	[0,1]	1
--	[0,1,2,5]	2

補足情報（FW/ツールのバージョンなど）

仕様ツール：redash

Comments

[sazi]

欠損をチェックする最大の配列は定義できますか？
例えば、[0,1,2,3,4,5]が最大の配列だとすると、これに対しての欠損の最小値が求められるので。

[Gorilland]

その部分についての記載を忘れていました。
最大の配列長は現状30です。つまり[0, 1,...,30]まであります。

しかしながら現状は最大ですが、これから先更新される可能性があるため、できるだけ可変長であると仮定していただければ幸いです。

Answer 1

配列がソートされている前提で、1からの連番であるordinalityを利用して欠番までの最大値で求める。
※0が欠番の場合はNullになります。必要ならcoalesce(max(i), 0)とでもして下さい。

with t as (
  select *
  from (values
      ( 1, array[0,1,2,4,5] ::int[])
    , ( 2, array[0,1,2,3]   ::int[])
    , ( 3, array[0,1,5]     ::int[])
    , ( 4, array[0,1]       ::int[])
    , ( 5, array[0,1,2,5]   ::int[])
    , ( 6, array[1,2,3]     ::int[])
  ) t ( id, _aggregate )
)
select  *
     , (select max(i) 
        from unnest(_aggregate) with ordinality as w(i, seq)
        where i = seq - 1
       ) result
from t

ただ、

array_agg()関数で集計して得られた配列に対して以下の条件のもと、値を抽出したいと考えています。

**array_agg()**する前に求めた方が効率的だと思いますよ。

Comments

[sazi]

mayu-さんの「配列をSELECT句で展開」と同じで、ちょっと表現を変えました。

Answer 2

※欠損の基準は、整数値を0から順に並べたときに欠けている整数のことを指します

配列長は現状最大30個までですが、これから先その最大長が更新される可能性があるため、
可変長であることを仮定したプログラム

以下でどうでしょう。

※ データサンプルは FROM...VALUES...( a, b ) の箇所です

SELECT a  _group
     , b  _aggregate
     , coalesce( min( case when d - c != 1 then e end )
               , max(c) 
       )  _result
FROM
(
    SELECT a, b, c, d
         , lag( c, 1, 0 ) over ( partition by a order by c ) e
    FROM 
    (
        VALUES
            ( 1, array[0,1,2,4,5]::int[] )
          , ( 2, array[0,1,2,3]::int[]   )
          , ( 3, array[0,1,5]::int[]     )
          , ( 4, array[0,1]::int[]       )
          , ( 5, array[0,1,2,5]::int[]   )
          , ( 6, array[1,2,3]::int[]     )
    ) t1 ( a, b )
    , LATERAL unnest( b ) with ordinality t2 ( c, d ) 
) q
GROUP BY a, b
ORDER BY 1
;

■ 結果

_group	_aggregate	_result
1	{0,1,2,4,5}	2
2	{0,1,2,3}	3
3	{0,1,5}	1
4	{0,1}	1
5	{0,1,2,5}	2
6	{1,2,3}	0

　　
以下はコメントいただいたロジックや回答とは別の記述方法:

• 配列をSELECT句で展開

SELECT a  _group
     , b  _aggregate
     , (
           SELECT coalesce(
                      min( coalesce( b[ d - 1 ], 0 ) ) filter( where d - c != 1 )
                    , max( c )
                  )
             FROM unnest( b )
                      with ordinality t ( c, d )
       )  _result
FROM
(
    VALUES
        ( 1, array[0,1,2,4,5]::int[] )
      , ( 2, array[0,1,2,3]::int[]   )
      , ( 3, array[0,1,5]::int[]     )
      , ( 4, array[0,1]::int[]       )
      , ( 5, array[0,1,2,5]::int[]   )
      , ( 6, array[1,2,3]::int[]     )
) q ( a, b )
ORDER BY 1
;

　

• 配列同士の差分比較

SELECT a  _group
     , b  _aggregate
     , (
           SELECT coalesce( min(i) - 1 + ( min(i) = 0 )::int
                          , b[ array_upper( b, 1 ) ] 
                  )
            FROM generate_series( 0, b[ array_length( b, 1 ) ] )
                    as nums(i)
           WHERE NOT array[i] <@ b
       ) _result
FROM 
(
    VALUES
        ( 1, array[0,1,2,4,5]::int[] )
      , ( 2, array[0,1,2,3]::int[]   )
      , ( 3, array[0,1,5]::int[]     )
      , ( 4, array[0,1]::int[]       )
      , ( 5, array[0,1,2,5]::int[]   )
      , ( 6, array[1,2,3]::int[]     )
) q ( a, b )
ORDER BY 1
;

　　

• 配列をFROM句で展開してビューのネストを無くす

SELECT a  _group
     , b  _aggregate
     , coalesce(
           min( coalesce( b[ d - 1 ], 0 ) ) filter( where d - c != 1 )
         , max( c )
       )  _result
FROM 
(
    VALUES
        ( 1, array[0,1,2,4,5]::int[] )
      , ( 2, array[0,1,2,3]::int[]   )
      , ( 3, array[0,1,5]::int[]     )
      , ( 4, array[0,1]::int[]       )
      , ( 5, array[0,1,2,5]::int[]   )
      , ( 6, array[1,2,3]::int[]     )
) t1 ( a, b )
, LATERAL unnest( b ) with ordinality t2 ( c, d )
GROUP BY a, b
ORDER BY 1
;

Comments

[Gorilland]

可変長ケースにも対応していただきありがとうございました。
私自身色々と勉強になる丁寧なコードの書き方をしてくださっているのでとても助かります。

これを参考にしてやりたいことが実装できそうです！ありがとうございました。

[sazi]

LATERAL ではなくサブクエリーだけで行う方法もありますね。
unnestによる展開とgroup byをサブクエリーで行った方がコストが断然下げられたケースを経験したことがあります。
質問のパターンに該当するかは記憶が曖昧ですが。

[mayu-]

@saziさん
コメント欄だとインデントが反映されませんので
LATERAL→サブクエリだけに書き換えたSQLを回答に追記しました。
unnestによる展開を FROM句ではなくSELECT句で実施する と解釈しています。

[sazi]

@mayu-さん
一番外側にgroup byが無いイメージでした。

[mayu-]

@saziさん
ものの見事に外していましたね。
unnest と group by を使わないとなると、、、回答にも再度追記しましたけど
配列をゴニョゴニョして結果を出すという認識で合っているでしょうか。

[sazi]

@mayu-さん
サブクエリー内ではunnestもgroup byも使っているイメージでした。
配列の要素数と配列最後が最大値である事を利用したんですね。
これはこれでアリですね。

[mayu-]

@saziさん
unnest + group by を利用するという理解は合っていたようで安心しました。
なので、(方向性が異なると感じて)一旦お蔵入りにしたSQL文も載せますね。
配列はFROM句で展開しておくと記述が楽でしたので LATERAL はそのまま使っていますけど
ビューのネストは無くしました。

[mayu-]

解釈が違っていた追記は消して
残したSQLについての概要を記述しました