मिसिंग डेटा से निपटना
SQL Server डेटाबेस में डेटा क्लीनिंग
Miriam Antona
Software Engineer
मिसिंग डेटा को समझना
| 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 |
| ... | ... | ... | ... | ... | ... | ... |
मिसिंग डेटा को समझना
| 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 |
| ... | ... | ... | ... | ... | ... | ... |
मिसिंग डेटा के कारण
- जानबूझकर
- डेटा डालते समय त्रुटि
- डेटा मौजूद नहीं है।
मिसिंग डेटा के समाधान
- अनुशंसित:
- जाँच करके मिसिंग वैल्यू ढूँढें
- बिज़नेस पर निर्भर:
- जैसा है वैसा छोड़ें
- मिसिंग वैल्यू वाली पंक्तियाँ हटाएँ
- किसी और वैल्यू से भरें (टेक्स्ट, औसत, आदि)
मिसिंग वैल्यू हटाएँ - 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 |
| ... | ... | ... | ... |
मिसिंग वैल्यू हटाएँ - 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 |
| ... | ... | ... | ... |
मिसिंग वैल्यू हटाएँ - 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 |
मिसिंग वैल्यू हटाएँ - <> ''
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 |
| ... | ... | ... | ... |
मिसिंग वैल्यू हटाएँ - <> ''
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 |
| ... | ... | ... | ... |
दूसरी वैल्यू से भरें - ISNULL
ISNULL ( check_expression , replacement_value )
- ISNULL <> IS NULL
दूसरी वैल्यू से भरें - 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 |
| ... | ... | ... | ... |
दूसरी वैल्यू से भरें - ISNULL और 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 |
| ... | ... | ... | ... |
NULLको औसत से बदलें।
दूसरी वैल्यू से भरें - ISNULL और 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 |
| ... | ... | ... | ... |
दूसरी वैल्यू से भरें - 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 |
| ... | ... | ... | ... |
अभ्यास करते हैं!
SQL Server डेटाबेस में डेटा क्लीनिंग
