更新日が違う物・DBに無いデータのみ抽出したい

Question

前提・実現したいこと

前提
postgreSQL
言語php
フレームワーク　Laravel

DBのデータ　key=id

id=1
name=ピアノ
last_update_time=2019/01/01 01:01:01

id=2
name=カバン
last_update_time=2019/01/01 01:01:01

更新用配列（array)

list[

id=1
name=ピアノ
last_update_time=2019/01/01 01:01:01
,
id=2
name=カバン
last_update_time=2019/01/31 10:01:01
,
id=3
name=筆箱
last_update_time=2019/02/01 10:01:01

]

■実現したいこと
・更新用対象配列の値の中で
対応するDBTableのlast_updated_timeが違う物・またはDBに無いデータを取得したいです。

上記の例で言えば
id = [2,3]
というidの配列を取得したいです

id=1 同じデータだから必要なし
id=2 更新日が違うから必要あり
id=3 DBに存在していない、新規の値だから必要あり

ということです

■聞きたいこと
取得を実現する為にSQL一本で可能かどうか

■試したこと・考え
①更新用配列を一件ずつループして、
idと更新日が同じデータがないselect文を発行し、データ0件の場合のみ、id[] にプッシュしていく、という方法

②更新用配列を一件ずつループして、動的SQLを作成して一回で取る方法
（db.id= 配列１[id][index] and db.last_update_time = 配列[index][last_update_time] OR)
というような条件文をループで繋げてselect文を作り、その配列と更新用配列のdeffを結果とする方法

できれば上記のような配列ループなどをせず、一本のsqlで組みたいのですが
実現可能でしょうか？？

Answer 1

こんなやり方もあるかなぁと思って記述してみました。

$data = DBのデータ（クエリビルダを利用してwhereinでidが同じものを
　　　　　列名idとlast_update_timeをselectした単純なSQL文）
$list　= 配列

        $compare = function ($x, $y) {
            return $x['id'] <=> $y['id'] ?: $x['last_update_time'] <=> $y['last_update_time'];
        };

        var_dump(array_merge(
            array_udiff($list, $data, $compare),
            array_udiff($data, $list, $compare)
        ));

これで差異のある配列
（例
array:2 [
  0 => array:2 [
    "id" => 2
    "last_update_time" => "2019/01/31 10:01:01"
  ]
  1 => array:2 [
    "id" => 3
    "last_update_time" => "2019/02/01 10:01:01"
  ]
]
コード
id = [2,3]

が取れるかなと思うのですがどうでしょうか？
配列操作は時間がかかるでしょうか？

Answer 2

あくまで「ループしない」という条件を満たすための方法として提案してみます。
※実処理として「おススメするかどうか」は別の話です。更新用配列のサイズが小さいなら、ループしたほうが早いかと……。

１．更新用配列を別テーブル（テンポラリテーブル）に入れる。

２．下記のクエリを発行する（テンポラリテーブルから、元テーブルと「idと最終更新時が一致するもの"以外"」を取得）。
※元テーブルを org 、「更新用配列を入れたテンポラリテーブル」を tmp としています。

SELECT * FROM tmp
WHERE NOT EXISTS(
    SELECT * FROM org
    WHERE
        org.id = tmp.id
        AND
        org.last_updated_time = tmp.last_updated_time
)    

動作検証はしていないので、あくまでイメージ図ということでお願いします。

Comments

[ms5025]

ありがとうございます。ループしたほうが早いということでしたら
そちらにしようかと思います。
量はわかりませんが、あるときは何千単位であるかと思います。
なので迷っていまして・・・
10,20件の運用場所もあるし
何千単位の運用場所もある、といったところです

Answer 3

**unnest()**で配列を行に展開できますので、展開した結果で比較すればSQLのみで可能です。
9.18. 配列関数と演算子

また、POSTGRESは構造体(Type)同士での比較が可能です。
Type定義を行いその構造体にCASTして比較を行うと、記述は簡潔になります。
CREATE TYPE

追記

full outer join (全外部結合）を使用して比較します。

with list as (
  select * from (values
    (1, 'ピアノ', '2019/01/01 01:01:01'::timestamp)
   ,(2, 'カバン', '2019/01/31 10:01:01'::timestamp)
   ,(3, '筆箱', '2019/02/01 10:01:01'::timestamp)
  ) as w(id, name, last_update_time)
)
select coalesce(tbl.id, list.id) as id
from  tbl full outer join list 
      on  tbl.id=list.id
where tbl.id is null
   or tbl.last_update_time!=list.last_update_time

Comments

[ms5025]

ありがとうございます。
Type定義は今の開発環境では難しいです。

また、unnest()で縦展開できても、id とlast_update_timeの紐付けは
どのようにするのでしょうか・・・

[sazi]

unnest()で展開したものとテーブルとの比較です。
MasakinさんのValuesの方が記述は簡単になりそうですけど、展開したものを比較するのは同じですよ。

[ms5025]

idを縦展開したテーブル
last_update_timeを縦展開したテーブル
この２つの紐付けはどうしたらいいんだろうと思いまして。

id=1 and last_update_time=1 のデータがDBにあるかどうか
というsqlが必要かなと思ったのですが。
検討違いでしたらすいません

[sazi]

追記しました。

[ms5025]

ありがとうございます！
やはりこの形になりますか！

Answer 4

検索結果のループはOKですか？

比較するDBはtt1としています。

with sel as (select * from (values
                      (1, 'ピアノ', '2019/01/01 01:01:01'::timestamp),
                      (2, 'カバン', '2019/01/31 10:01:01'::timestamp),
                      (3, '筆箱', '2019/02/01 10:01:01'::timestamp)
                  ) as a(id, name, last_update_time))
select id from sel
    where id = (select sel.id from sel left join tt1 on sel.id = tt1.id 
                     where tt.id is null)
       or id in (select sel.id from sel inner join tt1 on sel.id = tt1.id 
                     where sel.last_update_time <> tt1.last_update_time)

はじめのwith句作成時の配列ループは必要ですが、1本のSQLでいけそうです。
条件部分がもう少しきれいにできそうですが・・・

Comments

[ms5025]

ありがとうございます！
やはり条件文をループで作成する形になりますよね？？
むしろこれ以外方法ってない感じですよね・・・