Eksik verilerle çalışma
SQL Server Veritabanlarında Veri Temizleme
Miriam Antona
Software Engineer
Eksik veriyi anlama
| registration_code | airport_code | carrier_code | canceled | on_time | delayed | diverted |
|-------------------|--------------|--------------|----------|---------|---------|----------|
| ... | ... | ... | ... | ... | ... | ... |
| 000000119 | JFK | AA | 74 | 819 | 233 | 13 |
| 000000130 | JFK | HA | NULL | NULL | NULL | NULL |
| 000000131 | JFK | HA | NULL | NULL | NULL | NULL |
| 000000132 | MSP | YV | 0 | 6 | 1 | 0 |
| ... | ... | ... | ... | ... | ... | ... |
Eksik veriyi anlama
| registration_code | airport_code | carrier_code | canceled | on_time | delayed | diverted |
|-------------------|--------------|--------------|----------|---------|---------|----------|
| ... | ... | ... | ... | ... | ... | ... |
| 000000119 | JFK | AA | 74 | 819 | 233 | 13 |
| 000000130 | JFK | HA | | | | |
| 000000131 | JFK | HA | | | | |
| 000000132 | MSP | YV | 0 | 6 | 1 | 0 |
| ... | ... | ... | ... | ... | ... | ... |
Eksik veriyi anlama - Nedenler
- Kasıtlı olarak
- Veri eklerken hata
- Veri yok
Eksik veriyi anlama - Çözümler
- Önerilen:
- Eksikleri bulmak için araştırın
- İş ihtiyacına göre:
- Olduğu gibi bırakın
- Eksik değerli satırları kaldırın
- Başka bir değerle doldurun (metin, ortalama vb.)
Eksikleri kaldırma - IS NOT NULL
| airport_code | airport_name | airport_city | airport_state |
|--------------|-----------------------------------------|-------------------|---------------|
| ... | ... | ... | ... |
| DFW | Dallas/Fort Worth International | Dallas/Fort Worth | Texas |
| BOS | Logan International | Boston | Massachusetts |
| SEA | Seattle/Tacoma International | NULL | NULL |
| PHL | Philadelphia International | Philadelphia | NULL |
| SLC | Salt Lake City International | NULL | Utah |
| MCO | Orlando International | Orlando | Florida |
| TPA | Tampa International | Tampa | Fl |
| FLL | Fort Lauderdale-Hollywood International | Fort Lauderdale | FL |
| ... | ... | ... | ... |
Eksikleri kaldırma - IS NOT NULL
SELECT * FROM airports
WHERE airport_state IS NOT NULL
| airport_code | airport_name | airport_city | airport_state |
|--------------|-----------------------------------------|-------------------|---------------|
| ... | ... | ... | ... |
| DFW | Dallas/Fort Worth International | Dallas/Fort Worth | Texas |
| BOS | Logan International | Boston | Massachusetts |
| SLC | Salt Lake City International | NULL | Utah |
| MCO | Orlando International | Orlando | Florida |
| TPA | Tampa International | Tampa | Fl |
| FLL | Fort Lauderdale-Hollywood International | Fort Lauderdale | FL |
| ... | ... | ... | ... |
Eksikleri kaldırma - IS NOT NULL
SELECT * FROM airports
WHERE airport_state IS NULL
| airport_code | airport_name | airport_city | airport_state |
|--------------|------------------------------|--------------|---------------|
| SEA | Seattle/Tacoma International | NULL | NULL |
| PHL | Philadelphia International | Philadelphia | NULL |
Eksikleri kaldırma - <> ''
SELECT * FROM airports
| airport_code | airport_name | airport_city | airport_state |
|--------------|-----------------------------------------|-------------------|---------------|
| ... | ... | ... | ... |
| DFW | Dallas/Fort Worth International | Dallas/Fort Worth | Texas |
| BOS | Logan International | Boston | Massachusetts |
| SEA | Seattle/Tacoma International | NULL | |
| PHL | Philadelphia International | Philadelphia | |
| SLC | Salt Lake City International | NULL | Utah |
| MCO | Orlando International | Orlando | Florida |
| TPA | Tampa International | Tampa | Fl |
| FLL | Fort Lauderdale-Hollywood International | Fort Lauderdale | FL |
| ... | ... | ... | ... |
Eksikleri kaldırma - <> ''
SELECT * FROM airports
WHERE airport_state <> ''
| airport_code | airport_name | airport_city | airport_state |
|--------------|-----------------------------------------|-------------------|---------------|
| ... | ... | ... | ... |
| DFW | Dallas/Fort Worth International | Dallas/Fort Worth | Texas |
| BOS | Logan International | Boston | Massachusetts |
| SLC | Salt Lake City International | NULL | Utah |
| MCO | Orlando International | Orlando | Florida |
| TPA | Tampa International | Tampa | Fl |
| FLL | Fort Lauderdale-Hollywood International | Fort Lauderdale | FL |
| ... | ... | ... | ... |
Başka bir değerle doldurma - ISNULL
ISNULL ( check_expression , replacement_value )
- ISNULL <> IS NULL
Başka bir değerle doldurma - ISNULL
SELECT
airport_code,
airport_name,
airport_city,
ISNULL(airport_state, 'Unknown') AS airport_state
FROM airports
| airport_code | airport_name | airport_city | airport_state |
|--------------|-----------------------------------------|-----------------|---------------|
| ... | ... | ... | ... |
| SEA | Seattle/Tacoma International | NULL | Unknown |
| PHL | Philadelphia International | Philadelphia | Unknown |
| SLC | Salt Lake City International | NULL | Utah |
| MCO | Orlando International | Orlando | Florida |
| TPA | Tampa International | Tampa | Fl |
| FLL | Fort Lauderdale-Hollywood International | Fort Lauderdale | FL |
| ... | ... | ... | ... |
Başka bir değerle doldurma - ISNULL ve AVG
| registration_code | airport_code | carrier_code | canceled |
|-------------------|--------------|--------------|----------|
| ... | ... | ... | ... |
| 000000128 | JFK | B6 | 181 |
| 000000129 | JFK | EV | 18 |
| 000000130 | JFK | HA | NULL |
| 000000131 | JFK | HA | NULL |
| 000000132 | MSP | YV | 0 |
| 000000133 | MSP | AA | 15 |
| ... | ... | ... | ... |
NULLdeğerini ortalama ile değiştirin.
Başka bir değerle doldurma - ISNULL ve AVG
SELECT registration_code, airport_code, carrier_code,
ISNULL(canceled, (SELECT AVG(canceled) FROM flight_statistics)) AS canceled_fixed
FROM flight_statistics
GROUP BY registration_code, airport_code, carrier_code, canceled
| registration_code | airport_code | carrier_code | canceled |
|-------------------|--------------|--------------|----------|
| ... | ... | ... | ... |
| 000000128 | JFK | B6 | 181 |
| 000000129 | JFK | EV | 18 |
| 000000130 | JFK | HA | 65 |
| 000000131 | JFK | HA | 65 |
| 000000132 | MSP | YV | 0 |
| 000000133 | MSP | AA | 15 |
| ... | ... | ... | ... |
Başka bir değerle doldurma - COALESCE
COALESCE ( arg1, arg2, arg3, ... )
SELECT
airport_code,
airport_city,
airport_state,
COALESCE (airport_state, airport_city, 'Unknown') AS airport_state_fixed
FROM airports
| airport_code | airport_city | airport_state | airport_state_fixed |
|--------------|--------------|---------------|---------------------|
| ... | ... | ... | ... |
| SLC | NULL | Utah | Utah |
| PHL | Philadelphia | NULL | Philadelphia |
| SEA | NULL | NULL | Unknown |
| ... | ... | ... | ... |
Ayo berlatih!
SQL Server Veritabanlarında Veri Temizleme
