SQLでデータクリーニングするための8ステップ

言わずもがなデータは現代ビジネスでは欠かせない存在です。
しかし取ってきたデータそれ自体がすぐに役に立つということはありません。
データサイエンティストらは60〜80%の時間をデータクリーニングに費やしているとも言われます。
今回はSQLでデータクリーニングを行うための8ステップを紹介します。

ステップ1：データを理解する

まずはこれから扱うテーブルがどんなスキーマなのかを確認する必要があります。

DESCRIBE sales_data;

ステップ2：欠損値を見つける

欠損値は分析の結果を歪める恐れがあるので対象しなければなりません。

SELECT *
FROM sales_data
WHERE column_name IS NULL;

ステップ3：欠損値に対処する

見つけた欠損値に対処します。
やり方は色々ありますが、ここでは大きく3つに分けて行います。

無視する

データセットが十分に大きく比較的少数の欠損値を無視しても問題ない場合のやり方です。

SELECT *  
FROM sales_data  
WHERE column_name IS NOT NULL;

埋める

特定の値で埋めてしまう方法もあります。

UPDATE sales_data  
SET column_name = 'default_value'  
WHERE column_name IS NULL;

補間する

前後の行を参考に補間するやり方もあります。

WITH RankedData AS (  
	SELECT  
		column_name,  
		LAG(column_name) OVER (ORDER BY id) AS prev_value,  
		LEAD(column_name) OVER (ORDER BY id) AS next_value,  
		id  
	FROM  
		sales_data  
),  
InterpolatedData AS (  
	SELECT  
		id,  
		column_name,  
		CASE  
		WHEN column_name IS NULL THEN (prev_value + next_value) / 2  
		ELSE column_name  
		END AS interpolated_column  
	FROM  
		RankedData  
)  
SELECT *  
FROM InterpolatedData;

ステップ4：重複行を排除する

しばしば何らかの原因で重複した行が入ってくるので適宜排除します。

-- Create a new table with distinct records  
CREATE TABLE temp_sales_data AS  
SELECT DISTINCT *  
FROM sales_data;  
  
-- Delete the old table  
DROP TABLE sales_data;  
  
-- Rename the new table to the original table name  
ALTER TABLE temp_sales_data  
RENAME TO sales_data;

ステップ5：データ形式を統一する

分析する際にデータ形式が一貫していることは極めて重要です。

UPDATE sales_data  
SET date_column = CAST(date_column AS DATE)  
WHERE date_column IS NOT NULL;

ステップ6：数値列をバリデーションする

数値列の値が期待する範囲に収まっているかを確認します。

SELECT *  
FROM sales_data  
WHERE numeric_column NOT BETWEEN expected_minimum AND expected_maximum;

ステップ7：文字列をきれいにする

文字列には余計な空白が入っていたり大文字・小文字が入り混じったりしているのできれいにします。

UPDATE sales_data  
SET string_column = TRIM(string_column);

ステップ8：外れ値に対処する

エンドユーザーから渡ってくる値を扱う場合にはどうしても外れ値が入ってくるので対処します。
ここでは四分位数を用います。

WITH Quartiles AS (  
	SELECT  
		column_name,  
		PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY column_name) AS Q1,  
		PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY column_name) AS Q3  
	FROM  
		sales_data  
),  
IQR AS (  
	SELECT  
		Q1,  
		Q3,  
		(Q3 - Q1) AS IQR  
	FROM  
		Quartiles  
),  
Outliers AS (  
	SELECT  
		*,  
		Q1 - (1.5 * IQR) AS Lower_Bound,  
		Q3 + (1.5 * IQR) AS Upper_Bound  
	FROM  
		IQR  
)  
SELECT  
	*  
FROM  
	sales_data  
JOIN  
	Outliers  
ON  
	sales_data.column_name BETWEEN Lower_Bound AND Upper_Bound;

まとめ

データクリーニングは極めて重要な作業ですがとても手間のかかるものでもあります。
SQLの強力な表現力を有効活用して、ビジネスの意思決定に役立ててください！

参考

https://medium.com/illumination/clean-your-data-using-sql-efficiently-in-8-simple-steps-c267a64e0f3e