PIVOT in BigQuery

Zeilen in Spalten umwandeln

Manchmal möchten Sie vielleicht ein Tabellenergebnis neu formatieren, damit Sie separate Spalten für jeden eindeutigen Wert haben. Dies wird als Pivot-Tabelle bezeichnet – normalerweise ist es nur eine Anzeigefunktion, die von BI-Tools unterstützt wird. Es kann jedoch gelegentlich hilfreich sein, die Pivot-Tabelle in SQL zu erstellen. So verwenden Sie den PIVOT-Operator in Google BigQuery, um eine Pivot-Tabelle zu erstellen.

Bild von Gerd Altmann von Pixabay

Was macht Pivot?

Pivot wandelt Zeilen in Spalten um. Angenommen, wir haben eine Tabelle mit Flügen und Flugverspätungen wie diese:

Und wir wollen eine Ausgabe, in der wir die durchschnittliche Abflugverspätung für jedes Flughafen-Airline-Paar erhalten, wobei die Airline-Codes Spalten sind:

Wir würden einen PIVOT machen. Beachten Sie, dass wir einfach Folgendes tun könnten, wenn wir einfach die durchschnittliche Abfahrtsverspätung wollen und damit einverstanden sind, dass sich die Durchschnittswerte in verschiedenen Zeilen befinden:

SELECT 
  airline,
  departure_airport,
  AVG(departure_delay)
FROM `bigquery-samples.airline_ontime_data.flights`
GROUP BY 1, 2

Es ist die Tatsache, dass wir es als 2D-Tabelle benötigen, die einen PIVOT erfordert.

Syntax von PIVOT

Für den Pivot-Operator in BigQuery müssen Sie drei Dinge angeben:

  • from_item , das als Eingabe fungiert. Die drei Spalten (Fluggesellschaft, Abflug_Flughafen, Abflug_Verspätung) aus der Flights-Tabelle sind unser from_item.
  • aggregieren , da jede Zelle der Ausgabetabelle aus mehreren Werten besteht. Hier, das ist der AVG der Abfahrtsverzögerung
  • pivot_column , die Spalte, deren Werte die Spalten in der Ausgabetabelle bilden. Leider muss dies eine Konstante sein – Sie können dort keine Abfrage eingeben.

Flugtabelle PIVOT

Wir wollen Abflugverspätungen von Flughafen und Airline. Die Pivot-Abfrage lautet:

SELECT * FROM
(
  -- #1 from_item
  SELECT 
    airline,
    departure_airport,
    departure_delay
  FROM `bigquery-samples.airline_ontime_data.flights`
)
PIVOT
(
  -- #2 aggregate
  AVG(departure_delay)
  -- #3 pivot_column
  FOR airline in ('AA', 'KH', 'DL', 'AS', 'UA')
)

Ergebnis:

Stapelüberlauf PIVOT

Lassen Sie uns einen Pivot der Anzahl der Fragen mit einem bestimmten Tag nach Jahr erstellen

SELECT
    tag,
    EXTRACT(YEAR from creation_date) AS year,
    COUNT(*) AS n
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`,
    UNNEST(SPLIT(tags, '|')) AS tag
  WHERE tags IS NOT null
  GROUP BY 1, 2

      

SELECT * FROM 
(
  -- #1 from_item
  SELECT
    tag,
    EXTRACT(YEAR from creation_date) AS year
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`,
    UNNEST(SPLIT(tags, '|')) AS tag
  WHERE tags IS NOT null
)
PIVOT
(
  -- #2 aggregate
  COUNT(*) AS n
  -- #3 pivot_column
  FOR year in (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
)

      

Was ist, wenn Sie nicht alle möglichen Werte oder die Pivot-Spalte im Voraus auflisten können? Eine Lösung ist die Verwendung von Skripten. Hier erstelle ich eine Variable namens Airlines, die den String im richtigen Format ausgibt (probieren Sie es aus):

DECLARE airlines STRING;
SET airlines = (
  SELECT 
    CONCAT('("', STRING_AGG(DISTINCT airline, '", "'), '")'),
  FROM `bigquery-samples.airline_ontime_data.flights`
  WHERE airline != '9E'
);

EXECUTE IMMEDIATE format("""
SELECT * FROM
(
  SELECT 
    airline,
    departure_airport,
    departure_delay
  FROM `bigquery-samples.airline_ontime_data.flights`
)
PIVOT
(
  AVG(departure_delay)
  FOR airline in %s
)
ORDER BY departure_airport ASC
""", airlines);

Suggested posts

SQL-Grundlagen: Alles, was Sie wissen müssen, in einem kurzen Handbuch

SQL-Grundlagen: Alles, was Sie wissen müssen, in einem kurzen Handbuch

SQL oder Structured Query Language ist ein wesentliches Werkzeug zum Verwalten und Manipulieren von Daten in relationalen Datenbanken. Mit seiner großen Auswahl an Befehlen ermöglicht Ihnen SQL, Datenbanken und Tabellen anzuzeigen, zu erstellen und zu löschen, Daten zu ändern und Daten mit verschiedenen Befehlen auszuwählen.

Grundlagen der Advanced Tech.

Grundlagen der Advanced Tech.

Das Technologiefeld ist in den letzten Jahren sprunghaft gewachsen, und eine der aufregendsten Entwicklungen ist das Aufkommen der künstlichen Intelligenz (KI) und ihres Abkömmlings, der generativen KI. Aber was genau steckt in diesen Spitzentechnologien, die sie so fortschrittlich machen? Werfen wir heute einen genaueren Blick auf einige grundlegende technische Grundlagen, die diesen Innovationen zugrunde liegen.

Related posts

Dieses Bedrohungsarchiv: Band 7 Black Basta

Dieses Bedrohungsarchiv: Band 7 Black Basta

In der heutigen vernetzten Welt ist die Bedrohung durch Cyberangriffe größer denn je. Cyberkriminelle und nationalstaatliche Akteure versuchen ständig, Schwachstellen auszunutzen und wertvolle Daten zu stehlen, was ein erhebliches Risiko für Unternehmen jeder Größe darstellt.

Der Soloauftritt des rothaarigen Mädchens

Im Auftrag von Hayden filmt Katie sich selbst beim Masturbieren.

Der Soloauftritt des rothaarigen Mädchens

Es war nach Mitternacht, als Hayden Katie nach ihrem gemeinsamen Abend zurück in ihren Studentenwohnheim fuhr. Sie hatte morgens einen frühen Unterricht und hatte keine Kleidung mehr zum Wechseln, nachdem sie die Nacht zuvor bei ihm übernachtet hatte.

Sammeln von OSINT für die Bedrohungssuche

Sammeln von OSINT für die Bedrohungssuche

Hallo, Cyber-Enthusiasten! Willkommen zurück bei der OSINT-Serie zur Bedrohungsjagd. Im ersten Artikel habe ich Ihnen einen Überblick über OSINT und seine Bedeutung bei der Bedrohungsjagd gegeben.

Der Mord an Jordan Neely und das Problem des „patriotischen“ Rassismus

Können die USA den entscheidenden Schritt tun, rassistisches Verhalten nicht mit der Identität eines guten Amerikaners gleichzusetzen?

Der Mord an Jordan Neely und das Problem des „patriotischen“ Rassismus

[Alarm auslösen: Dieser Aufsatz enthält Screenshots von Menschen, die den Mörder von Jordan Neely loben.] Polizeibeamte haben endlich – und vielleicht widerstrebend – Daniel Penny, den Mann, der Jordan Neely bis zu seinem Tod gewürgt hat, wegen dieses Mordes angeklagt.