Googleデータアナリティックス - 17週目 データのクリーニング・SQLを使った処理

Googleデータアナリティックスという、データ分析を仕事にしたい人向けの講座を受講してます。全6か月分、計30週分のコースで優秀なデータアナリストになろうという内容です。

JRC（Google Career Certificates） | Coursera

今回はSQLを使ったデータクリーニングについて学びました。正直、SQLはいまいち「データを操作している」という実感が湧かなくてつまらないです。早くPowerBiやRStudioに移りたいな～と思いながらやってます。

データのクリーニング・SQLを使った処理

人間が人力で80 億人の名前をすべて読むには、 およそ100 年の時間がかかる。Excelでは一列で100万のデータを処理できるが約800ファイルに纏める必要があり、こちらも現実的ではない。
SQLを使えば、80 億人分の名前を数秒で処理することができる。

SQLはリレーショナルデータベースからテーブル・行列を条件式を使って抽出できるプログラミング言語である。
リレーショナル データベースとは 一連のテーブルを連結して 関連性を持たせるデータベースのことで、 1970 年に Edgar F.Codd が開発した。IBM のコンピュータ科学者はIBM System R からデータを操作して 取り出す方法を考案し、それらに改良が施され1986年にSQLとして一般公開された。

SQLを使うべき場面は、主にデータセットが100万行以上となり大規模である場合、また別ソフトウェアでさらに分析するためのデータを準備する場合、データベース全体のテーブルにアクセスし自動で抽出したい場合、等々が挙げられる。

基本的なSQLクエリ

customer.customer01というテーブルからname・cityの列を抽出する

SELECT
name,
city
FROM
customer.customer01

customer.customer01というテーブルに新しいデータを挿入する。
新規顧客の情報をデータベースに追加するときなどにつかう。

INSERT INTO customer.customer01
(customer_id, name, address, city, state, zipcode, country)
VALUES
(2645, "33 SQL Road", "Jackson", "MI", 49202, "US")

テーブルの特定行を更新し、新しいデータにする。

UPDATE customer.customer01
SET address = "123 New Address"
WHERE customer_id = 2645

num_of_doors 列に NULL 値が含まれているかどうかを確認する。

SELECT
*
FROM
cars.car_info
WHERE
num_of_doors IS NULL;

num_of_doors 列が NULL 値となる行列（Dodge gas sedansとMazda diesel sedans）のnum_of_doorsを更新する

UPDATE
cars.car_info
SET
num_of_doors = "four"
WHERE
make = "dodge"
AND fuel_type = "gas"
AND body_style = "sedan";

スペルミスがある行を更新する

UPDATE
cars.car_info
SET
num_of_cylinders = "two"
WHERE
num_of_cylinders = "tow";

データ行から最大値・最小値を抽出する

SELECT
MIN(compression_ratio) AS min_compression_ratio,
MAX(compression_ratio) AS max_compression_ratio
FROM
cars.car_info

数値の間違いがある行を削除する

DELETE cars.car_info
WHERE compression_ratio = 70;

LENGTH 文を使って文字列変数がそれぞれどれくらいの長さなのかを抽出する

SELECT
DISTINCT drive_wheels,
LENGTH(drive_wheels) AS string_length
FROM
cars.car_info;

TRIM 関数を使用して drive_wheels 列の余分なスペースをすべて削除する

UPDATE
cars.car_info
SET
drive_wheels = TRIM(drive_wheels)
WHERE TRUE;

4 文字未満のアルバム名をすべて返す

SELECT LENGTH(title) < 4
FROM
album

国 (country) 名の最初の 3 文字を取得する

SELECT
customer_id,
SUBSTR(country, 1, 3) AS new_countr
FROM
customer
ORDER BY
country

これまで学んだ SQL

SELECT 文を使った、テーブルからのデータ取得
DISTINCT, COUNT + WHERE などのコマンドを使用した、データ重複の削除
TRIM() と SUBSTR による文字列データの操作
CREATE TABLE と DROP TABLE を使ったテーブルの作成、および削除
CAST によるデータ型の変更