(postgres)以下の条件で削除するDELETE文があれば知りたい

Question

前提・実現したいこと

お世話になります。
SQLを使用して不要なデータを削除を行いたいのですが、条件をどのように指定すれば良いかわかりません。もし

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

本来、登録の際に商品ID１つに対して発注番号１つの所、セットで登録された商品IDが複数の発注番号に紐付けられており、増えてしまった不正なデータをうまく消したい。

該当のソースコード

商品ID	発注番号
gnb76xv9h1cn5uiw	23889
nklyqsf30bk2ghxx	23889
s6ul0m4wi9jxbim5	23889
gnb76xv9h1cn5uiw	23890
nklyqsf30bk2ghxx	23890
s6ul0m4wi9jxbim5	23890
gnb76xv9h1cn5uiw	23891
nklyqsf30bk2ghxx	23891
s6ul0m4wi9jxbim5	23891
7tdpb9uuppg23kkm	23959
rpyrgw558ltdubcf	23959
7tdpb9uuppg23kkm	23962
rpyrgw558ltdubcf	23962

以下同様なレコードが数百行

上記のレコードを

商品ID	発注番号
gnb76xv9h1cn5uiw	23889
nklyqsf30bk2ghxx	23890
s6ul0m4wi9jxbim5	23891
rpyrgw558ltdubcf	23959
7tdpb9uuppg23kkm	23962

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

PostgreSQL 10.14

###追記
発注番号の重複が無いように、紐付けが増えてしまった物を削除したいです。
下記の部分ですと

商品ID	発注番号
gnb76xv9h1cn5uiw	23889
nklyqsf30bk2ghxx	23889
s6ul0m4wi9jxbim5	23889
gnb76xv9h1cn5uiw	23890
nklyqsf30bk2ghxx	23890
s6ul0m4wi9jxbim5	23890
gnb76xv9h1cn5uiw	23891
nklyqsf30bk2ghxx	23891
s6ul0m4wi9jxbim5	23891

正しくは３レコードを登録したかったのですが、
同時に登録した商品IDの数だけ発注番号が重複して登録されております。

商品ID	発注番号
gnb76xv9h1cn5uiw	23889
nklyqsf30bk2ghxx	23890
s6ul0m4wi9jxbim5	23891

商品IDと発注番号の紐付けについては、以下のパターンでも問題有りません。

商品ID	発注番号
nklyqsf30bk2ghxx	23889
s6ul0m4wi9jxbim5	23890
gnb76xv9h1cn5uiw	23891

商品ID	発注番号
s6ul0m4wi9jxbim5	23889
gnb76xv9h1cn5uiw	23890
nklyqsf30bk2ghxx	23891

Comments

[退会済みユーザー]

残すべきものの条件を明確にしてください。発注番号が一番若いやつを残すのか、それとも。

[sazi]

発注番号からみて数百件のうち残すものがその５件の商品IDという事ですか？
残すものにルールが無いなら、地道に指定した削除を行うしかありませんが。

Answer 1

ブランクがあるので、期待する通りになるかわからない、拙いものでよろしければ。

delete from t_order
where order_no in (
  select order_no
  from (
    select product_id, order_no
    from t_order
    except
    select product_id, min(order_no) as order_no
    from t_order
    group by product_id
  )
);

要は、商品IDと発注番号の組から、商品IDごとの最も若い番号を持つものを除いたものから
発注番号のみ取り出して、
その発注番号を持つものを削除する、って流れのつもり。

Comments

[m6159]

回答ありがとうございます、最小値除外によって商品IDの重複を除外する方法参考になります。
発注番号の重複なく商品IDを紐付けたいので、頂いたSQLを参考にして考えてみます。

Answer 2

発注番号の重複が無いように、紐付けが増えてしまった物を削除したいです。

テストではなく、本番環境のデータ補正という事ですよね。
こういう不備の場合、手間を考えての自動化をするのではなく、状況をリストアップして、変更するものをチェックし、1件毎に対象を明らかにして、確実に処理する手法を取るべきです。

こういうところで質問をされるレベルなんですから、尚更です。
そもそも、状況が正しく伝えきれてないとしたら、回答も違うものになるので、それに頼るのは危険すぎます。

楽をしようとして何かあったら、結局、地道にやっておけば良かったと後悔する事になりますよ。

Answer 3

「1セットの中に同じ商品だけ2つ以上含まれており、誤ったDB更新の結果、以下のようなデータができてしまった」ということがありえない前提をとります。

商品ID	発注番号
EEEEEEEEEEEEEEEE	8
EEEEEEEEEEEEEEEE	8
EEEEEEEEEEEEEEEE	9
EEEEEEEEEEEEEEEE	9

この場合、以下のようなクエリで処理できるとは思います。

WITH U AS (
  SELECT 
    T1.商品ID,
    T1.発注番号,
    MOD(DENSE_RANK() OVER (PARTITION BY T2.商品IDリスト ORDER BY T1.発注番号) - 1, ARRAY_LENGTH(T2.商品IDリスト, 1)) + 1 AS RNK1,
    ROW_NUMBER() OVER (PARTITION BY T1.発注番号 ORDER BY T1.商品ID) AS RNK2
  FROM T AS T1
  JOIN (
    SELECT 発注番号, ARRAY_AGG(商品ID ORDER BY 商品ID) AS 商品IDリスト
    FROM T
    GROUP BY 発注番号
  ) AS T2 ON T1.発注番号 = T2.発注番号
)
DELETE
FROM T
WHERE EXISTS (
  SELECT *
  FROM U
  WHERE T.商品ID = U.商品ID
  AND T.発注番号 = U.発注番号
  AND U.RNK1 <> U.RNK2
)

個人的な意見を言うと、SQL一発で処理をせずに、スクリプト言語か何かでロジックを組んで処理したほうが良いかとは思います。

Comments

[m6159]

すごいです、重複した発注番号毎に結合した商品ID配列をJOINして集計関数で２通りの番号を振って一致したもの以外を除外する方法で条件を満たせるのですね、非常に参考になりました。

可能であればお聞きしたいのですが
MOD(DENSE_RANK() OVER (PARTITION BY T2.商品IDリスト ORDER BY T1.発注番号) - 1, ARRAY_LENGTH(T2.商品IDリスト, 1)) + 1 AS RNK1
の部分をDENSE_RANK() OVER (PARTITION BY T2.商品IDリスト ORDER BY T1.発注番号)ではなく上記の記載にした理由をお聞きしたいのですが、どのような場合に違いが出るのでしょうか。

[sazi]

@m6159さん
ピックアップの為にselectするなら良いですけど、いきなりDELETEされてしまうような事だけは避けた方が良いですよ。

[neko_the_shadow]

同じセットを複数回発注した場合にうまく処理するためのものです。たとえば以下のようなデータ対策となります。

(商品ID, 発注番号) = (AAA, 1), (BBB, 1), (AAA, 2), (BBB, 2), (AAA, 10), (BBB, 10), (AAA, 11), (BBB, 11)

[m6159]

なるほど！理解できました。
追加の質問まで回答頂き誠にありがとうございました。