Wie kann man in BigQuery Zeilen ohne native Pivot-Funktionalität in Spalten umwandeln?

Zeilen in Spalten in BigQuery transponieren (Pivot-Implementierung)

In diesem BigQuery-Tutorial wollen wir einen vorherrschenden Bedarf an Datentransformation ansprechen: Konvertieren von Zeilen in Spalten unter Beibehaltung von Schlüssel-Wert-Paaren. Diese als Pivoting bekannte Technik wird häufig in der Datenanalyse und Berichterstattung eingesetzt. Im Gegensatz zu herkömmlichen Datenbanksystemen, die Pivot-Operationen nativ unterstützen, fehlt BigQuery jedoch derzeit diese Funktionalität.

Pivotieren mit einer zusätzlichen Gruppierungsspalte

Um diese Einschränkung zu überwinden, stellen wir eine vor Workaround-Lösung, die eine zusätzliche Spalte in den Eingabedaten beinhaltet, die dazu dient, Zeilen zu gruppieren, die in der Ausgabe zu einer einzigen Zeile zusammengeführt werden sollen. So funktioniert der Ansatz:

Schritt 1: Konstruieren der Pivot-Abfrage

Wir beginnen mit der Erstellung einer Abfrage, die die eigentliche Pivot-Abfrage generiert. Diese Abfrage erstellt dynamisch den Code, der zum Durchführen des Pivotierungsvorgangs basierend auf dem Eingabedatensatz erforderlich ist.

SELECT 'SELECT id, ' + 
   GROUP_CONCAT_UNQUOTED(
      'MAX(IF(key = "' + key + '", value, NULL)) as [' + key + ']'
   ) 
   + ' FROM yourTable GROUP BY id ORDER BY id'
FROM (
  SELECT key 
  FROM yourTable
  GROUP BY key
  ORDER BY key
) 

Diese Abfrage identifiziert alle eindeutigen Schlüsselwerte in den Eingabedaten und generiert eine Zeichenfolge, die bei Ausführung als separate Abfrage angezeigt wird , führt den Pivot-Vorgang aus.

Schritt 2: Führen Sie den generierten Pivot aus Abfrage

Das Ergebnis der ersten Abfrage ist eine Zeichenfolge, die die eigentliche Pivot-Abfrage definiert. Wir müssen lediglich diese Zeichenfolge kopieren und als reguläre BigQuery-Abfrage ausführen. Das Ergebnis ist ein transponierter Datensatz mit den Schlüsseln als Spaltennamen und den Werten als Spaltenwerten.

Beispieleingabe und -ausgabe

Betrachten Sie die folgende Eingabetabelle:

id	Key	Value
1	channel_title	Mahendra Guru
1	youtube_id	ugEGMG4-MdA
1	channel_id	UCiDKcjKocimAO1tV
1	examId	72975611-4a5e-11e5
1	postId	1189e340-b08f
2	channel_title	Ab Live
2	youtube_id	3TNbtTwLY0U
2	channel_id	UCODeKM_D6JLf8jJt
2	examId	72975611-4a5e-11e5
2	postId	0c3e6590-afeb

Der resultierende Pivotdatensatz würde wie folgt aussehen:

id	channel_id	channel_title	examId	postId	youtube_id
1	UCiDKcjKocimAO1tV	Mahendra Guru	72975611-4a5e-11e5	1189e340-b08f	ugEGMG4-MdA
2	UCODeKM_D6JLf8jJt	Ab Live	72975611-4a5e-11e5	0c3e6590-afeb	3TNbtTwLY0U

Einschränkungen und Alternativen

Während dieser Ansatz das Wechselverhalten effektiv nachahmt, bringt er auch Einschränkungen mit sich. Vor allem gibt BigQuery ein Limit von 10.000 Spalten pro Tabelle vor, was die Pivotierung großer Datensätze mit zahlreichen eindeutigen Schlüsseln behindern kann. Erwägen Sie in solchen Szenarien die Erkundung alternativer Lösungen wie externes Pivotieren mit Python oder R oder die Verwendung eines föderierten Abfrageansatzes mit einem Datenbanksystem, das Pivotieren nativ unterstützt.

Das obige ist der detaillierte Inhalt vonWie kann man in BigQuery Zeilen ohne native Pivot-Funktionalität in Spalten umwandeln?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!