Cómo escribir SQL modular y legible usando conjuntos de resultados nombrados y funciones

Mi viaje profesional en computadoras ha involucrado C ++, luego Java y ahora Python. SQL permanece, en el mejor de los casos, en un idioma extranjero. Por mi propia cordura, por lo tanto, he traído algunas de mis mejores prácticas de programación a SQL. En particular, la declaración WITH ha sido mi amigo.

 

Si escribe SQL legible y modular, tendrá tiempo para largos paseos en bicicleta el fin de semana

Utilizaré un conjunto de datos públicos de bikeshares de Londres en Google BigQuery para demostrar. Digamos que queremos saber si las bicicletas se alquilan por períodos más largos los fines de semana.

1. Constantes, no números codificados

Un buen primer paso es definir las constantes que utilizaremos a lo largo de mi consulta (ver consulta completa ):

#standardsql 
CON constantes AS ( 
  SELECCIONE 600 COMO SHORT_DUR, 
         1800 AS LONG_DUR, 
         ['Dom', 'Mon', 'Mar', 'Miércoles', 'Jue', 'Viernes', 'Sáb'] COMO días de la semana 
),

Aquí, estoy definiendo paseos de menos de 10 minutos como “cortos” y paseos de más de 30 minutos como “largos”. Observe cómo, al definir estas constantes por adelantado, puedo hacer que sea bastante fácil probar diferentes números. El uso de constantes con nombre también hará que la consulta sea mucho más legible.

2. Conjuntos de resultados nombrados

Otra cosa que desea hacer para aumentar la legibilidad es descomponer la consulta en conjuntos de resultados nombrados. En lugar de escribir consultas y subconsultas y contar parantheses, tiendo a usar mucho las declaraciones WITH. Al igual que las funciones en lenguajes como C ++ o Python, los conjuntos de resultados nombrados permiten la reutilización y la separación lógica.

Primero defino una consulta para extraer los campos que quiero y llamo a este conjunto de resultados como bikeshare ( concompleta ):

bikeshare AS ( 
  SELECT 
    IF (duración <SHORT_DUR, 1, 0) AS short_ride, 
    IF (duración> LONG_DUR, 1, 0) AS long_ride, 
    días de la semana [ORDINAL (EXTRAER (DAYOFWEEK FROM start_date))] AS día de la semana 
  FROM `bigquery-public- data.licycles.cycle_hire`, constantes 
)

Observe que la cláusula FROM debe incluir las “constantes” para usar las constantes definidas.

3. Funciones de SQL

Puede descomponer consultas complejas utilizando la palabra clave WITH y crear conjuntos de resultados nombrados. ¿Pero qué pasa con el análisis complejo? En el código anterior, la línea tirando del día de la semana y la indexación en el DaysOfWeek matriz no se puede leer, ¿verdad? Y es bastante probable que esto sea algo que quieras en otro lugar.

Use una función SQL para que pueda reutilizar esta expresión:

CREAR FUNCIÓN TEMPORAL dayOfWeek (ts TIMESTAMP, días ARRAY <STRING>) AS ( días [ORDINAL (EXTRAER (DAYOFWEEK FROM ts))] );

Estoy definiendo una función dayOfWeek que, dada una marca de tiempo y una matriz de nombres de día, devolverá el día de la semana al que corresponde la hora en la marca de tiempo. Una vez que tenemos esta función definida, el conjunto de resultados nombrado en la sección anterior se vuelve más limpio ( consulta completa ):

bikeshare AS ( 
  SELECT 
    IF (duración <SHORT_DUR, 1, 0) AS short_ride, 
    IF (duración> LONG_DUR, 1, 0) AS long_ride, 
    dayOfWeek ( fecha_inicio, días de la semana) AS día de la semana 
  FROM `bigquery-public-data.london_bicycles.cycle_hire` , constantes 
)

Simplicidad en sí

Una vez que hemos nombra constantes y los conjuntos de resultados nombrados, la consulta final es la simplicidad misma:

SELECCIONAR 
  dayofweek, 
  SUM (short_ride) / COUNT (short_ride) AS frac_short_rides, 
  SUM (long_ride) / COUNT (long_ride) AS frac_long 
  COUNT (short_ride) AS num_all_rides 
FROM 
  bikeshare 
GROUP POR 
  dayofweek 
ORDER BY frac_long_rides DESC

Aquí está la consulta completa y el resultado siguiente:

Los días de la semana son para desplazamientos rápidos y cortos, y los fines de semana son para viajes largos y lentos. ¡Tiene mucho sentido!

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Ir a la barra de herramientas