डुप्लिकेट डेटा से बचना
SQL Server डेटाबेस में डेटा क्लीनिंग
Miriam Antona
Software Engineer
डुप्लिकेट डेटा क्या है?
| registration_code | airport_code | carrier_code | registration_date | canceled | on_time | delayed | diverted |
|-------------------|--------------|--------------|-------------------|----------|---------|---------|----------|
| 000000134 | MSP | DL | 2014-01-01 | 61 | 3148 | 925 | 4 |
| 000000134 | MSP | DL | 2014-01-01 | 61 | 3148 | 925 | 4 |
| registration_code | airport_code | carrier_code | registration_date | canceled | on_time | delayed | diverted |
|-------------------|--------------|--------------|-------------------|----------|---------|---------|----------|
| 000000134 | MSP | DL | 2014-01-01 | 61 | 3148 | 925 | 4 |
| 000000150 | MSP | DL | 2014-01-01 | 61 | 3148 | 925 | 4 |
| registration_code | airport_code | carrier_code | registration_date | canceled | on_time | delayed | diverted |
|-------------------|--------------|--------------|-------------------|----------|---------|---------|----------|
| 000000134 | MSP | DL | 2014-01-01 | 61 | 3148 | 925 | 4 |
| 000000150 | MSP | DL | 2014-01-01 | 3148 | 61 | 4 | 925 |
- डुप्लिकेट डेटा हमारे विश्लेषण में दखल दे सकता है!
दोहराए जाने वाले समूह पहचानना
flight_statistics
|registration_code|airport_code|carrier_code|registration_date|canceled|on_time|delayed|diverted|statistician_name|statistician_surname|
|-----------------|------------|------------|-----------------|--------|-------|-------|--------|-----------------|--------------------|
- airport_code
- carrier_code
- registration_date
दोहराए जाने वाले समूह पहचानना
डुप्लिकेट पंक्तियाँ हटाना आसान है:
|registration_code|airport_code|carrier_code|registration_date|canceled|on_time|delayed|diverted|statistician_name|statistician_surname|
|-----------------|------------|------------|-----------------|--------|-------|-------|--------|-----------------|--------------------|
|000000134 |MSP |DL |2014-01-01 |61 |3148 |925 |4 |Anne |Johnson |
|000000150 |MSP |DL |2014-01-01 |61 |3148 |925 |4 |Bernard |Ross |
|registration_code|airport_code|carrier_code|registration_date|canceled|on_time|delayed|diverted|statistician_name|statistician_surname|
|-----------------|------------|------------|-----------------|--------|-------|-------|--------|-----------------|--------------------|
|000000134 |MSP |DL |2014-01-01 |61 |3148 |925 |4 |Anne |Johnson |
|000000134 |MSP |DL |2014-01-01 |61 |3148 |925 |4 |Anne |Johnson |
दोहराए जाने वाले समूह पहचानना
- यहाँ एक समस्या है जिसे हटाना है!
|registration_code|airport_code|carrier_code|registration_date|canceled|on_time|delayed|diverted|statistician_name|statistician_surname|
|-----------------|------------|------------|-----------------|--------|-------|-------|--------|-----------------|--------------------|
|000000134 |MSP |DL |2014-01-01 |61 |3148 |925 |4 |Anne |Johnson |
|000000150 |MSP |DL |2014-01-01 |3148 |61 |4 |925 |Bernard |Ross |
जाँच करें!
डुप्लिकेट डेटा का पता लगाना - ROW_NUMBER()
ROW_NUMBER () OVER (
[ PARTITION BY
value_expression ,
... [ n ] ]
order_by_clause )
- Partitions = दोहराए जाने वाले समूह
- प्रत्येक पार्टिशन की पहली पंक्ति के लिए 1 से शुरू होने वाली संख्या देता है
- उसी पार्टिशन के भीतर हर पंक्ति के लिए क्रमिक संख्या देता है
डुप्लिकेट डेटा का पता लगाना - ROW_NUMBER()
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY
airport_code,
carrier_code,
registration_date
ORDER BY
airport_code,
carrier_code,
registration_date
) row_num
FROM flight_statistics
डुप्लिकेट डेटा का पता लगाना - ROW_NUMBER()
| registration_code | airport_code | carrier_code | registration_date | canceled | ... | statistician_name | statistician_surname | row_num |
|-------------------|--------------|--------------|-------------------|----------|-----|-------------------|----------------------|---------|
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 000000135 | MSP | AS | 2014-01-01 | 0 | ... | Anne | Johnson | 1 |
| 000000136 | MSP | DL | 2014-01-01 | 61 | ... | Bernard | Ross | 1 |
| 000000134 | MSP | DL | 2014-01-01 | 61 | ... | Anne | Johnson | 2 |
| 000000137 | MSP | EV | 2014-01-01 | 117 | ... | Michael | Andersen | 1 |
| 000000138 | MSP | F9 | 2014-01-01 | 0 | ... | Anne | Johnson | 1 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
डुप्लिकेट डेटा का पता लगाना - ROW_NUMBER()
| registration_code | airport_code | carrier_code | registration_date | canceled | ... | statistician_name | statistician_surname | row_num |
|-------------------|--------------|--------------|-------------------|----------|-----|-------------------|----------------------|---------|
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 000000135 | MSP | AS | 2014-01-01 | 0 | ... | Anne | Johnson | 1 |
| 000000136 | MSP | DL | 2014-01-01 | 61 | ... | Bernard | Ross | 1 *** |
| 000000134 | MSP | DL | 2014-01-01 | 61 | ... | Anne | Johnson | 2 |
| 000000137 | MSP | EV | 2014-01-01 | 117 | ... | Michael | Andersen | 1 |
| 000000138 | MSP | F9 | 2014-01-01 | 0 | ... | Anne | Johnson | 1 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
डुप्लिकेट डेटा का पता लगाना - ROW_NUMBER()
| registration_code | airport_code | carrier_code | registration_date | canceled | ... | statistician_name | statistician_surname | row_num |
|-------------------|--------------|--------------|-------------------|----------|-----|-------------------|----------------------|---------|
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 000000135 | MSP | AS | 2014-01-01 | 0 | ... | Anne | Johnson | 1 |
| 000000136 | MSP | DL | 2014-01-01 | 61 | ... | Bernard | Ross | 1 *** |
| 000000134 | MSP | DL | 2014-01-01 | 61 | ... | Anne | Johnson | 2 *** |
| 000000137 | MSP | EV | 2014-01-01 | 117 | ... | Michael | Andersen | 1 |
| 000000138 | MSP | F9 | 2014-01-01 | 0 | ... | Anne | Johnson | 1 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
- डुप्लिकेट पंक्तियाँ प्राप्त करें:
- row_num > 1
- डुप्लिकेट पंक्तियाँ हटाएँ:
- row_num = 1
सिर्फ डुप्लिकेट पंक्तियाँ निकालना
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY
airport_code,
carrier_code,
registration_date
ORDER BY
airport_code,
carrier_code,
registration_date
) row_num
FROM flight_statistics
)
SELECT * FROM cte
WHERE row_num > 1;
सिर्फ डुप्लिकेट पंक्तियाँ निकालना
|registration_code|airport_code|carrier_code|registration_date|canceled|...|statistician_name|statistician_surname|row_num|
|-----------------|------------|------------|-----------------|--------|---|-----------------|--------------------|-------|
|000000131 |JFK |EV |2014-02-28 |18 |...|Anne |Johnson |2 |
|000000134 |MSP |DL |2014-01-01 |61 |...|Anne |Johnson |2 |
|000000142 |MSP |OO |2014-01-01 |76 |...|Anne |Johnson |2 |
|000000143 |MSP |OO |2014-01-01 |76 |...|Anne |Johnson |3 |
डुप्लिकेट पंक्तियाँ हटाना
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY
airport_code,
carrier_code,
registration_date
ORDER BY
airport_code,
carrier_code,
registration_date
) row_num
FROM flight_statistics
)
SELECT * FROM cte
WHERE row_num = 1;
डुप्लिकेट पंक्तियाँ हटाना
|registration_code|airport_code|carrier_code|registration_date|canceled|...|statistician_name|statistician_surname|row_num|
|-----------------|------------|------------|-----------------|--------|...|-----------------|--------------------|-------|
|... |... |... |... |... |...|... |... |1 |
|000000126 |JFK |VX |2014-01-31 |12 |...|Bryan |Page |1 |
|000000133 |MSP |AA |2014-01-01 |15 |...|Peter |Johnson |1 |
|000000135 |MSP |AS |2014-01-01 |0 |...|Anne |Johnson |1 |
|000000136 |MSP |DL |2014-01-01 |61 |...|Bernard |Ross |1 |
|000000137 |MSP |EV |2014-01-01 |117 |...|Michael |Andersen |1 |
|000000138 |MSP |F9 |2014-01-01 |0 |...|Anne |Johnson |1 |
|000000139 |MSP |FL |2014-01-01 |8 |...|Anne |Johnson |1 |
|000000140 |MSP |MQ |2014-01-01 |20 |...|Anne |Johnson |1 |
|000000141 |MSP |OO |2014-01-01 |76 |...|Anne |Johnson |1 |
|000000132 |MSP |YV |2013-12-01 |0 |...|Michael |Andersen |1 |
|... |... |... |... |... |...|... |... |... |
अभ्यास करते हैं!
SQL Server डेटाबेस में डेटा क्लीनिंग
