Obwohl das Kompilieren von SQL-Abfragen für Data Scientists nicht das Interessanteste ist, ist ein gutes Verständnis von SQL für alle äußerst wichtig, die bei Aktivitäten mit Datenverarbeitung erfolgreich sein möchten. Der Punkt hier ist, dass SQL nicht nur ist SELECT, FROMund WHERE. Je mehr SQL-Konstruktionen ein Spezialist kennt, desto einfacher wird es für ihn, Anforderungen zu erstellen, um aus den Datenbanken alles zu erhalten, was er möglicherweise benötigt. Der Autor des Artikels, dessen Übersetzung wir heute veröffentlichen, sagt, dass er zwei Probleme lösen soll:
- Erkundung von Mechanismen, die über grundlegende SQL-Kenntnisse hinausgehen.
- Berücksichtigung mehrerer praktischer Aufgaben für die Arbeit mit SQL.
Dieser Artikel behandelt 5 SQL-Fragen von Leetcode. Sie stellen praktische Aufgaben dar, die häufig in Interviews auftreten.Frage Nr. 1: Zweiter Platz in Bezug auf das Gehalt
Schreiben Sie eine SQL-Abfrage, um aus der Tabelle mit den Gehaltsinformationen der Mitarbeiter ( Employee) einen Eintrag zu erhalten, der das zweithöchste Gehalt enthält.Beispielsweise sollte eine solche Abfrage, die für die folgende Tabelle ausgeführt wird, zurückgegeben werden 200. Wenn die Tabelle keinen niedrigeren Wert als das höchste Gehalt hat, sollte die Anforderung zurückgegeben werden null.+
| Id | Salary |
+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+
▍ Entscheidung A: Verwendung IFNULLundOFFSET
Hier sind die wichtigsten Mechanismen, die bei dieser Lösung des Problems verwendet werden:IFNULL(expression, alt): Diese Funktion gibt ihr Argument zurück, expressionwenn es nicht gleich ist null. Andernfalls wird das Argument zurückgegeben alt. Wir werden diese Funktion verwenden, um zurückzukehren, nullwenn die Tabelle nicht den gewünschten Wert enthält.OFFSET: Dieser Operator wird mit einem Ausdruck verwendet ORDER BY, um die ersten nZeilen zu verwerfen . Dies ist für uns nützlich, weil wir an der zweiten Zeile des Ergebnisses interessiert sind (dh am zweitgrößten Gehalt, dessen Daten in der Tabelle enthalten sind).
Hier ist eine fertige Anfrage:SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
), null) as SecondHighestSalary
FROM Employee
LIMIT 1
▍ Lösung B: verwenden MAX
Die folgende Abfrage verwendet die Funktion MAX. Hier wird der höchste Gehaltswert ausgewählt, der nicht dem in der Tabelle erhaltenen Maximalgehalt entspricht. Als Ergebnis bekommen wir, was wir brauchen - das zweitgrößte Gehalt.SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)
Frage 2: Doppelte E-Mail-Adressen
Schreiben Sie eine SQL-Abfrage, die Personalle doppelten E-Mail-Adressen in der Tabelle erkennt .+
| Id | Email |
+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+
▍ Entscheidung A: COUNTin der Unterabfrage
Zunächst erstellen wir eine Unterabfrage, in der die Häufigkeit des Auftretens jeder Adresse in der Tabelle bestimmt wird. Anschließend wird das von der Unterabfrage zurückgegebene Ergebnis mithilfe der Anweisung gefiltert WHERE count > 1. Die Abfrage gibt mehrmals Informationen zu Adressen zurück, die in der Quelltabelle gefunden wurden.SELECT Email
FROM (
SELECT Email, count(Email) AS count
FROM Person
GROUP BY Email
) as email_count
WHERE count > 1
▍ Lösung B: Ausdruck HAVING
HAVING: Dies ist ein Ausdruck, mit dem Sie eine Anweisung WHEREmit einem Ausdruck verwenden können GROUP BY.
SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1
Frage Nr. 3: steigende Temperatur
Schreiben Sie eine SQL-Abfrage, die in der Tabelle Weatheralle Daten (Datumsbezeichner) findet, an denen die Temperatur höher wäre als die Temperatur an den Daten davor. Das heißt, wir sind an Daten interessiert, an denen die „heutige“ Temperatur höher ist als die „gestrige“.+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+
▍Lösung: DATEDIFF
DATEDIFF: Diese Funktion berechnet die Differenz zwischen zwei Daten. Es wird verwendet, um einen genauen Vergleich der Temperaturen von heute und gestern zu ermöglichen.
Wenn wir die folgende Abfrage in gewöhnlicher Sprache formulieren, stellt sich heraus, dass sie die folgende Idee ausdrückt: Wir müssen Bezeichner so auswählen, dass die Temperatur, die den Daten entspricht, die sie darstellen, höher ist als die Temperatur für die Daten von gestern in Bezug auf sie.SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1
Frage Nummer 4: das höchste Gehalt in der Einheit
In der Tabelle Employeewerden Informationen zu Mitarbeitern des Unternehmens gespeichert. Jeder Eintrag in dieser Tabelle enthält Informationen über die Kennung ( Id) des Mitarbeiters, seinen Namen ( Name), sein Gehalt ( Salary) und die Abteilung des Unternehmens, in dem er arbeitet ( Department).+
| Id | Name | Salary | DepartmentId |
+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+
Die Tabelle Departmententhält Informationen zu den Unternehmensbereichen.+
| Id | Name |
+
| 1 | IT |
| 2 | Sales |
+
Schreiben Sie eine SQL-Abfrage, die in jeder Abteilung der Mitarbeiter mit dem maximalen Gehalt gefunden wird. Für die obigen Tabellen sollte eine ähnliche Abfrage beispielsweise die in der folgenden Tabelle dargestellten Ergebnisse zurückgeben (die Reihenfolge der Zeilen in der Tabelle spielt keine Rolle):+
| Department | Employee | Salary |
+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+
▍Lösung: Team IN
Mit dem Befehl INkönnen Sie in der Anweisung WHEREBedingungen festlegen , die der Verwendung mehrerer Befehle entsprechen OR. Zum Beispiel sind die beiden folgenden Konstrukte identisch:WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).
Hier möchten wir eine Tabelle erhalten, die den Namen der Abteilung ( Department), den Namen des Mitarbeiters ( Employee) und sein Gehalt ( Salary) enthält. Zu diesem Zweck erstellen wir eine Tabelle, die Informationen über die Einheitenkennung ( DepartmentID) und das maximale Gehalt für diese Einheit enthält. Dann kombinieren wir die beiden in einer Konditionstabelle, nach der der Eintrag in der resultierenden Tabelle nur dann fällt, wenn DepartmentIDund Salaryzuvor in der Tabelle gebildet hat.SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary)
IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
Frage Nr. 5: Transplantation von Studenten
Mary ist eine Highschool-Lehrerin. Sie hat eine Tabelle seat, in der die Namen der Schüler und Informationen über ihre Plätze im Klassenzimmer gespeichert sind. Der Wert idin dieser Tabelle steigt ständig an. Mary will benachbarte Studenten tauschen.Hier ist eine Tabelle der Erstplatzierung der Schüler:+
| id | student |
+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+
Folgendes sollte nach der Transplantation benachbarter Schüler passieren:+
| id | student |
+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+
Schreiben Sie eine Anfrage, mit der der Lehrer das oben genannte Problem lösen kann.Bitte beachten Sie, dass Sie bei einer ungeraden Anzahl von Schülern den letzten Schüler nirgendwo hin transferieren müssen.▍Lösung: Verwenden eines Operators WHEN
Ein SQL-Konstrukt CASE WHEN THENkann als Operator ifbei der Programmierung betrachtet werden.In unserem Fall wird der erste Operator WHENverwendet, um zu überprüfen, ob der ungerade Bezeichner der letzten Zeile in der Tabelle zugewiesen ist. In diesem Fall kann sich die Leitung nicht ändern. Der zweite Operator WHENist dafür verantwortlich, 1 zu jeder ungeraden Kennung zu addieren (z. B. 1, 3, 5 wird zu 2, 4, 6) und 1 von jeder geraden Kennung zu subtrahieren (2, 4, 6 wird zu 1, 3, 5).SELECT
CASE
WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
WHEN id%2 = 1 THEN id + 1
ELSE id - 1
END AS id, student
FROM seat
ORDER BY id
Zusammenfassung
Wir haben verschiedene SQL-Aufgaben untersucht und dabei einige erweiterte Tools erörtert, mit denen SQL-Abfragen kompiliert werden können. Wir hoffen, dass das, was Sie heute gelernt haben, bei Interviews in SQL nützlich sein und sich in der täglichen Arbeit als nützlich erweisen wird.PS Auf unserem Marktplatz gibt es ein Docker-Image mit SQL Server Express, das mit einem Klick installiert wird. Sie können den Betrieb von Containern auf VPS überprüfen. Alle Neukunden erhalten 3 Tage kostenlos zum Testen.Liebe Leser! Was können Sie denjenigen raten, die die Kunst des Erstellens von SQL-Abfragen beherrschen möchten?