120 lines
4.2 KiB
SQL
120 lines
4.2 KiB
SQL
-- ============================================================================
|
|
-- 🗄️ SQL 12: WINDOW FUNCTIONS (OVER, PARTITION BY, RANK, LAG) 🗄️
|
|
-- ============================================================================
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 1. TABELLEN-SCHEMA & TESTDATEN
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS artikel (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
kategorie_id INTEGER NOT NULL,
|
|
preis REAL NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tages_umsatz (
|
|
datum TEXT PRIMARY KEY,
|
|
umsatz REAL NOT NULL,
|
|
bestellungen INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS transaktionen (
|
|
id INTEGER PRIMARY KEY,
|
|
kunde_id INTEGER NOT NULL,
|
|
datum TEXT NOT NULL,
|
|
betrag REAL NOT NULL
|
|
);
|
|
|
|
-- Testdaten
|
|
INSERT INTO artikel (id, name, kategorie_id, preis) VALUES
|
|
(1, 'Laptop Gaming X', 1, 1499.00),
|
|
(2, 'Laptop Business Ultrabook', 1, 1499.00),
|
|
(3, 'Laptop Student Basic', 1, 699.00),
|
|
(4, 'Tastatur Mechanisch', 2, 129.00),
|
|
(5, 'Tastatur Membran', 2, 39.00),
|
|
(6, 'Maus RGB Gaming', 2, 69.00),
|
|
(7, 'Maus Ergonomisch', 2, 69.00),
|
|
(8, 'Maus Standard Office', 2, 19.00);
|
|
|
|
INSERT INTO tages_umsatz (datum, umsatz, bestellungen) VALUES
|
|
('2024-03-01', 1200.00, 15),
|
|
('2024-03-02', 1800.00, 22),
|
|
('2024-03-03', 1500.00, 19),
|
|
('2024-03-04', 2100.00, 26),
|
|
('2024-03-05', 2400.00, 30),
|
|
('2024-03-06', 1900.00, 24),
|
|
('2024-03-07', 2800.00, 35);
|
|
|
|
INSERT INTO transaktionen (id, kunde_id, datum, betrag) VALUES
|
|
(1, 101, '2024-01-05', 150.00),
|
|
(2, 101, '2024-01-20', 300.00),
|
|
(3, 101, '2024-02-10', 450.00),
|
|
(4, 102, '2024-01-08', 500.00),
|
|
(5, 102, '2024-02-14', 250.00),
|
|
(6, 103, '2024-01-12', 120.00),
|
|
(7, 103, '2024-01-19', 80.00),
|
|
(8, 103, '2024-02-25', 400.00);
|
|
|
|
|
|
-- ============================================================================
|
|
-- 🎯 AUFGABEN
|
|
-- ============================================================================
|
|
|
|
-- 🎯 TODO 1: Ranking-Vergleich (ROW_NUMBER vs. RANK vs. DENSE_RANK)
|
|
-- Berechne für alle Artikel eine Rangliste innerhalb ihrer kategorie_id,
|
|
-- sortiert nach dem Preis absteigend (teuerstes Produkt zuerst).
|
|
-- Gib folgende Spalten aus:
|
|
-- - id, name, kategorie_id, preis
|
|
-- - row_num: ROW_NUMBER() OVER (PARTITION BY kategorie_id ORDER BY preis DESC)
|
|
-- - preis_rank: RANK() OVER (PARTITION BY kategorie_id ORDER BY preis DESC)
|
|
-- - dense_rank: DENSE_RANK() OVER (PARTITION BY kategorie_id ORDER BY preis DESC)
|
|
-- Sortiere das Gesamtergebnis nach kategorie_id ASC, row_num ASC.
|
|
SELECT
|
|
id,
|
|
name,
|
|
kategorie_id,
|
|
preis
|
|
-- TODO: Window-Funktionen ergänzen
|
|
FROM artikel;
|
|
|
|
|
|
-- 🎯 TODO 2: Time-Series Analyse mit LAG (Wachstumsraten & Delta)
|
|
-- Analysiere die täglichen Umsätze in tages_umsatz:
|
|
-- - datum, umsatz
|
|
-- - vortag_umsatz: Umsatz des Vortages mit LAG(umsatz, 1, 0) OVER (ORDER BY datum)
|
|
-- - delta_umsatz: Differenz zum Vortag (umsatz - LAG(umsatz, 1, umsatz) OVER (ORDER BY datum))
|
|
-- - wachstum_prozent: Prozentuales Wachstum zum Vortag gerundet auf 2 Stellen:
|
|
-- ROUND((umsatz - LAG(umsatz, 1, umsatz) OVER (ORDER BY datum)) * 100.0 / LAG(umsatz, 1, umsatz) OVER (ORDER BY datum), 2)
|
|
-- Sortiere chronologisch nach datum ASC.
|
|
SELECT
|
|
datum,
|
|
umsatz
|
|
-- TODO: LAG Berechnungen ergänzen
|
|
FROM tages_umsatz;
|
|
|
|
|
|
-- 🎯 TODO 3: Running Total (Kumulierter Kunden-Umsatz)
|
|
-- Berechne für jede Transaktion den fortlaufenden Gesamtumsatz des jeweiligen Kunden.
|
|
-- Spalten:
|
|
-- - id, kunde_id, datum, betrag
|
|
-- - laufender_umsatz: SUM(betrag) OVER (PARTITION BY kunde_id ORDER BY datum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
-- Sortiere nach kunde_id ASC, datum ASC.
|
|
SELECT
|
|
id,
|
|
kunde_id,
|
|
datum,
|
|
betrag
|
|
-- TODO: Running Total Window Function ergänzen
|
|
FROM transaktionen;
|
|
|
|
|
|
-- 🎯 TODO 4: Gleitender 3-Tage Durchschnitt (Moving Average)
|
|
-- Berechne über die Tabelle tages_umsatz einen 3-Tage-Durchschnitt:
|
|
-- - datum, umsatz
|
|
-- - ma_3_tage: ROUND(AVG(umsatz) OVER (ORDER BY datum ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)
|
|
-- Sortiere nach datum ASC.
|
|
SELECT
|
|
datum,
|
|
umsatz
|
|
-- TODO: Moving Average Window Function ergänzen
|
|
FROM tages_umsatz;
|