Abfragedaten

Abgeschlossen

Diese Einheit befasst sich mit PostgreSQL-spezifischen Abfragefeatures und Mustern, die für KI-Anwendungen unerlässlich sind. Die Einheit konzentriert sich auf die einzigartigen Fähigkeiten und fortgeschrittenen Muster von PostgreSQL für die Verwaltung von Konversationsverlauf, Verarbeitungsstatus und Anwendungsmetadaten.

Diese Einheit setzt voraus, dass Sie mit den standardmäßigen SQL-Konzepten wie SELECT, WHEREVerknüpfungen und Aggregation vertraut sind. Wenn Sie eine Auffrischung benötigen, ist der Lernpfad Zum Einstieg in Abfragen mit Transact-SQL eine gute Ressource.

Abfrageausführung und Gültigkeitsbereich von Aliases

Selbst erfahrene SQL-Entwickler tun sich schwer mit der Ausführungsreihenfolge, insbesondere bei der Verwendung von Spaltenaliassen. SQL-Anweisungen werden in einer bestimmten logischen Reihenfolge ausgeführt, die sich von der Schreibrichtung unterscheidet, und das Verständnis dieser Reihenfolge hilft Ihnen bei der Behandlung von kryptischen Fehlern "Spalte ist nicht vorhanden".

Bestellung	Klausel	Zweck
1	FROM	Identifizieren Sie Quelltabellen
2	WHERE	Filterzeilen
3	GROUP BY	Gruppieren von Zeilen für Aggregation
4	HAVING	Filtergruppen
5	SELECT	Auswählen von Spalten und Berechnungsausdrücken
6	ORDER BY	Ergebnisse sortieren
7	LIMIT / OFFSET	Einschränken der Ergebnisanzahl

Aliasbereichsregel: Spaltenaliasen, die SELECT definiert sind, sind nur für Klauseln sichtbar, die hinterherSELECT ausgeführt werden , nämlich ORDER BY und LIMIT/OFFSET. Klauseln, die zuvor ausgeführt werden (WHERE, , GROUP BY) können nicht auf diese Aliase verweisen, HAVINGda sie noch nicht definiert sind.

Dies bedeutet, dass Sie nicht nach einem Alias in WHERE filtern können, aber Sie können nach einem Alias in ORDER BY sortieren:

-- This FAILS: WHERE executes before SELECT, so 'msg_date' doesn't exist yet
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE msg_date > '2024-01-01';  -- Error: column "msg_date" does not exist

-- This WORKS: repeat the expression in WHERE
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE DATE(created_at) > '2024-01-01';

-- This also WORKS: ORDER BY executes after SELECT, so aliases are available
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE DATE(created_at) > '2024-01-01'
ORDER BY msg_date;

PostgreSQL-spezifische Filterung

PostgreSQL erweitert sql standard mit Operatoren, die allgemeine Filteraufgaben vereinfachen. Diese Features sind nützlich für KI-Anwendungen, die flexible Textsuche und strukturierte Metadatenabfragen benötigen.

Der ILIKE Operator ermöglicht die Mustererkennung ohne Berücksichtigung der Groß-/Kleinschreibung, ohne Funktionen wie LOWER() zu benötigen. Dies ist nützlich für benutzerorientierte Suchfunktionen, bei denen die Übereinstimmung unabhängig von Groß- und Kleinschreibung erfolgen soll: SELECT * FROM messages WHERE content ILIKE '%error%'

Mit PostgreSQL können Sie steuern, an welcher Stelle NULL Werte in sortierten Ergebnissen mithilfe von NULLS FIRST oder NULLS LAST erscheinen. Standardmäßig werden NULL-Werte sortiert, als wären sie größer als alle anderen Werte. Verwenden Sie ORDER BY ended_at NULLS LAST, um unvollständige Unterhaltungen am Ende zu positionieren, oder ORDER BY completed_at NULLS FIRST, um nicht verarbeitete Aufgaben zuerst anzuzeigen.

Die COALESCE Funktion gibt den ersten Wert ungleich Null aus den Argumenten zurück. Verwenden Sie sie, um Standardwerte in Abfrageergebnissen (COALESCE(title, 'Untitled')) bereitzustellen oder nullfähige Spalten in Ausdrücken zu verarbeiten.

Abfragen von JSONB-Daten

Der JSONB-Typ von PostgreSQL speichert strukturierte Daten, die nicht in ein festes Schema passen. KI-Anwendungen verwenden häufig JSONB für Metadaten, Konfiguration, Modellparameter und variable Antwortstrukturen. PostgreSQL bietet spezielle Operatoren zum Extrahieren von Werten, zur Überprüfung der Struktur und zum Filtern basierend auf JSON-Inhalten.

Wird -> verwendet, um ein JSON-Element als JSON zu extrahieren oder ->> als Text zu extrahieren (für Vergleiche und Anzeige). Verwenden Sie #> (gibt JSON zurück) für geschachtelte Pfade, oder #>> (gibt Text zurück). Extrahiert beispielsweise metadata->>'status' das Statusfeld als Text, während checkpoint_data#>>'{results,0,score}' er in einem geschachtelten Pfad navigiert, um einen bestimmten Wert abzurufen.

Existenz- und Eindämmungsoperatoren ermöglichen eine effiziente Filterung. Der ?-Operator prüft das Vorhandensein von Schlüsseln (WHERE metadata ? 'priority'), während @> die Zugehörigkeit prüft (WHERE checkpoint_data @> '{"status": "completed"}'). Diese Operatoren können GIN-Indizes für eine effiziente Filterung in großen Tabellen verwenden.

Wenn JSONB-Spalten Arrays enthalten, verwenden Sie jsonb_array_elements, um sie zum Filtern oder Aggregieren zu erweitern.

-- Find conversations tagged with 'support'
SELECT DISTINCT c.*
FROM conversations c,
     jsonb_array_elements_text(c.metadata->'tags') AS tag
WHERE tag = 'support';

Effiziente Paginierung mit Keyset-Paginierung

Die herkömmliche OFFSETPaginierung wird bei großen Tabellen langsam, da PostgreSQL alle übersprungenen Zeilen scannen und verwerfen muss. Seite 1.000 mit 20 Zeilen pro Seite erfordert das Scannen von 20.000 Zeilen und das Verwerfen von 19.980 Zeilen. Keyset-Paginierung (auch cursorbasierte Paginierung genannt) verwendet WHERE Klauseln zum Überspringen von Zeilen, die unabhängig davon, wie tief Sie paginieren, konsistent ausgeführt werden.

Anstatt die Seitenzahlen zu verfolgen, verfolgen Sie den letzten Wert, den Sie gesehen haben, und filtern Sie von dort. Für diesen Ansatz ist eine eindeutige, sortierbare Spalte (oder eine Kombination aus Spalten) erforderlich:

-- First page: get the 20 most recent messages
SELECT id, conversation_id, content, created_at
FROM messages
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: filter by the last seen timestamp and id
SELECT id, conversation_id, content, created_at
FROM messages
WHERE (created_at, id) < ('2024-06-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Wenn id mehrere Zeilen den gleichen Zeitstempel aufweisen, werden Verknüpfungen sowohl in den Klauseln ORDER BY als auch in den WHERE-Klauseln behandelt. Ihre Anwendung speichert die Sortierwerte der letzten Zeile und übergibt sie an die nächste Abfrage. Ändern Sie < zu > und DESC zu ASC für die aufsteigende Reihenfolge.

Allgemeine Tabellenausdrücke (CTEs)

CTEs definieren benannte temporäre Resultsets, die nur während der Ausführung einer Abfrage vorhanden sind. Sie verbessern die Lesbarkeit, indem Sie komplexe Abfragen schritt für Schritt erstellen und rekursive Abfragen für hierarchische Daten aktivieren.

Verwenden Sie CTEs, um komplexe Abfragen in logische Schritte zu unterteilen. Jede CTE kann auf zuvor definierte CTEs verweisen und eine Pipeline von Transformationen erstellen:

WITH recent_conversations AS (
    SELECT id, user_id, started_at
    FROM conversations
    WHERE started_at > CURRENT_DATE - INTERVAL '7 days'
),
message_stats AS (
    SELECT conversation_id, COUNT(*) AS message_count, MAX(created_at) AS last_message_at
    FROM messages
    GROUP BY conversation_id
)
SELECT rc.user_id, rc.started_at, COALESCE(ms.message_count, 0) AS message_count
FROM recent_conversations rc
LEFT JOIN message_stats ms ON rc.id = ms.conversation_id;

Rekursive CTEs-Abfragestrukturstrukturen wie Aufgabenhierarchien, Organigramme oder Threadunterhaltungen. Sie bestehen aus einem Basisfall (Anker) und einem rekursiven Fall, der auf das CTE selbst verweist. Schließen Sie immer einen Tiefengrenzwert oder eine andere Beendigungsbedingung ein, um endlose Schleifen zu verhindern, wenn Ihre Daten Zyklen enthalten:

WITH RECURSIVE task_tree AS (
    -- Base case: start with the parent task
    SELECT id, parent_id, title, 1 AS depth
    FROM tasks WHERE id = 1
    UNION ALL
    -- Recursive case: find children of current level
    SELECT t.id, t.parent_id, t.title, tt.depth + 1
    FROM tasks t
    INNER JOIN task_tree tt ON t.parent_id = tt.id
    WHERE tt.depth < 10
)
SELECT * FROM task_tree ORDER BY depth, id;

KI-Anwendungen müssen häufig Unterhaltungsthreads abrufen, bei denen Nachrichten auf übergeordnete Nachrichten verweisen:

WITH RECURSIVE thread AS (
    -- Start with the root message
    SELECT id, parent_id, content, role, 0 AS depth
    FROM messages
    WHERE id = :root_message_id

    UNION ALL

    -- Get all replies
    SELECT m.id, m.parent_id, m.content, m.role, t.depth + 1
    FROM messages m
    INNER JOIN thread t ON m.parent_id = t.id
    WHERE t.depth < 50
)
SELECT * FROM thread ORDER BY depth, id;

EINFÜGEN mit RÜCKGABE

Die PostgreSQL-Klausel RETURNING ruft Werte aus eingefügten, aktualisierten oder gelöschten Zeilen in einem einzigen Durchlauf ab. Dies ist wichtig, um automatisch generierte IDs, Zeitstempel oder berechnete Standardwerte ohne separate Abfrage zu erhalten.

-- Get the generated ID after inserting a conversation
INSERT INTO conversations (user_id, session_id)
VALUES ('user123', 'sess_abc')
RETURNING id;

-- Get multiple generated values
INSERT INTO messages (conversation_id, role, content)
VALUES (1, 'user', 'Hello')
RETURNING id, created_at;

-- Use RETURNING with UPDATE
UPDATE tasks SET status = 'completed', completed_at = CURRENT_TIMESTAMP
WHERE id = 5
RETURNING id, status, completed_at;

Upserts mit ON CONFLICT

Die INSERT ... ON CONFLICT Klausel behandelt eindeutige Einschränkungsverletzungen, wodurch "upsert"-Vorgänge aktiviert werden, die neue Zeilen einfügen oder vorhandene aktualisieren. Dieses Muster ist für idempotente Vorgänge und Zustandsverwaltung in KI-Anwendungen wertvoll.

Wenn ein Konflikt bei einer eindeutigen Einschränkung auftritt, können Sie die vorhandene Zeile mit neuen Werten unter Verwendung von DO UPDATE aktualisieren. Die EXCLUDED Pseudotabelle verweist auf die Werte, die eingefügt wurden:

INSERT INTO user_preferences (user_id, preference_key, preference_value)
VALUES ('user123', 'theme', 'dark')
ON CONFLICT (user_id, preference_key)
DO UPDATE SET
    preference_value = EXCLUDED.preference_value,
    updated_at = CURRENT_TIMESTAMP;

Verwenden Sie DO NOTHING, um Zeilen, die gegen Einschränkungen verstoßen würden, stillschweigend zu überspringen: INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING.

Sie können für bedingte Logik eine WHERE Klausel DO UPDATE hinzufügen, die nur aktualisiert wird, wenn sich der neue Wert von der vorhandenen unterscheidet. Kombinieren Sie ON CONFLICT mit RETURNING, um festzustellen, ob eine Zeile eingefügt oder aktualisiert wurde – der Ausdruck (xmax = 0) gibt true für neu eingefügte Zeilen und false für aktualisierte Zeilen zurück.

Praktische Muster für KI-Anwendungen

Diese Muster kombinieren die zuvor behandelten Features in Lösungen für allgemeine KI-Anwendungsanforderungen. Die folgende Abfrage ruft den Unterhaltungsverlauf mit Metadaten für die Kontexterstellung ab:

WITH conversation_context AS (
    SELECT c.id, c.session_id, c.metadata->>'model' AS model
    FROM conversations c
    WHERE c.session_id = :session_id AND c.ended_at IS NULL
)
SELECT cc.session_id, cc.model, m.role, m.content, m.created_at
FROM conversation_context cc
INNER JOIN messages m ON cc.id = m.conversation_id
ORDER BY m.created_at
LIMIT 50;

Das folgende Beispiel zeigt das Aufzeichnen von Aufgabenprüfpunkten mit Statusverwaltung mithilfe von Upsert:

INSERT INTO task_checkpoints (task_id, step_number, checkpoint_data)
VALUES (:task_id, :step_number, :checkpoint_json::jsonb)
ON CONFLICT (task_id, step_number)
DO UPDATE SET checkpoint_data = EXCLUDED.checkpoint_data, updated_at = CURRENT_TIMESTAMP
RETURNING id, created_at, (xmax = 0) AS is_new;

Das folgende Beispiel zeigt eine paginierte Suche mit JSONB-Filtern:

SELECT c.id, c.session_id, c.started_at, c.metadata->>'status' AS status
FROM conversations c
WHERE c.user_id = :user_id
  AND c.metadata @> :filter_json::jsonb
  AND (c.started_at, c.id) < (:last_started_at, :last_id)
ORDER BY c.started_at DESC, c.id DESC
LIMIT 20;

Weitere Ressourcen

• Json-Funktionen und Operatoren von PostgreSQL
• PostgreSQL INSERT-Dokumentation
• PostgreSQL WITH-Abfragen (CTEs)