Saman taulun rivien vertaaminen
Itseviittausliitosten mallit parien, kaksoiskappaleiden ja vierekkäisten tietueiden löytämiseen
Saman taulun rivien vertaaminen 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.
Self joinit rivien väliseen vertailuun
Hierarkioiden lisäksi self joinin toinen merkittävä käyttötapa on saman taulun rivien vertaileminen keskenään. Vanhempi–lapsisuhteen sijaan yhdistätte mielivaltaisia rivejä löytääksenne duplikaatteja, lähes vastaavia tai vierekkäisiä tietueita.
Malli on sama: antakaa taululle kaksi aliasta ja kirjoittakaa ON-ehto, joka ilmaisee yhdistettävien rivien välisen suhteen.
Samaan ryhmään kuuluvien parien etsiminen
Klassinen kysymys: etsikää kaikki työntekijäparit, jotka työskentelevät samassa osastossa. Liittäkää taulu itseensä yhtä suuren department-arvon perusteella, mutta pitäkää kaksi riviä erillisinä.
Yksinkertainen liitos yhdistäisi myös jokaisen työntekijän itseensä ja tuottaisi jokaisen parin kahdesti. Korjaamme tämän seuraavaksi.
SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b ON a.department = b.department;Itsensä kanssa muodostuvien parien ja peilausduplikaattien poistaminen
Samaan ryhmään kuuluvien parien muodostamisessa on kaksi ongelmaa: rivi täsmää itseensä (Alice ja Alice), ja jokainen pari esiintyy kahdesti (Alice–Bob ja Bob–Alice).
Korjatkaa molemmat yhdellä epäyhtälöllä: a.id < b.id. Tämä takaa, että rivit ovat eri rivejä ja säilyttää kustakin parista vain yhden järjestyksen.
SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b
ON a.department = b.department
AND a.id < b.id;Miksi a.id < b.id eikä a.id <> b.id
a.id <> b.id poistaa itsensä kanssa muodostuvat parit, mutta palauttaa edelleen molemmat järjestykset, jolloin tuloksia tulee kaksinkertainen määrä. a.id < b.id poistaa itsensä kanssa muodostuvat parit ja poistaa peilausduplikaatit yhdellä ehdolla.
Haastattelijat kiinnittävät erityisesti huomiota valintaan < ja <> -operaattorien välillä; se osoittaa, että ymmärrätte self joinien kombinatoriikan.
-- <> keeps Alice-Bob AND Bob-Alice (duplicated)
-- < keeps only Alice-Bob (correct unique pairs)Duplikaattirivien etsiminen
Kun haluatte löytää toisensa avainsarakkeissa toistavat tietueet, tehkää self join näiden sarakkeiden perusteella ja vaatikaa eri perusavaimia.
Tässä tuomme esiin asiakkaat, joilla on sama sähköpostiosoite. a.id < b.id pitää kunkin duplikaattiparin mukana kerran. Usein GROUP BY ... HAVING COUNT(*) > 1 on selkeämpi, mutta self join näyttää todelliset ongelmalliset parit rinnakkain.
SELECT a.id, b.id, a.email
FROM customers a
JOIN customers b
ON a.email = b.email
AND a.id < b.id;Peräkkäisten tietueiden vertaileminen
Yleinen analyytikon tehtävä on verrata jokaista riviä järjestyksessä seuraavaan riviin, esimerkiksi kunkin päivän myyntiä edellisen päivän myyntiin. Self joinilla voidaan yhdistää peräkkäiset rivit.
Tässä yhdistämme kunkin päivän täsmälleen päivää aiempaan riviin muutoksen laskemista varten. Tämä toimii, kun jaksossa ei ole aukkoja.
SELECT t.day, t.amount,
t.amount - y.amount AS change_vs_prev
FROM daily_sales t
JOIN daily_sales y
ON y.day = t.day - INTERVAL '1 day';Aukko-ongelma peräkkäisten rivien self joinissa
Edellinen kysely ei toimi, jos jokin päivä puuttuu: täsmälleen yhtä päivää aiempaa riviä ei ole, joten kyseinen rivi jää pois (inner joinissa) tai joudutte käsittelemään NULL-arvoja.
Siksi haastattelijat ohjaavat usein käyttämään ikkunafunktioita, kuten LAG-funktiota, kun halutaan ”verrata edelliseen riviin”. Ne käyttävät järjestyssijaa arvon vastaavuuden sijaan ja käsittelevät aukot luontevasti.
-- LAG handles gaps; the self join assumed contiguous days
SELECT day, amount,
amount - LAG(amount) OVER (ORDER BY day) AS change_vs_prev
FROM daily_sales;Self join vai ikkunafunktio
Tiedosta kompromissi:
- self join vertaa rivejä arvojen perusteella (sama osasto, aiempi päivämäärä). Se on joustava, mutta voi kasvattaa rivimäärää moninkertaisesti ja käsitellä aukkoja väärin.
- ikkunafunktio vertaa rivejä järjestettyyn osioon kuuluvan järjestyssijan perusteella. Se sopii selkeämmin edellisen tai seuraavan rivin käsittelyyn.
Kun haluat ”verrata viereiseen riviin”, suosi LAG- tai LEAD-funktiota. Kun haluat ”etsiä kaikki ehdon täyttävät parit”, self join on luonteva työkalu.
Omaa ryhmäänsä paremmin suoriutuvien rivien etsiminen
Toinen yleinen käyttötapaus on niiden työntekijöiden etsiminen, jotka ansaitsevat enemmän kuin vähintään yksi kollega omalla osastollaan. Self join ilmaisee tämän suoraan.
Liitämme jokaisen työntekijän saman osaston muihin työntekijöihin, jotka ansaitsevat vähemmän, ja säilytämme tuloksesta esiintyvät yksilölliset työntekijät. Tämä vastaa lähes suoraan englanninkielistä lausetta.
SELECT DISTINCT a.name, a.department, a.salary
FROM employees a
JOIN employees b
ON a.department = b.department
AND a.salary > b.salary;Varo fan-out-ilmiötä
Liitokset epäyksilöllisen sarakkeen perusteella moninkertaistavat rivit. Kun 100 henkilön osastolla muodostetaan pareja, ehdokkaita syntyy ennen suodatusta noin 100 x 100.
Käytä aina duplikaatit poistavaa ehtoa (a.id < b.id) ja lisää DISTINCT tai ryhmittely, kun tarvitset vain osallistuvat rivit etkä jokaista paria. Mainitse työhaastattelussa, että ymmärrät tämän rivien moninkertaistumisen vaikutuksen.
Vertailutyökalun valitseminen
Ohje taulun sisäiseen vertailuun:
- Kaikki täsmäävät parit (duplikaatit, saman ryhmän yhdistelmät): self join ehdolla
a.id < b.id. - Järjestyksen edellinen tai seuraava rivi: ikkunafunktio (
LAG/LEAD). - Jokaisen rivin vertaaminen ryhmäkoosteeseen: korreloitu alikysely tai ikkunakooste.
Pikatarkistus
Haluat kaikki yksilölliset tuotepareja, joilla on sama luokka, siten ettei tuotetta yhdistetä itseensä eikä sama pari esiinny eri järjestyksissä.
Kertaus: rivien vertaaminen yhdessä taulussa
Tärkeimmät opit:
- Liitä taulu itseensä muodostaaksesi sen omista riveistä pareja duplikaattien etsimistä ja saman ryhmän yhdistelmiä varten.
- Poista itseensä viittaavat parit ja peilikuvat yhdellä ehdolla käyttämällä ehtoa
a.id < b.id. - Self join -liitokseen perustuvat vierekkäisyysvertailut rikkoutuvat aukkojen kohdalla; suosi
LAG- taiLEAD-funktiota edellisen tai seuraavan rivin käsittelyyn. - Huomioi aina fan-out-ilmiö, kun liität epäyksilöllisten sarakkeiden perusteella.
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
Usein kysytyt kysymykset
Onko oppitunti ”Saman taulun rivien vertaaminen” ilmainen?
Kyllä – oppitunnin ”Saman taulun rivien vertaaminen” 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 ”Saman taulun rivien vertaaminen”?
Itseviittausliitosten mallit parien, kaksoiskappaleiden ja vierekkäisten tietueiden löytämiseen 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 ”Saman taulun rivien vertaaminen”-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
- CROSS JOIN ja karteesiset tulot
- SELF JOIN hierarkioille
- Saman taulun rivien vertaaminen
- Oikean liitostyypin valitseminen