Subconsultas

Manipulación de datos en Snowflake

Jake Roach

Field Data Engineer

¿Qué son las subconsultas?

Las subconsultas permiten usar el resultado de una consulta dentro de otra.

Combina varias consultas
Prioriza la legibilidad
Aporta modularidad
Facilita la manipulación de datos

FROM ( ... ) o WHERE ... IN ( ... )

Proceso lógico para manipular datos de forma más legible y modular con subconsultas

Subconsultas y FROM

SELECT
    ...
-- Pull from query, not from a table
FROM (

    -- Create a result set that will 
    -- be used by the main query
    SELECT 
        <fields>
    FROM <table>
    WHERE ...

);

Recupera datos del resultado de otra consulta en lugar de extraerlos directamente de una tabla.

Separa la preparación del análisis
Hace las consultas más fáciles de leer y entender
Permite «portabilidad» y cambios más simples
JOIN, WHERE, etc.

Antes de una subconsulta

SELECT
    month_num,
    -- windchill - temperature has to be used twice here. What if this changes?
    AVG(windchill - temperature) AS avg_differential
    MIN(windchill - temperature) AS most_differential
FROM weather
WHERE 
    -- Filtering is taking place in the same query as aggregation/analysis
    season = 'Winter' AND
    temperature < 32
GROUP BY month_num;

After a subquery

-- Start with the subquery, then aggregate

SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM (


    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND
        temperature < 32


)
GROUP BY month_num;

          | month_num | differential |
          | --------- | ------------ |
          |     12    |      -12     |
          |     1     |      -3      |
          |     1     |       0      |
          |     2     |      -7      |

| month_num | avg_differential | most_differential |
| --------- | ---------------- | ----------------- |
|     12    |       -5.77      |        -14        |
|     1     |       -1.91      |        -8         |
|     2     |       -8.13      |        -22        |

Una vez depurados los datos, es fácil entender y ajustar el análisis.

WHERE ... IN ( ... )

...

-- Filter by records with a value in 
-- the subquery result set
WHERE <field> IN (

    SELECT <other-field> FROM ... 

);

Crea un conjunto de resultados pequeño para usar al transformar, filtrar o manipular datos.

Filtra registros IN un conjunto de resultados no constante
Puede usarse en otras partes de la consulta
AVG, MAX, MIN, etc.

WHERE ... IN( ... )

SELECT
    todays_date,
    temperature,
    status
FROM weather
WHERE todays_date IN (  -- Filter by all days with home games that were won

    SELECT
        game_date
    FROM game_schedule
    WHERE stadium = 'Home' AND did_win = TRUE

);

WHERE ... IN ( ... )

Conjunto de resultados de una consulta que usa una subconsulta para obtener datos meteorológicos de cada partido en casa

¡Vamos a practicar!

Manipulación de datos en Snowflake