Spaltenorientierte Datenbanken und Zeitreihen

Kurz gesagt: Eine spaltenorientierte Datenbank speichert jede Spalte einzeln, damit Berichte über Millionen Zeilen schnell lesen. Eine Zeitreihen-Datenbank speichert Messwerte mit Zeitstempel, zum Beispiel von Sensoren.

Problem

Der Bericht will zwei Spalten und liest sieben

Die Tabelle sales aus der vorigen Lektion hat sieben Spalten: Bestellung, Tag, Stadt, Kategorie, Produkt, Menge und Betrag. Der Bericht über den Umsatz pro Monat will zwei davon, day und amount.

Eine gewöhnliche Datenbank speichert die Daten zeilenweise: Alles zu einem Verkauf liegt zusammen auf der Platte. Um zwei Spalten zu lesen, liest sie die ganzen Zeilen und wirft die anderen fünf weg. Bei einer Milliarde Verkäufe sind das hunderte Gigabyte, von denen etwas mehr als ein Viertel gebraucht wird.

SQLDatenbank: Shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS rows, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT category) AS categories FROM sales;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS rows, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT category) AS categories FROM sales;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die Idee

Jede Spalte für sich

Eine spaltenorientierte Datenbank (ClickHouse, BigQuery, Snowflake, DuckDB) speichert jede Spalte in einer eigenen Datei. Der Umsatzbericht öffnet nur die Dateien von day und amount. Und eine Spalte hält gleichartige Werte nebeneinander (nur Daten, nur Städte), deshalb lässt sie sich viel besser komprimieren als eine Zeile, in der alles gemischt ist.

Die einfachste Kompression ist eine Folge: Statt „Sofia, Sofia, Sofia …“ schreibst du „Sofia × 50“. Die ganze Spalte city, nach Stadt sortiert, sind sieben Folgen:

SQLDatenbank: Shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT city, COUNT(*) AS repeated
FROM sales
GROUP BY city
ORDER BY city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT city, COUNT(*) AS repeated FROM sales GROUP BY city ORDER BY city;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Das funktioniert nur, wenn die gleichen Werte nebeneinander liegen. So viele Folgen hat die Spalte, wenn die Zeilen in der Reihenfolge der Bestellungen stehen, und so viele, wenn sie nach Stadt sortiert sind:

SQLDatenbank: Shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS runs
FROM (
  SELECT city, LAG(city, 1, '-') OVER (ORDER BY order_id, product) AS before
  FROM sales
) AS t
WHERE before IS NOT city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS runs FROM ( SELECT city, LAG(city, 1, '-') OVER (ORDER BY order_id, product) AS before FROM sales ) AS t WHERE before IS NOT city;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

SQLDatenbank: Shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS runs
FROM (
  SELECT city, LAG(city, 1, '-') OVER (ORDER BY city, order_id, product) AS before
  FROM sales
) AS t
WHERE before IS NOT city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS runs FROM ( SELECT city, LAG(city, 1, '-') OVER (ORDER BY city, order_id, product) AS before FROM sales ) AS t WHERE before IS NOT city;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Von 128 Werten bleiben 39 Folgen, wenn die Zeilen nicht sortiert sind, und 7, wenn sie es sind. Deshalb wird in ClickHouse jede Tabelle mit ORDER BY angelegt. Die Reihenfolge der Spalten darin ist die Sortierung auf der Platte.

Genauer, für Neugierige

LAG(city, 1, '-') liefert die Stadt der vorigen Zeile (und '-' für die erste Zeile), und jede Zeile, in der sich die Stadt ändert, beginnt eine neue Folge. IS NOT statt <> steht da, weil NULL <> NULL nicht wahr ist, während NULL IS NOT NULL ein klares „nein“ ist. Echte spaltenorientierte Datenbanken nutzen auch andere Arten der Kompression: ein Wörterbuch (aus den Städten werden die Zahlen 1–7), Differenzen zwischen benachbarten Zahlen und obendrauf einen allgemeinen Algorithmus (LZ4, ZSTD). Eine Kompression um das Zehnfache ist nicht selten.

Die Idee

Wann spaltenorientiert, wann nicht

Eine spaltenorientierte Datenbank ist hervorragend für Berichte: wenige Spalten, viele Zeilen, Summen und Gruppen. Für die Kasse ist sie schlecht. Eine neue Bestellung bedeutet ein Schreiben in sieben getrennte Dateien, und ein UPDATE einer Zeile heißt oft, einen ganzen Block der Spalte neu zu schreiben. Deshalb schreiben spaltenorientierte Datenbanken gern in großen Paketen (tausende Zeilen auf einmal) und ändern selten. Das ist die Trennung zwischen OLTP und OLAP aus der vorigen Lektion, nur auf der Platte.

Problem

Die Milchkühlung

Die Kühlung mit den Milchprodukten hat einen Sensor, der alle 15 Minuten die Temperatur aufzeichnet. Über 8 °C verderben Käse und Milch schnell, und der Shop muss wissen, wann das passiert ist. Solche Daten sind eine Zeitreihe: nach Zeit geordnete Werte, die nur hinzukommen und fast nie geändert werden. Zwei Tage Messwerte:

SQLDatenbank: Shop

SELECT * FROM fridge ORDER BY at LIMIT 5;

SELECT * FROM fridge ORDER BY at LIMIT 5;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

SQLDatenbank: Shop

SELECT COUNT(*) AS readings, MIN(at) AS first, MAX(at) AS last FROM fridge;

SELECT COUNT(*) AS readings, MIN(at) AS first, MAX(at) AS last FROM fridge;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Zwei Tage mit je 96 Messwerten müssten 192 sein, es sind aber 184. Acht fehlen, und du wirst sehen, wo.

Die Idee

Downsampling auf Stunden

Niemand sieht sich 35.000 Punkte in einem Diagramm für ein Jahr an. Die Messwerte werden pro Stunde (oder Tag) gruppiert, mit ein paar Zahlen für jede: dem Durchschnitt, dem niedrigsten und dem höchsten Wert. Das ist Downsampling. Die Stunde sind die ersten 13 Zeichen der Zeit:

SQLDatenbank: Shop

SELECT substr(at, 1, 13) || ':00' AS hour,
       ROUND(AVG(temp), 1) AS avg_temp,
       MAX(temp) AS max_temp
FROM fridge
WHERE at >= '2026-10-24 12:00' AND at < '2026-10-24 18:00'
GROUP BY hour
ORDER BY hour;

SELECT substr(at, 1, 13) || ':00' AS hour, ROUND(AVG(temp), 1) AS avg_temp, MAX(temp) AS max_temp FROM fridge WHERE at >= '2026-10-24 12:00' AND at < '2026-10-24 18:00' GROUP BY hour ORDER BY hour;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Der Durchschnitt für 14:00 ist 8,9 °C, und der höchste Wert ist 9,7 °C. Die Tür stand offen.

Probier es aus

Wann ist Alarm

Zeige die Stunden, in denen mindestens ein Messwert über 8 °C liegt (die Spalten hour und max_temp, die höchste Temperatur der Stunde), in der Reihenfolge der Stunde.

SQLDatenbank: Shop

SELECT substr(at, 1, 13) || ':00' AS hour FROM fridge LIMIT 5;

SELECT substr(at, 1, 13) || ':00' AS hour FROM fridge LIMIT 5;

Prüfen▶ Ausführen

Die Idee

Ein gleitender Durchschnitt

Sensoren liefern manchmal einen einzelnen falschen Wert, und die Kühlung wird von jedem Kunden für Sekunden geöffnet. Deshalb schaut der Alarm oft nicht auf einen Messwert, sondern auf den Durchschnitt der letzten vier (der letzten Stunde). Er wandert mit jeder neuen Zeile mit: eine Fensterfunktion mit ROWS BETWEEN 3 PRECEDING AND CURRENT ROW:

SQLDatenbank: Shop

SELECT *
FROM (
  SELECT at, temp,
         ROUND(AVG(temp) OVER (ORDER BY at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2) AS last_hour
  FROM fridge
) AS t
WHERE at BETWEEN '2026-10-24 13:30' AND '2026-10-24 15:15'
ORDER BY at;

SELECT * FROM ( SELECT at, temp, ROUND(AVG(temp) OVER (ORDER BY at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2) AS last_hour FROM fridge ) AS t WHERE at BETWEEN '2026-10-24 13:30' AND '2026-10-24 15:15' ORDER BY at;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Der Durchschnitt geht erst um 14:45 über 8 °C, als die Tür eine Stunde lang offen war. Ein zufälliger hoher Messwert würde ihn um etwa 1 °C anheben, aber nicht über 8.

Die Fensterfunktion steht in der inneren Abfrage, der Zeitraum außerhalb. WHERE läuft vor dem Fenster: Steht die Bedingung für den Zeitraum in derselben Abfrage, sieht der Durchschnitt für 13:30 die Zeilen 12:45, 13:00 und 13:15 nicht und besteht nur aus einem Messwert.

Probier es aus

Die Lücke

Der Sensor misst alle 15 Minuten. Finde, wo er die Verbindung verloren hat: den letzten Messwert vor der Lücke und den ersten danach (die Spalten last_before und first_after).

SQLDatenbank: Shop

SELECT at, LAG(at) OVER (ORDER BY at) AS before FROM fridge LIMIT 5;

SELECT at, LAG(at) OVER (ORDER BY at) AS before FROM fridge LIMIT 5;

Prüfen▶ Ausführen

Die Idee

Eine Aufbewahrungsdauer

Die Rohmesswerte wachsen für einen Sensor um 35.000 Zeilen im Jahr, und der Shop hat hunderte. Üblich ist eine Tabelle pro Stunde, die lange aufbewahrt wird, und Rohmesswerte, die nur kurz bleiben (hier einen Tag). Die Stundentabelle entsteht wie im Data Warehouse, mit CREATE TABLE … AS SELECT:

SQLDatenbank: Shop

CREATE TABLE fridge_hourly AS
SELECT substr(at, 1, 13) || ':00' AS hour,
       ROUND(AVG(temp), 2) AS avg_temp, MIN(temp) AS min_temp, MAX(temp) AS max_temp,
       COUNT(*) AS readings
FROM fridge
GROUP BY hour;

SELECT * FROM fridge_hourly WHERE hour BETWEEN '2026-10-25 02:00' AND '2026-10-25 05:00';

CREATE TABLE fridge_hourly AS SELECT substr(at, 1, 13) || ':00' AS hour, ROUND(AVG(temp), 2) AS avg_temp, MIN(temp) AS min_temp, MAX(temp) AS max_temp, COUNT(*) AS readings FROM fridge GROUP BY hour; SELECT * FROM fridge_hourly WHERE hour BETWEEN '2026-10-25 02:00' AND '2026-10-25 05:00';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Auch die Stunden 03:00 und 04:00 fehlen darin: Es gibt keinen einzigen Messwert von dort. Das Diagramm muss eine leere Stelle zeigen und nicht 02:00 und 05:00 mit einer geraden Linie verbinden, als wäre alles in Ordnung.

Übung

Aufräumen und ein Bericht

1. Die Stundentabelle ist schon angelegt.

Lösche die Rohmesswerte vor dem 25. Oktober 2026. Die Tabelle fridge_hourly bleibt vollständig.

SQLDatenbank: Shop

SELECT COUNT(*) FROM fridge;

SELECT COUNT(*) FROM fridge;

Prüfen▶ Ausführen

2. Ein Bericht für den Tag.

Für jeden Tag: die niedrigste, die höchste und die durchschnittliche Temperatur (auf 0.01 gerundet) und die Anzahl der Messwerte (die Spalten day, min_temp, max_temp, avg_temp, readings), in der Reihenfolge des Tages.

SQLDatenbank: Shop

SELECT substr(at, 1, 10) AS day FROM fridge LIMIT 3;

SELECT substr(at, 1, 10) AS day FROM fridge LIMIT 3;

Prüfen▶ Ausführen

Die Idee

Datenbanken für Zeitreihen

Es gibt auch eigene Datenbanken für Zeitreihen: TimescaleDB (eine Erweiterung von PostgreSQL, die die Tabelle selbst nach Zeit aufteilt und selbst auf Stunden herunterrechnet), InfluxDB und Prometheus (für Servermetriken). Alle tun dasselbe wie in der Lektion: nach Zeit sortieren, die Spalten komprimieren, heruntergerechnete Tabellen führen und Rohdaten nach einer Frist löschen. Auch die spaltenorientierten ClickHouse und DuckDB kommen damit gut zurecht.

Genauer, für Neugierige

In TimescaleDB ist eine Stundentabelle, die sich selbst aktualisiert, CREATE MATERIALIZED VIEW … WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', at) …, und die Aufbewahrungsdauer ist SELECT add_retention_policy('fridge', INTERVAL '1 day'). SQLite hat nichts Vergleichbares: Die Stundentabelle und das Löschen führt das Programm nach Zeitplan aus.

Test

Teste dich

1. Ein Bericht summiert die Spalte amount in einer Tabelle mit 7 Spalten und 100 Millionen Zeilen. Wie viel der Daten liest eine spaltenorientierte Datenbank?

Nur die Spalte amount (und die Spalten in WHERE)Alle 7 SpaltenNur die erste Zeile

2. Warum sortieren spaltenorientierte Datenbanken die Zeilen (ORDER BY in einem ClickHouse-CREATE-TABLE)?

Damit es hübsch aussiehtWeil sie ohne nicht funktionierenGleiche Werte liegen dann nebeneinander, und die Spalte schrumpft auf ein paar Folgen

3. Der Sensor misst alle 15 Minuten, und das Diagramm für ein Jahr muss schnell sein. Was macht man meist?

Man misst seltenerMan führt eine Tabelle pro Stunde (oder Tag) mit AVG, MIN und MAX und löscht die Rohmesswerte nach einer FristDas Diagramm liest alle Messwerte

4. Der gleitende Durchschnitt der letzten Stunde steht in einer Unterabfrage, und das WHERE für den Zeitraum außerhalb. Warum nicht in derselben Abfrage?

WHERE läuft vor der Fensterfunktion, und die ersten Zeilen des Zeitraums würden ihre vorige Stunde verlierenWeil SQLite es nicht erlaubtEs gibt keinen Unterschied

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: CREATE TABLE: eine Tabelle anlegen · Natürliche Schlüssel und Denormalisierung · Indizes · OLTP und OLAP
• Geht weiter in: Wie du eine Datenbank wählst
• Aufgaben zum Thema: Arten von Datenbanken