Dieser Leitfaden ist eine Fortsetzung des Data Explorer-Tutorials – Teil 1: Ihre erste Abfrage schreiben.
Dieser Leitfaden ist eine Fortsetzung des Jetzt, da Sie gesehen haben, wie Data Explorer in der Praxis funktioniert, sprechen wir über einige SQL-Grundlagen, die Ihnen bei Ihren Datenexplorationsabenteuern hilfreich sein werden.
SQL-Anweisungen
Alle Aktionen, die Sie in der Discourse-Datenbank ausführen müssen, werden mit SQL-Anweisungen durchgeführt.
selectist dasselbe wieSELECT. In diesem Tutorial und als beste Praxis schreiben wir jedoch alle SQL-Schlüsselwörter in Großbuchstaben.
Hier sind einige der häufigsten SQL-Anweisungen, die Sie möglicherweise verwenden. Beachten Sie, dass wir einige davon in unserer vorherigen Beispielabfrage verwendet haben:
- SELECT: Wird verwendet, um Daten aus einer Datenbank auszuwählen. Die zurückgegebenen Daten werden in einer Ergebnistabelle, dem sogenannten Result-Set, gespeichert.
SELECT column1, column2, ...
FROM table_name;
- WHERE: Wird verwendet, um Datensätze zu filtern.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
- ORDER BY: Wird verwendet, um das Result-Set in aufsteigender oder absteigender Reihenfolge zu sortieren.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
- COUNT: Gibt die Anzahl der Zeilen zurück, die einem angegebenen Kriterium entsprechen.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
- GROUP BY: Wird häufig mit Aggregatfunktionen (COUNT, MAX, MIN, SUM, AVG) verwendet, um das Result-Set nach einer oder mehreren Spalten zu gruppieren.
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
- HAVING: Wird verwendet, um die Ergebnisse einer GROUP BY-Operation zu filtern. Die HAVING-Klausel wird in SQL verwendet, da das WHERE-Schlüsselwort nicht mit Aggregatfunktionen verwendet werden kann (z. B.
COUNT(),MAX(),MIN(),SUM(),AVG()).
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
- IN: Wird verwendet, um zu prüfen, ob ein Wert in einer Menge von Werten vorhanden ist oder von einer Unterabfrage zurückgegeben wird. Es ist eine Abkürzung für mehrere
OR-Bedingungen und macht Ihre SQL-Abfragen prägnanter und leichter lesbar.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
PostgreSQL
Discourse verwendet derzeit PostgreSQL Version 13 für seine Datenbank, ein Open-Source-relationales Datenbankverwaltungssystem (RDBMS), das auf Erweiterbarkeit und SQL-Konformität setzt.
Für ein detailliertes Verständnis von PostgreSQL empfiehlt es sich, die offizielle PostgreSQL-Dokumentation zu lesen. Insbesondere die Abschnitte zu Abfragen, Datentypen und Funktionen sind beim Schreiben von SQL-Abfragen besonders relevant.
Datenbanktabellen
Die Discourse-Datenbank besteht aus Tabellen. Tabellen sind die grundlegende Methode zur Speicherung von Daten in einem relationalen Datenbankverwaltungssystem.
Jede Tabelle in Discourse wird durch einen Namen identifiziert (z. B. posts oder topics), und jede Tabelle enthält Datensätze (Zeilen) mit Daten. Spalten in Tabellen werden häufig als Felder bezeichnet.
Der Data Explorer listet die 9 wichtigsten Tabellen zuerst im Query-Edit-UI-Panel auf. Sie können die Spaltenstruktur und -typen aller Tabellen mit einem Klick einsehen:
Datenbankschema
In PostgreSQL sowie in anderen relationalen Datenbanken sind die Konzepte Primärschlüssel und Fremdschlüssel von grundlegender Bedeutung. Sie dienen dazu, Beziehungen zwischen Tabellen herzustellen und die Konsistenz und Integrität der Daten sicherzustellen.
Primärschlüssel
Ein Primärschlüssel ist eine Spalte oder eine Menge von Spalten in einer Tabelle, die jede Zeile in dieser Tabelle eindeutig identifiziert. Keine zwei Zeilen in einer Tabelle können denselben Primärschlüsselwert haben. Ein Primärschlüsselwert darf nicht NULL sein und muss eindeutig sein. Der Primärschlüssel wird verwendet, um die Daten in der Tabelle zu indizieren, was die Datenabfrage erheblich beschleunigt. Im Discourse-Schema-Explorer wird der Primärschlüssel einer Tabelle immer zuerst aufgeführt.
Beispiel: Der Primärschlüssel der Tabelle posts ist das Feld id.
Fremdschlüssel
Ein Fremdschlüssel ist eine Spalte oder eine Menge von Spalten in einer Tabelle, die verwendet wird, um eine Verbindung zwischen den Daten in zwei Tabellen herzustellen. Er fungiert als Querverweis zwischen Tabellen, da er auf den Primärschlüssel einer anderen Tabelle verweist und somit eine Verbindung zwischen ihnen herstellt. Die Tabelle mit dem Fremdschlüssel wird als Kindtabelle bezeichnet, und die Tabelle mit dem Primärschlüssel als referenzierte oder Elterntabelle. Im Discourse-Schema-Explorer wird ein Fremdschlüssel durch den Text fkey gefolgt vom Namen der Elterntabelle gekennzeichnet.
Beispiel: In der Tabelle posts ist user_id ein Fremdschlüssel der Tabelle users.
Verwendung von Primär- und Fremdschlüsseln
Wenn Sie Abfragen im Data Explorer schreiben, können Sie diese Primär- und Fremdschlüssel verwenden, um Tabellen mit JOIN zu verbinden und komplexere Daten zu erhalten. Wenn Sie beispielsweise alle Beiträge eines bestimmten Benutzers finden möchten, könnten Sie eine Abfrage wie diese schreiben:
SELECT p.id, p.created_at, p.raw
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.username = 'username_here'
In dieser Abfrage verwenden wir den Fremdschlüssel user_id in der Tabelle posts, um mit dem Primärschlüssel id in der Tabelle users zu verbinden. Dadurch können wir alle Beiträge eines bestimmten Benutzers finden.
SQL-JOINS
Wie bereits erwähnt, wird die JOIN-Anweisung verwendet, um Zeilen aus zwei oder mehr Tabellen basierend auf einer verwandten Spalte zwischen ihnen zu kombinieren.
Es ist wichtig zu beachten, dass es verschiedene Arten von SQL-JOIN-Anweisungen gibt, die jeweils einen anderen Zweck erfüllen:
-
INNER JOIN: Das Schlüsselwort
INNER JOINwählt Datensätze aus, die in beiden Tabellen übereinstimmende Werte haben. Es gibt die Zeilen aus beiden Tabellen zurück, in denen eine Übereinstimmung besteht. Verwenden SieINNER JOIN, wenn Sie nur die Datensätze zurückgeben möchten, bei denen eine Übereinstimmung in beiden Tabellen vorliegt. WennJOINohne Angabe des Join-Typs (wieLEFT,RIGHToderFULL) verwendet wird, ist standardmäßigINNER JOINgemeint. -
LEFT (OUTER) JOIN: Das Schlüsselwort
LEFT JOINgibt alle Datensätze aus der linken Tabelle (table1) und die übereinstimmenden Datensätze aus der rechten Tabelle (table2) zurück. Das Ergebnis ist NULL auf der rechten Seite, wenn keine Übereinstimmung vorliegt. Verwenden SieLEFT JOIN, wenn Sie alle Datensätze aus der linken Tabelle und die übereinstimmenden Datensätze aus der rechten Tabelle zurückgeben möchten. Wenn keine Übereinstimmung vorliegt, ist das Ergebnis auf der rechten Seite NULL. -
RIGHT (OUTER) JOIN: Das Schlüsselwort
RIGHT JOINgibt alle Datensätze aus der rechten Tabelle (table2) und die übereinstimmenden Datensätze aus der linken Tabelle (table1) zurück. Das Ergebnis ist NULL auf der linken Seite, wenn keine Übereinstimmung vorliegt. Verwenden SieRIGHT JOIN, wenn Sie alle Datensätze aus der rechten Tabelle und die übereinstimmenden Datensätze aus der linken Tabelle zurückgeben möchten. Wenn keine Übereinstimmung vorliegt, ist das Ergebnis auf der linken Seite NULL. -
FULL (OUTER) JOIN: Das Schlüsselwort
FULL JOINgibt alle Datensätze zurück, wenn eine Übereinstimmung in entweder der linken (table1) oder der rechten (table2) Tabelle vorliegt. Verwenden SieFULL JOIN, wenn Sie alle Datensätze zurückgeben möchten, wenn eine Übereinstimmung in einer der Tabellen vorliegt.
Sie können auch mehrere JOIN-Anweisungen kombinieren, um mehrere Tabellen miteinander zu verbinden. Beispielsweise können wir die Tabelle topic_tags mit der Tabelle topics verbinden und dann die Tabelle tags mit der Tabelle topic_tags verbinden, um Zugriff auf die Namen der Tags zu erhalten, die einem Thema zugeordnet sind.
SELECT
t.id AS topic_id,
tg.name as tag_name
FROM
topics t
JOIN topic_tags tt ON t.id = tt.topic_id
JOIN tags tg ON tg.id = tt.tag_id
WITH-Anweisungen und Unterabfragen
Die SQL-WITH-Anweisung, auch bekannt als Common Table Expression (CTE), wird verwendet, um ein temporäres Result-Set zu erstellen, das innerhalb einer anderen SELECT-Anweisung referenziert werden kann. Dies kann besonders nützlich sein, wenn Sie mit komplexen SQL-Abfragen arbeiten, da sie dazu beitragen können, diese zu vereinfachen und somit lesbarer und wartbarer zu machen.
Hier ist ein einfaches Beispiel für eine WITH-Anweisung:
WITH post_counts AS (
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id
)
SELECT u.username, post_counts.post_count
FROM users u
JOIN post_counts ON u.id = post_counts.user_id
ORDER BY post_counts.post_count DESC;
In dieser Abfrage erstellt die WITH-Anweisung eine temporäre Tabelle post_counts, die die Benutzer-ID und die Gesamtanzahl der Beiträge jedes Benutzers enthält. Die Hauptabfrage verbindet diese Tabelle dann mit der Tabelle users, um die Benutzernamen zu erhalten, die jeder ID zugeordnet sind, und sortiert das Ergebnis nach der Anzahl der Beiträge in absteigender Reihenfolge.
Diese Abfrage würde die Anzahl der Beiträge zurückgeben, die jeder Benutzer erstellt hat.
Unterabfragen sind Abfragen, die innerhalb einer anderen Abfrage verschachtelt sind. Eine Unterabfrage kann innerhalb von SELECT-Anweisungen oder innerhalb einer anderen Unterabfrage verwendet werden. Eine Unterabfrage kann eine Menge von Datensätzen, einen einzelnen Datensatz oder einen einzelnen Wert zurückgeben.
Hier ist ein Beispiel für eine Unterabfrage:
SELECT u.username
FROM users u
WHERE u.id IN (
SELECT p.user_id
FROM posts p
GROUP BY p.user_id
HAVING COUNT(p.id) > 100
)
In diesem Beispiel wählt die Unterabfrage (SELECT p.user_id FROM posts p GROUP BY p.user_id HAVING COUNT(p.id) > 100) die user_id aus der Tabelle posts für Benutzer aus, die mehr als 100 Beiträge erstellt haben. Die Hauptabfrage ruft dann den username aus der Tabelle users für diese user_ids ab.
Diese Abfrage würde eine Liste von Benutzernamen für Benutzer zurückgeben, die mehr als 100 Beiträge erstellt haben.