true
und false
, kein unknown
. Verwende stattdessen null
is [not] unknown
. Verwende stattdessen is [not] null
SQLite 3.25.0 führt Window-Funktionen ein. Wer Window-Funktionen kennt, weiß, dass das eine große Sache ist. Wer Window-Funktionen nicht kennt, sollte sie kennenlernen! Dieser Artikel ist nicht der richtige Ort, um Window-Funktionen zu erklären. Ich übertreibe aber nicht, wenn ich sage, dass Window-Funktionen die wichtigste „moderne SQL“-Funktion überhaupt sind.
Der Funktionsumfang der Over
-Klausel ist bei SQLite ziemlich nahe an dem anderer Datenbanken. Der einzig nennenswerte Rückstand ist, dass Range
-Rahmen (frame) keine Numerischen- oder Intervall-Distanzen unterstützen (nur current row
und unbounded preceding|following
). Das ist dieselbe Einschränkung, die auch SQL Server hat, und PostgreSQL hatte, als Window-Funktionen in SQLite eingeführt wurden. Mit PostgreSQL 11 wurde diese Beschränkung in der Zwischenzeit aufgehoben.
<Distanz>
(nur unbounded
und current row
)<Distanz>
(nur unbounded
und current row
) und keine Datums- und Zeit-TypenDie Menge der angebotenen Window-Funktionen ist bei SQLite ebenfalls am aktuellen Stand anderer Datenbanken. Die nennenswerten Lücken (distinct
in Aggregatfunktionen, width_bucket
, respect|ignore nulls
und from first|last
) gibt es auch in einigen anderen Produkten.
DISTINCT
-Aggregate werden nicht unterstütztORDER BY
-Klauselrespect|ignore nulls
Null
-Behandlung: lead(<expr>, 'IGNORE NULLS')
(ein String-Argument)respect|ignore nulls
ignore nulls
Null
-Behandlung: first_value(<expr> IGNORE NULLS)
(kein Komma)Null
-Behandlung: first_value(<expr>, 1, null, 'IGNORE NULLS')
(ein String-Argument)ignore nulls
from last
ignore nulls
• Kein from last
Filter
-KlauselObwohl die Filter
-Klausel nur eine syntaktische Schönheitsmaßname ist – mit einem Case
-Ausdruck kann man dasselbe Ergebnis erzielen – denke ich, dass die Filter
-Klausel essenziell ist, weil sie das Lernen maßgeblich erleichtert.
Betrachte dazu die folgenden Select
-Klauseln. Welche ist einfacher zu verstehen?
SELECT SUM(umsatz) gesamt_umsatz
, SUM(CASE WHEN produkt = 1
THEN umsatz
END
) prod1_umsatz
...
vs.
SELECT SUM(umsatz) gesamt_umsatz
, SUM(umsatz) FILTER(WHERE produkt = 1) prod1_umsatz
...
Dieses Beispiel fasst zusammen, was die Filter
-Klausel macht: Sie ist ein Zusatz zu Aggregatfunktionen, der dafür sorgt, dass nur die Werte bestimmter Zeilen aggregiert werden. Die Pivot-Technik ist der häufigste Anwendungsfall der Filter
-Klausel. Dazu zählt auch die Transformation von Attributen im Entity-Attribute-Value (EAV) Modell in Spalten.
SQLite 3.25.0 führ die Filter
-Klausel für Aggregatfunktionen ein, wenn sie die Over
-Klausel verwenden — also nicht mit group by
. Leider bedeutet das, dass man die Filter
-Klausel für die oben genannten Anwendungsfälle in SQLite noch nicht nutzen kann. Man muss dafür also nach wie vor auf Case
-Ausdrücke zurückgreifen. Ich hoffe, dass sich das bald ändern wird.
Insert … on conflict
(„Upsert“)Mit SQLite 3.24.0 wurde das sogenannte „Upsert“ eingeführt: ein Insert
-Statement, das die Behandlung von Primärschlüssel- und Unqiue-Constraint-Verletzungen erlaubt. Damit kann man diese Fehler ignorieren (on conflict … do nothing
) oder ein Update auf die bereits existierende Zeile ausführen (on conflict … do update …
).
Upsert ist eine proprietäre SQL-Erweiterung – d. h. es kommt nicht aus dem SQL-Standard und ist daher in der Matrix unten Grau gehalten. SQLite verwendet jedoch dieselbe Syntax wie PostgreSQL.0 Der SQL-Standard sieht für diese Anforderung das Merge
-Statement vor.
Anders als PostgreSQL hat SQLite aufgrund einer syntaktischen Mehrdeutigkeit ein Problem mit der folgenden Anweisung.
INSERT INTO ziel
SELECT *
FROM quelle
ON CONFLICT (id)
DO UPDATE SET wert = excluded.wert
Laut Dokumentation liegt das Problem darin, dass der Parser nicht weiß, ob das Token on
zu einem Join gehört, oder eine Upsert-Klausel einleitet. Diese Mehrdeutigkeit kann man vermeiden, indem man der Abfrage eine weitere Klausel hinzufügt – z. B. where true
.
INSERT INTO target
SELECT *
FROM source
WHERE true
ON CONFLICT (id)
DO UPDATE SET val = excluded.val
log errors
für insert
, update
, delete
und merge
(„DML error logging“)On conflict
darf nicht direkt auf die From
-Klausel einer Abfrage folgen. Wenn nötig, füge where true
hinzuEine weitere proprietäre Erweiterung, die SQLite eingeführt hat, ist das Umbenennen von Spalten in Basistabellen.1 Der SQL-Standard bietet keine Funktion dafür an.2
SQLite folgt der Syntax, die auch von vielen anderen Produkten angeboten wird.
ALTER TABLE … RENAME COLUMN … TO …
sp_rename
Neben den SQL-Änderungen gab es in 2018 natürlich auch Erweiterungen der API. Ein Blick in die Neuerungen von SQLite lohnt sich.
Der nächste Artikel über PostgreSQL 11 ist bereits in Arbeit. Folge modern-sql.com via Twitter, E-Mail oder RSS.
Wenn Du mehr über modernes SQL lernen willst, solltest du dir meine Schulung im Mai ansehen. Neben den oben erwähnten Window-Funktionen behandle ich dort auch Rekursionen, Indizierung und verfestige die Grundlagen. Schulungsgrundlage ist der aktuelle Entwurf meines nächsten Buches. Schulung jetzt ansehen!
20 Jahre SQL-Evolution kann man nicht an einem Tag nachholen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen und modern-sql.com am Radar zu behalten.
Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how
SQLite folgt häufig der PostgreSQL-Syntax. Richard Hipp bezeichnet das als den „What Would PostgreSQL Do (WWPD)“-Ansatz.
Basistabellen sind Tabellen, die mittels create table
angelegt wurden. In Derived-Tables (z. B. das Ergebnis einer Abfrage) können Spalten in der Select
-Klausel, in der From
-Klausel oder mit der With
-Klausel umbenannt werden.
Soweit ich weiß. Mit updateable Views kann man ähnliche Effekte erzielen.