PIVOT dans BigQuery

Conversion de lignes en colonnes

Parfois, vous souhaiterez peut-être reformater un résultat de table afin d'avoir des colonnes séparées pour chaque valeur unique. Cela s'appelle un tableau croisé dynamique - normalement, il ne s'agit que d'une fonction d'affichage prise en charge par les outils BI. Cependant, il peut parfois être utile de créer le tableau croisé dynamique dans SQL. Voici comment utiliser l'opérateur PIVOT dans Google BigQuery pour créer un tableau croisé dynamique.

Image par Gerd Altmann de Pixabay

Que fait Pivot?

Pivot change les lignes en colonnes. Par exemple, supposons que nous ayons un tableau des vols et des retards de vol comme celui-ci:

Et nous voulons une sortie où nous obtenons le retard de départ moyen pour chaque paire aéroport-compagnie aérienne, où les codes de compagnie aérienne sont des colonnes:

Nous ferions un PIVOT. Notez que si nous voulons simplement le retard de départ moyen et que nous acceptons que les moyennes soient sur des lignes différentes, nous pourrions simplement faire:

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

C'est le fait que nous en avons besoin sous forme de tableau 2D qui nécessite un PIVOT.

Syntaxe de PIVOT

L' opérateur Pivot dans BigQuery vous demande de spécifier trois choses:

  • from_item qui fonctionne comme entrée. Les trois colonnes (compagnie aérienne, aéroport de départ, délai de départ) de la table des vols sont notre élément from_item.
  • agréger puisque chaque cellule du tableau de sortie se compose de plusieurs valeurs. Ici, c'est l'AVG du depart_delay
  • pivot_column , la colonne dont les valeurs forment les colonnes de la table de sortie. Malheureusement, cela doit être une constante - vous ne pouvez pas y mettre de requête.

Tableau des vols PIVOT

Nous voulons des retards de départ par aéroport et par compagnie aérienne. La requête pivot est:

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

Résultat:

PIVOT de débordement de pile

Créons un pivot du nombre de questions avec une balise donnée par année

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

      

Que faire si vous ne pouvez pas lister toutes les valeurs possibles ou la colonne pivot au préalable? Une solution consiste à utiliser des scripts. Ici, je crée une variable appelée compagnies aériennes avec qui imprime la chaîne dans le bon format (essayez-le):

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

Les bases de SQL : tout ce que vous devez savoir dans un petit guide

Les bases de SQL : tout ce que vous devez savoir dans un petit guide

SQL, ou Structured Query Language, est un outil essentiel pour gérer et manipuler les données dans les bases de données relationnelles. Avec sa large gamme de commandes, SQL vous permet d'afficher, de créer et de supprimer des bases de données et des tables, de modifier des données et de sélectionner des données à l'aide de diverses commandes.

Fondamentaux de la technologie avancée.

Fondamentaux de la technologie avancée.

Le domaine de la technologie s'est développé à pas de géant ces dernières années et l'un des développements les plus passionnants est l'émergence de l'intelligence artificielle (IA) et de sa progéniture, l'IA générative. Mais que se passe-t-il exactement dans ces technologies de pointe qui les rendent si avancées ? Aujourd'hui, examinons de plus près certains principes fondamentaux de la technologie qui sous-tendent ces innovations.

Related posts

3 façons éprouvées d'améliorer vos compétences en écriture

Les avantages de l'écriture et les stratégies pour vous aider à le faire

3 façons éprouvées d'améliorer vos compétences en écriture

En 2022, avec la sortie de GPT-3, la compétence en écriture semble devenir moins importante. Avec l'IA, les personnes ayant une mauvaise écriture peuvent également générer du personnel de qualité.

Plus de mots de passe - comment les clés d'accès remplaceront votre mot de passe

Plus de mots de passe - comment les clés d'accès remplaceront votre mot de passe

Pour éviter toute ambiguïté, les termes suivants seront utilisés ; Authentificateur ; Le téléphone mobile utilisé pour effectuer la connexion au site Web ; Le site Web ou l'application que l'utilisateur utilise pour effectuer la demande de connexion au service ; Le fournisseur fournissant le service donné, par exemple

"La fosse des Mariannes dévoilée : dévoiler les terrifiantes vérités cachées de l'abîme"

"La fosse des Mariannes dévoilée : dévoiler les terrifiantes vérités cachées de l'abîme"

Introduction :Bienvenue, courageux lecteurs, dans un voyage qui nous emmènera dans les profondeurs les plus sombres de notre planète. Dans ce blog, nous plongeons dans le monde énigmatique de la fosse des Mariannes, découvrant ses secrets cachés et découvrant les vérités effrayantes qui se cachent sous sa surface.

La révolution du lieu de travail : les meilleurs experts discutent du travail hybride et à distance

La révolution du lieu de travail : les meilleurs experts discutent du travail hybride et à distance

Dans le paysage commercial en évolution rapide d'aujourd'hui, le concept de travail hybride et à distance a pris un élan sans précédent. Alors que les entreprises continuent de s'adapter à l'évolution des besoins et des attentes de leurs employés, des experts de divers domaines se sont réunis pour discuter des avantages, des défis et de l'avenir de cette façon de travailler transformatrice.