calculando os totais em execução usando o SQL 20 de outubro de 2015 | Andy Granowitz
quantos utilizadores aderiram nos últimos 5 meses? Quais foram as vendas totais no Q2? Quanto é que o lucro da marcha veio para a coorte?embora estas perguntas possam ser respondidas com um único número, pode ser útil ver um total em execução ao longo do tempo: quantos utilizadores únicos se juntaram, ou quanta receita cumulativa foi recebida por dia ao longo de algum período.Normalmente, os dados são armazenados de forma incremental. Por exemplo, aqui está uma tabela de vendas por dia:
| 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. Neste caso, estamos comparando cada data a qualquer data menor ou igual a ela, a fim de calcular o total de execução. Concretamente, tomamos a soma de sales na segunda tabela sobre cada linha que tem uma data menor ou igual à data que vem da primeira tabela. Esta é a sintaxe Postgres / Redshift, mas outros dialetos SQL são muito semelhantes.
Esta não é uma má abordagem; é uma boa mostra de como extensível SQL pode ser usando apenas a tag selectfromjoin e group by instruções.
mas é um monte de código para uma tarefa simples. Vamos tentar uma função de janela. Eles são projetados para calcular uma métrica sobre um conjunto de linhas. No nosso caso, queremos SOMAR cada linha onde a data é menor ou igual à data na linha atual.
A função da janela pode filtrar e organizar o conjunto de linhas para executar a função. Aqui o order by date rows unbounded preceding limita a função de soma a apenas sales antes da data da linha atual. As funções da janela são incrivelmente úteis para consultas analíticas baseadas no tempo; para saber mais, os docs Postgres são um ótimo lugar para começar.
O passo final de criar um gráfico e compartilhá-lo triunfalmente com seus companheiros de equipe é facilmente realizado usando Wagon. Funções da janela para a vitória!
Wagon é um editor SQL moderno para analistas e engenheiros: escrever consultas, visualizar dados e compartilhar gráficos com sua equipe. Inscrição gratuita: