【SQL】SQL ～ 集合演算の応用 ～

SQL

■ はじめに

https://dk521123.hatenablog.com/entry/2023/01/16/000000
https://dk521123.hatenablog.com/entry/2024/09/12/003814

で、SQLの集合演算について、扱った。

　今回は、SQLの集合演算を利用して、
delete + insert 後のデータ確認してみる。

ランキング参加中
プログラミング

目次

【１】データ準備
　１）使用データ
　２）データ登録
【２】データ確認
　１）新規データを抽出する
　２）更新データを抽出する
　３）既存データを抽出する

【１】データ準備

* 試すDBがなければ、以下のサイトで行うといいかも、、、

SQL Fiddle
https://www.db-fiddle.com/

１）使用データ

* PostgreSQL15を想定

データ定義 

CREATE TABLE member_20240914 (
  id       INT,
  name     VARCHAR(50),
  gender     VARCHAR(1),
  country_code     VARCHAR(3),
  class    VARCHAR(10),
  updated_date    DATE
);

CREATE TABLE member_20240915 (
  id       INT,
  name     VARCHAR(50),
  gender     VARCHAR(1),
  country_code     VARCHAR(3),
  class    VARCHAR(10),
  updated_date    DATE
);

使用データ 

-- INSERT member_20240914
INSERT INTO member_20240914 (id,name,gender,country_code,class,updated_date) VALUES
  (1,'Mike','m','USA','bronze','2024-09-14'),
  (2,'Tom',NULL,'GBR','gold','2024-09-14'),
  (3,'Smith','f',NULL,'silver','2024-09-14'),
  (4,'Ken',NULL,'JPN','gold','2024-09-14'),
  (5,'Jack','m','UKR','gold','2024-09-14'),
  (6,'Henry','m','AUS','bronze','2024-09-14'),
  (7,'Sophia','f','JPN','platinum','2024-09-14'),
  (8,'George','m','USA',NULL,'2024-09-14'),
  (9,'Lily','f','JPN','bronze','2024-09-14'),
  (10,'Daisy','f','USA','platinum','2024-09-14')
;

-- INSERT member_20240915
INSERT INTO member_20240915
 (id,name,gender,country_code,class,updated_date) VALUES
  (2,'Tom','m','GBR','platinum','2024-09-15'), -- ☆更新１
  (4,'Ken','m','JPN','gold','2024-09-15'), -- ☆更新２
  (6,'Henry','m','AUS','silver','2024-09-15'), -- ☆更新３
  (8,'George','m','USA','silver','2024-09-15'), -- ☆更新４
  (11,'Leo','m',NULL,NULL,'2024-09-15'), -- ★新規１
  (12,'Sam',NULL,'AUS','bronze','2024-09-15'), -- ★新規２
  (13,'Olivia',NULL,'NZL','silver','2024-09-15'), -- ★新規３
  (14,'John',NULL,'USA','silver','2024-09-15'), -- ★新規４
  (15,'Naomi','f','JPN','bronze','2024-09-15'), -- ★新規５
  (16,'Clara','f','USA','gold','2024-09-15') -- ★新規６
;

２）データ登録

[1] 初回データ登録 

-- 初回登録
CREATE TABLE member AS SELECT * FROM member_20240914;

-- データ確認
SELECT * FROM member ORDER BY id;
id name gender country_code class updated_date
1 Mike m USA bronze 2024-09-14
2 Tom GBR gold 2024-09-14
3 Smith f silver 2024-09-14
4 Ken JPN gold 2024-09-14
5 Jack m UKR gold 2024-09-14
6 Henry m AUS bronze 2024-09-14
7 Sophia f JPN platinum 2024-09-14
8 George m USA 2024-09-14
9 Lily f JPN bronze 2024-09-14
10 Daisy f USA platinum 2024-09-14

[2] 二回目登録 (delete + insert) 

-- 2-0) 実行前にバックアップ(今回の場合、member_20240914と同じだが)
CREATE TABLE member_previous AS SELECT * FROM member;

-- 2-1) delete
DELETE FROM member WHERE id IN (SELECT id FROM member_20240915);

-- 2-2) INSERT
INSERT INTO member (id,name,gender,country_code,class,updated_date)
  SELECT id,name,gender,country_code,class,updated_date
  FROM member_20240915;

-- データ確認
SELECT * FROM member ORDER BY id;

現在のテーブル

id name gender country_code class updated_date
1 Mike m USA bronze 2024-09-14T00:00:00.000Z
2 Tom m GBR platinum 2024-09-15T00:00:00.000Z
3 Smith f silver 2024-09-14T00:00:00.000Z
4 Ken m JPN gold 2024-09-15T00:00:00.000Z
5 Jack m UKR gold 2024-09-14T00:00:00.000Z
6 Henry m AUS silver 2024-09-15T00:00:00.000Z
7 Sophia f JPN platinum 2024-09-14T00:00:00.000Z
8 George m USA silver 2024-09-15T00:00:00.000Z
9 Lily f JPN bronze 2024-09-14T00:00:00.000Z
10 Daisy f USA platinum 2024-09-14T00:00:00.000Z
11 Leo m 2024-09-15T00:00:00.000Z
12 Sam AUS bronze 2024-09-15T00:00:00.000Z
13 Olivia NZL silver 2024-09-15T00:00:00.000Z
14 John USA silver 2024-09-15T00:00:00.000Z
15 Naomi f JPN bronze 2024-09-15T00:00:00.000Z
16 Clara f USA gold 2024-09-15T00:00:00.000Z

【２】データ確認

ここからが、本題、、、

delete + insert の場合でのデータ投入時で、
以下に分類できたとする

(a) 新規データ
(b) 更新データ
(c) 既存データ(変わらずに残っているデータ)

このデータを集合演算を使って抽出してみる
データ投入回数 投入データ日付 総カウント (a)新規データ数 (b)更新データ数 (c)既存データ数 備考
1回目投入時 2024-09-14 10 - - -
2回目投入時 2024-09-15 16 6 4 5 更新データは偶数ID(#2~8)のもの

１）新規データを抽出する

サンプル 

-- 方法１：現在のテーブルから過去のテーブルでの差集合
SELECT id FROM member
EXCEPT
SELECT id FROM member_previous
ORDER BY
  id
;

-- [実行後のテーブルを使わない場合]
--  => 入力したデータ と前テーブル がUniq key不一致したデータを出力する
SELECT *
FROM member_20240915 AS m0915
WHERE m0915.id NOT IN (SELECT id FROM member_previous)
ORDER BY m0915.id;
id
11
12
13
14
15
16

[実行後のテーブルを使わない場合]

id name gender country_code class updated_date
11 Leo m 2024-09-15
12 Sam AUS bronze 2024-09-15
13 Olivia NZL silver 2024-09-15
14 John USA silver 2024-09-15
15 Naomi f JPN bronze 2024-09-15
16 Clara f USA gold 2024-09-15

２）更新データを抽出する

サンプル 

-- 方法１：実行前のテーブルから現在のテーブルで差集合する
SELECT
 updated.id
FROM(
-- ★実行前のテーブルから現在のテーブルで差集合する
SELECT id,name,gender,country_code,class,updated_date FROM member_previous AS old
  EXCEPT
SELECT id,name,gender,country_code,class,updated_date FROM member AS m
) AS updated
ORDER BY updated.id;

-- [実行後のテーブルを使わない場合]
--  => 前テーブル と 入力したデータ でUniq key一致したデータを出力する
SELECT *
FROM member_previous AS mp
WHERE mp.id IN (SELECT id FROM member_20240915)
ORDER BY mp.id;
id
2
4
6
8

[実行後のテーブルを使わない場合]

id name gender country_code class updated_date
2 Tom GBR gold 2024-09-14
4 Ken JPN gold 2024-09-14
6 Henry m AUS bronze 2024-09-14
8 George m USA 2024-09-14

３）既存データを抽出する

サンプル 

-- 方法１：現在のテーブルから実行前のテーブルの全項目の共通部分（積集合）をする
SELECT id,name,gender,country_code,class,updated_date FROM member AS m
  INTERSECT
SELECT id,name,gender,country_code,class,updated_date FROM member_previous AS old
ORDER BY id;

-- [実行後のテーブルを使わない場合]
--  => 前テーブル と 入力したデータ でUniq key不一致したデータを出力する
--  => 結果は同じなので省略
SELECT *
FROM member_previous AS mp
WHERE mp.id NOT IN (SELECT id FROM member_20240915)
ORDER BY mp.id;
id name gender country_code class updated_date
1 Mike m USA bronze 2024-09-14T00:00:00.000Z
3 Smith f silver 2024-09-14T00:00:00.000Z
5 Jack m UKR gold 2024-09-14T00:00:00.000Z
7 Sophia f JPN platinum 2024-09-14T00:00:00.000Z
9 Lily f JPN bronze 2024-09-14T00:00:00.000Z
10 Daisy f USA platinum 2024-09-14T00:00:00.000Z

関連記事

SQL ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2023/01/16/000000
Snowflake ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2024/09/12/003814
SQL ～ テーブル差分を調査することを考える ～
https://dk521123.hatenablog.com/entry/2024/09/22/220455

ランキング参加中
プログラミング