Consultas dentro de consultas - guillermopetcho/Coursera-Certificate----Data-Analytics-Google GitHub Wiki

Esta lectura esboza los pasos que el instructor realiza en el siguiente video, Consultas dentro de consultas

. En el video, el instructor presenta las subconsultas, otro tipo de consulta SQL, y demuestra cómo utilizarlas para construir consultas más complejas.

Mantenga esta guía paso a paso abierta mientras ve el video. Puede servir como una herramienta de referencia útil si necesita contexto adicional o clarificación mientras sigue los pasos del video. No se trata de una actividad calificada, pero puedes completar estos pasos para practicar las habilidades demostradas en el vídeo.

Lo que necesitas

Para seguir los pasos del instructor, deberá iniciar sesión en su cuenta de BigQuery y acceder a la base de datos pública denominada new_york. Para encontrar este conjunto de datos, desplázate por los conjuntos de datos del proyecto bigquery-public-data. A partir de esta base de datos, utilizarás las tablas denominadas citibike_stations y citibike_trips.

Importante

BigQuery tiene dos bases de datos diferentes que contienen información muy similar: new_york es una base de datos y new_york_citibike es otra. Ambas bases de datos contienen tablas llamadas citibike_stations y citibike_trips. Sin embargo, estas tablas no son exactamente iguales entre ambas bases de datos. Este paso a paso utiliza la base de datos new_york. Tendrá que desplazarse para encontrar este conjunto de datos bajo el proyecto bigquery-public-data en el panel Explorador; no aparece en una búsqueda. El panel BigQuery Explorer muestra los conjuntos de datos new_york y new_york_citibike.

Además, ASÍ COMO MUCHAS DE LAS BASES DE DATOS PÚBLICAS EN BigQuery, estas tablas se actualizan regularmente, por lo que si encuentra que sus resultados no coinciden exactamente con los resultados en el video, esta es una explicación probable de por qué.

Ejemplo 1: Utilizar una subconsulta en una sentencia SELECT

En esta consulta, comparará el número de bicicletas disponibles en una estación concreta con la media general de bicicletas disponibles en todas las estaciones.

Escriba o copie y pegue la siguiente consulta en una nueva ventana de consulta de BigQuery.

SELECT
	station_id,
	num_bikes_available,
	(SELECT
		AVG(num_bikes_available)
	FROM bigquery-public-data.new_york.citibike_stations) AS avg_num_bikes_available
FROM bigquery-public-data.new_york.citibike_stations;

En este ejemplo, la subconsulta se utilizó en una sentencia SELECT. La sentencia externa SELECT (que comienza en la línea 1) enumera los nombres de las columnas que deben recuperarse de la tabla citibike_stations. La sentencia interna SELECT (que comienza en la línea 4) es la subconsulta, que se utiliza para crear una nueva columna que aún no está disponible en la tabla.

Observe que en el vídeo la sentencia SELECT de las líneas 4-6 se escribió en primer lugar. Esta es una subconsulta para calcular el promedio de la columna num_bikes_available y alias los resultados como una nueva columna en los resultados llamada avg_num_bikes_available. La subconsulta se encierra entre paréntesis, lo que la marca como una subconsulta.

A continuación, toda la subconsulta se incorpora a una consulta externa. La subconsulta está sangrada al mismo nivel que station_id y num_bikes_available, que son las otras columnas que se devolverán en los resultados de la consulta.

Así, la consulta final debería devolver una tabla que contenga columnas para station_id, num_bikes_available y avg_num_bikes_available. A continuación se muestra un ejemplo de salida, pero recuerde que sus resultados podrían diferir debido a las actualizaciones de la tabla.

Ejemplo 2: Utilización de una subconsulta en una sentencia FROM

En esta consulta, utilizará la tabla citibike_trips para calcular el número total de viajes que comenzaron en cada estación y devolverlo como una columna llamada number_of_rides_starting_at_station junto con las columnas station_id y name de la tabla citibike_stations.

Escriba o copie y pegue la siguiente consulta en una nueva ventana de consulta de BigQuery.

Nota: Las líneas etiquetadas con #** difieren del código del vídeo adjunto. Esto se debe a los cambios en las tablas de datos que han dado lugar a un tipo de datos no coincidente (Int64 & STRING) entre la columna start_station_id y la columna station_id en las tablas respectivas. Para que tengan el mismo tipo de datos, la columna start_station_id se convierte a STRING mediante la palabra clave CAST.

SELECT 
	station_id,
	name,
	number_of_rides AS number_of_rides_starting_at_station
FROM
	(
		SELECT
			CAST(start_station_id AS STRING) AS start_station_id_str, #**
			COUNT(*) AS number_of_rides
		FROM 
      		bigquery-public-data.new_york.citibike_trips
		GROUP BY 
			CAST(start_station_id AS STRING) #**
	)
	AS station_num_trips
	INNER JOIN 
		bigquery-public-data.new_york.citibike_stations 
	ON 
		station_id = start_station_id_str #**
	ORDER BY 
		number_of_rides DESC;

Esto es lo que ocurre en este ejemplo. Las líneas 1-5 son la Consulta externa. Comienzan con una sentencia SELECT seguida de los nombres de las columnas que desea que se devuelvan en la tabla de resultados de la consulta final: station_id name y number_of_rides_starting_at_station.

El problema es que la columna number_of_rides_starting_at_station no existe en ninguna de las tablas. Hay que crearla. Además, las columnas station_id y name existen en la tabla citibike_stations, mientras que la información necesaria para crear number_of_rides_starting_at_station se encuentra en la tabla citibike_trips.

Las líneas 6-19 resuelven este problema. Primero, fíjese en la subconsulta de las líneas 6-14. Esta subconsulta toma la tabla citibike_trips (línea 11) y la agrupa por start_station_id (convertida a STRING, líneas 12-13).

A partir de los datos agrupados, se selecciona (línea 7) la columna start_station_id (convertida a cadena y con el alias start_station_id_str, línea 8) y el COUNT de todas las filas que comienzan con cada start_station_id. El recuento se aliasa como una nueva columna llamada number_of_rides (línea 9). Toda la subconsulta se encierra entre paréntesis (líneas 6 y 14) y la tabla resultante se aliasea como station_num_trips (línea 15).

station_num_trips es una tabla auxiliar. Contiene dos columnas: start_station_id y number_of_rides. Hay un ID para cada estación única y el número correspondiente de viajes desde esa estación.

Todos los datos de esa subconsulta proceden de la tabla citibike_trips. Aún es necesario conectarla a la tabla citibike_stations. Las líneas 16-19 hacen la conexión. Usted INNER JOIN (línea 16) la tabla de ayuda station_num_trips con la tabla citibike_stations (línea 17) usando la columna station_id en la tabla citibike_stations y la columna start_station_id_str en la tabla de ayuda station_num_trips como claves comunes (líneas 18-19).

El resultado es una gran tabla que contiene todas las columnas de la tabla citibike_stations, así como las columnas start_station_id y number_of_rides de la tabla de ayuda station_num_trips. Sin embargo, no necesitas todas estas columnas. Sólo necesita tres: station_id name y number_of_rides_starting_at_station. Estas son las columnas que se seleccionan en las líneas 1-4.

Los resultados finales de la consulta deben contener estas tres columnas, con filas en orden descendente por número de viajes. A continuación se muestra un ejemplo de salida, pero recuerde que sus resultados pueden diferir debido a las actualizaciones de la tabla.

Ejemplo 3: Uso de una subconsulta en una sentencia WHERE

Por último, escribirá una consulta que devuelva una tabla que contenga dos columnas: las station_id y name (de la tabla citibike_stations ) de sólo aquellas estaciones que fueron utilizadas por personas clasificadas como abonados, información que se encuentra en la tabla citibike_trips.

Escriba o copie y pegue la siguiente consulta en una nueva ventana de consulta de BigQuery.

Nota: La línea 10 (etiquetada con #**) difiere del código del vídeo adjunto. Esto se debe a los cambios en las tablas de datos que han dado lugar a un tipo de datos no coincidente (Int64 & STRING) entre la columna start_station_id y la columna station_id en las tablas respectivas. Para que tengan el mismo tipo de datos, la columna start_station_id se convierte a STRING mediante la palabra clave CAST.

SELECT
	station_id,
	name
FROM
	bigquery-public-data.new_york.citibike_stations
WHERE
	station_id IN
	(
		SELECT
			CAST(start_station_id AS STRING) AS start_station_id_str #**
		FROM
	    	bigquery-public-data.new_york.citibike_trips
	  	WHERE
			usertype = 'Subscriber'
  	);

Para entender esta consulta, divídala en tres secciones. Sección 1:

La primera sección comienza con las líneas 8-15. Se trata de la subconsulta, indicada por los paréntesis de las líneas 8 y 15. Este segmento toma la tabla citibike_trips (líneas 11-12) y la filtra utilizando la cláusula WHERE (línea 13) para que sólo contenga filas en las que la columna usertype contenga Subscriber como valor (línea 14).

De esta tabla filtrada, se selecciona start_station_id, que se convierte a cadena y se aliasa como start_station_id_str (líneas 9-10).

En este punto, tiene una tabla intermedia con una sola columna -start_station_id_str- que contiene los ID de cada fila que tenía Subscriber en la columna usertype de la tabla original. Sección 2:

La segunda sección de la consulta se encuentra en las líneas 4-7. Esta parte utiliza la información de la tabla intermedia de la sección 1 para filtrar la tabla citibike_stations. Comienza con la tabla citibike_stations completa (línea 5). A continuación, filtra esta tabla utilizando una cláusula WHERE (línea 6) para que sólo contenga filas en las que los valores de su columna station_id también se encuentren en la lista de start_station_id_strs resultante de la sección 1.

En este punto, ahora tiene una tabla intermedia que contiene todas las columnas de la tabla citibike_stations, pero sólo las filas de las estaciones que fueron la estación de partida de un abonado. Apartado 3:

La última parte es la más sencilla. Sólo tiene que seleccionar las columnas pertinentes de la tabla intermedia del apartado 2. Esto ocurre en las líneas 1-4, donde seleccionas las columnas station_id y name y añades la cláusula FROM en la línea 5. A continuación se selecciona todo lo que se explicó en las secciones 1 y 2.

Los resultados finales de la consulta deben contener dos columnas: station_id y name. A continuación se muestra un ejemplo de salida, pero recuerde que sus resultados específicos pueden diferir debido a las actualizaciones de la tabla.