Wie kann ich Daten mithilfe der Tabellenfunktion von PostgreSQL effektiv in mehreren Spalten positionieren?

Verwenden Sie Tablefunc für mehrspaltige Pivot-Daten

Bei der Verarbeitung von Daten, die mehrere Attribute und Kennzahlen enthalten, kann es für eine effiziente Analyse erforderlich sein, sie vom Langformat in das Breitformat zu konvertieren. Die Tablefunc-Funktionalität von PostgreSQL bietet eine praktische Lösung für solche Konvertierungen. Es ist jedoch wichtig, die Einschränkungen bei der Arbeit mit mehreren Pivotspalten zu verstehen.

In einer Antwort auf eine frühere Anfrage bat ein Benutzer um Anleitung zur Verwendung von tablefunc für die Pivotierung, stieß jedoch bei der Arbeit mit mehreren Pivotspalten auf Probleme. Da tablefunc konsistente zusätzliche Spalten für jeden Zeilennamen erwartet, führt die ursprüngliche Abfrage zu unvollständigen Daten.

Problem gelöst

Um dieses Problem zu beheben, halten Sie sich unbedingt an die von tablefunc angegebene Reihenfolge:

1. Zeilenname: Diese Spalte muss immer an erster Stelle stehen.
2. Zusätzliche Spalten (optional): Alle zusätzlichen Spalten sollten bei Bedarf nach der Spalte mit dem Zeilennamen stehen.
3. Kategorie und Wert (letzte zwei Spalten): Die Spalten „Pivot-Kategorie“ und „Wert“ müssen in dieser Reihenfolge wie die letzten beiden Spalten vorliegen.

Implementierung

Im gegebenen Beispiel erfordert die gewünschte Ausgabe die Pivotierung auf zwei Spalten (Entität und Status). Dazu wurde die Abfrage wie folgt geändert:

<code class="language-sql">SELECT *
FROM crosstab(
   'SELECT entity, timeof, status, ct
    FROM t4
    ORDER BY 1'
 , 'VALUES (1), (0)'
   ) AS ct (
      "Attribute" character
    , "Section" timestamp
    , "status_1" int
    , "status_0" int
      );</code>

Durch die Verwendung von „entity“ als Zeilennamen und das Vertauschen der Reihenfolge von „timeof“ und „entity“ wird die Abfrage erfolgreich auf mehrere Spalten umgestellt.

Variationen mit unterschiedlichen Einstellungen

Für das in der Antwort erwähnte Setup, bei dem die Daten nach „localt“ und „entity“ sortiert sind, lautet die geänderte Abfrage wie folgt:

<code class="language-sql">SELECT localt, entity
     , msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05  -- , more?
FROM crosstab(
  'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name
        , localt, entity -- additional columns
        , msrmnt, val
   FROM test
-- WHERE  ???   -- instead of LIMIT at the end
   ORDER BY localt, entity, msrmnt
-- LIMIT ???'   -- instead of LIMIT at the end
, 'SELECT generate_series(1,5)'  -- more?
   ) AS ct (row_name int, localt timestamp, entity int
          , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
            )
LIMIT 1000  -- ?!</code>

Diese Abfrage verwendet dense_rank(), um Proxy-Zeilennamen zu generieren, und enthält eine optionale WHERE-Klausel, um die Daten vor der Verarbeitung zu filtern. Darüber hinaus wurde die LIMIT-Bedingung aus der Unterabfrage entfernt, um die Leistung zu verbessern, indem nur die erforderlichen Zeilen verarbeitet werden.

Fazit

Wenn Sie die Einschränkungen verstehen und die von tablefunc angegebene Reihenfolge einhalten, können Sie auch bei großen Datensätzen effektiv auf mehreren Spalten schwenken. Denken Sie daran, Abfragen durch die Verwendung geeigneter WHERE-Klauseln oder LIMIT-Bedingungen zu optimieren, um unnötige Verarbeitung zu vermeiden.

Das obige ist der detaillierte Inhalt vonWie kann ich Daten mithilfe der Tabellenfunktion von PostgreSQL effektiv in mehreren Spalten positionieren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!