0Pricing
SQL Academy · Lektion

Analytische Abfragen schreiben

Schneiden Sie Metriken auf, analysieren und aggregieren Sie sie

Analytische Abfragen schreiben ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 4 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was sind analytische Abfragen?

Analytische Abfragen gehen über einfache Zeilenabfragen hinaus. Statt zu fragen, welche Bestellung Kunde 42 aufgegeben hat, fragen sie beispielsweise, wie hoch der Gesamtumsatz nach Region und Quartal ist oder wie sich dieser Monat im Vergleich zum letzten Monat entwickelt.

In einem auf einem Star-Schema basierenden Data-Warehouse Slicen analytische Abfragen Fakten (eine Dimension filtern), führen ein Dice durch (mehrere Dimensionen filtern) und Roll-ups durch (auf eine gröbere Granularität aggregieren), um geschäftliche Erkenntnisse zu gewinnen.

Auffrischung zum Star-Schema

Ein Star-Schema enthält eine zentrale Faktentabelle (z. B. fact_sales), die von Dimensionstabellen umgeben ist (z. B. dim_date, dim_product, dim_store). Analytische Abfragen verknüpfen die Faktentabelle mit den Dimensionen, die für die aktuelle Analyse benötigt werden.

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

Slicing: Eine Dimension filtern

Slicing bedeutet, die Ergebnismenge auf einen einzelnen Wert einer Dimension zu beschränken – beispielsweise nur Daten für das Jahr 2024 zu betrachten. Die WHERE-Klausel ist Ihr Werkzeug für das Slicing.

Wenn Sie frühzeitig slicen, reduzieren Sie die Anzahl der Zeilen, die die Datenbank aggregieren muss. Dadurch bleiben Abfragen auch bei großen Faktentabellen schnell.

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Dicing: Mehrere Dimensionen filtern

Dicing bedeutet, gleichzeitig Filter auf zwei oder mehr Dimensionen anzuwenden – beispielsweise Verkäufe von Elektronik in der Region Nord während des ersten Quartals zu betrachten. Jede zusätzliche WHERE-Bedingung grenzt einen kleineren Datenwürfel ab.

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

Roll-up: Auf eine höhere Granularität aggregieren

Roll-up bedeutet, von einer detaillierten Granularität (tägliche Verkäufe pro Filiale) zu einer gröberen Granularität (monatliche Verkäufe pro Region) überzugehen. Dazu entfernen Sie untergeordnete GROUP-BY-Spalten und aggregieren die Daten erneut.

Mit dem ROLLUP-Zusatz können Sie Zwischensummen und Gesamtsummen in einer einzigen Abfrage erzeugen, statt mehrere UNION-ALL-Blöcke zu schreiben.

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date  d ON d.date_id  = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

Periodenvergleiche mit LAG

Ein besonders häufiges Muster bei analytischen Abfragen ist der Vergleich einer Kennzahl mit derselben Kennzahl aus einer früheren Periode. Mit der Fensterfunktion LAG() können Sie den Wert der vorherigen Zeile direkt in die aktuelle Zeile übernehmen, ohne einen Self-Join zu verwenden.

Hier berechnen wir das Umsatzwachstum gegenüber dem Vormonat als Prozentsatz.

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

Laufende Summen mit SUM OVER

Eine laufende Summe (kumulative Summe) addiert den Wert jeder Zeile zur Summe aller vorherigen Zeilen in einer festgelegten Reihenfolge. Das eignet sich ideal, um den kumulierten Umsatz im Jahresverlauf zu verfolgen oder den Budgetverbrauch zu überwachen.

Die Frame-Klausel ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW macht das Fenster explizit und eindeutig.

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Dimensionen mit DENSE_RANK ordnen

Mit Rangfolgen können Sie die besten oder schlechtesten Ergebnisse innerhalb einer Gruppe ermitteln. DENSE_RANK() vergibt bei Gleichständen fortlaufende Ränge ohne Lücken und ist daher die bevorzugte Wahl für Ranglisten in BI-Berichten.

Wenn Sie das Ergebnis mit Rangfolge in einen CTE einschließen und nach dem Rang filtern, wird das Top-N-Muster übersichtlich und gut lesbar.

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

Beitragsprozentsatz mit einer Fensterfunktion

Der absolute Umsatz eines Produkts ist nützlich. Aussagekräftiger ist jedoch beispielsweise die Information, dass es 38 % des Kategorieumsatzes ausmacht. Eine Fensterfunktion mit SUM() über die gesamte Partition liefert den Nenner, ohne dass eine Unterabfrage verknüpft werden muss.

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

Gleitende Durchschnitte zur Glättung von Trends

Tägliche oder wöchentliche Verkaufszahlen sind unruhig. Ein gleitender Durchschnitt glättet kurzfristige Schwankungen, sodass der zugrunde liegende Trend sichtbar wird. Hier wird ein gleitender 3-Monats-Durchschnitt mithilfe eines gleitenden Fensterrahmens berechnet.

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

CUBE für alle Dimensionskombinationen

CUBE erweitert ROLLUP, indem Zwischensummen für jede mögliche Kombination der aufgeführten Dimensionen berechnet werden – nicht nur entlang des hierarchischen Roll-up-Pfads. Dadurch entsteht die vollständige dimensionsübergreifende Zusammenfassung in einem Durchlauf. Das ist nützlich für multidimensionale Dashboards, in denen Benutzer frei zwischen Perspektiven wechseln können.

NULL in einer Gruppierungsspalte bedeutet alle Werte dieser Dimension. Verwenden Sie GROUPING(), um beabsichtigte NULL-Werte in den Daten von NULL-Werten aus dem Roll-up zu unterscheiden.

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

Welche Operation beschränkt die Ergebnisse auf den Wert einer einzelnen Dimension?

Testen Sie Ihr Verständnis der in Data-Warehousing verwendeten Terminologie für analytische Abfragen.

Zusammenfassung: Analytische Abfragen schreiben

In dieser Lektion haben Sie die wichtigsten Muster zum Schreiben analytischer Abfragen für ein Star-Schema kennengelernt:

  • Slice – mit WHERE eine Dimension filtern, um sich auf ein bestimmtes Segment zu konzentrieren.
  • Dice – mehrere Dimensionen gleichzeitig filtern, um einen genau abgegrenzten Datenwürfel zu bilden.
  • Roll-up – auf eine gröbere Granularität aggregieren; ROLLUP oder CUBE für mehrstufige Zwischensummen verwenden.
  • LAG / LEAD – Periodenvergleiche ohne Self-Joins.
  • Laufende Summen & gleitende Durchschnitte – kumulative und geglättete Kennzahlen mithilfe von Fensterrahmen.
  • DENSE_RANK – übersichtliche Top-N-Rangfolgen innerhalb von Partitionen.
  • Beitragsprozentsatz – SUM über ein Fenster als Nenner für Anteilsberechnungen.

Mit diesen Mustern decken Sie die große Mehrheit der BI- und Reporting-Anforderungen ab, denen Sie in produktiven Data-Warehouses begegnen werden.

Häufig gestellte Fragen

Ist die Lektion „Analytische Abfragen schreiben“ kostenlos?

Ja — der vollständige Text von „Analytische Abfragen schreiben“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Analytische Abfragen schreiben“?

Schneiden Sie Metriken auf, analysieren und aggregieren Sie sie Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Academy zu starten?

Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 4 von 4.

Wie lange dauert die Lektion „Analytische Abfragen schreiben“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?

Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. OLTP vs. OLAP
  2. Fakten- und Dimensionstabellen
  3. Stern- und Schneeflockenschemata
  4. Analytische Abfragen schreiben
← Zurück zu SQL Academy