Cálculo de totales en ejecución usando SQL 20 de octubre de 2015 | Andy Granowitz
¿Cuántos usuarios se han unido en los últimos 5 meses? ¿Cuáles fueron las ventas totales en el Q2? ¿Cuántos ingresos se obtuvieron de la cohorte de inscripción de marzo?
Aunque estas preguntas se pueden responder con un solo número, puede ser útil ver un total continuo a lo largo del tiempo: cuántos usuarios únicos se unieron o cuántos ingresos acumulados se recibieron por día durante algún período.
Normalmente, los datos se almacenan de forma incremental. Por ejemplo, aquí hay una tabla de ventas por día:
| Date | Sales |
|---|---|
| 10/1/2015 | 5 |
| 10/2/2015 | 3 |
| 10/3/2015 | 7 |
| 10/4/2015 | 8 |
| 10/5/2015 | 2 |
| 10/6/2015 | 3 |
| 10/7/2015 | 6 |
How do we generate the following table of cumulative sales over time? In SQL, there are two typical approaches: a self join or a window function.
| Date | Running Total of Sales |
|---|---|
| 10/1/2015 | 5 |
| 10/2/2015 | 8 |
| 10/3/2015 | 15 |
| 10/4/2015 | 23 |
| 10/5/2015 | 25 |
| 10/6/2015 | 28 |
| 10/7/2015 | 34 |
A self join is a query that compares a table to itself. En este caso, estamos comparando cada fecha con cualquier fecha menor o igual a ella para calcular el total corriente. Concretamente, tomamos la suma de sales en la segunda tabla sobre cada fila que tiene una fecha menor o igual a la fecha que proviene de la primera tabla. Esta es la sintaxis Postgres / Redshift, pero otros dialectos SQL son muy similares.
Esto no es un mal enfoque; es una buena muestra de cómo extensible SQL se puede utilizar sólo selectfromjoin y group by estados de cuenta.
Pero es mucho código para una tarea sencilla. Probemos con una función de ventana. Están diseñados para calcular una métrica sobre un conjunto de filas. En nuestro caso, queremos sumar cada fila donde la fecha es menor o igual a la fecha en la fila actual.
La función de ventana puede filtrar y organizar el conjunto de filas para ejecutar la función. Aquí el order by date rows unbounded preceding limita la función suma a solo sales antes de la fecha de la fila actual. Las funciones de ventana son increíblemente útiles para consultas analíticas basadas en el tiempo; para obtener más información, los documentos de Postgres son un excelente lugar para comenzar.
El paso final de crear un gráfico y compartirlo triunfalmente con tus compañeros de equipo se logra fácilmente usando Wagon. Funciones de ventana para ganar!
Wagon es un editor SQL moderno para analistas e ingenieros: escriba consultas, visualice datos y comparta gráficos con su equipo. Registro gratis: