¿Empezando aquí? Esta lección es parte de un tutorial completo sobre el uso de SQL para análisis de datos. Vea el principio.
En esta lección, cubriremos:
- Introducción a las funciones de ventana
- Sintaxis básica de ventanas
- Los sospechosos habituales: SUM, COUNT y AVG
- ROW_NUMBER ()
- RANK () y DENSE_RANK ()
- NTILE
- LAG y LEAD
- Definición de un alias de ventana
- Técnicas avanzadas de ventanas
Esta lección utiliza datos del Programa Capital Bikeshare de Washington DC, que publica datos históricos detallados a nivel de viaje en su sitio web. Los datos se descargaron en febrero de 2014, pero se limitan a los datos recopilados durante el primer trimestre de 2012. Cada fila representa un viaje. La mayoría de los campos se explican por sí mismos, excepto rider_type: «Registrado» indica una membresía mensual en el programa de viaje compartido, «Casual» indica que el pasajero compró un pase de 3 días. Los campos start_time y end_time se limpiaron de sus formularios originales para adaptarse al formato de fecha SQL; se almacenan en esta tabla como marcas de tiempo.
Introducción a las funciones de ventana
La documentación de PostgreSQL hace un excelente trabajo al introducir el concepto de funciones de ventana:
Una función de ventana realiza un cálculo en un conjunto de filas de la tabla que de alguna manera están relacionadas con la fila actual. Esto es comparable al tipo de cálculo que se puede hacer con una función agregada. Pero a diferencia de las funciones agregadas normales, el uso de una ventana La función no hace que las filas se agrupen en una sola fila de salida; las filas conservan sus identidades separadas. Detrás de escena, la función de ventana puede acceder a más que solo la fila actual del resultado de la consulta.
El ejemplo más práctico de esto es un total acumulado:
Puede ver que la consulta anterior crea una agregación (running_total) sin usar GROUP BY. Analicemos la sintaxis y veamos cómo funciona.
Sintaxis básica de ventanas
La primera parte de la agregación anterior, SUM(duration_seconds) , se parece mucho a cualquier otra agregación. Agregar OVER la designa como una función de ventana. Puede leer la agregación anterior como «tomar la suma de duration_seconds en todo el conjunto de resultados, en orden por start_time «.
Si» desea reducir la ventana de todo el conjunto de datos a grupos individuales dentro el conjunto de datos, puede usar PARTITION BY para hacerlo:
La consulta anterior agrupa y ordena la consulta por start_terminal . Dentro de cada valor de start_terminal, está ordenado por start_time, y las sumas totales acumuladas en la fila actual y todas las filas anteriores de duration_seconds. Desplácese hacia abajo hasta que el valor start_terminal cambie y verá que running_total comienza de nuevo. Eso es lo que sucede cuando agrupa usando PARTITION BY. En caso de que todavía esté perplejo por ORDER BY, simplemente ordena por la columna designada (s) de la misma manera que lo haría la cláusula ORDER BY, excepto que trata cada partición como separada. También crea el total acumulado; sin ORDER BY, cada valor será simplemente una suma de todos los valores duration_seconds en su start_terminal. Intente ejecutar la consulta anterior sin ORDER BY para tener una idea:
ORDER y PARTITION define lo que se conoce como la» ventana «, el subconjunto ordenado de datos sobre el cual se realizan los cálculos.
Nota: No puede usar funciones de ventana y agregaciones estándar en el mismo consulta. Más específicamente, no puede «incluir funciones de ventana en una GROUP BY cláusula.
Problema de práctica
Escriba una modificación de la consulta de la consulta de ejemplo anterior que muestre la duración de cada viaje como un porcentaje del tiempo total acumulado por los pasajeros de cada start_terminal
Pruébelo Vea la respuesta
Los sospechosos habituales: SUM, COUNT y AVG
Al utilizar funciones de ventana, puede aplicar los mismos agregados que utilizaría en circunstancias normales: SUM, COUNT y AVG. La forma más sencilla de comprenderlos es volver a ejecutar el ejemplo anterior con algunas funciones adicionales. Haga
Alternativamente, las mismas funciones con ORDER BY:
Asegúrese de conectar las dos consultas anteriores en Modo y ejecutarlas. El siguiente problema de práctica es muy similar a los ejemplos, así que intente modificar el código anterior en lugar de empezar desde cero.
Problema de práctica
Escriba una consulta que muestre un total acumulado de la duración de los paseos en bicicleta (similar al último ejemplo), pero agrupados por end_terminal, y con la duración del viaje ordenada en orden descendente.
Pruébelo Vea la respuesta
ROW_NUMBER ()
ROW_NUMBER() hace exactamente lo que parece: muestra el número de una fila determinada. Comienza con 1 y numera las filas de acuerdo con la ORDER BY de la declaración de la ventana. ROW_NUMBER() no requiere que especifiques una variable entre paréntesis:
El uso de la cláusula PARTITION BY te permitirá comience a contar 1 nuevamente en cada partición. La siguiente consulta inicia el conteo nuevamente para cada terminal:
RANK () y DENSE_RANK ()
RANK() es ligeramente diferente de ROW_NUMBER(). Si ordena por start_time, por ejemplo, podría darse el caso de que algunas terminales tengan viajes con dos horas de inicio idénticas. En este caso, se les da el mismo rango, mientras que ROW_NUMBER() les da números diferentes. En la siguiente consulta, observa las observaciones cuarta y quinta para start_terminal 31000; ambas tienen una clasificación de 4 y el siguiente resultado recibe una clasificación de 6:
También puede utilizar DENSE_RANK() en lugar de RANK() según su aplicación. Imagine una situación en la que tres entradas tienen el mismo valor. Con cualquiera de los dos comandos, todos obtendrán el mismo rango. Por el bien de este ejemplo, digamos que es «2». Así es como los dos comandos evaluarían los siguientes resultados de manera diferente:
-
RANK()le daría a las filas idénticas un rango de 2, luego omitiría los rangos 3 y 4, por lo que el siguiente resultado sería 5 -
DENSE_RANK()aún daría a todas las filas idénticas un rango de 2, pero la siguiente fila sería 3 — no los rangos se omitirían.
Problema de práctica
Escriba una consulta que muestre los 5 viajes más largos desde cada terminal de partida, ordenados por terminal, y los viajes más largos a más cortos dentro de cada terminal. Límite de viajes que ocurrieron antes del 8 de enero de 2012.
Pruébelo Vea la respuesta
NTILE
Puede usar funciones de ventana para identificar en qué percentil (o cuartil, o cualquier otra subdivisión) se ubica una fila determinada. La sintaxis es NTILE(*# of buckets*). En este caso, ORDER BY determina qué columna usar para determinar los cuartiles (o cualquier número de «mosaicos especificar). Por ejemplo:
Si observa los resultados de la consulta anterior, puede ver que la columna percentile no «calcula exactamente como podría esperar. Si solo tenía dos registros y estaba midiendo percentiles, esperaría que un registro defina el percentil 1 y el otro registro para definir el percentil 100. Con la función NTILE, lo que realmente vería es un registro en el percentil 1 y uno en el percentil 2. Puede ver esto en los resultados de start_terminal 31000: la columna percentile parece una clasificación numérica. Si se desplaza hacia abajo hasta start_terminal 31007, puede ver que calcula correctamente los percentiles porque hay más de 100 registros para eso start_terminal. Si está trabajando con ventanas muy pequeñas, tenga esto en cuenta y considere usar cuartiles o elementos similares bandas.
Problema de práctica
Escriba una consulta que muestre solo la duración del viaje y el percentil en el que esa duración cae (en todo el conjunto de datos, no dividido por terminal).
Pruébelo Vea la respuesta
LAG y LEAD
A menudo puede ser útil comparar filas con filas anteriores o siguientes, especialmente si tiene los datos en un orden que tenga sentido. Puede usar LAG o LEAD para crear columnas que extraigan valores de otros filas: todo lo que necesita hacer es ingresar de qué columna extraer y cuántas filas le gustaría extraer. LAG extrae de filas anteriores y LEAD extrae de las siguientes filas:
Esto es especialmente útil si desea calcular diferencias entre filas:
La primera fila de la columna difference es nula porque no hay una fila anterior de la que extraer. De manera similar, el uso de LEAD creará nulos al final del conjunto de datos. Si desea que los resultados sean un poco más limpios, puede envolverlos en una consulta externa para eliminar los nulos:
Definiendo un alias de ventana
Si planea escribir varias funciones de ventana en la misma consulta, utilizando la misma ventana, puede crear un alias.Tome el NTILE ejemplo anterior:
Esto se puede reescribir como:
El WINDOW la cláusula, si se incluye, siempre debe ir después de la WHERE cláusula.
Técnicas avanzadas de ventanas
Puede consultar una lista completa de ventanas funciones en Postgres (la sintaxis que usa el modo) en la documentación de Postgres. Si está utilizando funciones de ventana en una base de datos conectada, debería consultar la guía de sintaxis adecuada para su sistema.