Questo articolo spiega il processo di eseguire attività di eliminazione SQL per righe duplicate da una tabella SQL.
Introduzione
Dobbiamo seguire alcune best practice durante la progettazione di oggetti in SQL Server. Ad esempio, una tabella dovrebbe avere chiavi primarie, colonne di identità, indici cluster e non cluster, vincoli per garantire lintegrità dei dati e le prestazioni. Anche noi seguiamo le migliori pratiche e potremmo dover affrontare problemi come righe duplicate. Potremmo anche ottenere questi dati in tabelle intermedie durante limportazione dei dati e desideriamo rimuovere le righe duplicate prima di inserirle effettivamente nelle tabelle di produzione.
Supponi che la tua tabella SQL contenga righe duplicate e desideri rimuovere quelle righe duplicate . Molte volte affrontiamo questi problemi. È anche una buona pratica utilizzare le chiavi pertinenti, vincoli per eliminare la possibilità di righe duplicate tuttavia se abbiamo righe duplicate già nella tabella. Dobbiamo seguire metodi specifici per ripulire i dati duplicati. Questo articolo esplora i diversi metodi per rimuovere i dati duplicati dalla tabella SQL.
Creiamo una tabella Employee di esempio e inseriamo alcuni record in essa.
Nella tabella, abbiamo un pochi record duplicati e dobbiamo rimuoverli.
SQL elimina le righe duplicate utilizzando Group By e con clausola
In questo metodo, usiamo la clausola SQL GROUP BY per identificare il duplicato righe. La clausola Group By raggruppa i dati secondo le colonne definite e possiamo usare la funzione COUNT per controllare loccorrenza di una riga.
Ad esempio, esegui la seguente query e otteniamo quei record con occorrenza maggiore di 1 nella tabella Employee.
Nelloutput sopra, abbiamo due record duplicati con ID 1 e 3.
- Emp ID 1 ha due occorrenze nella tabella Employee
- Emp ID 3 ha tre occorrenze nella tabella Employee
Dobbiamo mantenere una singola riga e rimuovere le righe duplicate. Dobbiamo rimuovere solo le righe duplicate dalla tabella. Ad esempio, lEmpID 1 viene visualizzato due volte nella tabella. Vogliamo rimuovere solo una sua occorrenza.
Usiamo la funzione SQL MAX per calcolare lid massimo di ogni riga di dati.
Nello screenshot seguente, possiamo vedere che il sopra listruzione Select esclude lID max di ogni riga duplicata e otteniamo solo il valore ID minimo.
Per rimuovere questi dati, sostituire il prima selezionare con listruzione SQL delete come da query seguente.
Dopo aver eseguito listruzione delete, eseguire una selezione su una tabella Employee e otteniamo i seguenti record che non contengono righe duplicate.
SQL elimina le righe duplicate utilizzando CTE (Common Table Expressions)
È possibile utilizzare espressioni di tabella comuni comunemente note come CTE per rimuovere le righe duplicate in SQL Server. È disponibile a partire da SQL Server 2005.
Usiamo una funzione SQL ROW_NUMBER e aggiunge un numero di riga sequenziale univoco per la riga.
Nella seguente CTE, partiziona il dati utilizzando la clausola PARTITION BY per la colonna e e genera un numero di riga per ogni riga.
Nelloutput, se una riga ha il valore di colonna maggiore di 1, mostra che è un duplicato riga.
Possiamo rimuovere le righe duplicate utilizzando il seguente CTE.
Rimuove le righe con un valore maggiore di 1
La funzione RANK per SQL elimina le righe duplicate
Possiamo usare la funzione SQL RANK anche per rimuovere le righe duplicate. La funzione SQL RANK fornisce un ID riga univoco per ogni riga indipendentemente dalla riga duplicata.
Nella seguente query, utilizziamo una funzione RANK con la clausola PARTITION BY. La clausola PARTITION BY prepara un sottoinsieme di dati per le colonne specificate e fornisce il rango per quella partizione.
Nello screenshot, puoi notare che dobbiamo rimuovere la riga con Rank maggiore di uno. Rimuoviamo quelle righe utilizzando la seguente query.
Usa il pacchetto SSIS per SQL elimina le righe duplicate
Il servizio di integrazione di SQL Server fornisce vari operatori di trasformazione che aiutano sia gli amministratori che gli sviluppatori a ridurre lo sforzo manuale e ottimizzare le attività. Il pacchetto SSIS può rimuovere anche le righe duplicate da una tabella SQL.
Utilizza loperatore di ordinamento in un pacchetto SSIS per rimuovere le righe duplicate
Possiamo utilizzare un operatore di ordinamento per ordinare i valori in una tabella SQL. Potresti chiederti in che modo lordinamento dei dati può rimuovere le righe duplicate?
Creiamo il pacchetto SSIS per mostrare questa attività.
- In SQL Server Data Tools, crea un nuovo pacchetto di integrazione.Nel nuovo pacchetto, aggiungi una connessione di origine OLE DB
-
Apri leditor di origine OLE DB e configura la connessione di origine e seleziona la tabella di destinazione
-
Fai clic su Anteprima dati per vedere che abbiamo ancora dati duplicati nella tabella di origine
-
Aggiungi un operatore di ordinamento dalla casella degli strumenti SSIS per loperazione di eliminazione SQL e uniscilo ai dati di origine
Per la configurazione delloperatore di ordinamento, fare doppio clic su di esso e selezionare le colonne che contengono valori duplicati. Nel nostro caso, il valore duplicato è in,, colonne.
Possiamo anche utilizzare i tipi di ordinamento crescente o decrescente per le colonne. Il metodo di ordinamento predefinito è crescente. Nellordinamento possiamo scegliere lordinamento delle colonne. Lordinamento 1 mostra la colonna che verrà ordinata per prima.
In basso a sinistra, nota una casella di controllo Rimuovi righe con valori di ordinamento duplicati.
Sarà compito di rimuovere le righe duplicate dai dati di origine. Mettiamo un segno di spunta in questa casella di controllo e facciamo clic su OK. Esegue lattività di eliminazione SQL nel pacchetto SSIS.
Dopo aver fatto clic su OK, si torna alla scheda del flusso di dati, e possiamo vedere il seguente pacchetto SSIS.
Possiamo aggiungere destinazioni SQL Server per memorizzare i dati dopo aver rimosso le righe duplicate. Vogliamo solo verificare che loperatore di ordinamento stia eseguendo lattività per noi o meno.
Aggiungi una trasformazione multicast SQL dalla casella degli strumenti SSIS come mostrato di seguito.
Per visualizzare i dati distinti, fare clic con il pulsante destro del mouse sul connettore tra Sort e Multicast. Fai clic su Abilita visualizzatore dati.
Il pacchetto SSIS complessivo ha il seguente aspetto.
Esegui il pacchetto per eseguire loperazione di eliminazione SQL. Apre il visualizzatore dei dati di output per lordinamento nellattività Flusso di dati. In questo visualizzatore di dati, puoi vedere dati distinti dopo aver rimosso i valori duplicati.
Chiudi questo e il pacchetto SSIS viene visualizzato correttamente eseguito.
Conclusione
In questo articolo, abbiamo esplorato il processo di eliminazione di righe duplicate tramite SQL vari modi come T-SQL, CTE e pacchetto SSIS. Puoi usare il metodo in cui ti senti a tuo agio. Tuttavia, suggerirei di non implementare queste procedure e impacchettare direttamente i dati di produzione. Dovresti eseguire il test in un ambiente inferiore.
- Autore
- Post recenti
È lautore di una delle più grandi raccolte online gratuite di articoli su un singolo argomento, con la sua serie di 50 parti sui gruppi di disponibilità Always On di SQL Server. Sulla base del suo contributo alla comunità di SQL Server, è stato riconosciuto con vari premi tra cui il prestigioso “Miglior autore dellanno” ininterrottamente nel 2020 e 2021 a SQLShack.
Raj è sempre interessato a nuove sfide, quindi se hai bisogno di consulenza aiuto su qualsiasi argomento trattato nei suoi scritti, può essere raggiunto a [email protected]
Visualizza tutti i post di Rajendra Gupta
- Esecuzione di aggiornamenti delle versioni minori e maggiori per AWS RDS SQL Server – 29 gennaio 2021
- Distribuzione di istanze PostgreSQL di AWS RDS – 27 gennaio 2021
- Migrazione dei database SQL locali ad AWS RDS SQL Server utilizzando AWS DMS – 25 gennaio , 2021