修正が入ったデータを取り出すSQLを教えてください。

SQLで教えてください。
次のようなリストがあります。
データ取得 | 社員番号 | 名前 | 所属部署 | 役職 | 営業所名 |
20201201 | 3xxx001 | SQL太郎 | 営業部 | 課長 | 東京 |

データ数2000件ほどのリストを毎日DLして新しいtableに入れていきます。「データ取得」のフィールドにはDLした日付が入ります。基本的にはデータ取得の日時だけがアップデートされていきますが、その他のデータに修正が入ることあります。どこにどのような修正が入ったかを取り出すSQLを作りたいです。

試したこと

データ取得の列以外で重複行を取りだして、重複していない行を比較するようにするのかと考えてみましたが、よくわかりませんでした。

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

SQLITE3

20201230 追記

期待する出力は、前日にinsertした内容と比較して、日付以外で変更があれば、そのその変更前の行と変更後の行を取り出すことができればと考えています。
insertされたリスト

データ取得	社員番号	名前	所属部署	役職	営業所名
20201201	3xxx001	SQL太郎	営業部	課長	東京
20201201	3xxx001	SQL次郎	営業部	課長	東京
20201201	3xxx001	SQL三郎	営業部	課長	東京

次の日にinsertされた結果

データ取得	社員番号	名前	所属部署	役職	営業所名
20201201	3xxx001	SQL太郎	営業部	課長	東京
20201201	3xxx001	SQL次郎	営業部	課長	東京
20201201	3xxx001	SQL三郎	営業部	課長	東京
20201202	3xxx001	SQL太郎	営業部	課長	東京
20201202	3xxx001	SQL次郎	営業部	課長	神奈川
20201202	3xxx001	SQL三郎	営業部	課長	東京

取り出したい修正箇所が分かるリスト

データ取得	社員番号	名前	所属部署	役職	営業所名
20201201	3xxx001	SQL次郎	営業部	課長	東京
20201202	3xxx001	SQL次郎	営業部	課長	神奈川

このDBの大元は別にあり、毎日その日の最新情報がCSVで送られてくるのを別にDBを作り、そこに毎日データを積み上げていっています。前日送られてきたデータに修正が無ければ全く同じなのですが、修正が入ったとき、それがどこに修正入ったかを知りたいためこのようなことを行っています。大元のDBにアクセスできないがための状況です。

同一日付、同一人物で、複数件のデータがDLされるんですか？

→同じ日付であれば同一人物のデータは１件となります。

20201202:神奈川は、どこから発生したのですか？

→大元のDBに修正が入ったため、次の日に送られてくるデータが修正されています。

比較対象データは、社員番号がキーですか？

→社員番号がキーとなります。

比較対象は、常に、前日のデータですか？

→常に毎日送られてくるわけでもないので、前日のデータとはならないこともあります。ただ、一個前に送られてきたデータです。

前日のデータがない場合は、あり得ますか？

→あり得ます。

前日のデータがなく、それ以前のデータが存在することはありますか？

→データが積みあがっていくため、以前のデータはあります。同一人物に修正が入ってきた履歴をあとからたどる必要もあり、古いデータも残してあります。

以上です。

よろしくお願いします。

YT0014

2020/12/29 16:38

現在行っていること、及び、実現したいことを、詳細かつ具体的にご提示ください。「毎日DLして新しいtableに入れていきます。」というのは、毎日、create tableを行って、table20201201のような年月日の入ったテーブルを作成して、そこにDLしたデータをinsertするということでしょうか？それとも、同一のテーブルに、DLしたデータをinsertして、テーブルに新しいレコードが追加されるということでしょうか？または、同一のテーブルの、社員番号の一致するレコードを、DLしたデータでupdateして、該当社員が居ない場合は、レコードが追加されるということでしょうか？ここに記載した選択肢と参考にしていただき、期待する出力も併せて、質問文を編集してください。また、テーブルは、マークダウン形式にて、CREATE TABLE文にて、ご提示ください。

NobIshi

2020/12/30 01:50 編集

失礼いたしました。＞同一のテーブルに、DLしたデータをinsertして、テーブルに新しいレコードが追加されるということでしょうか？ →こちらになります。期待する出力は、前日にinsertした内容と日付以外で変更があれば、そのその変更前の行と変更後の行を取り出すことができればと考えています。 DLするリスト | データ取得 | 社員番号 | 名前 | 所属部署 | 役職 | 営業所名 | | ------ | ------ | ------ | ------ | ------ | ------ | | 20201201 | 3xxx001 | SQL太郎 | 営業部 | 課長 | 東京 | | 20201201 | 3xxx001 | SQL次郎 | 営業部 | 課長 | 東京 | | 20201201 | 3xxx001 | SQL三郎 | 営業部 | 課長 | 東京 | insertされたリスト | データ取得 | 社員番号 | 名前 | 所属部署 | 役職 | 営業所名 | | ------ | ------ | ------ | ------ | ------ | ------ | | 20201201 | 3xxx001 | SQL太郎 | 営業部 | 課長 | 東京 | | 20201201 | 3xxx001 | SQL次郎 | 営業部 | 課長 | 東京 | | 20201201 | 3xxx001 | SQL三郎 | 営業部 | 課長 | 東京 | | 20201202 | 3xxx001 | SQL太郎 | 営業部 | 課長 | 東京 | | 20201202 | 3xxx001 | SQL次郎 | 営業部 | 課長 | 神奈川 | | 20201202 | 3xxx001 | SQL三郎 | 営業部 | 課長 | 東京 | 取り出したいリスト | データ取得 | 社員番号 | 名前 | 所属部署 | 役職 | 営業所名 | | ------ | ------ | ------ | ------ | ------ | ------ | | 20201201 | 3xxx001 | SQL次郎 | 営業部 | 課長 | 東京 | | 20201202 | 3xxx001 | SQL次郎 | 営業部 | 課長 | 神奈川 | よろしくお願いします。

YT0014

2020/12/30 03:30

質問は編集できますので、補足情報は質問に追記してください。

YT0014

2020/12/30 03:41

同一日付、同一人物で、複数件のデータがDLされるんですか？ 20201202:神奈川は、どこから発生したのですか？比較対象データは、社員番号がキーですか？比較対象は、常に、前日のデータですか？前日のデータがない場合は、あり得ますか？前日のデータがなく、それ以前のデータが存在することはありますか？仕様の再検討と、サンプルデータの見直しをお願いいたします。

NobIshi

2020/12/30 14:30

最初の質問に、「20201230 追記」を加えました。説明の仕方を変えています。よろしくお願いします。

YT0014

2020/12/31 01:13

文章とサンプルデータが矛盾しているんですが、どちらを信じれば良いのでしょうか？また、QA形式ではなく(質問に漏れがある可能性が高いので)、明確な仕様としてご提示ください。現状だと、追加の質問が増えていくだけです。例：前日以前のデータが存在しない場合はあるのか？あるのなら、その場合は、どのような出力が必要なのか？

NobIshi

2020/12/31 04:32

取り出したいデータは前回入れたデータと今回入れたデータの差異となります。データを入れるタイミングが毎日ではなく、不規則にかわったりします。よって、前日のデータということでは無い可能性もあります。前回と比べて修正がどこに入ったかを確認するために必要となります。サンプルではSQL次郎の営業所が東京から神奈川に代わっています。この修正をわかりたいです。明確な仕様の記載方法が分からずご理解いただきたく。よろしくお願いします。

行動規範の内容に同意します

回答1件

ベストアンサー

半分だけ、かつ、未検証ですが、変更があった列を取得するSQL、こうなるかと。

SQLite3
1SELECT DISTINCT * FROM テーブル名
2  WHERE
3    データ取得 = (データ取得日) AND 社員番号 IN (
4      SELECT thisTime.社員番号 FROM テーブル名 AS thisTime
5        LEFT OUTER JOIN テーブル名 AS oldTime ON thisTime.社員番号 = oldTime.社員番号
6        WHERE 
7          (oldTime.データ取得, oldTime.社員番号) IN (
8            SELECT MAX(データ取得), 社員番号 FROM テーブル名 GROUP BY 社員番号 WHERE データ取得 < (データ取得日)
9            )
10          AND (thisTime.名前 <> oldTime.名前 OR thisTime.所属部署 <> oldTime.所属部署 OR thisTime.役職 <> oldTime.役職 OR thisTime.営業所名 <> oldTime.営業所名)
11      )

まず、過去のデータから、当日より前の最終日付を、社員番号毎に取得します。
SELECT MAX()の箇所です。
それキーに直前データテーブル(oldTime)を取得し、当日データテーブルと名前、所属部署、役職、営業所名が異なるレコードの社員番号を抽出。
最後に、上記の社員番号の、当日分のレコードを、重複なしで取得。

同様の手順で、直前データテーブルから、直前レコードを抽出して、上記とUNIONし、ORDER BYで、並べ替えれば、ご希望のViewが取得できるのではないでしょうか？

不具合や仕様が異なる場合は、申し訳ありませんが、質問者様にて、ご調整ください。

投稿2020/12/31 05:30

YT0014

総合スコア1708