グループ分けをPostgreSQLのSQL文で実行したいです

Question

前提

次のようなテーブルがあります
NO FLD1 FLD2 Group
1 A B
2 B C
3 C D
4 C E
5 E F
6 F G
7 G H
8 I J
9 I K
10 L M

（FLD1,FLD2）のセットはユニークで
FLD1＜FLD2となるように並べて
行としても昇順に格納されています。
AとBは同一グループ
BとCは同一グループ
CとDは同一グループ
という意味で、演繹法で
A,B,C,D,E,F,G,Hは一つのグループ
I,J,Kは同一グループです。

実現したいこと

次のようにグループ分けしたときの
Noの最小値をGroupに
書き込みしたいと思います。
NO FLD1 FLD2 Group
1 A B 1
2 B C 1
3 C D 1
4 C E 1
5 E F 1
6 F G 1
7 G H 1
8 I J 8
9 I K 8
10 L M 10

発生している問題・エラーメッセージ

お試しでACCESS VBAでテストデータ10件を
順次処理をすると出来ましたが、
本番はPostgreSQLで
現在約4万件、日々データが増えているので
PostgreSQLのSQL文で実行したいです。
自己結合クエリとか再帰クエリで
書けるのかな？と思うのですが
わかりません。

該当のソースコード

ACCESS VBA
ソースコード

Option Compare Database

Sub 順次処理()
'ADOを使うための変数宣言
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim int_min_Group As Integer

'SQL文を格納するための変数宣言
Dim strSQL As String
Dim strFLD As String

DoCmd.SetWarnings False

'GroupフィールドNOセット
strSQL = "UPDATE TEST SET TEST.Group = TEST.NO"
DoCmd.RunSQL strSQL

strSQL = "SELECT TEST.NO,TEST.FLD1,TEST.FLD2 FROM TEST ORDER BY TEST.NO"


'カレントデータベースに接続する。
Set cn = CurrentProject.Connection

'SQL文を開く
rs.Open strSQL, cn

'先頭レコードに移動
rs.MoveFirst
Do Until rs.EOF = True

    'イミディエイトウィンドウに値を表示
    strFLD = " (""" & rs.Fields(1) & """,""" & rs.Fields(2) & """)"
    strSQL = "(TEST.FLD1 in" & strFLD & " OR TEST.FLD2 in" & strFLD & ") AND NO <= " & rs.Fields(0)
    'グループ番号の最小値を求める
    int_min_Group = DMin("Group", "TEST", strSQL)
    'グループ番号を最小値で更新
    strSQL = "UPDATE TEST SET TEST.Group = " & int_min_Group & " WHERE TEST.NO = " & rs.Fields(0)
    DoCmd.RunSQL strSQL
    '次のレコードに移動する。
    rs.MoveNext

Loop
  
'レコードセットを閉じる。
rs.Close

'データベース接続を閉じる。
cn.Close
Set cn = Nothing

DoCmd.SetWarnings True

End Sub

試したこと

WITH RECURSIVEの親子構造の扱いを見てみましたが
それとはちょっと違うような―

SQLで実現できるでしょうか
どうぞよろしくお願いいたします。<(_ _)>

Comments

[kikukiku]

ストアドプロシージャを勉強して使ってみたらどうでしょう。
ループも簡単にできますし。

[YT0014]

対象のPostgreSQLのバージョンをご提示ください。

[LAVENDER]

>kikukikuさま
ありがとうございます。　SQLServerのストアドプロシージャは
長年経験あります。　PostgreSQLはまだ駆け出しです。
でも同じように使えそうですね。　勉強してみます。

>YT0014さま
psql (PostgreSQL) 14.0です。
再帰SQLのご提案ありがとうございます。
会議が終わりましたら、本日の午後から
試してみたいと思います。

Answer 1

Group は予約語の為、Gpに変更しました。

準備。

create table wk (
	NO integer not null default 0,
	FLD1 char(1) not null default '',
	FLD2 char(1) not null default '',
	Gp integer
);

INSERT INTO wk
VALUES
( 1,'A','B',null),
( 2,'B','C',null),
( 3,'C','D',null),
( 4,'C','E',null),
( 5,'E','F',null),
( 6,'F','G',null),
( 7,'G','H',null),
( 8,'I','J',null),
( 9,'I','K',null),
(10,'L','M',null);

処理は、
１．再帰SQLで、FLD1をたどり、全部の（FLD１,FLD2）を作成
２．１から開始のFLD1(例の場合、A、I、L)の組だけを抽出
３．上記のFLD1に該当するNoを連結
元のテーブルのGpを、FLD2が一致するレコードの３のNoをGpに設定する
で組み立てました。

更新クエリ

UPDATE wk 
SET Gp = wk3.gp
FROM 
(WITH RECURSIVE temp (fld1, fld2) as (
	SELECT fld1, fld2 FROM wk
	UNION ALL
	SELECT temp.fld1, wk.fld2
	FROM wk, temp
	WHERE wk.fld1=temp.fld2
)
SELECT * FROM temp
WHERE fld1 IN (SELECT DISTINCT wk1.fld1 FROM wk wk1 left join wk wk2 ON wk1.fld1=wk2.fld2 WHERE wk2.fld2 is null)
) wk2 JOIN 
(SELECT fld1, MIN(no) as gp FROM wk GROUP BY fld1) wk3 on wk2.fld1=wk3.fld1
WHERE wk.fld2=wk2.fld2

Comments

[LAVENDER]

ありがとうございます。
ご教示いただきました
SQLで
魔法のように
やりたいことが出来ました
すばらしいです。
WITH RECURSIVE
初体験です。
どうしてこれでできるのか
きちんと理解して
身に着けたいと思います。

[LAVENDER]

本番データ4万件でもPostgreSQLで瞬時にグループ分けできました。
感動的です。　SQLの理解は。。。まだできていません。
ありがとうございました。　このSQLが書けるように頑張り💪ます😍

[YT0014]

お役に立てて何よりです。
このような複雑なSQLの場合、単独で成立する部分を取り出してみると、理解しやすくなります。
今回の場合、以下のようなSQL
・「WITH RECURSIVE ～ SELECT * FROM temp」(あえてWHEREを除外)
・「SELECT DISTINCT wk1.fld1 ～ WHERE wk2.fld2 is null」など