SQL-Tipps und -Tricks

 (github.com/ben-n93)
7 Punkte von GN⁺ 2024-09-26 | 2 Kommentare | Auf WhatsApp teilen

Inhaltsverzeichnis

Formatierung/Lesbarkeit

  • Führende Kommas beim Trennen von Feldern verwenden
  • Dummy-Wert in der WHERE-Klausel verwenden
  • Saubere Code-Einrückung
  • Bei komplexen Abfragen CTEs in Betracht ziehen

Nützliche Funktionen

  • Den ::-Operator zur Umwandlung von Datentypen verwenden
  • Anti-Joins nutzen
  • QUALIFY zum Filtern von Window-Funktionen verwenden
  • GROUP BY kann mit Spaltenpositionen verwendet werden

Fallstricke, die man vermeiden sollte

  • Vorsicht bei NOT IN zusammen mit NULL-Werten
  • Namen berechneter Felder ändern, um Mehrdeutigkeiten zu vermeiden
  • Angeben, zu welcher Tabelle jede Spalte gehört
  • Die Ausführungsreihenfolge verstehen
  • Code kommentieren
  • Die gesamte Dokumentation lesen

Formatierung/Lesbarkeit

Führende Kommas beim Trennen von Feldern verwenden

  • In der SELECT-Klausel können führende Kommas verwendet werden, um neue Spalten klar zu trennen
  • Führende Kommas liefern einen visuellen Hinweis, mit dem sich leicht erkennen lässt, ob ein Komma fehlt
SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

Dummy-Wert in der WHERE-Klausel verwenden

  • Ein Dummy-Wert in der WHERE-Klausel ermöglicht es, Bedingungen dynamisch hinzuzufügen oder zu entfernen
SELECT *
FROM employees
WHERE 1=1 -- 더미 값
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

Saubere Code-Einrückung

  • Durch Einrückung wird die Lesbarkeit verbessert und es wird für Kolleg:innen und das eigene zukünftige Ich leichter, den Code zu verstehen
-- Schlechtes Beispiel:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

-- Gutes Beispiel:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

Bei komplexen Abfragen CTEs in Betracht ziehen

  • Statt verschachtelter Inline-Views können Common Table Expressions (CTEs) verwendet werden, um Lesbarkeit und Struktur des Codes zu verbessern
-- Verwendung von Inline-Views:
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM
  (SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id) AS cs
INNER JOIN
  (SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;

-- Verwendung von CTEs:
WITH cinema_sales AS (
  SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id
),
vhs_sales AS (
  SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id
)
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;

Nützliche Funktionen

Den ::-Operator zur Umwandlung von Datentypen verwenden

  • In einigen RDBMS kann der ::-Operator verwendet werden, um Werte in einen anderen Datentyp umzuwandeln
SELECT CAST('5' AS INTEGER); -- Verwendung der CAST-Funktion
SELECT '5'::INTEGER; -- Verwendung der ::-Syntax

Anti-Joins nutzen

  • Anti-Joins sind sehr nützlich, wenn Zeilen zurückgegeben werden sollen, die nur in einer Tabelle existieren
  • Man kann auch Unterabfragen verwenden, aber Anti-Joins sind in der Regel schneller
-- Anti-Join:
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

-- Unterabfrage:
SELECT
  *
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);

-- Korrelierte Unterabfrage:
SELECT
  *
FROM video_content
WHERE NOT EXISTS (
  SELECT 1
  FROM archive a
  WHERE a.series_id = vc.series_id
);

-- EXCEPT:
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;

QUALIFY zum Filtern von Window-Funktionen verwenden

  • Mit QUALIFY lassen sich die Ergebnisse von Window-Funktionen filtern
  • Das ist nützlich, um die Anzahl der Codezeilen zu reduzieren
-- Verwendung von QUALIFY:
SELECT
  product,
  market,
  SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;

-- Ohne QUALIFY:
SELECT
  product,
  market,
  market_revenue
FROM (
  SELECT
    product,
    market,
    SUM(revenue) AS market_revenue,
    DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
  FROM sales
  GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;

GROUP BY kann mit Spaltenpositionen verwendet werden

  • Statt Spaltennamen können Spaltenpositionen in GROUP BY oder ORDER BY verwendet werden
  • Für temporäre Abfragen ist das nützlich, im Produktionscode sollten aber immer Spaltennamen referenziert werden
SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no ist die erste Spalte in der SELECT-Klausel
ORDER BY 2 DESC;

Fallstricke, die man vermeiden sollte

Vorsicht bei NOT IN zusammen mit NULL-Werten

  • NOT IN funktioniert nicht, wenn NULL-Werte enthalten sind
  • Stattdessen sollte NOT EXISTS verwendet werden
INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- Funktioniert wegen des NULL-Werts nicht
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- Lösung
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

Namen berechneter Felder ändern, um Mehrdeutigkeiten zu vermeiden

  • Wenn ein berechnetes Feld auf denselben Namen wie eine bestehende Spalte umbenannt wird, kann es zu unerwartetem Verhalten kommen
INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- Die Window-Funktion stuft das Produkt 'Robot' auf Rang 1 ein
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

Angeben, zu welcher Tabelle jede Spalte gehört

  • In komplexen Abfragen erleichtert es die Fehlersuche, wenn angegeben wird, zu welcher Tabelle jede Spalte gehört
SELECT
  vc.video_id,
  vc.series_name,
  metadata.season,
  metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;

Die Ausführungsreihenfolge verstehen

  • Der wichtigste Ratschlag für alle, die SQL lernen, ist, die Ausführungsreihenfolge zu verstehen
  • Wer die Ausführungsreihenfolge versteht, schreibt Abfragen auf eine völlig andere Weise

Code kommentieren

  • Beim Schreiben von Code sollten Kommentare hinzugefügt werden, die das Warum erklären
  • Kolleg:innen und das eigene zukünftige Ich werden dankbar sein
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- Das neue CMS kann das Archiv-Videoformat nicht verarbeiten
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

Die gesamte Dokumentation lesen

  • Das vollständige Lesen der Dokumentation hilft dabei, unerwartete Probleme zu vermeiden
  • Es dauert nur wenige Minuten und kann helfen, unerwartete Probleme zu lösen
-- Mit mehr Lektüre der Dokumentation hätte sich das NULL-Problem lösen lassen
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- Die Funktion GREATEST_IGNORE_NULLS ist verfügbar
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

Zusammenfassung von GN⁺

  • Dieser Beitrag bietet verschiedene Tipps und Tricks, um SQL effizienter und lesbarer zu schreiben
  • Sowohl für Einsteiger:innen in SQL als auch für erfahrene Datenanalyst:innen gibt es viele nützliche Hinweise
  • Besonders bei komplexen Abfragen sind der Einsatz von CTEs, Anti-Joins und QUALIFY in der Praxis sehr hilfreich
  • Es ist wichtig, die Ausführungsreihenfolge von SQL zu verstehen, Code zu kommentieren und die Dokumentation gründlich zu lesen
  • Andere Tools mit ähnlicher Funktionalität sind PostgreSQL, MySQL und Oracle

Verwandte Beiträge

2 Kommentare

 
hiyama 2024-09-26

Die führenden Kommata sind in diesem Beitrag alle als nachgestellte Kommata geschrieben. Im Original sind sie als führende eingetragen.

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt; 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt;= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;
 
GN⁺ 2024-09-26
Hacker-News-Kommentar

  • Die Notwendigkeit, den DB-Server gut zu verstehen und Ausführungspläne regelmäßig zu prüfen

    • EXISTS ist oft schneller als IN
    • NOT EXISTS und EXCEPT verhalten sich bei der Behandlung von NULL-Werten unterschiedlich
    • Es wird empfohlen, Subquery-Spalten statt Tabellen-Joins zu verwenden
    • Tabellenscans sollten vermieden und Indizes hinzugefügt werden
    • Beim Filtern von Ausdrücken können berechnete Spalten und Indizes verwendet werden
    • UNION ALL kann schneller sein als OR
    • Über Subquery-JOINs lässt sich die Filterreihenfolge erzwingen

  • Tipps für die Arbeit mit komplexen Stored Procedures

    • Permanente Tabellen in temporäre Tabellen kopieren und nur die benötigten Zeilen filtern
    • Temporäre Tabellen bearbeiten
    • Permanente Tabellen innerhalb einer Transaktion aktualisieren, bei Fehlern Rollback
    • Vorsicht bei der Arbeit mit Remote-Tabellen; empfohlen wird, sie zuerst in temporäre Tabellen zu kopieren und dann zu bearbeiten
    • Da Ausführungspläne unübersichtlich werden können, die Arbeit in kleine Schritte aufteilen
    • Ausführungspläne immer prüfen

  • Anmerkungen zur Lesbarkeit von Code

    • Die ersten beiden Beispiele opfern Lesbarkeit zugunsten einfacherer Erstellung
    • Beim letzten Beispiel bringt die Einrückung keinen großen Effekt

  • Vorschlag zur Verwendung von FROM-first- und Piping-Syntax in SQL

    • Die Erfahrung mit der Kusto Query Language war ein großer Fortschritt

  • Tipp zu Anti Join

    • Die Verwendung von EXISTS wird empfohlen; bei bedingungsbasierten Subqueries ist es vorteilhaft, wenn geprüft werden soll, ob Zeilen vorhanden sind

  • Vorteile führender Kommata in SELECT-Anweisungen

    • Einzelne Zeilen können auskommentiert werden
    • Die Code-Einrückung verbessert die Lesbarkeit

  • In MSSQL wird bei Kommentaren /* */ statt -- empfohlen

    • Der Query Store speichert Abfragen ohne Zeilenumbrüche

  • Die Verwendung von Fensterfunktionen wird empfohlen

  • Kontroverse um die Verwendung von 1=1 in der WHERE-Klausel

  • Vorstellung von AI2sql

    • SQL-Abfragen können aus englischen Prompts in Klartext generiert werden
    • Nützlich beim Schreiben komplexer Abfragen