【SQL】データサイエンス100本ノック（構造化データ加工編）に挑戦！！10〜20

バックエンド SQL

こんにちは、ドイです！
前回に引き続き、データサイエンス100本ノックに挑戦していきます。

前回の記事

tech.arms-soft.co.jp

挑戦する前に

前回、ブラウザ上でSQLを実行しましたが、今回はエラーが表示され、SQLを実行できませんでした；；

# エラー内容
UsageError: Cell magic `%%sql` not found.

解決方法

エラー自体は解決できなかったため、今回はDBクライアントツールでSQLを実行することにします。
業務では、Sequel Aceを使用していますが、こちらはMySQLをGUIで管理するためのツールです。データサイエンス100本ノックはPostgresを使用しているため、Postgres に対応しているTablePlusを使用することにしました。

TablePlusインストール方法

  1. こちらからDLします。 https://tableplus.com/

  2. （dockerを立ち上げた上で）TablePlusを開いて、RDBMSの選択画面から接続するRDBMSを選択します。

  3. 接続情報を入力して［Connect］をクリックします。

これで接続完了です。

SQLを実行する場合は、［SQL］ボタンをクリックすると入力する箇所が表示されます。今回はこちらを使用して、データサイエンス100本ノックを始めていきます！
※今回は、DBクライアントツールでSQLを実行するため「%%sql」を省略しています。ブラウザ上で実行する場合は、SQLの前に「%%sql」をつけてください。

S-011: 顧客データ（customer）から顧客ID（customer_id）の末尾が1のものだけ全項目抽出し、10件表示せよ。

SELECT * FROM  customer WHERE customer_id LIKE '%1' LIMIT 10;

S-012: 店舗データ（store）から、住所 （address） に"横浜市"が含まれるものだけ全項目表示せよ。

SELECT * FROM  store WHERE address LIKE '%横浜市%';

S-013: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し、10件表示せよ。

SELECT * FROM customer WHERE status_cd ~ '^[A-F]' LIMIT 10;

S-014: 顧客データ（customer）から、ステータスコード（status_cd）の末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

SELECT * FROM customer WHERE status_cd ~ '[1-9]$' LIMIT 10;

S-015: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

SELECT * FROM customer WHERE status_cd ~ '^[A-F]' AND status_cd ~ '[1-9]$' LIMIT 10;

S-016: 店舗データ（store）から、電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。

SELECT * FROM store WHERE tel_no ~ '[0-9]{3}-[0-9]{3}-[0-9]{4}';

S-017: 顧客データ（customer）を生年月日（birth_day）で高齢順にソートし、先頭から全項目を10件表示せよ。

SELECT * FROM customer ORDER BY birth_day ASC LIMIT 10;

S-018: 顧客データ（customer）を生年月日（birth_day）で若い順にソートし、先頭から全項目を10件表示せよ。

SELECT * FROM customer ORDER BY birth_day DESC LIMIT 10;

S-019: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。

SELECT customer_id, amount, RANK() OVER(ORDER BY amount DESC) FROM receipt LIMIT 10;

S-020: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合でも別順位を付与すること。

SELECT customer_id, amount, ROW_NUMBER() OVER(ORDER BY amount DESC) FROM receipt LIMIT 10;

ウィンドウ関数

ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、通常の集約関数とは異なり、ウィンドウ関数の使用は単一出力行に行をグループ化しません。 行はそれぞれ個別の身元を維持します。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。
https://www.postgresql.jp/docs/9.4/tutorial-window.html

S-019、S-020では、下記のような構文でデータを取得しています。ここで使用しているウィンドウ関数と呼ばれるものについて詳しく見ていきます。

SELECT 列名 RANK() OVER([ORDER BY 列名 DESC] FROM  テーブル名

ウィンドウ関数とは、特定の関数を指すのではなく、テーブルに対して関数を実行する機能のことを指します。
ウィンドウ関数と呼ばれるもの中には、ウィンドウ関数としてしか使用できない関数とウィンドウ関数として使用できる集約関数の2種類が存在します。

集約関数のAVG()で挙動を見ていきます。 receiptテーブルのamountの平均を出力します。

SELECT AVG(amount) FROM receipt;

下記のように単一の結果が返ってきます。

平均値は取得できますが、同時に全体のデータを取得することができません。
次にAVG() + OVER句を使用してデータを取得します。OVER句を使用することにより、どの行に対する処理を行うのかを指定します。

SELECT customer_id, amount, AVG(amount) OVER() FROM receipt;

下記のようなデータが返ってきます。

OVER句でORDER BYを使用してみます。amountに対してORDER BYした平均が返ってきました。

SELECT customer_id, amount, AVG(amount) OVER(ORDER BY amount DESC) FROM receipt;

試しに、S-019でOVER句内で何も指定しない状態にしてみます。
ウィンドウ関数は、OVER句内で指定したパーティションに対して実行されるので、指定のない場合はRANK()の結果が全て1となりました。

SELECT customer_id, amount, RANK() OVER() FROM receipt LIMIT 10;

OVER句に指定する処理によって、取得できる値が異なるので、気をつけて使っていきたいですね。

まとめ

MySQLやPostgresなど、それぞれのRDBMSで書き方に違いがあるので、ちょっとしたことで動作が違ったり思うようなデータが取れないことがあります。
普段LaravelのEloquentORMを使用していると、意識せずに補完してくれているのでありがたさが身に沁みる一方で、基本的な知識は身につけておきたいなと思いました。

これからも引き続き、勉強していきます。