Dr. Mario's 2nd 🧠

Google BigQuery. The Definitive Guide

2 min read

rw-book-cover

Metadata

Highlights

2. Query essentials

  • Talk about capitalization in SQL.

Remark this

if you want to filter for NULLs in a WHERE clause, use the IS NULL or IS NOT NULL operators because other comparison operators (=, !=, <, >) applied to a NULL return NULL and therefore will never match the WHERE condition. (View Highlight)

Ask if these examples were understood

UNNEST is a function that returns the elements of an array as rows, so you can UNNEST the result array to get a row corresponding to each item in the array (View Highlight)

Give more context on this

Data warehouse schemas often rely on a primary “fact” table that contains events, and satellite “dimension” tables that contain extended, slowly changing information. For example, a retail schema might have a “Sales” table as the fact table and then “Products” and “Customers” tables as dimensions (View Highlight)

Later in the course we will talk about these explosions:

Notice that we get a row for each time that the join condition is met. Because there are two rows for which the state doesn’t match, we get two rows for each row in the original from_item_a. If the join condition is not met for some row, that row’s data items will not make it to the output. (View Highlight)

“Outer” is usually omitted

Table 2-2. Summary of types of joins and their outputs (View Highlight)

New highlights added 2023-12-22

SELECT * REPLACE(num_bikes_available + 5 AS num_bikes_available) FROM bigquery-public-data.new_york_citibike.citibike_stations (View Highlight)

SELECT gender, AVG(tripduration / 60) AS avg_trip_duration FROM bigquery-public-data.new_york_citibike.citibike_trips WHERE tripduration IS NOT NULL GROUP BY gender HAVING avg_trip_duration > 14 ORDER BY avg_trip_duration (View Highlight)

New highlights added 2024-02-07

Bastante desaconsejado vs. with

Correlated subquery (View Highlight)

Contar la alternativa con Hex

Defining constants (View Highlight)

New highlights added 2024-02-07

‘Maharashtra’ (View Highlight)

New highlights added 2024-02-14

( (View Highlight)

Graph View