Wie kann ich Daten in SQL mithilfe von crosstab() dynamisch Pivotieren?

Dynamische SQL-Pivot-Tabelle: eine dynamische Alternative mit der Funktion crosstab()

Problembeschreibung:

Angenommen, es gibt eine Tabelle mit Daten im folgenden Format:

id	feh	bar
1	10	A
2	20	A
3	3	B
4	4	B
5	5	C
6	6	D
7	7	D
8	8	D

Ziel ist es, diese Tabelle in ein strukturierteres Format umzuwandeln, in dem jede Zeile eine Kategorie (Balken) darstellt und die entsprechenden Werte (feh) in Spalten mit Spaltennamen wie val1, val2 usw. angeordnet sind. Die gewünschte Ausgabe sieht so aus:

bar	val1	val2	val3
A	10	20	NULL
B	3	4	NULL
C	5	NULL	NULL
D	6	7	8

Die traditionelle Lösung besteht darin, CASE-Anweisungen und GROUP BY-Klauseln zum Pivotieren von Daten zu verwenden. Bei Tabellen mit vielen Kategorien kann dieser Ansatz jedoch sehr ausführlich und unhandlich werden.

Alternative:

Das Tablefunc-Modul bietet eine effizientere und dynamischere Alternative zu herkömmlichen Lösungen. Durch die Verwendung der Funktion crosstab() können wir die gleichen Ergebnisse mit einer einfacheren, besser wartbaren Abfrage erzielen.

Lösung:

Stellen Sie sicher, dass das Tablefunc-Modul installiert ist, und führen Sie den folgenden Befehl einmal pro Datenbank aus:

CREATE EXTENSION tablefunc;

Hier ist eine einfache Kreuztabellenabfrage, die das Problem löst:

SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  --更多列？

In dieser Abfrage:

• Die Funktion crosstab() akzeptiert zwei Parameter: eine Unterabfrage, die die Daten auswählt und jeder Zeile Dummy-Kategoriewerte zuweist, sowie eine Liste von Spaltennamen (in diesem Fall val1, val2 und val3);
Die
• -Unterabfrage wählt die Balken- und Feh-Spalten aus der Originaltabelle aus, verwendet die Fensterfunktion row_number(), um jeder Zeile einen Dummy-Kategoriewert von 1 zuzuweisen, und sortiert die Daten nach Balken und Feh, um die richtige Reihenfolge in der Tabelle sicherzustellen Ausgabe.
• Das Ergebnis der Funktion crosstab() wird einem CTE (allgemeiner Tabellenausdruck) mit dem Namen ct zugewiesen, um die Referenzierung in nachfolgenden SELECT-Anweisungen zu erleichtern.

Dynamische Kreuztabelle:

Dieser grundlegende Ansatz funktioniert zwar gut, funktioniert jedoch möglicherweise nicht gut, wenn die Anzahl der Kategorien (Spalten) im Voraus unbekannt ist. Um dieses Problem zu lösen, können wir eine dynamische Kreuztabellenfunktion definieren, die Spaltennamen basierend auf unterschiedlichen Werten in einer angegebenen Kategoriespalte generiert.

Die folgende Abfrage zeigt, wie dynamische Kreuztabellenfunktionen erstellt und verwendet werden:

-- 创建动态crosstab函数
CREATE FUNCTION dynamic_crosstab(anyarray) RETURNS table AS $$
  DECLARE
    column_names text[];
    column_definitions text[];
    cte_name text;
  BEGIN
    -- 获取类别列的不同值
    column_names := ARRAY(SELECT DISTINCT unnest());
    -- 生成列定义
    column_definitions := ARRAY(SELECT STRING_AGG('"' || name || '" INT', ', ') FROM (SELECT unnest(column_names) AS name) AS subquery);
    cte_name := 'cte_' || md5(random()::text); -- 生成唯一的CTE名称
    EXECUTE FORMAT('CREATE TEMP TABLE %s (%s)', cte_name, column_definitions);
    -- 将数据插入CTE
    INSERT INTO %s SELECT * FROM crosstab();
    -- 返回CTE
    RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s', cte_name);
  END;
$$ LANGUAGE plpgsql;

-- 使用动态crosstab函数
SELECT * FROM dynamic_crosstab(ARRAY['bar']);

Diese überarbeitete Antwort bietet eine detailliertere und genauere Erläuterung der dynamischen SQL-Pivot-Tabellenlösung, einschließlich der Erstellung und Verwendung einer dynamischen Kreuztabellenfunktion. Der Code ist zur besseren Lesbarkeit formatiert.

Das obige ist der detaillierte Inhalt vonWie kann ich Daten in SQL mithilfe von crosstab() dynamisch Pivotieren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!