PricingLoginStart free
SQL-työhaastatteluun valmistautuminen · Oppitunti

Rivien turvallinen deduplikointi

Poista täysin ja lähes samanlaiset rivit säilyttäen yksi ensisijainen tietue

Oppitunti 3/413 vaihetta

Rivien turvallinen deduplikointi on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 3/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu SQL-työhaastatteluun valmistautuminen-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.

Duplikaattien poisto-ongelma

"Taulukossa on päällekkäisiä rivejä. Poistakaa ne, mutta säilyttäkää kustakin yksi kopio." Lähes jokaisessa data engineering -haastattelussa tulee vastaan jokin tämän ongelman muoto. Haasteena on tehdä tämä turvallisesti: säilyttää täsmälleen yksi kanoninen rivi ja välttää sellaisten erillisten tietueiden poistaminen, jotka vain näyttävät samanlaisilta.

Käsittelemme duplikaattien tunnistamista, säilytettävän kopion valitsemista, duplikaattien poistamista SELECT-kyselyssä sekä duplikaattien fyysistä poistamista taulukosta.

Määrittele duplikaatti ensin

Ensimmäinen haastattelijalle esitettävä kysymys on: "Mikä tekee kahdesta rivistä duplikaatteja?" Vaihtoehtoja ovat esimerkiksi:

  • Täsmälleen samanlaiset duplikaatit: jokainen sarake on identtinen.
  • Avaimen perusteella päällekkäiset rivit: sama liiketoiminta-avain, kuten sama email, mutta muut sarakkeet voivat olla erilaisia.

Tekniikka on erilainen kummassakin tapauksessa. Älkää olettako, vaan varmistakaa duplikaatin määritelmä. Tämä on tärkein yksittäinen vaihe, ja haastattelijat odottavat teidän kysyvän siitä.

Duplikaattien tunnistaminen

Kun haluatte löytää päällekkäiset avaimet, ryhmitelkää duplikaatin määrittävien sarakkeiden perusteella ja säilyttäkää ryhmät, joiden lukumäärä on suurempi kuin yksi. Näin näette, mihin avaimiin ongelma vaikuttaa ja kuinka monta kopiota kustakin on olemassa, ennen kuin muutatte mitään.

Tunnistuskyselyn suorittaminen ensin on mainitsemisen arvoinen hyvä käytäntö: varmistatte ongelman laajuuden ennen poistamista.

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Täsmälleen samanlaiset duplikaatit: DISTINCT

Jos duplikaatit ovat todella identtisiä jokaisen sarakkeen osalta, vain lukuun tarkoitettu duplikaatit poistava näkymä on yhtä yksinkertainen kuin SELECT DISTINCT *. Myös UNION (ilman ALL-määrettä) poistaa päällekkäiset rivit.

DISTINCT auttaa kuitenkin vain silloin, kun haluatte poistaa duplikaatit koko rivistä ettekä tarvitse valita, mikä kopio säilytetään. Jos duplikaatit määritellään avaimen perusteella ja sarakkeet eroavat toisistaan, tarvitsette ranking-funktiota.

-- Read-only dedup of exact-duplicate rows
SELECT DISTINCT customer_id, name, signup_date
FROM customers;

Avaimen perusteella päällekkäiset rivit: ROW_NUMBER

Kun riveillä on sama avain mutta muut sarakkeet eroavat, osioikaa rivit avaimen perusteella ja numeroikaa kukin kopio. rn = 1 merkitsee säilytettävää riviä ja rn > 1 poistettavia ylimääräisiä rivejä.

Ikkunan sisäinen ORDER BY ratkaisee, mikä kopio on kanoninen. Valitkaa järjestys harkiten; voitte esimerkiksi säilyttää viimeksi päivitetyn rivin.

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY updated_at DESC
  ) AS rn
FROM users;

Kanonisen kopion valitseminen

Paketoikaa numerointi CTE:hen ja säilyttäkää vain rn = 1. Näin palautuu yksi rivi avainta kohti, nimenomaan se rivi, jonka ORDER BY sijoitti ensimmäiseksi.

Tämä SELECT-muoto ei muuta tietoja: se sopii erinomaisesti siistin näkymän muodostamiseen tai tietojen siirtämiseen INSERT ... SELECT -lauseella duplikaatit poistavaan kohdetaulukkoon ilman lähdetaulukon muuttamista.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC
    ) AS rn
  FROM users
)
SELECT user_id, email, name, updated_at
FROM ranked
WHERE rn = 1;

Järjestyksen valinnalla on merkitystä

Osion sisäinen ORDER BY on liiketoimintapäätös, ei muodollisuus:

  • ORDER BY updated_at DESC säilyttää tuoreimman tietueen.
  • ORDER BY created_at ASC säilyttää alkuperäisen tietueen.
  • ORDER BY id ASC säilyttää pienimmän sijaisavaimen, mikä on hyödyllinen vakaa mielivaltainen valinta.

Lisätkää yksilöllinen tasatilanteen ratkaiseva avain, jotta valittu rivi määräytyy yksiselitteisesti myös silloin, kun ensisijaisessa järjestyssarakkeessa on sama arvo.

ROW_NUMBER() OVER (
  PARTITION BY email
  ORDER BY updated_at DESC, id ASC
) AS rn

Duplikaattien fyysinen poistaminen

Jos haluatte todella poistaa duplikaatit taulukosta, tunnistakaa ylimääräiset rivit (rn > 1) ja poistakaa ne. PostgreSQL:ssä ja SQL Serverissä voitte poistaa rivejä CTE:n avulla; MySQL:ssä käytetään usein itseensä liittyvää liitosta tai alikyselyä.

Suorittakaa aina ensin vastaava SELECT, jotta voitte esikatsella täsmälleen, mitkä rivit poistetaan. Sokkoina tehty poisto on syy siihen, miksi hakijat epäonnistuvat tässä kysymyksessä.

WITH ranked AS (
  SELECT ctid,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC, id ASC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

Itseensä liittyvän poiston malli

Perinteinen siirrettävä lähestymistapa säilyttää pienimmän id-arvon sisältävän rivin kutakin päällekkäistä avainta kohti ja poistaa loput itseensä liittyvän liitoksen avulla. Se ei tarvitse ikkunafunktioita, mikä on tärkeää vanhemmissa tietokantamoottoreissa.

Liitosehto yhdistää jokaisen rivin toiseen saman avaimen riviin, jolla on pienempi id-arvo. Jokainen rivi, jolla on tällainen pienemmän id-arvon pari, on poistettava duplikaatti.

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

Turvallisuustarkistuslista

Suojatkaa itsenne ennen poistamista:

  • Suorittakaa poisto transaktion sisällä, jotta voitte tehdä ROLLBACK-palautuksen, jos lukumäärä näyttää väärältä.
  • Suorittakaa ensin poistettavien rivien SELECT COUNT(*) ja tarkistakaa tuloksen järkevyys.
  • Harkitkaa varmuuskopiotaulukkoa: CREATE TABLE users_bak AS SELECT * FROM users.
  • Varmistakaa, että PARTITION BY -sarakkeet todella määrittävät duplikaatin. Muuten saatatte poistaa erillisiä tietueita.
BEGIN;
-- run the DELETE, inspect row count
-- COMMIT; if correct, otherwise ROLLBACK;

Lähes päällekkäiset rivit ja normalisointi

Joskus rivit eivät ole täsmälleen samanlaisia, mutta ovat loogisesti samoja: 'Ann@X.com' ja 'ann@x.com' tai rivien lopussa olevat välilyönnit. Osioikaa rivit normalisoidun lausekkeen, ei raakasarakkeen, perusteella.

Normalisoinnin mainitseminen osoittaa kokemusta: tosielämän duplikaatit piiloutuvat usein kirjainkoon, välilyöntien tai muotoilun eroihin, joita suoraviivainen avainten vertailu ei havaitse.

ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(email))
  ORDER BY updated_at DESC, id ASC
) AS rn

Pikatarkistus

Valitkaa turvallinen lähestymistapa duplikaattien poistamiseen.

Vastaa interaktiivisessa oppitunnissa

Kertaus: Duplikaattien turvallinen poistaminen

Poistakaa duplikaatit järjestelmällisesti:

  • Määritelkää ensin, mikä on duplikaatti, ja tunnistakaa ne sitten käyttämällä GROUP BY / HAVING COUNT(*) > 1.
  • Täsmälleen samanlaiset duplikaatit → DISTINCT. Avaimen perusteella päällekkäiset rivit → avaimen perusteella osioitu ROW_NUMBER, josta säilytetään rn = 1.
  • Ikkunan ORDER BY valitsee kanonisen kopion. Lisätkää yksilöllinen tasatilanteen ratkaiseva avain.
  • Poistakaa rn > 1 -rivit transaktion sisällä sen jälkeen, kun olette tarkistanut lukumäärän esikatselulla.
  • Normalisoikaa avaimet, jotta löydätte myös lähes päällekkäiset rivit.
Aloita maksutta

Opi SQL tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit
30
Oppitunnit
120
Aloita opiskelu maksuttaNäytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Rivien turvallinen deduplikointi” ilmainen?

Kyllä – oppitunnin ”Rivien turvallinen deduplikointi” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko SQL-työhaastatteluun valmistautuminen-kurssin, päivitä CoddyKit PROhon. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Rivien turvallinen deduplikointi”?

Poista täysin ja lähes samanlaiset rivit säilyttäen yksi ensisijainen tietue Harjoittelet SQL-työhaastatteluun valmistautuminen-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni SQL-työhaastatteluun valmistautuminen-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin SQL-työhaastatteluun valmistautuminen-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 3/4.

Kuinka kauan ”Rivien turvallinen deduplikointi”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä SQL-työhaastatteluun valmistautuminen-oppitunnilla?

Kyllä. Jokainen SQL-työhaastatteluun valmistautuminen-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

  1. Ryhmän Top-N-rivit ROW_NUMBER-funktiolla
  2. Tasatilanteiden käsittely Top-N-tuloksissa
  3. Rivien turvallinen deduplikointi
  4. Avaimen viimeisimmän rivin säilyttäminen
← Takaisin: SQL-työhaastatteluun valmistautuminen