okinawa

IT勉強メモ

【SQL】テーブル結合で行を増殖させないポイント

DB・SQL

テーブル結合で行を増殖させないポイント

ON句の条件を

  • １対１
  • １対多

にする。

1対1で結合


↑元テーブル

SELECT t4.id, t5.id FROM table4 t4
left join table5 t5
on t4.id = t5.id2

実行結果
行数は3行のまま。

1対多で結合


↑元テーブル

SELECT t4.id, t5.id2 FROM table4 t4
left join table5 t5
on t4.id = t5.id2

実行結果2

行数は5行のままなので、左側は増殖、右側は増殖していない。

多対多で結合


↑元テーブル

SELECT t4.id, t5.id2 FROM table4 t4
left join table5 t5
on t4.id = t5.id2

実行結果3

9行に増殖している。
id=２と３が２つずつなので　2×2　で4つに増殖。

left joinなのでイメージはこんな感じ↓

イメージ

1個めの左のid2 = 2　に右のid2 = 2が2個結合。
2個めの左のid2 = 2　に右のid2 = 2が2個結合。
で4つに増える。

DISTINCT・GROUP BYで増殖対策


↑元テーブル

SELECT t4.id2, t5.id2 FROM table4 t4
left join ( select distinct id2 from table5) t5
on t4.id2 = t5.id2

SELECT t4.id2, t5.id2 FROM table4 t4
left join ( select id2 from table5 group by id2) t5
on t4.id2 = t5.id2

実行結果4

どっちのSQLでも同じ結果。

【SQL】３値論理とは

DB・SQL

３値論理

普通は　TRUE・FALSE　の２つ。
SQLは　TRUE・FALSE・UNKNOWNの３つ。

いつUNKNOWNになるの？

NULLが絡むとUNKNOWNになる。

WHERE
A = NULL -- UNKNOWNになる

なのでIS NULLやIS NOT NULLがある。

WHERE
A IS NULL
OR A IS NOT NULL

NOT AND ORでUNKNOWNを評価するとどうなる？

一応これを知っておかないとハマりそう。

真理値の優先順位

とりあえずこれだけ覚えておけばOK

  • AND：FALSE > UNKNOWN > TRUE
  • OR： TRUE > UNKNOWN > FALSE

論理演算の評価一覧

NOT

  • NOT TRUE = FALSE
  • NOT FALSE = TRUE
  • NOT UNKNOWN = UNKNOWN

AND

  • TRUE AND TRUE = TRUE
  • TRUE AND FALSE = FALSE
  • TRUE AND UNKNOWN = UNKNOWN
  • FALSE AND UNKNOWN = FALSE
  • UNKNOWN AND UNKNOWN = UNKNOWN

OR

  • TRUE OR TRUE = TRUE
  • TRUE OR UNKNOWN = TRUE
  • FALSE OR FALSE = FALSE
  • FALSE OR UNKNOWN = UNKNOWN
  • UNKNOWN OR UNKNOWN = UNKNOWN

集合指向言語と手続き型言語の違い

DB・SQL

見返す用のかんたんなメモとして。

集合指向言語と手続き型言語

集合指向：まとめて処理
手続き型：１個ずつ処理

JavaのStreamAPIやC#のLinQなども集合指向だとChat GPT氏が言ってた。

SQLとJavaで同じことをやってみる

SQL

元テーブル

まとめて処理。
簡潔に書ける。

update Table1
set
value = value + 1
where tenpo_cd = 1

実行結果

Java

擬似的なコード↓ ループ文で１件ずつ処理。

for(int i = 0; i < Table1.size(); i++) {
    String tenpo_cd = Table(i).tenpo_cd;
    if(tenpo_cd == 1) {
        元テーブル(i).value += 1;
    ]
}

※注意：Javaはオブジェクト指向型言語。手続き型でも書ける。

集合指向言語の思想

思想の１つに「ループ文を使わずに繰り返し処理をできるようにしよう。」というのがあるらしい。

余談：MAX関数の内部処理

たしかにSQLではfor文みたいなループ文はないけど、内部ではどんな処理やっているの？と思ったのでBing AIに聞いてみた。

結論としては手続き型言語と一緒でした。
内部処理は一緒だけどプログラマーとしては簡潔に書けていいよね、ということなのかな。

以下、Bing AI氏の回答↓

MAX関数の内部処理についてお答えします。

MAX関数は、指定したカラムのレコードを一つずつ比較して、最大値を見つけるというシンプルなアルゴリズムで動作します²。例えば、次のようなテーブルがあるとします。

id name score
1 A 50
2 B 80
3 C 60

このテーブルからscoreカラムの最大値を取得するには、次のようなSQL文を実行します。

SELECT MAX(score) FROM table;

すると、MAX関数はscoreカラムのレコードを一つずつ読み込んで、最大値を保持する変数（仮にmaxと呼びます）と比較します。最初はmaxには何も入っていないので、50が入ります。次に80が読み込まれると、80 > max（50）なので、maxに80が入ります。次に60が読み込まれると、60 < max（80）なので、maxは変わりません。これで全てのレコードを読み終わったので、max（80）が結果として返されます²。

このように、MAX関数は内部的には単純な比較操作を繰り返して最大値を探しています。

関連記事

dodosu.hatenablog.jp

参考

ソース: Bing との会話 2023/2/25
(1) 【SQL】MAX関数（SELECT MAX）を1分でわかりやすく解説. https://it-biz.online/it-skills/sql-max/ アクセス日時 2023/2/25.
(2) 【超便利！】SQLのMAX関数・MIN関数の使い方をわかり .... https://style.potepan.com/articles/17740.html アクセス日時 2023/2/25.
(3) 【SQL】グループごとに最大の値を持つレコードを取得する方法3 .... https://takakisan.com/sql-max-in-each-group/ アクセス日時 2023/2/25.

SQLの集合演算をテーブル結合でやってみる

DB・SQL

集合演算子を使うよりテーブル結合のほうが処理が速いこともあるらしい。

SQLの集合演算

和集合：UNION / UNION ALL
差集合：EXCEPT
積集合：INTERSECT
関係除算：なし

和集合：UNION / UNION ALL

UNIONは重複削除。
UNION ALLは重複も含む。こっちのが処理速度速い。

　
↑元テーブル(classaとclassb）

テーブル結合ではFULL OUTER JOIN。

select * from classa a
full outer join classb b
on a.name = b.name

select name from classa
union
select name from classb

UNIONは縦に結合。
FULL OUTER JOINは横に結合。

差集合：EXCEPT

引き算。classA - classB

select a.name from classa a
left outer join classb b
on a.name = b.name
where
b.name IS NULL --ここがポイント

select name from classa
except
select name from classb

実行結果

積集合：INTERSECT

select a.name from classa a
inner join classb b
on a.name = b.name

select name from classa
intersect
select name from classb

実行結果

関係除算を自力でやる

関係除算とは、あるテーブルから別のテーブルに含まれる全ての値を持つレコードを抽出する操作です。

方法は下記の3つがある。

  1. NOT EXISTSを入れ子にする
  2. HAVING句を使った1対１対応を利用する
  3. 割り算を引き算で表現する


元テーブル（classb・classc）

余りのある関係除算

・割り算を引き算で表現する

SELECT * FROM classc c1
where
not exists(
select name from classb
except
select name from classc c2
where
c1.team = c2.team
)

実行結果

HAVING句を使った1対１対応を利用する

select c.team from classc c
inner join classb b
on c.name = b.name
group by c.team
having count(b.name) = (select count(name) from classb)

実行結果

上記の２つは余りのある関係除算。
イメージは下記。

classC　÷　classＢ　＝　teamA余り（山田）
　　　　　　　+
classC　÷　classB　 ＝　teamB余りなし

＝2余り（山田）

厳密な関係除算

余りなしの関係除算のこと。

・割り算を引き算で表現する

SELECT * FROM classc c1
where
not exists(
select name from classb
except
select name from classc c2
where
c1.team = c2.team
)
and not exists(
select name from classc c3
where
c1.team = c3.team
except
select name from classb
)
# 「B - C = 0」 ＆ 「C - B = 0」　なら余りなしの厳密な除算になる

実行結果

HAVING句を使った1対１対応を利用する

select c.team from classc c
left outer join classb b #left outer join
on c.name = b.name
group by c.team
having count(b.name) = (select count(name) from classb) #結合後のb.nameの数と結合前のb.nameの数の比較
and
count(c.name) = (select count(name) from classb) #結合後のc.nameの数と結合前のb.nameの数の比較

実行結果

参考

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ（ミック）｜翔泳社の本

www.reddit.com

【SQL】特性関数とは

DB・SQL

SQLの特性関数について

特性関数とは、入力項がある集合に含まれるかどうかを調べるための関数で、含まれるなら 1 を、含まれないなら 0 を返すものです²。例えば、次のようなテーブルがあるとします。

id name gender
1 A M
2 B F
3 C M

このテーブルからgenderカラムがMであるレコードの数を取得するには、次のようなSQL文を実行します。

SELECT SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) FROM table;

すると、CASE文が特性関数として動作して、genderカラムがMであれば1を、そうでなければ0を返します。その結果をSUM関数で合計することで、genderカラムがMであるレコードの数（2）が取得できます²。

このように、特性関数は集合に対する条件判定や集計に便利な関数です。

参考

ソース: Bing との会話 2023/2/25
(1) 開始日と終了日をまとめる. http://mickindex.sakura.ne.jp/database/celko/celko_se.html アクセス日時 2023/2/25.
(2) 開始日と終了日をまとめる. https://bing.com/search?q=sql+%e7%89%b9%e6%80%a7%e9%96%a2%e6%95%b0 アクセス日時 2023/2/25.
(3) 特性関数 (確率論) - Wikipedia. https://ja.wikipedia.org/wiki/%E7%89%B9%E6%80%A7%E9%96%A2%E6%95%B0_(%E7%A2%BA%E7%8E%87%E8%AB%96) アクセス日時 2023/2/25.
(4) Microsoft SQL データベース関数とは - SQL Server. https://learn.microsoft.com/ja-jp/sql/t-sql/functions/functions?view=sql-server-ver16 アクセス日時 2023/2/25.
(5) 第11回 SQL流集合操作（4） 集合の性質を調べる | gihyo.jp. https://gihyo.jp/dev/serial/01/sql_academy/0011 アクセス日時 2023/2/25.

今回はBing AIさんに聞いたことをブログに転記しました。

GROUP BYってなんなの？

DB・SQL

Group Byとは

部分集合に分けるのがGroup By。

表示結果をみると行が減るから行が圧縮されるような印象を持っていたがそんなことはない。

元テーブル

・実行SQL

select tenpo_cd from Table1
group by tenpo_cd

実行結果

イメージ図↓

Group By後

実行結果だけ見るとburuiやsyohin列は消えたのか？と思うが、部分集合に切り分けているだけなのでその部分集合の中に列が入っている。

GROUPING演算子とWINDOW関数のフレーム

DB・SQL

あまりちゃんと理解できていない。
自分用のメモとして。

GROUPING演算子

ROLLUP：小計、合計を出力
CUBE：3次元以上のデータ。よくわからなかった。→参考:https://it-trend.jp/bi/article/cube

元テーブル

select tenpo_cd, bunrui, sum(value) from Table1
group by ROLLUP(tenpo_cd, bunrui)

実行結果

bunruiがNULLの行にtenpo_cd毎の小計。
tenpo_cdとbunruiがNULLの行に全合計が出力されている。

WINDOW関数のフレーム

前後行の値を集計可能

PARTITION BYは省略可能。
ORDER BYは必須。

・構文

OVER(PARTITION BY カラム名
    ORDER BY カラム名
    ROWS(RANGE) ~)

1行前の行との合計値を出力するクエリ↓

select 
tenpo_cd
, bunrui
, value
, sum(value)over(partition by tenpo_cd order by tenpo_cd  rows between 1 preceding and current row) 
from Table1

実行結果

・参考サイト

https://ichiroku11.hatenablog.jp/entry/2017/04/19/225831 https://zenn.dev/nanbunan/articles/ad96d04fb2e616