異なる型のデータを変換する

SQL Server データベースでのデータクレンジング

Miriam Antona

Software Engineer

望ましくないデータ型の導入

SELECT * FROM series
WHERE rating BETWEEN 0 AND 10

SELECT * FROM series
WHERE rating >= 0 AND rating <= 10

| id  | name            | rating | ... |
|-----|-----------------|--------|-----|
| 1   | Adventure Time  | 8.4    | ... |
| 2   | Dexter          | 8.6    | ... |
| 3   | Futurama        | 9.2    | ... |
| 4   | Game of Thrones | 9.3    | ... |
| ... | ...             | ...    | ... |

望ましくないデータ型の導入

SELECT * FROM series
WHERE rating BETWEEN 0 AND 10

SELECT * FROM series
WHERE rating >= 0 AND rating <= 10

| id  | name            | rating | ... |
|-----|-----------------|--------|-----|
| 1   | Adventure Time  | 8.4    | ... |
| 2   | Dexter          | 8.6    | ... |
| 3   | Futurama        | 9.2    | ... |
| 4   | Game of Thrones | 9.3    | ... |
| ... | ...             | ...    | ... |

Column_name	Type
id	int
name	varchar
premiered	date
official_site	varchar
contact_number	varchar
rating	float
summary	varchar

望ましくないデータ型の導入

Column_name	Type
id	int
name	varchar
premiered	date
official_site	varchar
contact_number	varchar
rating	varchar
summary	varchar

望ましくないデータ型の例

SELECT * FROM series
WHERE rating BETWEEN 0 AND 10

SELECT * FROM series
WHERE rating >= 0 AND rating <= 10

varchar 値 '8.4' をデータ型 int に変換中に失敗しました。

望ましくないデータ型の例

SELECT AVG(rating)
FROM series

演算子 avg に対してオペランドのデータ型 varchar は無効です。

型変換の問題を解決する

SELECT * FROM series
WHERE CAST(rating AS FLOAT) BETWEEN 0 AND 10

SELECT * FROM series
WHERE CONVERT(FLOAT, rating) BETWEEN 0 AND 10

| id  | name            | premiered  | contact_number | rating | ... |
|-----|-----------------|------------|----------------|--------|-----|
| 1   | Adventure Time  | 2010-04-05 | 555-906-8845   | 8.4    | ... |
| 2   | Dexter          | 2006-01-10 | 555-156-8845   | 8.6    | ... |
| 3   | Futurama        | 1999-03-28 | 555-210-9951   | 9.2    | ... |
| 4   | Game of Thrones | 2011-04-17 | 555-543-6641   | 9.3    | ... |
| ... | ...             | ...        | ...            | ...    | ... |

型変換の問題を解決する

SELECT AVG(CAST(rating AS FLOAT)) AS rating_casted
FROM series
WHERE CAST(rating AS FLOAT) BETWEEN 0 AND 10

SELECT AVG(CONVERT(FLOAT, rating)) AS rating_casted
FROM series
WHERE CONVERT(FLOAT, rating) BETWEEN 0 AND 10

| rating_casted    |
|------------------|
| 8,62857142857143 |

練習しましょう！

SQL Server データベースでのデータクレンジング