Introduction to Cleaning Data
การทำความสะอาดข้อมูลในฐานข้อมูล SQL Server
Miriam Antona
Software Engineer
หัวข้อที่ครอบคลุม
- บทที่ 1: เริ่มต้นกับการทำความสะอาดข้อมูล
- บทที่ 2: การจัดการค่า null ข้อมูลซ้ำ และวันที่
- บทที่ 3: การจัดการค่านอกช่วง ประเภทข้อมูล และการจับคู่รูปแบบ
- บทที่ 4: การรวม แยก และแปลงข้อมูล
ชุดข้อมูล: เที่ยวบินสายการบินรายเดือนของสนามบินในสหรัฐอเมริกา ปี 2014-2015
airports
| airport_code | airport_name | airport_city | airport_state |
|--------------|-------------------------------------------|-------------------|---------------|
| MSP | Minneapolis-St Paul International | Minneapolis | Minnesota |
| JFK | John F. Kennedy International | New York City | New York |
| LAX | Los Angeles International | Los Angeles | California |
| DFW | Dallas/Fort Worth International | Dallas/Fort Worth | Texas |
| BOS | Logan International | Boston | Massachusetts |
| SFO | San Francisco International | San Francisco | Californiaa |
| ATL | Hartsfield-Jackson Atlanta International | Atlanta | Georgia |
| ... | ... | ... | ... |
ชุดข้อมูล: เที่ยวบินสายการบินรายเดือนของสนามบินในสหรัฐอเมริกา ปี 2014-2015
carriers
| code | name |
|------|---------------------------------|
| YV | Mesa Airlines Inc. |
| AA | American Airlines Inc. |
| DL | Delta Air Lines Inc. |
| HA | Hawaiian Airlines Inc. |
| MQ | American Eagle Airlines Inc. |
| EV | ExpressJet Airlines Inc. |
| ... | ... |
ชุดข้อมูล: เที่ยวบินสายการบินรายเดือนของสนามบินในสหรัฐอเมริกา ปี 2014-2015
flight statistics
| registration_code | airport_code | carrier_code | canceled | on_time | delayed | ... |
|-------------------|--------------|--------------|----------|---------|---------|-----|
| ... | ... | ... | ... | ... | ... | ... |
| 000000119 | JFK | AA | 74 | 819 | 233 | ... |
| 120 | JFK | B6 | 438 | 1865 | 1010 | ... |
| 000000121 | JFK | HA | 0 | 25 | 3 | ... |
| 122 | JFK | MQ | 102 | 386 | 159 | ... |
| 000000124 | JFK | UA | 22 | 296 | 88 | ... |
| 000000125 | JFK | US | 15 | 191 | 63 | ... |
| 000000126 | JFK | VX | 12 | 225 | 61 | ... |
| ... | ... | ... | ... | ... | ... | ... |
ชุดข้อมูล: เที่ยวบินสายการบินรายเดือนของสนามบินในสหรัฐอเมริกา ปี 2014-2015
pilots
| pilot_code | pilot_name | pilot_surname | carrier_code | entry_date |
|------------|------------|---------------|--------------|------------|
| 1 | Thomas | Peters | HA | 2011-10-01 |
| 2 | Hiroki | Konoe | MQ | 2011-01-21 |
| 3 | Arturo | Montero | UA | 2012-12-28 |
| 4 | David | Captain | US | 2000-10-01 |
| 5 | Ainhoa | Guerrera | VX | 2000-10-05 |
| 6 | Alvin | Andersen | OO | 2012-01-15 |
| 7 | William | Champy | F9 | 2011-03-15 |
| ... | ... | ... | ... | ... |
ทำไมการทำความสะอาดข้อมูลจึงสำคัญ?
- มักได้รับข้อมูลที่ยุ่งเหยิง/สกปรกซึ่งยังไม่พร้อมสำหรับการวิเคราะห์
- ใช้เวลาจำนวนมากกับการทำความสะอาดข้อมูลมากกว่าการวิเคราะห์
- กระบวนการทำความสะอาด -> ข้อมูลที่ชัดเจน
การเติมศูนย์นำหน้าตัวเลข
SELECT * FROM flight_statistics
| registration_code | airport_code | carrier_code | canceled | on_time | delayed | ... |
|-------------------|--------------|--------------|----------|---------|---------|-----|
| ... | ... | ... | ... | ... | ... | ... |
| 000000119 | JFK | AA | 74 | 819 | 233 | ... |
| 120 | JFK | B6 | 438 | 1865 | 1010 | ... |
| 000000121 | JFK | HA | 0 | 25 | 3 | ... |
| 122 | JFK | MQ | 102 | 386 | 159 | ... |
| 000000124 | JFK | UA | 22 | 296 | 88 | ... |
| 000000125 | JFK | US | 15 | 191 | 63 | ... |
| 000000126 | JFK | VX | 12 | 225 | 61 | ... |
| ... | ... | ... | ... | ... | ... | ... |
การเติมศูนย์นำหน้าตัวเลข
ถูกต้อง: 000000128 - ครบ 9 หลัก
ไม่ถูกต้อง: 128
การเติมศูนย์นำหน้า - ใช้ REPLICATE และ LEN
REPLICATE (string, integer)
ทำซ้ำสตริงตามจำนวนที่กำหนด
การเติมศูนย์นำหน้า - ใช้ REPLICATE และ LEN
REPLICATE (string, integer)
ทำซ้ำสตริงตามจำนวนที่กำหนด
REPLICATE('0', 9 - LEN(registration_code))
การเติมศูนย์นำหน้า - ใช้ REPLICATE และ LEN
REPLICATE (string, integer)
ทำซ้ำสตริงตามจำนวนที่กำหนด
REPLICATE('0', 9 - LEN(registration_code))
-- registration_code: 120 => LEN(120) = 3
REPLICATE('0', 6)
การเติมศูนย์นำหน้า - ใช้ REPLICATE และ LEN
ตัวดำเนินการ +
SELECT
REPLICATE('0', 9 - LEN(registration_code)) + registration_code AS registration_code
FROM flight_statistics
CONCAT - ตั้งแต่ SQL Server 2012
SELECT
CONCAT(REPLICATE('0', 9 - LEN(registration_code)), registration_code) AS registration_code
FROM flight_statistics
การเติมศูนย์นำหน้า - ใช้ REPLICATE, LEN และ CONCAT
| registration_code |
|-------------------|
| ... |
| 000000119 |
| 000000120 |
| 000000121 |
| 000000122 |
| 000000123 |
| 000000124 |
| 000000125 |
| 000000126 |
| ... |
การเติมศูนย์นำหน้า - ใช้ FORMAT
FORMAT (value, format [, culture ] )
- ใช้งานได้ตั้งแต่ SQL Server 2012
- value: ตัวเลข วันที่ และเวลา
SELECT
FORMAT(CAST(registration_code AS INT), '000000000') AS registration_code
FROM flight_statistics;
การเติมศูนย์นำหน้า - ใช้ FORMAT
| registration_code |
|-------------------|
| ... |
| 000000119 |
| 000000120 |
| 000000121 |
| 000000122 |
| 000000123 |
| 000000124 |
| 000000125 |
| 000000126 |
| ... |
มาฝึกกันเถอะ!
การทำความสะอาดข้อมูลในฐานข้อมูล SQL Server
