Utilizar subconsultas para refinar Datos - guillermopetcho/Coursera-Certificate----Data-Analytics-Google GitHub Wiki

Subconsultas en BibQuery

Seleccione COMPOSE A NEW QUERY en el área de trabajo SQL de BigConsulta. O, si tiene abierta la tabla new_york_citibike.citibike_trips, seleccione Consulta en el menú de la tabla para componer una nueva consulta.

SELECT
    subquery.start_station_id,
    subquery.avg_duration
FROM
    (
    SELECT
        start_station_id,
        AVG(tripduration) as avg_duration
FROM bigquery-public-data.new_york_citibike.citibike_trips
GROUP BY start_station_id) as subquery
ORDER BY avg_duration DESC;

Explicación:

En la línea 1, introduzca SELECT y, a continuación, pulse Intro/Retorno.

3. En la línea 2, pulse Tabulador y, a continuación, introduzca subquery.start_station_id seguido de una coma. A continuación, pulse Intro/Retorno.

4. En la línea 3, introduzca subquery.avg_duration, y pulse Intro/Retorno.

Esta sentencia SELECT se utiliza para crear la consulta externa. Los campos identificados en las líneas 2 y 3 permiten que la sentencia SELECT funcione de forma similar a una tabla con un alias, por ejemplo SELECT alias.column_name1, alias.column_name2. Esto se basa en la subconsulta siguiente para poblar la tabla de resultados de la Consulta.

5. En la línea 4, pulse Retroceso para eliminar la sangría y alinear el texto con la sentencia SELECT. Introduzca FROM y pulse Intro/Retorno.

6. En la línea 5, pulse Tabulador y, a continuación, introduzca un paréntesis abierto [(]. A continuación, pulse Intro/Retorno.

7. En la línea 6, pulse Tabulador e introduzca SELECCIONAR. A continuación, pulse Intro/Retorno.

8. En la línea 7, pulse Tabulador y, a continuación, introduzca start_station_id seguido de una coma. A continuación, pulse Intro/Retorno.

9. En la línea 8, introduzca AVG(tripduration) como avg_duration. A continuación, pulse Intro/Retorno.

La subconsulta se crea utilizando otra sentencia SELECT, situada dentro de una cláusula FROM. Se utiliza para determinar la duración media del viaje por estación. La estación se indica mediante start_station_id. La media se calcula utilizando la función AVG y se le da el alias avg_duration utilizando AS. Aparecerá como título de columna en la tabla de resultados después de ejecutar la consulta.

10. En la línea 9, pulse Retroceso para eliminar la sangría y alinear el texto con la sentencia SELECT.

11. Todavía en la línea 9, introduzca FROM bigquery-public-data.new_york_citibike.citibike_trips, y pulse Intro/Retorno.

12. En la línea 10, introduzca GROUP BY start_station_id) as subquery; Observe que esta línea cierra los paréntesis que se abrieron en la línea 5.

13. En la línea 11, añada ORDER BY avg_duration DESC;.

FROM indicarán a la subconsulta de dónde debe extraer los datos. En este caso, los extraerá de la tabla citibike_trips. GROUP BY agrupará las filas por columnas. Observe que tiene el alias subquery. Esto es lo que enlaza con los dos campos designados para ser los títulos de las columnas de resultados, como se indica en la consulta externa.

13. Select RUN. Los resultados aparecerán en la ventana Resultados de la consulta.

Nota: Sus resultados deben contener dos columnas: start_station_id y avg_duration, sin embargo, las filas específicas pueden diferir de lo que se representa aquí debido a las actualizaciones periódicas de las tablas de origen en BigQuery.

Comparar la duración media del trayecto por estación con la duración media general del trayecto de todas las estaciones.

SELECT
    starttime,
    start_station_id,
    tripduration,
    (
        SELECT ROUND(AVG(tripduration),2)
        FROM bigquery-public-data.new_york_citibike.citibike_trips
        WHERE start_station_id = outer_trips.start_station_id
    ) AS avg_duration_for_station,
    ROUND(tripduration - (
        SELECT AVG(tripduration)
        FROM bigquery-public-data.new_york_citibike.citibike_trips
        WHERE start_station_id = outer_trips.start_station_id), 2) AS difference_from_avg
FROM bigquery-public-data.new_york_citibike.citibike_trips AS outer_trips
ORDER BY difference_from_avg DESC
LIMIT 25;

Cree una nueva consulta para comparar la duración media del trayecto por estación con la duración media general del trayecto de todas las estaciones. Esto proporcionará información sobre el tiempo que la gente suele utilizar las bicicletas que obtiene de una estación concreta en comparación con la media general. Esta subconsulta se aplicará a la cláusula FROM de una sentencia SELECT. Para empezar:

1. Seleccione la pestaña azul con el signo más para componer una nueva consulta.

2. En la línea 1, introduzca SELECT y pulse Intro/Retorno.

3. En la línea 2, pulse Tabulador e introduzca starttime seguido de una coma. A continuación, pulse Intro/Retorno.

4. En la línea 3, introduzca start_station_id seguido de una coma y pulse Intro/Retorno.

5. En la línea 4, introduzca tripduration seguido de una coma y pulse Intro/Retorno.

Para comenzar la consulta, observe que hay tres campos de los que recoger datos para solicitar las duraciones medias de los viajes. Aquí, utilice starttime (un identificador algo único para los resultados) para marcar cuándo comenzaron los viajes desde una estación en particular; el start_station_id, que identifica el ID de la estación para cada viaje; y el tripduration, que mide la duración de cada viaje en segundos. Estas serán las tres primeras columnas de su tabla de resultados.

Generará dos columnas adicionales en la tabla de resultados: avg_duration_for_station y difference_from_average. Comience con la subconsulta para avg_duration_for_station:

6. En la línea 5, introduzca un paréntesis abierto (

7. En la línea 6, introduzca SELECT ROUND(AVG(tripduration), 2)

8. En la línea 7, introduzca FROM bigquery-public-data.new_york_citibike.citibike_trips.

9. En la línea 8, introduzca WHERE start_station_id = outer_trips.start_station_id.

10. En la línea 9, cierre los paréntesis ) que se abrieron en la línea 5, asegurándose de que ambos se alinean en sangría. A continuación, en la misma línea, asigne un nombre a la columna escribiendo AS avg_duration_for_station,

Se utiliza una sentencia SELECT para iniciar una subconsulta que devolverá la duración media de los trayectos de cada estación. Observe que la función ROUND se utiliza para redondear el valor numérico de la duración de cada trayecto a la centésima de segundo más próxima. (Recuerde: la duración de los viajes se mide en segundos.) FROM indica que desea que la consulta se extraiga de la tabla citibike_trips dentro del conjunto de datos new_york_citibike dentro de la base de datos bigquery-public-data. WHERE indica a la consulta que vincule start_station_id con la salida de la consulta: una nueva columna denominada avg_duration_for_station.

Ahora, inicie la subconsulta para crear la segunda columna nueva en la tabla de resultados: difference_from_avg.

10. En la línea 10, introduzca ROUND(tripduration - (. Asegúrese de que comienza en el mismo nivel de sangría que las columnas anteriores que ha seleccionado.

11. En las líneas 11, 12 y 13 introduzca: SELECT AVG(tripduration)
    FROM bigquery-public-data.new_york_citibike.citibike_trips
    WHERE start_station_id = outer_trips.start_station_id), 2) AS difference_from_avg

Esto cierra la subconsulta que crea la columna difference_from_avg. Ahora, lo que queda es cerrar la consulta externa.

12. En las líneas 14, 15, y 16 outdent todo el camino de vuelta a la izquierda y entrar: FROM bigquery-public-data.new_york_citibike.citibike_trips AS outer_trips ORDER BY difference_from_avg DESC LIMIT 25;

13. Seleccione EJECUTAR. Los resultados aparecerán en la ventana Resultados de la consulta.

Nota: Sus resultados deberían contener cinco columnas: starttime, start_station_id, tripduration, avg_duration_for_station, y difference_from_avg, sin embargo, las filas específicas pueden diferir de lo que se representa aquí debido a las actualizaciones periódicas de las tablas de origen en BigQuery.

14. Examine la tabla de resultados. En la última columna, difference_from_avg, hay algunas diferencias muy grandes respecto a la duración media, lo que indica que estas estaciones tienen algunos valores atípicos significativos. Probablemente valdría la pena examinarlo más a fondo.

15. Para continuar con esta actividad, puede guardar esta consulta para poder volver a ella más tarde y, a continuación, crear una nueva consulta. Alternativamente, puede sobrescribir la consulta existente.

Consulta para filtrar los datos e incluir sólo los viajes de las cinco estaciones con la mayor duración media del viaje:

SELECT
    tripduration,
    start_station_id
FROM bigquery-public-data.new_york_citibike.citibike_trips
WHERE start_station_id IN
    (
        SELECT
            start_station_id
        FROM
        (
            SELECT
                start_station_id,
                AVG(tripduration) AS avg_duration
            FROM bigquery-public-data.new_york_citibike.citibike_trips
            GROUP BY start_station_id
        ) AS top_five
        ORDER BY avg_duration DESC
        LIMIT 5
    );

Líneas 1-4: Esta consulta comienza identificando las columnas que aparecerán en la tabla de resultados de la consulta: tripduration y start_station_id.

Sin embargo, no quiere que se seleccionen todos los registros. Sólo quiere aquellos registros en los que el start_station_id coincida con una de las cinco estaciones con la mayor media tripduration. Para ello, necesita una subconsulta.

Línea 5: Este es el comienzo de la subconsulta. Utiliza un operador IN para filtrar los registros en función de si el start_station_id se encuentra en una lista de start_station_ids producida por la subconsulta.

Líneas 6-19: Se trata de una subconsulta interna. Crea una tabla derivada llamada top_five realizando los siguientes pasos:

 Selecciona start_station_id de la tabla principal.

Para cada start_station_id, calcula la media tripduration y etiqueta esta media como avg_duration.

Agrupa los resultados por start_station_id, lo que significa que la duración media del viaje se calcula por separado para cada start_station_id único.

A partir de estos datos agrupados y agregados, ordena los resultados por avg_duration en orden descendente.

Limita los resultados a los cinco primeros. En otras palabras, sólo conserva los registros de los cinco start_station_ids con las duraciones medias de viaje más altas.

El resultado de toda la consulta es una lista de registros de la tabla principal, concretamente los tripduration y start_station_id de cada registro, pero sólo los de los registros en los que el start_station_id se encuentra entre las cinco estaciones con las mayores duraciones medias de viaje. Si examina los resultados de la consulta, descubrirá que sólo cinco de los valores de start_station_id aparecen en la columna dos. El resultado de la consulta debería ser similar a éste:

Nota: Sus resultados deberían contener dos columnas: tripduration y start_station_id, sin embargo, las filas concretas pueden diferir de lo que se representa aquí debido a las actualizaciones periódicas de las tablas de origen en BigQuery.