小規模データのSQL処理がよくわかる!　前処理・分析の書き方のキホン&応用

ファイルの特徴を知る

私たちがコンピュータを使うとき、一般的にはデータをファイルに保存します。Wordのような文書作成ソフトで文書を作成すれば、そのデータは文書ファイルとして保存されますし、インターネットを使っていて何か気になるデータがあれば、それをダウンロードしてファイルに保存しておくこともあるでしょう。スマートフォンで写真を撮影すれば、それもファイルとして保存されていますし、スマートフォンのアプリなどもすべてファイルです。

このように、私たちはコンピュータやスマートフォンを使うとき、ファイル単位でさまざまなデータを管理しています。このとき、1つのファイルを1つの単位として扱います。音楽であれば1曲の音楽が1つのファイル、写真であれば1枚の写真が1つのファイル、アプリであれば1つのアプリが1つのファイルです(図1)。

図1：ファイルへの保存

これはわかりやすい一方で、複数のデータをまとめて扱うほうが便利な場面があります。たとえば、住所録(アドレス帳)を考えてみましょう。

連絡先の情報を1件ずつそれぞれのファイルとして保存することもできますが、登録した連絡先の一覧を表示したい場合には、それらすべてのファイルを1つずつ開かなければいけません。同様に、名前や住所の一部で検索したいときも、すべてのファイルを開いて、検索条件に当てはまるかを1件ずつ調べなければなりません。

これでは不便なので、住所録のようなデータを作成するときは、表計算ソフトがよく使われます。表計算ソフトではそれぞれのデータを行単位に格納し、列単位で名前や住所、電話番号などを管理します(図2)。

図2：表計算ソフトでのデータの管理

そして、これを1つのファイルとして保存します。これにより、ファイルを1つ開くだけで、保存している連絡先を一覧としてすべて表示することもできますし、何らかの条件を指定したデータを容易に検索できます。表計算ソフトを使うと項目を自由に追加、編集することもでき、便利に使えます。

一方で、誤った項目を編集しても問題なく更新できてしまいます。郵便番号の項目に住所を入れたり、電話番号の項目に名前を入れたりしても登録できてしまうのです。名前を入力することなく連絡先を保存してしまうと、住所や電話番号が記録されていてもそれが誰の連絡先なのかわからなくなります。また、項目を自由にいくつも追加できるため、目的のデータがどこに保存されているのかわからなくなることもあるでしょう。

表計算ソフトを使って複数のデータを1つのファイルに保管できることは便利ですが、特定のデータを更新したいだけなのに、すべてのデータを開かなければいけないという問題もあります。複数人でこのファイルを使おうとすると、ある人が更新している最中は他の人が更新できない、もしくは誰かが更新した内容が知らないうちに他の人の更新によって失われる、といった事態が発生します。

このため、企業などの組織において複数人が1つの同じファイルを更新するような使い方をする場合、表計算ソフトを使って自由に入力する方法ではデータの管理という面で不安が残ります。

大量のデータを登録したときに、検索や更新に時間がかかることも考えられます。ちょっとしたデータの管理であれば手軽に使えて便利ですが、数百万件、数千万件といったデータを扱う用途には向いていないのです。

データベースに求められるものを知る

上述のようなファイルが持つ課題を解決するために使われるのが データベース です。Webアプリケーションのように複数人が同時にアクセスして膨大なデータを扱うような場合でも、データベースは安全にデータを蓄積し、管理できるように作られています。

データベースは単独で動作するだけでなく、さまざまなアプリケーションと連携してデータを処理します。ファイルが持つ課題を解決するために、データベースは図3のような特徴を備えることが求められています。

図3：データベースの特徴

これらは、一般的なデータベース製品であれば標準的な機能として備えています。それに加えて、他のシステムと連携できるようになっています。

本書で解説するような統計的な使い方をするためには、データベースの内部で数学的な処理を実行するだけでなく、登録されたデータを出力してファイルに保存できる機能も必要です。CSV形式など一般的に使えるデータとして出力(エクスポート)することで、他の専門的なプログラミング言語を使って分析できるようにするためです。

データ分析にも使えるデータベース

データベースは、企業内でのデータの保管に使われているだけではありません。データは蓄積するだけでは意味がなく、それを活用してはじめて役に立ちます。そして、その活用の段階ではデータ分析が必要で、統計の考え方が求められます。

集めたデータを分析する、そこから必要なデータだけを選択して解釈する、第三者に伝えるためにグラフを描く、といったすべての段階において、データを効率よく扱うためにもデータベースは必要不可欠です。収集したデータを分析するためには、登録されているデータが信頼できる内容である必要があります。表計算ソフトの例で紹介したように、不適切なデータが登録されている場合、分析する前にそのようなデータを検索して除外しなければなりません。データベースでは、格納されるデータに制限を加えられるため、不適切なデータの登録をある程度は防げます。

また、企業などが扱うデータは膨大になっています。ビッグデータとも呼ばれ、 「3つのV(Volume、Variety、Velocity)」 という言葉が使われるように、「大量で」「多様な」データを「高速に」処理しなければなりません。このように次から次へと更新されるデータを管理するためには、複数の人が更新しても問題ない仕組みが必要です。

さらに、集めたデータから必要なデータだけを選択して解釈するには、データ間の関係に注目することも求められます。あるデータに関係するデータを紐づけて抽出して分析するためには、複数の表を関連づけられる仕組みが必要です。

このような機能をすべて備えているのがデータベースなのです。

関係データベース

データを表計算ソフトのような表形式で管理し、複数の表を関連づけて構成するデータベースを関係データベースといいます。英語のまま、リレーショナルデータベース(Relational Database：RDB)と呼ばれることもあります。データベースとして多く使われる種類であり、本書でもこれ以降、単に「データベース」と書いたときは、関係データベースを指すものとします。関係データベースでは、1つのデータベースの中で複数のテーブル(表)を扱います(図4)。

図4：データベースと表の関係

そして、これらのテーブルの操作や、テーブルに格納したデータの操作にSQLという言語を使います。このため、関係データベースには、MySQLやPostgreSQL、SQLiteのように、「SQL」という単語が含まれた名前がつけられていることが多いものです。

これらは データベース管理システム(DBMS：Database Management System) と呼ばれ、関係データベースであることから リレーショナルデータベース管理システム(RDBMS：Relational Database Management System) と呼ばれることもあります。

RDBMSは、企業の顧客管理や注文管理、在庫管理など多くのシステムで使われています。これらは複数のテーブルを扱い、それぞれのテーブルの間で連携してさまざまな処理を実行します。このとき、一部のテーブルにだけデータが登録され、関連するデータが他のテーブルに登録されないような事態が発生すると困ります。

そこで、RDBMSは一連の処理をまとめて実行する トランザクション という機能を用意しています。トランザクションでは、複数の操作を1つの処理としてまとめ、一括して実行します(図5)。

図5：トランザクション

トランザクションにおける一部の処理が失敗すると、全体を取り消し、すべての処理が成功したときにだけ全体を確定します。このように、トランザクションを使うときに求められる特性として、 ACID(アシッド)特性 があります。これは、図6に示す4つの特性の頭文字を取ったもので、関係データベースはこのACID特性に基づいて設計されています。

図6：ACID特性

NoSQLデータベース

関係データベースは一連の処理を確実に実行できる一方で、クラウドのようにネットワーク経由でやり取りするなど、複数のサーバー間でデータを同期するような場合には性能面で不利な場面があります。整合性を確保するためには、それらの間の連携が求められるためです。また、ビッグデータのような多種多様なデータを扱う場合には、関係データベースのような表形式の保存方法では扱いにくいものです。

そこで、図7の特性を満たすようなデータベースが考えられました。これらの特性の頭文字を取って、BASE特性と呼ばれています。

図7：BASE特性

つまり、つねに整合性を保つようなACID特性のような信頼性を一時的に犠牲にしても、可用性や性能を重視したデータベースだといえます。このような考え方で開発されたさまざまなデータベースが提供されています。

たとえば、「キーバリュー型」と呼ばれる「キー」と「値」を対応づけて管理するものや、「ドキュメント指向」と呼ばれるドキュメントごとに異なるデータ構造を持つものなどがあります。いずれも、関係データベースのようなSQLを使わないことから、 NoSQLデータベース と呼ばれています。この「NoSQL」は「Not Only SQL」だとされることもあります。具体的なNoSQLデータベースとして、MongoDBやCouchDB、Apache Cassandra、HBase、Amazon DynamoDB、Redis、Elasticsearchなどがあげられます。

データ分析の業務でも、性能を重視してこのようなNoSQLが使われることが増えていますが、本書ではSQLを使って分析することを前提としているため、NoSQLについては解説しません。

関係データベースにおけるテーブル

関係データベースでは1つのデータベースに複数のテーブルが含まれることを解説しましたが、このテーブルで使われる基本的な用語を整理しておきます。テーブルは2次元の表で表わされ、これはレコード(行)とカラム(列)で構成されています。そして、個々の値が格納される場所をフィールドといいます。

たとえば、「顧客」という名前のテーブルがあれば、1つのレコードで1人の顧客を表わし、氏名や住所、電話番号といったカラムでそれぞれの項目を定めています(図8)。

図8：顧客テーブルの例

図8の顧客テーブルでのIDのように、テーブルにはレコードを識別するための特別なカラムが設定されます。これは、テーブル内のレコードを特定するときの鍵となるもので、 キー と呼びます。とくに、テーブル内のレコードを一意に識別する(1つに特定する)ために設定されるカラムを 主キー (Primary Key)といいます。主キーはテーブルに1つだけ設定され、そのカラムにはすべてのレコードで異なる値が格納されます(複数のカラムにまたがることはありますが、主キーは1つです)。

データベースには複数のテーブルが格納されますが、これらのテーブル間に何らかの関係がある場合があります。たとえば、図9のような「注文」という名前のテーブルがあったとします。このテーブルには「顧客ID」というカラムがあり、これが顧客テーブルのIDと紐づいています。

このように、他のテーブルと関連したテーブルを作成するとき、テーブル間でデータの整合性を確保するためにカラムに設定されるキーを 外部キー (Foreign Key)といいます。

図9：外部キー

データベースを操作する

テーブルを作成する前に、データベースを用意しなければなりません。関係データベースのデータベースを用意するには、RDBMSの製品から1つを選んで、その製品を使ってデータベースを作成します。

本書では、RDBMSとしてMySQL、PostgreSQL、SQLiteの3つを中心に解説します。他にもOracleやSQL Server、DB2、AccessなどのさまざまなRDBMS製品がありますが、MySQLやPostgreSQL、SQLiteはオープンソースのソフトウェアであり、無料でも利用できることが特徴です。

これらの関係データベースは、SQLという言語を使って操作することは前述した通りです。SQLにはANSIやISO、JISなどの標準化機関にて「標準SQL」という規格が定められており、複数のRDBMSで同じように使えることを目指して改正が続けられています。しかし、それぞれのRDBMS製品は標準SQLを拡張して独自の機能が追加されているため、現在も標準SQLへの対応状況には差があります。それでも、MySQL、PostgreSQL、SQLiteで動く書き方を学んでおけばおおむねカバーできます。

データベースを作成するときは、好みに合ったRDBMS製品を選び、パソコンやサーバーにインストールする方法が一般的です。しかし、手軽に試していただくために、本書ではWebブラウザを使ってオンラインでSQLを試せる「DB Fiddle(https://www.db-fiddle.com/)」というサービスを使用します。このサービス上で提供される、MySQL、PostgreSQL、SQLiteの最新バージョンを使って解説することにします。

このDB Fiddleを使う場合、新たにデータベースを作成する必要はありません。Webサイトにアクセスするだけですでにデータベースが用意されています。図10の中央部分(Schema SQL)の欄でテーブルの作成やデータの登録などの指示を、右側(Query SQL)の欄でテーブルからの抽出などの指示を入力します。

図10：DB Fiddleの画面

DB FiddleはWebブラウザで動作し、過去の実行内容は保存されていません。このため、「Run」ボタンを押したときに入力欄に入力されている内容が使われます。データベースの作成やデータの登録、取得などの処理をすべて並べて記載してから、「Run」ボタンを押してください。なお、DB-FiddleにおけるMySQLのバージョンは8.0でないと、日本語が正しく表示されませんので、ご注意ください。

図11：入力欄にすべてのSQLを記述してから「Run」で実行