de Lak Lakshmanan

Explorarea unui model SQL puternic: ARRAY_AGG, STRUCT și UNNEST

Poate fi extrem de rentabil (atât în ​​ceea ce privește stocarea, cât și în ceea ce privește timpul de interogare) să folosiți câmpuri imbricate, mai degrabă decât să vă aplatizați toate datele. Câmpurile imbricate și repetate sunt foarte puternice, dar SQL necesar pentru interogarea lor pare puțin necunoscut. Deci, merită să petreceți puțin timp cu STRUCT, UNNEST și ARRAY_AGG. Utilizarea acestor trei în combinație face, de asemenea, unele tipuri de interogări mult, mult mai ușor de scris.

Explorarea unui model SQL puternic ARRAY AGG STRUCT si UNNEST
Un tipar SQL puternic este acela de a face o serie de structuri și apoi de a-l anula

Sarcină

Să luăm un Masă BigQuery de cicloni tropicali. Iată o previzualizare a tabelului:

Explorarea unui model SQL puternic ARRAY AGG STRUCT si UNNEST
Tabel de intrare

Sarcina este de a găsi maximul usa_sshs (mai cunoscut ca „categorie”) la care a ajuns fiecare uragan nord-american (basin=NA) din sezonul 2010 și momentul la care a fost atinsă prima dată categoria. Vreau să pot spune ceva de genul „Uraganul Danielle a ajuns la categoria 4 la ora 18:00 UTC în 2010-08-27 când era la (27.1, -60.1)”.

1611169144 702 Explorarea unui model SQL puternic ARRAY AGG STRUCT si UNNEST
Uraganul Danielle a ajuns la categoria 4 la ora 18:00 UTC în 2010-08-27 când era la (27.1, -60.1)

Iată interogare soluție. În acest articol, îl voi construi bucată cu bucată.

Unde este uraganul?

Primul meu pas a fost să creez o istorie a locațiilor uraganelor. În esență, vreau să ajung la:

1611169144 71 Explorarea unui model SQL puternic ARRAY AGG STRUCT si UNNEST
Istoria fiecărui uragan

Putem filtra după bazin și sezon:

#standardsqlWITH hurricanes AS (SELECT  NAME, iso_time, latitude, longitude, usa_sshsFROM  `bigquery-public-data.noaa_hurricanes.hurricanes`WHERE  season = '2010' AND basin = 'NA')SELECT * from hurricanes LIMIT 5

Dar acest lucru ne oferă un amestec de rânduri care îndeplinesc criteriile necesare. Avem nevoie să obținem o listă ordonată de locații pentru fiecare uragan. Doar adăugând un GROUP BY la interogarea de mai sus nu va funcționa. (De ce nu? Încercați!)

Cu toate acestea, această interogare funcționează:

#standardsqlWITH hurricanes AS (SELECT  MIN(NAME) AS name,  ARRAY_AGG(STRUCT(iso_time, latitude, longitude, usa_sshs) ORDER BY iso_time ASC) AS trackFROM  `bigquery-public-data.noaa_hurricanes.hurricanes`WHERE  season = '2010' AND basin = 'NA'GROUP BY  sid)
SELECT * from hurricanes LIMIT 5

Să dezmembrăm interogarea:

  1. Ne grupăm după id-ul furtunii, dar când ne grupăm, obținem o grămadă de rânduri. Adesea ceea ce am face este să facem o agregare precum SUM() sau AVG() din rândurile din grup pentru a coborî la o singură valoare pe rând din setul de rezultate.
  2. Pentru a păstra toate rândurile din grup, utilizați ARRAY_AGG(). În această matrice, nu vrem doar un câmp, ci vrem patru. Deci, fac din cele patru câmpuri (timp, lat, lon, puterea uraganului) o structură. Structura îmi permite să păstrez relația element cu element dintre aceste patru coloane.
  3. Comandați matricea în funcție de timp.

Categorie maximă

Acum, că avem istoria fiecărui uragan, să aflăm categoria maximă la care a ajuns uraganul. Ce vrem este:

1611169145 698 Explorarea unui model SQL puternic ARRAY AGG STRUCT si UNNEST
Categorie maximă atinsă de uragan

Iată suplimentul WITH:

WITH hurricanes AS (  ...),
cat_hurricane AS (SELECT name,track, (SELECT MAX(usa_sshs) FROM UNNEST(track))  AS categoryfrom hurricanesORDER BY category DESC)
SELECT * from cat_hurricane

Selectarea numelui din tabelul uraganelor este destul de evident. Este doar o coloană. Dar ce înseamnă selectarea track do? pentru că track este o matrice, obțineți întreaga matrice.

Pentru a obține un singur rând din matricea de piste, trebuie să parcurgem UNNEST(). Când suni UNNEST(track), face o masă, deci UNNEST() poate fi utilizat numai în FROM clauza BigQuery. Odată ce ai înțeles asta UNNEST(track) face un tabel cu patru coloane (cele patru coloane din STRUCT), vezi asta MAX(usa_sshs) calculează pur și simplu puterea maximă atinsă de fiecare uragan.

Ora la care se atinge categoria maximă

Cum găsim momentul la care se atinge categoria maximă? În esență, găsiți toate rândurile din UNNEST(track) tabel pentru care usa_sshs coloana este categoria maximă și limitați-o la 1, pentru a obține primul rând la care categoria este îndeplinită:

SELECT   name,   category,   (SELECT AS STRUCT iso_time, latitude, longitude   FROM UNNEST(track)    WHERE usa_sshs = category ORDER BY iso_time LIMIT 1).*FROM cat_hurricaneORDER BY category DESC, name ASC

Iată interogare completă. Jucați-vă cu câteva variante pentru a înțelege ce se întâmplă:

  1. De ce am .*? Jucați-vă cu interogarea pentru a vedea ce se întâmplă dacă nu includ .*? (Sugestie: are legătură cu numele coloanei).
  2. Ce se întâmplă dacă nu fac asta AS STRUCT de mai sus?
  3. Ce se întâmplă dacă nu fac asta LIMIT 1?