07. データベース

目次

7.1 Excelを用いたデータ操作のための概念 7.2 SQL ＆ NoSQL 7.3 SQLを使ってみよう

7.1 Excelを用いたデータ操作のための概念

本ウェブページは『超入門 はじめてのAI・データサイエンス』第7章に対応しています。Excelを用いた クエリ のイメージづけのために，パワークエリ（Power Query）を操作しているところをお見せします。Macをお使いの方は，パワークエリの操作がMacではWindowsと同様にはできないので留意してください。

（Win）Excelでは，リボンの データ にパワークエリの機能が入っています。Macでも利用可能なMicrosoft社のBI（ビジネスインテリジェンス）ツールであるPower BIがすでに発達しているので，ここでPQをお見せするのはPQを使う必要性からというよりは，Excelからパワークエリに入ったり戻ったりする様子をお見せして，クエリのイメージを掴んでもらうためです。

下の動画では，クエリ上でのデータ取得のイメージづくりため，パワークエリのさまざまな機能のうち，列の操作を紹介しています。Excelから一旦離れて，クエリの中でデータの取得と変換が可能で，パワークエリはそのプロセスも記録してくれます。『超入門　はじめてのAI・データサイエンス』ではクエリの概念を理解すればよいセクションなので，以下の説明と動画は関心のある人のみ参考にしてください。

Windows × 日本語

English subtitles in YouTube: Click on the gear button for "Settings" > Subtitles > Auto-translate > Choose "English" -> Click on CC Button for Subtitles/closed captions

動画のタスクは，年度と番号が合体している請求書の番号を，年度の列と番号だけの列に分割することです。ただし，支店のセルが結合されているという問題があります。動画では，まず，セルの結合を解除していますが，そうすると結合されていた部分の最上部のセルのみに支店の名前がはいり，その下は空欄のセルになってしまいます。

そこで空欄を埋めるタスク，そして請求書の番号を分割するタスク，この2つのタスクをPQで解決しています。その手順は次のとおりです。

  1. PQでクエリを出現させるには，加工したい表の中のどこでもよいのでカーソルで選択します。
  2. 図の赤い丸で示された「テーブルまたは範囲から」をクリックします。
  3. クエリで扱うデータ範囲が問われるので，範囲を確かめて「OK」をクリックします。
  4. クエリが出現します。
alternative

ExcelとPQの違いに，クエリのリボンは，下図のように クエリ専用のリボン であること，空欄が null という欠損値に変換されること，クエリは列の操作専用で セルの操作 ができないことがあります。たとえていえば，クエリは在庫管理調整用の倉庫なので，そこでは個々の商品を開封できません。入荷した商品は，種別に梱包されたまま整理されます。データでいうと「種別」梱包は列，個々の商品はセルに相当するので，クエリでできるのは 列の操作のみ です。

alternative

まず第1のタスクである，空欄を埋めるタスクについて，動画ではカーソルで支店の列を選び，クエリのリボンの変換から，フィルそして「下」を選んでいます。福岡支店（FKO）から東京支店（TKY）まで，簡単にセルが埋まりました。

次に動画では，第2のタスクである，列の分割を行っています。手順は，以下の通りです。

  1. 請求書番号の列をカーソルで選びます。
  2. リボンの 列の分割 の中から，区切り記号による分割 を選びます。
  3. 区切り記号として「スペース」を指定します。
  4. 今回はスペースが1か所なので，分割位置の選択はそのままで「OK」をクリックします。
  5. 「請求書番号（ハイフン）1」と「請求書番号（ハイフン）2」の2列が作成されます。
  6. それぞれの列名をダブルクリックして，「年度」と「請求書番号」に変更します。

クエリの名前を「支店別請求書」としています。右側の「適用したステップ」が増えて，プロセスが記録されています。さて，タスクを完了したので，これをExcelに読み込みます。これも，以下に手順をまとめておきました。

  1. クエリの名前を「支店別請求書」とします。
  2. 右側の「適用したステップ」が増えて，プロセスが記録されていることを確認します。
  3. クエリリボンのホームに戻り，閉じて次に読み込むを選びます。
  4. 「新規ワークシート」のままで「OK」をクリックします。
  5. 元のExcelに新しくクエリの名前のシートが作成されます。

最後に，動画で「更新」について見せている部分についてです。ほぼタスクを完了したのち，ひとつのセルが空欄になっているとき，動画では，元のExcelに移動して，関数をコピーして空欄を埋めました。クエリのシートに戻っても，元データの変更はそのままでは反映されないのですが，更新で自動反映させることができます。クエリのテーブル上で右クリックすると更新があるので，これをクリックしても良いですし，または，リボン右のテーブルデザインのさらに右に「クエリ」という項目があるので，ここから「更新」を選ぶと，元のシートでの変更が反映されます。いったん閉じたクエリも，クエリとの接続で該当のクエリをダブルクリックすれば，いつでも接続できます。

なお，クエリの数式バーに見えるのがM言語と呼ばれる言語です。数式バーが見えない場合は，リボンから表示させることができます。クエリと再接続してから，特に新たなステップは加えていない場合は，閉じるときに読み込む必要はありません。単にウィンドウ画面を閉じればクエリとの接続が閉じられます。

7.1.2 結合

結合は理解しておきたい概念です。結合の種類には，左外部 (left-outer），右外部 (right-outer），完全外部（full-outer），内部 (inner），左反 (left-anti），右反 (right-anti），クロス (cross）があります。次の動画では，架空の学生データを，クエリのマージを使って統合しているので視聴してください。

Windows × 日本語

English subtitles in YouTube: Click on the gear button for "Settings" > Subtitles > Auto-translate > Choose "English" -> Click on CC Button for Subtitles/closed captions

ここでのタスクは，資格を得るための研修をうける学生を特定するのに，これまでに研修を受けていない人の中で，まだ資格を取得していない人を抜き出すというタスクです。ひとつのファイルには資格の有無のデータが入っていて，そのデータを読み込んだクエリには「資格」という名前を付けています。もうひとつのファイルには，これまでに研修を受けていない人のデータが入っていて，そのデータを読み込んだクエリには「要研修」という名前を付けました。

「要研修」のクエリからクエリのマージを選んでいます。マージダイアログボックスでは，上部にすでに「要研修」が入っているので，下部にマージすべき「資格」を選びます。マージするときには，学籍番号を照合してデータを統合したいので，それをキー変数，ここでは照合列として学籍番号を選んでいます。マージウィンドウ内の上を左，下を右として，マージの種類は下の図の関係になります。

alternative

動画の場合は，「要研修」（左）の中で，すでに資格を持っている人（右）を除外したかったので，左反を選んでいます。そしてExcelに閉じて読み込むにすると，N=795だったデータから150人除外されてN=645となった「要研修」のシートが出来ました。動画を視聴してパワークエリによる結合のイメージを掴めたらこのセクションは終了です。

7.2 SQL ＆ NoSQL

このセクションにはコードも動画もありません。書籍を参照してください。

7.3 SQLを使ってみよう

RDBMSには，Oracle DatabaseやMicrosoft SQL Serverのように商用のものもあれば，MySQLやPostgreSQLのように無償のものもあります。ここでは無償のSQLiteでSQLを使ってみます。まずSQLiteをこちらからダウンロードします。各自のPCのOSに合わせてダウンロードしてください。Macの場合はzipファイルを1つダウンロードすれば良いですが，Windowsでは2つ必要です。

下の動画はWindowsへのインストールの方法です。ダウンロードしたらzipファイルを解凍してください。上手く解凍できない場合は，7-zipをインストールしてからzipファイルを右クリックして「ここに展開」を選んでください。デスクトップにsqlite3というフォルダを作って，解凍して得られた全てのファイルをその中に移動します。

次にGUI（グラフィカルユーザインターフェース）ベースでSQLiteを操作することのできる DB Browser for SQLite をダウンロードします。ちなみに本授業の目的は SQLという言語を使ってみること であって，GUIでグラフィカルにデータベースを操作することではないので，DB Browser for SQLiteは SQLコードの実行結果を確認 したりする目的で用います。こちらからダウンロードしましょう。各自のPCに合わせてダウンロードしてください。ダウンロードしたファイルをダブルクリックしたら，基本的にNextボタンで先に進んでインストールしてください。適宜，デバイスの変更を許可して進めてください。

Windows × 日本語

Mac × 日本語

English subtitles in YouTube: Click on the gear button for "Settings" > Subtitles > Auto-translate > Choose "English" -> Click on CC Button for Subtitles/closed captions

それではSQLiteを使ってみましょう。ここで学びたいのは，SQLiteデータベースをGUIで管理するDB Browser for SQLiteアプリの操作に慣れることではなく，SQLの言語を用いて操作する経験をすることです。

SQLではセミコロン (;) までがひとつのSQL文として認識されるので，; まで入力してから実行します。大文字と小文字はあくまでもコードをわかりやすくするために使い分けるもので，SQL側では区別しません。通常はコマンドを大文字で書き，テーブル名やカラム名は小文字にしておくのが一般的です。コマンドを半角で書くのはExcelやPythonと同じですが，SQLはコマンドを半角スペースで区切るため，全角スペースを使わないように注意しましょう。以下の項目を実習してみます。

  1. csvファイルの読込 import csv file - CSV import
  2. テーブル作成 create table - CREATE TABLE
  3. カラム名の変更 rename column - ALTER TABLE RENAME COLUMN
  4. 変更の書込 finalize transaction - (COMMIT)
  5. 選択 selection - SELECT WHERE
  6. 射影 projection - SELECT
  7. 結合 join - SELECT JOIN
  8. 演算 calculate - Operators
  9. 集計 aggregate - Aggregate Functions
  10. 行の並び替え sort - SELECT ORDER BY

7.3.1 テーブル

先ほど挙げた項目を一つずつSQLでやってみます。一通りの流れを把握するために，まずは動画を見てみましょう。

Mac × 日本語

Windows × English

SQLiteで2つのcsvファイルを読み込んでテーブルを作成し，kingakuとcustomerというテーブルを作成します。以下の手順で行ってください。なお，csvファイルではない tsv（tab-separated values）でも，同様に「フィールド区切り」を「タブ」に設定すれば取り込み可能です。

Download the Data 1 Download the Data 2
  1. SQLのフォルダに kingaku.csv と customer.csv を入れておきます。
  2. 新しいデータベースを作り，任意の名前を付けます。
  3. 「テーブルの定義を編集」はキャンセルします。
  4. 「ファイル」→「インポート」→「CSVファイルからテーブルへ」を選択します。
  5. 表示される「CSVファイルをインポート」ダイアログで，「先頭行をカラム名に」などの設定を行い，OK。
  6. kingaku というテーブルが作成されます。
  7. 日本語で文字化けが起きた場合は，ダイアログボックスの「エンコード」から「その他」を選び，Shift_JISと入力。

もし間違ってやり直したかったら，データベースを切断して破棄しても良いですし，いざというときには下記 コード1 で テーブルを削除 することもできます。

コード 1

DROP TABLE
    kingaku
;

テーブル定義の変更は ALTER TABLE で行います。下記 コード2 はカラム名の変更で，payment というカラム名を amount に変更します。「データ閲覧」で列名が変わっていることが確認できます。

コード 2

ALTER TABLE
    kingaku
RENAME COLUMN
    payment
TO
    amount
;

「変更を書き込み」でテーブルへの変更を保存します。保存しないと変更は破棄されます。なおツールⅠの対象はデータ分析を行うユーザーなので，分析者は基本的に元データには変更を加えず，query（照会）して取得したデータを分析するのみであることを心得ておきましょう。

なお，SQLのコードで COMMIT で終わるコードを見かけることがあるかもしれません。COMMIT はSQLでテーブルに加えた変更の確定処理を行うコマンドで，COMMIT で実行が確定されるまでの一連のSQLの処理は トランザクション と呼ばれます。DB Browser for SQLiteでは，「変更を書き込み」が COMMIT に相当します。

7.3.2 選択，射影，結合，演算

SELECT文では，データベースのテーブルはそのままでデータを取得することが可能です。下記 コード3 は 選択（selection） です。データベースの用語で「選択」とはレコードを選択して取得することです。SELECT 文を用いて，FROM 句で customer というテーブルから，number カラムの値が 10847 のレコードの *（=すべての）列を選択します。

コード 3

SELECT * 
FROM customer 
WHERE number = 10847
;

つぎは，射影 (projection)です。行の条件抽出をした上記の「選択」に対して，「射影」とは列を取得することを言います。下記 コード4 では customer テーブルの first_name と family_name のカラムを SELECT 文で取得します。

コード 4

SELECT first_name, family_name 
FROM customer
;

下記 コード5 では，SELECT 文を用いて customer の number カラムの値が 10847 のレコードの first_name と family_name を取得します。

コード 5

SELECT first_name, family_name
FROM customer
WHERE number = 10847
;

下記 コード6 では 結合 (JOIN) を行い，kingaku と customer を結合して，それを invoice というテーブルに格納します。手順としては，まず invoice というテーブルを CREATE で作成し，kingaku の行番号（rowid）と customer の id 列を結合キーとして，kingaku テーブルと customer テーブルを結合したデータを invoice とします。

コード 6

CREATE TABLE invoice AS
SELECT *
FROM kingaku
JOIN customer ON kingaku.rowid = customer.id
;

下記 コード7 は，演算 を行い，円をドルに換算してそれを新しい列に追加します。テーブル定義を変更する ALTER TABLE を使います。ADD COLUMN で新しい列を追加し，そのカラムの値のデータ型を 浮動小数点型（REAL）に定義します。UPDATE で新しく追加した dollar カラムの値を amount / 140（本執筆時のレート）の戻り値にしてテーブルを更新します。

コード 7

ALTER TABLE invoice
ADD COLUMN dollar REAL
;
UPDATE invoice
SET dollar = amount / 140
;

7.3.3 集計と行の並べ替え

つぎの コード8 は 集計 です。支店ごとの invoice テーブルの amount の支店ごとの平均値を出します。SELECT 文に，FROM と GROUP BY が続いています。

コード 8

SELECT branch, AVG(amount) AS avg_amount
FROM invoice
GROUP BY branch
;

下記 コード9 では，四捨五入（に近いまるめ）をするために，ROUND(値, 小数点桁数) に平均値を入れています。

コード 9

SELECT branch, ROUND(AVG(amount), 1) AS avg_amount
FROM invoice
GROUP BY branch
;

SELECT 文の ORDER BY という句を使った行の 並べ替え です。下記 コード10 は amount の大きい順に並べます。

コード 10

SELECT *
FROM invoice
ORDER BY amount DESC
;

© Chikako Takeishi. Designed by Chikafumi Nakamura. All Rights Reserved.