PostgreSQL SQLの文字列の条件について 2

Question

以下のデータがテーブルにあります。

この時、FixIdが最新の各[ShortId VersionId]をすべて表示したいです。（←文中の[～]は下の書式参考。）
下の表の場合、2～5行目です。
（NO-000000 の場合、1行目と2行目で重複しているので、FixIdが最新である2行目を採用するといった感じです。）

SQLで条件指定することは可能でしょうか。
前回似たような質問しましたが、抽出条件が異なります。

FullId	ShortId
NO-00000-01	NO-000
NO-00000-02	NO-000
NO-00001-01	NO-000
NO-00100-01	NO-001
NO-00200-01	NO-002

FullIdの書式: ShortId VersionId - FixId
ShortIdとVersionIdは区切り文字なしで連結。
VersionIdとFixIdはハイフンで連結。

Answer 1

ShortId VersionId毎のFixIdの順番を求めて最新のみにする。

select *
from (
  select *
       , row_number() 
           over(
               partition by (string_to_array(FullId,'-'))[1]     -- ShortId VersionId
               order by (string_to_array(FullId,'-'))[2] desc  -- FixId 降順
           ) 
         as Reverse_ver
  from (values 
         ('NO00000-01','NO-000')
        ,('NO00000-02','NO-000')
        ,('NO00001-01','NO-000')
        ,('NO00100-01','NO-001')
        ,('NO00200-01','NO-002')
      ) as tbl(FullId, ShortId)
) step1
where Reverse_ver=1

Answer 2

（注釈）

• 本質的にはテーブル設計が悪いです。機械的に結合することで生成できる文字列を1つの列にまとめて入れてしまうのではなく、その要素（つまり VersionId 、 FixId を列として持つべきです。
• その上で、提示されている情報のみでの回答となるので、例えばテーブル構造に別の列があるなど提示されていない条件がある場合、この回答で満足できるものとならない可能性があります。

回答の考え方

• ShortId VersionId でグループ化すれば、 FixId の最新を考慮する必要なく求めるデータは集まる（それでも念のため FixId の Max を表示する）。
• REGEXP_SPLIT_TO_ARRAY を使って、 FullId を - で分割し配列化する処理をサブクエリとして行い、各要素を利用してグループ化、集計する。

回答例となるSQL

SELECT
    (D[1] || '-' || D[2]) AS ShortId_VersionId ,
    MAX(D[3]) AS MAX_FixId
FROM
    (SELECT
        REGEXP_SPLIT_TO_ARRAY(FullId,'-') as D
    FROM
        [使っているTABLE名]
    ) as T
GROUP BY
    ShortId_VersionId;

結果

ShortId_VersionId	MAX_FixId
NO-00000	02
NO-00001	01
NO-00200	01
NO-00100	01

［追記］ 生成列

本題ではないですが、テーブル設計に後悔があるようなので一案です。

PostgreSQL 12 以降、生成列という仕様があります。
別の列の値を元に機械的に算出される列をテーブルに定義できる、というものです。

これを利用して、 FullId 列から VersionId 列、FixId 列を生成列として定義してはいかがでしょうか。

例）

-- VesionId 列を生成
--   FullID から ShortId 置換（削除）して、 - を区切りにで配列化した1つ目の要素
ALTER TABLE [使っているTABLE名] ADD COLUMN VersionId text
  GENERATED ALWAYS AS (
      (REGEXP_SPLIT_TO_ARRAY(REPLACE(FullId, ShortId, ''), '-'))[1]
    )  STORED;

-- FixId 列を生成
--   FullID から - を区切りにで配列化した3つ目の要素
ALTER TABLE [使っているTABLE名] ADD COLUMN FixId text
  GENERATED ALWAYS AS (
      (REGEXP_SPLIT_TO_ARRAY(FullId, '-'))[3]
    )  STORED;

上記により VersionId 列、 FixId 列が追加できるかと思います。

これができてしまえばあとは単純なクエリが作れるようになるのではないでしょうか。

Comments

[person]

> 本質的にはテーブル設計が悪いです。

やっぱり妥協無しでそれぞれの列を作っておくべきだったと後悔しています･･･。

質問ですが、取得する列に
[使っているTABLE名].*
を追加することは可能でしょうか？

実際には他にもいくつかデータがあって、
そのデータを参照できるといいのですが。

もっとわがままを言うとWHEREで他列の条件もある程度指定したいです。

[kaz.Suenaga]

［追記］ 生成列 として、テーブル構造の改善案を追記しました。

上記コメントの質問の回答としては、やりようはありますがテーブル構造を変えるほうがおすすめ、となります。