SQLのJOINとGROUP BYを試す

リレーショナルデータベースではテーブルを結び付けるJOINが大事なのですが、「プロになるJava」ではページの都合で取り上げていなかったので補足。

追記 2023/8/11: H2コンソールでは自動でデータベースは作成されないので、とりあえずコードを追加しています。

データベースの準備

ここではSpring BootにH2データベースを導入してH2コンソールのWebインタフェースを使って進めていきます。

「プロになるJava」に従ってすでにH2コンソールでSQLを試している場合は、テーブルの作成から進めてください。

CodespacesやVS Codeである必要はないですが、Spring Bootの環境はこちらを参照。
GitHub CodespacesでSpring Bootの開発を始める - きしだのHatena

H2を導入

まずH2データベースを使える状態にします。

pom.xmlのdependenciesに次のdependencyを追加します。

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>runtime</scope>
</dependency>

今回はH2コンソールを使うだけなので、Spring Data JDBCのdependencyは追加していません。

DBの作成

H2ではJDBC接続時にデータベースが自動作成されますが、H2コンソールからは自動作成されません。

Javaプログラムで接続したときには自動生成されるので、次のコードを実行してください。

package com.example.tasklist;

import java.sql.DriverManager;
import java.sql.SQLException;

public class ConDB {
    public static void main(String[] args) throws SQLException {
        var d = DriverManager.getConnection("jdbc:h2:~/taskdb", 
            "projava", "projavadb");
    }
}

Spring BootでH2コンソールのWebインタフェースを有効にする

src/main/resourcesの下のapplication.propertiesを編集します。

次の1行を追加します。

spring.h2.console.enabled=true

H2コンソールを使うだけなのでspring.datasource関連は追加していません。

H2コンソールへのアクセス

Spring Bootを起動して、URLに/h2-console/を追加します。

ログイン画面が表示されたら、左上の「English」をクリックして、「日本語」を選んでおきましょう。

各項目に次のような値を入力して「接続」をクリックします。

項目	値
ドライバクラス	org.h2.Driver
JDBC URL	jdbc:h2:~/taskdb
ユーザー名	projava
パスワード	projavadb

H2コンソールが表示されるはずです。

ただ、この画面で作業しているとCodespaceのほうで操作がないとみなされて切断されることがあります。反応がなさそうなときはCodespaceの画面を確認してください。

Codespacesでの注意

Codespacesを使っている場合、VS Code内のシンプルブラウザではうまく動かないようです。

また、アクセス時に最後のスラッシュのない/h2-consoleにアクセスしようとするとスラッシュのついた/h2-console/へのリダイレクトが発生しますが、Codespacesを使っているとき8080ポートが追加されてしまってうまく表示されなくなります。URLから「:8080」を削除してください。

テーブルの作成

それではテーブルを作成します。ここではshohinテーブルとtradeテーブルのふたつのテーブルを作ります。

shohinテーブル

CREATE TABLE shohin(
id INT PRIMARY KEY,
name VARCHAR(256),
price int
);

左側のツリーにSHOHINが追加されています。

データを追加しましょう。

INSERT INTO shohin
values
(1, 'APPLE', 300),
(2, 'BANANA', 200)

tradeテーブル

もうひとつのtradeテーブルを作成します。

CREATE TABLE trade (
  id INT PRIMARY KEY,
  shohin_id INT,
  quantity INT,
  date VARCHAR(16),
  FOREIGN KEY(shohin_id) REFERENCES shohin(id)
)

ここでこの一行はshohin_idカラムをshohinテーブルのidカラムと結び付けるものです。

FOREIGN KEY(shohin_id) REFERENCES shohin(id)

データを追加します。

INSERT INTO trade
values
(1, 1, 3, '2024-07-24'),
(2, 1, 2, '2024-07-25'),
(3, 2, 4, '2024-07-25')

ここで２番目の値がshohin_idになりますが、次のようにshohinテーブルに存在しない値を追加しようとするとエラーになります。

INSERT INTO trade
values
(4, 3, 1, '2024-07-26')

FOREIGN KEYを設定すると、対応するテーブルのデータがなければエラーが出るようになり、確実にデータの対応をとることができるようになります。

データベースでは、このようにデータの整合性を保つ機能を持っています。

JOIN

さて、それではこれらのふたつのテーブルを結び付けて見ましょう。

まずはtradeテーブルのデータを全部表示します。

SELECT *
FROM trade

しかしこれではshouhin_idが実際にどの商品を表しているかわかりませんね。

FROM句にshohinも付け加えてみます。

SELECT *
FROM trade, shohin

たしかにtradeテーブルとshohinテーブルが並んで表示されていますが、すべての組み合わせが表示されてしまっています。

WHERE句にtradeテーブルのshohin_idとshohinテーブルのidが一致するような条件を書いてみましょう。

SELECT *
FROM trade, shohin
WHERE trade.shohin_id=shohin.id

tradeとshohinが対応しているものだけが表示されました。

ところで次のようにshohin_idに値を設定せずにtradeテーブルにデータを追加してみましょう。INSERT文ではテーブル名のあとに追加先のカラムを指定することができます。

INSERT INTO trade(id, quantity, date)
VALUES(4, 1, '2024-07-26')

それでは先ほどのSQLを実行してみましょう。「オートコンプリート」の左のボタンで履歴が表示されるので、再実行したいSQLの左の鉛筆ボタンで編集領域に表示されます。バグがあるようで10がついてくるので削除してください。

実行しても、先ほどと変わりませんね。

tradeテーブルだけを見てみると、確かにID=4のデータが入っています。

このようなとき、shohinに対応したデータがなくてもtradeは全部表示したい、ということがあります。そういうときに使うのがleft joinです。

SELECT *
FROM trade LEFT JOIN shohin ON trade.shohin_id = shohin.id

そうすると、shohin_idに値がないものも表示されるようになります。

leftのないJOINの場合、WHERE句での連結と同じになります。

また、RIGHT JOINにすると右に書いたshohinテーブルが全て表示されるようになります。

最後に、項目を見やすいようにしておきましょう。SELECT句に表示するカラムを指定しておきます。ここで、idについてはtradeテーブルにもshohinテーブルにもあるのでtrade.idとしてtradeテーブルのものを指定しています。また、as totalとしてquantity * priceにtotalという名前をつけています。

SELECT trade.id, name, quantity, quantity * price AS total, date
FROM trade LEFT JOIN shohin ON trade.shohin_id = shohin.id

GROUP BY

では、データをグループ化させるGROUP BYを見ておきましょう。

SELECT shohin_id, name, SUM(quantity * price) AS total
FROM trade LEFT JOIN shohin ON trade.shohin_id = shohin.id
GROUP BY shohin_id

GROUP BYをつけると、SELECT句に*は使えなくなります。GROUP BYで指定したカラムか、そのカラムに対応付いたテーブルのカラム、そしてSUMやCOUNTなどの集計関数だけを指定できます。

集計関数には次のようなものがあります。

集計関数	用途
SUM	合計
COUNT	個数
AVG	平均
MIN	最小
MAX	最大

集計関数は、GROUP BYがなくても使えます。

SELECT COUNT(*)
FROM trade

WHERE句をつかって集計対象を指定することができます。

SELECT shohin_id, name, SUM(quantity * price) AS total
FROM trade LEFT JOIN shohin ON trade.shohin_id = shohin.id
WHERE date='2024-07-25'
GROUP BY shohin_id

ORDER BYを使うと並べ替えができます。ORDER BYはGROUP BYがなくても使えます。ASCをつけるか省略した場合は昇順、DESCを付けると降順になります。

SELECT shohin_id, name, SUM(quantity * price) AS total
FROM trade LEFT JOIN shohin ON trade.shohin_id = shohin.id
WHERE date='2024-07-25'
GROUP BY shohin_id
ORDER BY total DESC

ORDER BYがない場合の出力順は決まっていないので、一覧表示などの場合には必ず指定するようにしましょう。

集計した結果から絞り込みを行う場合はHAVINGを使います。

SELECT shohin_id, name, SUM(quantity * price) AS total
FROM trade LEFT JOIN shohin ON trade.shohin_id = shohin.id
WHERE date='2024-07-25'
GROUP BY shohin_id
HAVING total > 700
ORDER BY total DESC

これでSELECT文で書ける句が出そろいました。順番もこの通りで書く必要があります。

プロになるJava―仕事で必要なプログラミングの知識がゼロから身につく最高の指南書