PIVOT w BigQuery

Konwertowanie wierszy na kolumny

Czasami możesz chcieć przeformatować wynik w tabeli, aby mieć oddzielne kolumny dla każdej unikalnej wartości. Nazywa się to tabelą przestawną - zwykle jest to tylko funkcja wyświetlania obsługiwana przez narzędzia BI. Jednak czasami pomocne może być utworzenie tabeli przestawnej w języku SQL. Oto jak użyć operatora PIVOT w Google BigQuery do utworzenia tabeli przestawnej.

Obraz Gerd Altmann z Pixabay

Co robi Pivot?

Pivot zmienia wiersze w kolumny. Na przykład załóżmy, że mamy tabelę lotów i opóźnień lotów, taką jak ta:

Potrzebujemy danych wyjściowych, w których otrzymamy średnie opóźnienie odlotów dla każdej pary lotnisko-linia lotnicza, gdzie kody linii lotniczych są kolumnami:

Zrobilibyśmy PIVOT. Zwróć uwagę, że jeśli po prostu chcemy średniego opóźnienia odlotu i nie przeszkadza nam to, że średnie znajdują się w różnych rzędach, możemy po prostu zrobić:

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

Chodzi o to, że musimy go rozłożyć jako stół 2D, który wymaga PIVOT.

Składnia PIVOT

Operator Pivot w BigQuery potrzebuje określić trzy rzeczy:

  • from_item, który działa jako wejście. Trzy kolumny (linie lotnicze, lotnisko_wylotu, opóźnienie_lotu) w tabeli lotów to nasza pozycja from_item.
  • agregować, ponieważ każda komórka tabeli wyjściowej składa się z wielu wartości. Tutaj jest to wartość AVG dla parametru exit_delay
  • pivot_column , kolumna, której wartości tworzą kolumny w tabeli wyjściowej. Niestety musi to być stała - nie można tam umieścić zapytania.

Tabela lotów PIVOT

Chcemy opóźnień odlotów przez lotniska i linie lotnicze. Zapytanie przestawne to:

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')
)

Wynik:

PIVOT przepełnienia stosu

Stwórzmy oś liczby pytań z danym tagiem według roku

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)
)

      

Co jeśli nie możesz wcześniej wymienić wszystkich możliwych wartości lub kolumny przestawnej? Jednym z rozwiązań jest użycie skryptów. Tutaj tworzę zmienną o nazwie linie lotnicze, która wypisuje ciąg w odpowiednim formacie (wypróbuj):

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

Podstawy SQL: wszystko, co musisz wiedzieć w jednym krótkim przewodniku

Podstawy SQL: wszystko, co musisz wiedzieć w jednym krótkim przewodniku

SQL, czyli Structured Query Language, jest niezbędnym narzędziem do zarządzania i manipulowania danymi w relacyjnych bazach danych. Dzięki szerokiej gamie poleceń SQL umożliwia przeglądanie, tworzenie i usuwanie baz danych i tabel, modyfikowanie danych i wybieranie danych za pomocą różnych poleceń.

Podstawy zaawansowanych technologii.

Podstawy zaawansowanych technologii.

Dziedzina technologii rozwinęła się skokowo w ostatnich latach, a jednym z najbardziej ekscytujących wydarzeń jest pojawienie się sztucznej inteligencji (AI) i jej potomka, generatywnej sztucznej inteligencji. Ale co dokładnie kryje się w tych najnowocześniejszych technologiach, które czynią je tak zaawansowanymi? Dziś przyjrzyjmy się bliżej niektórym podstawowym podstawom technologicznym leżącym u podstaw tych innowacji.

Related posts

Nauka w tygodniu pracy ma większe znaczenie niż myślisz

Nauka w tygodniu pracy ma większe znaczenie niż myślisz

Często mamy trudności z wdrożeniem tego, co wiemy, że jest dla nas dobre. Od mówienia drugiej osobie, że ją kochamy, po pamiętanie, by mówić jak pirat, zawsze znajdą się tacy, którzy twierdzą, że dzień, tydzień lub miesiąc wydarzeń nie jest potrzebny, ponieważ już to robią.

Koniec z hasłami — w jaki sposób klucze dostępu zastąpią Twoje hasło

Koniec z hasłami — w jaki sposób klucze dostępu zastąpią Twoje hasło

Aby uniknąć niejasności, zostaną użyte następujące terminy; uwierzytelniający; Telefon komórkowy używany do tworzenia Witryny logowania; Witryna internetowa lub aplikacja, z której korzysta użytkownik, aby dokonać żądania logowania do Usługi; Sprzedawca świadczący daną usługę np

Rewolucja w miejscu pracy: najlepsi eksperci omawiają pracę hybrydową i zdalną

Rewolucja w miejscu pracy: najlepsi eksperci omawiają pracę hybrydową i zdalną

W dzisiejszym szybko zmieniającym się krajobrazie biznesowym koncepcja pracy hybrydowej i zdalnej nabrała bezprecedensowego rozpędu. Ponieważ firmy wciąż dostosowują się do zmieniających się potrzeb i oczekiwań swoich pracowników, eksperci z różnych dziedzin zebrali się, aby omówić korzyści, wyzwania i przyszłość tego transformacyjnego sposobu pracy.

Prześladowania chrześcijan: przestępstwo z nienawiści, o którym nie mówimy

Zapomnij o islamofobii: Chrześcijanie (i Żydzi) są głównymi ofiarami przestępstw z nienawiści na tle religijnym.

Prześladowania chrześcijan: przestępstwo z nienawiści, o którym nie mówimy

Każdy racjonalnie myślący wie, że w kręgach politycznych i medialnych dominuje lewica, nawet skrajna lewica, co powoduje zachwianie równowagi w dyskursie politycznym. W dziedzinie religii ta nierównowaga jest wyraźnie widoczna: lewicowe gazety i sieci nie spieszą się z obroną wiary chrześcijańskiej.