DE78,79,80,83,85,86,87,91,121:SQL【データサイエンティスト検定（リテラシーレベル）補習ノート】

2021年9月19日 23:04

データサイエンティスト検定（リテラシーレベル）の公式リファレンスブックで躓いたところをまとめていきます。

自分を含めデータベースの経験がない人にとってはSQLが一番鬼門だと思います。DS検定でもSQLが関わる部分は広く、何かしら出題されるものと思っておいて間違いないと思います。ここではSQLの簡易版であるSQLiteを使って、SQLの操作を体験してみたいと思います。

SQLite

SQLとは異なりサーバー無しで単独で動作します。SQL文が使えて、インストールも超絶簡単で、日本語で表示できるということで選んでみました。ただしMySQL等とは異なる部分もあるので少々注意が必要です。

DB Browser for SQLiteを使ってみる

今回はSQLiteのデータベースをGUIで触れるDB Browser for SQLiteを使うことにします。データベースの中身がGUIで確認できるので初心者向きだと思います。

データベースの作成

2つのCSVを用意しておき、これをDB Browserで読み込んでデータベースを作成してみます。

sales.csv

OrderID,CustomerID,CustomerName,Date,ItemID,Quantity
0001,101,Aさん,2021-09-01,001,2
0001,101,Aさん,2021-09-01,002,2
0002,102,Bさん,2021-09-02,005,1
0002,102,Bさん,2021-09-02,003,4
0003,103,Cさん,2021-09-02,002,3
0004,101,Aさん,2021-09-03,003,4
0005,102,Bさん,2021-09-03,003,2
0006,103,,2021-09-03,001,1
0006,103,Cさん,2021-09-03,003,4
0007,103,,2021-09-04,002,2

item.csv

ItemID,ItemName,Price,Freeze
001,ポテチ,￥160,No
002,チョコ,￥180,No
003,アイス,￥150,Yes
004,ケーキ,￥280,Yes

適当に新しいデータベースを作成し、［ファイル＞インポート＞CSVファイルからテーブルへ］のメニューから、CSVファイルを取り込みます。

取り込むとこんな感じになります。右側のウィンドウで［表示するSQLの送信元］を「アプリケーション」にすると、GUI操作に相当するSQL文が表示されるので参考になります。

データを確認する

［データ閲覧］タブを開くと、テーブルの内容を確認できます。

salesのほうが販売データ、itemのほうが商品データになっています。両者のItemIDをくっつければ、誰がどの商品を買って、いくらの売り上げがあったかを知ることができそうです。あと、いくつかNULLがありますね。

DE78:データのソート

データのソートは簡単です。テーブルを表示している状態で、たとえば一番上の［CustomerName］と書いてあるところをクリックすると昇順、もう一度クリックすると降順になります。

右側のSQLログを見るとORDER BYでソートするカラム名を指定し、ASCで昇順、DESCで降順に並び替えていることがわかります。

DE79:内部結合・外部結合

内部結合はそれぞれのテーブルのカラムの値が一致するデータだけを取得する方法です。さっそくやってみます。

［SQL実行］タブに移って以下のSQL文を記入して、再生ボタンをクリックするとSQL文が実行されます。

SELECT * FROM sales INNER JOIN item ON sales."ItemID" = item."ItemID"

実行結果はこちら。よく見ると、元のsalesテーブルの3番目にあった、Bさんが購入したItemID=5の行がなくなっています。itemテーブルのカラムの値の中に5というデータがなかったので、データを取得しなかったということです。

ちなみにsalesとitemを入れ替えるとこんな感じになります。どの商品を誰が買ったか、という目線で分析したいときにはこちらのほうが向いているかと思います。

続いて外部結合です。内部結合の場合は、一致しないデータは取得しませんでしたが、外部結合の場合は一致しない場合も取得するという違いがあります。さっそくやってみます。

SELECT * FROM item LEFT OUTER JOIN sales ON item."ItemID" = sales."ItemID"

先ほどのINNER JOINと似ていますが、一番下にItemName「ケーキ」の行が増えています。salesテーブルには「ケーキ」の購入データはありませんでしたが、itemテーブルには存在するのでNullでデータが入っています。

ちなみにsalesとitemをひっくり返すとこうなります。この場合、ItemIDが「5」の行が残っています。

NULLと空文字は違う

データベースでややこしいのがNULLと空文字が違うということです。空文字はデータベース上では「空文字という文字列が存在する」と解釈されておりNULLではないのです。

このあたり説明が難しいので、下記の記事をご参照ください。

DE80:NULLを取り除く

ではNULLを取り除く処理を試してみたいと思います。まずは今後あれこれ面倒なので、以下のようにして新しいテーブルresを作っておきます。

CREATE TABLE res AS SELECT * FROM sales LEFT OUTER JOIN item ON sales."ItemID" = item."ItemID"

するとresという新しいテーブルが出来ました。

CustomerNameがNULLの行を選択除外します。

SELECT * FROM res WHERE "CustomerName" IS NOT NULL

NULLと空白の違いを確認する

先ほどNULLと空白は違うという話をしたので、実際に試してみます。テーブルを表示した段階でゆっくりダブルクリックすると記入できるようになるので、一番下のデータを空にしてみます。

そして先ほどと同じSQL文を実行すると、今度は空白の行が表示されました！　NULLと空白は違うことが確認できました。

もし空白も選択除外したい場合は次のようにします。

SELECT * FROM res WHERE ("CustomerName" IS NOT NULL) AND ("CustomerName" != '')

DE85:ランダムサンプリング

ランダムサンプリングを試してみます。以下はランダムに3つ抽出するという意味です。

SELECT * FROM sales ORDER BY RANDOM() LIMIT 3

実行するたびに結果が変わります。

DE86:合計・最大値・最小値・レコード数の算出

合計を算出するにはSUMまたはTOTALを使います。SUMのほうは集計対象が全部整数の場合は整数で値を返しますが、TOTALは浮動小数点で返します。

SELECT SUM("Quantity"), TOTAL("Quantity") FROM res

最大値・最小値を算出するにはMAXとMINを使います。

SELECT MAX("Price"), MIN("Price") FROM res

レコード数を算出するにはCOUNTを使います。ItemNameにはNULLが1個あるので数が変わります。

SELECT COUNT("OrderID"), COUNT("ItemName") FROM res

DE87:データに対する四則演算

今回はQuantity（数量）にPrice（価格）をかけて合計金額を算出したいと思います。

しかし、よくみるとPriceには「￥150」のように通貨記号が入っているのでそのままでは演算できません。そこで、通貨記号を取り除いたデータを新たな列「IntPrice」に追加して、別のテーブルを作ります。

CREATE TABLE item2 AS SELECT *, REPLACE(Price, '￥', '') AS IntPrice FROM item;

一見よさそうに思いましたが、データ型を見てみるとTEXTになっていました。

SELECT TYPEOF(IntPrice) from item2

このままだと演算に不具合が出ると予想されるので、いったん消します。

もっとマシな方法があるかもしれませんが、ここでは次のようにしてみました。
・テーブルitem2をitem2bに名前を変える
・テーブルitem2を新たに定義し、IntPriceを整数型に変える
・テーブルitem2bの内容をitem2にコピーする
・テーブルitem2bを削除する

CREATE TABLE item2 AS SELECT *, REPLACE(Price, '￥', '') AS IntPrice FROM item;
ALTER TABLE item2 RENAME TO item2b;
CREATE TABLE item2(ItemID, ItemName, Price, Freeze, IntPrice INTEGER);
INSERT INTO item2(ItemID, ItemName, Price, Freeze, IntPrice) SELECT ItemID, ItemName, Price, Freeze, IntPrice FROM item2b;
DROP TABLE item2b;

これでitem2にはIntPriceが整数型で入りました。

以前と同様の方法でテーブルを連結します。

CREATE TABLE res2 AS SELECT * FROM sales LEFT OUTER JOIN item2 ON sales."ItemID" = item2."ItemID";

ようやく四則演算できる状態になったので、このようにコマンド入力すると、数量×価格が計算できました！（あ～長かった！）

SELECT OrderID, Quantity, IntPrice, Quantity*IntPrice FROM res2;

同じような方法で足し算や引き算などもできます。

DE91:テーブル仕様

テーブルを作成するときに、データの型やNULLを許可しないなどの制約をつけることができます。

ここではUserIDは重複を許可しない（一意性制約）をつけ、NameはNULLを許可しない（NOT NULL制約）をつけています。

CREATE TABLE user (UserID INTEGER NOT NULL UNIQUE, Name TEXT NOT NULL, Address TEXT)

右側の画面でDBスキーマを表示すると、どのような指定をしたかがわかります。

ではデータを追加してみます。

INSERT INTO user values (1, '太郎', '東京都新宿区')

これは問題なく追加されました。

IDを重複したものを入れようとすると「UNIQUE constraint failed」というエラーになります。

INSERT INTO user values (1, '次郎', '東京都千代田区')

Nameを空にして入れようとすると「NOT NULL constraint failed」というエラーになります。ややこしいのですが、NULLは空白ではないので、NULLの代わりに''（シングルコーテーション2つ）とするとエラーになりません。

Addressが空でも制約はついていないのでエラーになりません。

INSERT INTO user values (2, '次郎', NULL)

また、NameはUNIQUE制約をつけていないので、同じ名前でもエラーになりません。

INSERT INTO user values (3, '次郎', '東京都千代田区')

DE121:SQLの構文

ここまでで登場していないSQL文を使ってみます。

UPDATE（データ更新）

元のデータは3行目が次郎になっているので、三郎に変えてみます。

UPDATE user SET Name = '三郎' WHERE UserID = 3;

GROUP BY（データの集計）

データをグループ化して集計します。ここでは以前使ったテーブルres（下図）で商品名ごとに販売した数量の合計を出してみます。

SELECT ItemName, Quantity, SUM(Quantity) FROM res GROUP BY ItemID;

HAVING（GROUP BYで集計後のデータに対する条件抽出）

GROUP BYで抽出するときに条件設定したい場合、WHEREとHAVINGがあります。WHEREがグループ化する前の処理であるのに対し、HAVINGはグループ化した後の処理になります。

こちらは合計の数量を出した後に、合計の数量が5より大きいものだけ抽出する例です。

SELECT ItemName, Quantity, SUM(Quantity) FROM res GROUP BY ItemID HAVING SUM(Quantity) > 5;

ちなみにWHEREの場合、たとえば各レコード（行）で数量が1個より大きい、つまり複数個売れたときのみ集計するといった使い方もできます。

SELECT ItemName, Quantity, SUM(Quantity) FROM res WHERE Quantity > 1 GROUP BY ItemID ;

初心者にはなかなかハードでしたが、SQLの基本的な構文を覚えると、いろいろ役立ちそうです。

参考資料

この記事が気に入ったらサポートをしてみませんか？