转换不同类型的数据

在 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

数据类型 varchar 不适用于 avg 运算符。

解决类型转换问题

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 |

Ayo berlatih!

在 SQL Server 数据库中清洗数据