PIVOT no BigQuery

Converter linhas em colunas

Às vezes, você pode querer reformatar o resultado de uma tabela para ter colunas separadas para cada valor exclusivo. Isso é chamado de tabela dinâmica - normalmente, é apenas uma função de exibição suportada por ferramentas de BI. No entanto, ocasionalmente pode ser útil criar a tabela dinâmica em SQL. Veja como usar o operador PIVOT no Google BigQuery para criar uma tabela dinâmica.

Imagem de Gerd Altmann de Pixabay

O que o Pivot faz?

O Pivot transforma linhas em colunas. Por exemplo, suponha que temos uma tabela de voos e atrasos de voos como esta:

E queremos uma saída em que obtenhamos o atraso médio de partida para cada par aeroporto-companhia aérea, onde os códigos da companhia aérea são colunas:

Faríamos um PIVOT. Observe que se quisermos simplesmente o atraso médio de partida e concordarmos com as médias em linhas diferentes, poderíamos simplesmente fazer:

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

É o fato de que precisamos colocá- lo em uma tabela 2D que requer um PIVOT.

Sintaxe de PIVOT

O operador Pivot no BigQuery precisa que você especifique três coisas:

  • from_item que funciona como entrada. As três colunas (companhia aérea, partida_airport, partida_delay) da tabela de voos é nosso from_item.
  • agregado, pois cada célula da tabela de saída consiste em vários valores. Aqui, esse é o AVG do departure_delay
  • pivot_column , a coluna cujos valores formam as colunas na tabela de saída. Infelizmente, isso precisa ser uma constante - você não pode colocar uma consulta lá.

Tabela de voos PIVOT

Queremos atrasos nas partidas do aeroporto e da companhia aérea. A consulta dinâmica é:

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

Resultado:

Stack Overflow PIVOT

Vamos criar um pivô do número de questões com uma determinada tag por ano

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

      

E se você não puder listar todos os valores possíveis ou a coluna dinâmica de antemão? Uma solução é usar scripts. Aqui, eu crio uma variável chamada companhias aéreas com que imprime a string no formato certo (experimente):

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

Noções básicas de SQL: tudo o que você precisa saber em um breve guia

Noções básicas de SQL: tudo o que você precisa saber em um breve guia

SQL, ou Linguagem de Consulta Estruturada, é uma ferramenta essencial para gerenciar e manipular dados em bancos de dados relacionais. Com sua ampla variedade de comandos, o SQL permite visualizar, criar e excluir bancos de dados e tabelas, modificar dados e selecionar dados usando vários comandos.

Fundamentos de Tecnologia Avançada.

Fundamentos de Tecnologia Avançada.

O campo da tecnologia cresceu aos trancos e barrancos nos últimos anos e um dos desenvolvimentos mais empolgantes é o surgimento da Inteligência Artificial (IA) e sua prole, a IA generativa. Mas o que exatamente há nessas tecnologias de ponta que as tornam tão avançadas? Hoje, vamos dar uma olhada em alguns fundamentos tecnológicos básicos que fundamentam essas inovações.

Related posts

VIDA SEM LIMITES

Quando você cresce, tende a ouvir que o mundo é do jeito que é e sua vida é apenas viver sua vida dentro do mundo, nunca arriscando sua vida, apenas siga um caminho definido e morra no final, e tente não bater demais nas paredes. Mas essa é uma vida muito limitada.

Rastreie qualquer pessoa com apenas um número de telefone | Investigação OSINT

Rastreie qualquer pessoa com apenas um número de telefone | Investigação OSINT

Você pode ser um investigador OSINT, jogador CTF ou simplesmente alguém que está recebendo chamadas de spam. Alguém que está tentando verificar o número que você viu em um anúncio.

A perseguição aos cristãos: o crime de ódio do qual não falamos

Esqueça a islamofobia: cristãos (e judeus) são as principais vítimas de crimes de ódio religioso.

A perseguição aos cristãos: o crime de ódio do qual não falamos

Qualquer mente racional sabe que os círculos políticos e midiáticos são dominados pela esquerda, até mesmo pela extrema esquerda, um estado de coisas que cria um desequilíbrio no discurso político. No campo da religião, esse desequilíbrio é bem visível: jornais e redes de esquerda não se apressam em defender a fé cristã.

"A Fossa das Marianas Revelada: Revelando as Terríveis Verdades Ocultas do Abismo"

"A Fossa das Marianas Revelada: Revelando as Terríveis Verdades Ocultas do Abismo"

Introdução:Bem-vindos, corajosos leitores, a uma jornada que nos levará às profundezas mais sombrias do nosso planeta. Neste blog, mergulhamos no mundo enigmático da Fossa das Marianas, revelando seus segredos ocultos e revelando as verdades arrepiantes que se escondem sob sua superfície.