Trennkriterium für "Obere" und "Untere" in einem Ranking in DuckDB SQL

06 Februar 2025 - Veröffentlicht unter Community & Best Practices von schwarer2006

Um ein Trennkriterium für "Obere" und "Untere" in einem Ranking in DuckDB SQL zu erstellen, können wir eine Kombination aus Fensterfunktionen und bedingter Logik verwenden. Das Ziel ist es, die Daten in zwei Gruppen zu unterteilen: die obere Hälfte und die untere Hälfte des Rankings.

Beispiel:

Angenommen, wir haben eine Tabelle sales mit den Spalten id, salesperson, und sales_amount. Wir möchten die Verkäufer nach ihrem Verkaufsvolumen (sales_amount) ranken und sie dann in die obere und untere Hälfte einteilen.

Schritt 1: Ranking erstellen

Zuerst erstellen wir ein Ranking basierend auf der sales_amount:

WITH ranked_sales AS (
    SELECT
        id,
        salesperson,
        sales_amount,
        RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank,
        COUNT(*) OVER () AS total_salespeople
    FROM
        sales
)

Schritt 2: Trennkriterium anwenden

Nun teilen wir die Verkäufer in die obere und untere Hälfte ein. Wir verwenden dazu die sales_rank und total_salespeople:

SELECT
    id,
    salesperson,
    sales_amount,
    sales_rank,
    CASE
        WHEN sales_rank <= total_salespeople / 2 THEN 'Obere Hälfte'
        ELSE 'Untere Hälfte'
    END AS ranking_group
FROM
    ranked_sales
ORDER BY
    sales_rank;

Vollständiges Beispiel:

-- Tabelle erstellen und Daten einfügen
CREATE TABLE sales (
    id INT,
    salesperson VARCHAR,
    sales_amount DECIMAL(10, 2)
);

INSERT INTO sales (id, salesperson, sales_amount) VALUES
(1, 'Alice', 1500.00),
(2, 'Bob', 2000.00),
(3, 'Charlie', 1200.00),
(4, 'David', 1800.00),
(5, 'Eve', 2200.00),
(6, 'Frank', 1300.00),
(7, 'Grace', 1700.00),
(8, 'Hank', 1900.00),
(9, 'Ivy', 2100.00),
(10, 'Jack', 1400.00);

-- Ranking und Trennung in obere/untere Hälfte
WITH ranked_sales AS (
    SELECT
        id,
        salesperson,
        sales_amount,
        RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank,
        COUNT(*) OVER () AS total_salespeople
    FROM
        sales
)
SELECT
    id,
    salesperson,
    sales_amount,
    sales_rank,
    CASE
        WHEN sales_rank <= total_salespeople / 2 THEN 'Obere Hälfte'
        ELSE 'Untere Hälfte'
    END AS ranking_group
FROM
    ranked_sales
ORDER BY
    sales_rank;

Erklärung:

RANK() OVER (ORDER BY sales_amount DESC): Diese Funktion weist jedem Verkäufer einen Rang basierend auf der sales_amount zu. Der Verkäufer mit dem höchsten Verkaufsvolumen erhält den Rang 1.
COUNT(*) OVER (): Diese Funktion zählt die Gesamtanzahl der Verkäufer in der Tabelle.
CASE-Statement: Hier wird entschieden, ob ein Verkäufer zur oberen oder unteren Hälfte gehört. Wenn der Rang eines Verkäufers kleiner oder gleich der Hälfte der Gesamtanzahl der Verkäufer ist, wird er der "Oberen Hälfte" zugeordnet, andernfalls der "Unteren Hälfte".

Ergebnis:

Das Ergebnis dieser Abfrage wird eine Liste der Verkäufer sein, die nach ihrem Verkaufsvolumen geordnet ist, zusammen mit einer Spalte ranking_group, die angibt, ob sie zur oberen oder unteren Hälfte gehören.

 id | salesperson | sales_amount | sales_rank | ranking_group
----+-------------+--------------+------------+---------------
  5 | Eve         |      2200.00 |          1 | Obere Hälfte
  9 | Ivy         |      2100.00 |          2 | Obere Hälfte
  2 | Bob         |      2000.00 |          3 | Obere Hälfte
  8 | Hank        |      1900.00 |          4 | Obere Hälfte
  4 | David       |      1800.00 |          5 | Obere Hälfte
  7 | Grace       |      1700.00 |          6 | Untere Hälfte
  1 | Alice       |      1500.00 |          7 | Untere Hälfte
 10 | Jack        |      1400.00 |          8 | Untere Hälfte
  6 | Frank       |      1300.00 |          9 | Untere Hälfte
  3 | Charlie     |      1200.00 |         10 | Untere Hälfte

In diesem Beispiel gehören die ersten 5 Verkäufer zur "Oberen Hälfte" und die restlichen 5 zur "Unteren Hälfte".

Erweiterte Idee: Wir werden Datenpunkte basierend auf einer oder mehreren Kennzahlen (Metriken) zu segmentieren. Das Ranking kann durch eine Rangfolge bestimmt werden, wobei die oberen und unteren Segmente durch einen definierten Schnittpunkt getrennt werden.

Schritt-für-Schritt-Ansatz:

Datenmodell definieren: Wir erstellen eine Tabelle mit Produkten, deren Verkaufszahlen und Bewertungen wir analysieren möchten.
Ranking berechnen: Basierend auf der Summe der Verkaufszahlen oder einer anderen Metrik wird ein Ranking erstellt.
Trennkriterium festlegen: Wir teilen die Produkte in "Obere" und "Untere" Gruppen, z.B. durch den Median oder einen bestimmten Prozentsatz (z.B. Top 50%).
Eigenschaften der bevorzugten Kennzahl berücksichtigen: Zusätzlich zur Verkaufszahl können wir auch andere Eigenschaften wie Durchschnittsbewertung oder Preis berücksichtigen.

Beispiel: Produkt-Ranking mit Trennung in Obere/Untere

1. Erstellen der Tabelle

Wir erstellen eine Tabelle produkte mit den folgenden Spalten: - produkt_id: Eindeutige ID des Produkts - produkt_name: Name des Produkts - verkaufszahl: Anzahl verkaufter Einheiten - durchschnittsbewertung: Durchschnittliche Bewertung des Produkts (1 bis 5) - preis: Preis des Produkts

CREATE TABLE produkte (
    produkt_id INTEGER PRIMARY KEY,
    produkt_name VARCHAR,
    verkaufszahl INTEGER,
    durchschnittsbewertung DECIMAL(3, 2),
    preis DECIMAL(10, 2)
);

-- Beispieldaten einfügen
INSERT INTO produkte (produkt_id, produkt_name, verkaufszahl, durchschnittsbewertung, preis) VALUES
(1, 'Produkt A', 100, 4.5, 29.99),
(2, 'Produkt B', 75, 4.2, 19.99),
(3, 'Produkt C', 200, 4.8, 49.99),
(4, 'Produkt D', 50, 3.9, 9.99),
(5, 'Produkt E', 150, 4.6, 39.99),
(6, 'Produkt F', 30, 3.7, 14.99),
(7, 'Produkt G', 80, 4.1, 24.99),
(8, 'Produkt H', 120, 4.3, 34.99);

2. Ranking berechnen

Wir berechnen das Ranking basierend auf der verkaufszahl. Optional können wir zusätzliche Kriterien wie durchschnittsbewertung oder preis hinzufügen.

WITH ranking AS (
    SELECT 
        produkt_id,
        produkt_name,
        verkaufszahl,
        durchschnittsbewertung,
        preis,
        RANK() OVER (ORDER BY verkaufszahl DESC, durchschnittsbewertung DESC) AS rank
    FROM produkte
)
SELECT * FROM ranking;

Erklärung: - RANK() OVER (ORDER BY verkaufszahl DESC, durchschnittsbewertung DESC) ordnet die Produkte nach absteigender Verkaufszahl und bei gleicher Verkaufszahl nach absteigender Bewertung.

3. Trennkriterium festlegen

Wir teilen die Produkte in "Obere" und "Untere" Gruppen. Hier verwenden wir den Median als Trennkriterium. Alternativ könnten wir auch einen festen Prozentsatz (z.B. Top 50%) verwenden.

WITH ranking AS (
    SELECT 
        produkt_id,
        produkt_name,
        verkaufszahl,
        durchschnittsbewertung,
        preis,
        RANK() OVER (ORDER BY verkaufszahl DESC, durchschnittsbewertung DESC) AS rank,
        COUNT(*) OVER () AS total_count -- Gesamtanzahl der Produkte
    FROM produkte
),
median_rank AS (
    SELECT 
        CASE 
            WHEN rank <= (total_count / 2) THEN 'Obere'
            ELSE 'Untere'
        END AS gruppe,
        *
    FROM ranking
)
SELECT * FROM median_rank ORDER BY rank;

Erklärung: - COUNT(*) OVER () AS total_count: Zählt die Gesamtanzahl der Produkte. - CASE WHEN rank <= (total_count / 2) THEN 'Obere' ELSE 'Untere' END: Weist jedem Produkt eine Gruppe ("Obere" oder "Untere") zu, basierend auf seinem Rang im Vergleich zur Hälfte der Gesamtprodukte.

4. Berücksichtigung weiterer Eigenschaften

Um die bevorzugte Kennzahl (z.B. durchschnittsbewertung) zu berücksichtigen, können wir zusätzliche Filter oder Gewichtungen einführen. Zum Beispiel: - Nur Produkte mit einer Bewertung von mindestens 4.0 in der "Obere"-Gruppe aufnehmen. - Produkte mit niedrigerem Preis in der "Untere"-Gruppe priorisieren.

WITH ranking AS (
    SELECT 
        produkt_id,
        produkt_name,
        verkaufszahl,
        durchschnittsbewertung,
        preis,
        RANK() OVER (ORDER BY verkaufszahl DESC, durchschnittsbewertung DESC) AS rank,
        COUNT(*) OVER () AS total_count
    FROM produkte
),
filtered_ranking AS (
    SELECT 
        CASE 
            WHEN rank <= (total_count / 2) AND durchschnittsbewertung >= 4.0 THEN 'Obere'
            WHEN rank > (total_count / 2) OR durchschnittsbewertung < 4.0 THEN 'Untere'
            ELSE NULL
        END AS gruppe,
        *
    FROM ranking
)
SELECT * FROM filtered_ranking WHERE gruppe IS NOT NULL ORDER BY rank;

Erklärung: - durchschnittsbewertung >= 4.0: Filtert Produkte mit einer Bewertung von mindestens 4.0 in der "Obere"-Gruppe. - durchschnittsbewertung < 4.0: Weist Produkte mit einer geringeren Bewertung automatisch der "Untere"-Gruppe zu.

Ergebnis

Das obige Beispiel zeigt, wie man ein Ranking erstellt und Produkte in "Obere" und "Untere" Gruppen aufteilt, wobei verschiedene Kennzahlen wie Verkaufszahl, Bewertung und Preis berücksichtigt werden. Die Abfrage ist flexibel und kann je nach Anforderungen angepasst werden.

Endgültige Antwort:

WITH ranking AS (
    SELECT 
        produkt_id,
        produkt_name,
        verkaufszahl,
        durchschnittsbewertung,
        preis,
        RANK() OVER (ORDER BY verkaufszahl DESC, durchschnittsbewertung DESC) AS rank,
        COUNT(*) OVER () AS total_count
    FROM produkte
),
median_rank AS (
    SELECT 
        CASE 
            WHEN rank <= (total_count / 2) AND durchschnittsbewertung >= 4.0 THEN 'Obere'
            ELSE 'Untere'
        END AS gruppe,
        *
    FROM ranking
)
SELECT * FROM median_rank WHERE gruppe IS NOT NULL ORDER BY rank;

Nächster Voriger