4 maneiras de calcular um total em execução com o SQL

SeattleDataGuy Blocked Desbloquear Seguir Seguindo 13 de janeiro

Calcular um total em execução / soma de rolagem em SQL é uma habilidade útil de se ter.

Muitas vezes, pode ser útil para relatórios e até mesmo no desenvolvimento de aplicativos. Às vezes, os usuários podem querer ver um total de pontos acumulados ou talvez o dinheiro que ganharam. Como muitos problemas no SQL, existem várias maneiras de resolver esse problema.

Você pode usar funções analíticas, auto-uniões ou uma tabela agregada que rastreia a soma em execução. Aqui estão alguns exemplos (também, pule para baixo caso você queira apenas assistir a estas explicações em forma de vídeo).

Usando uma função analítica

Usar uma função analítica é a maneira mais fácil de calcular um total em execução. Uma função analítica permite particionar dados por campo específico. Por exemplo, neste caso, podemos dividir a soma contínua por driver_id e mês. Isso nos dará o total de execução por cliente e mês. Então, todo mês começará novamente em 0.

Veja a consulta abaixo para ver como usar uma função analítica:

 - Estamos supondo que um campo de mês e ano esteja na tabela f_daily rides ----, que é anormal. 
 - Provavelmente você teria uma datakey e uma tabela de dimensões para todas as datas. No entanto, nós não queremos adicionar uma junção extra que - talvez possa complicar o conceito que estamos explicando 
 Selecione 
SUM (Profit) over (partição por driver_id, ano, mês order by day)
, driver_id
,dia
FROM f_daily_rides

Isso pode ser útil para comparar o lucro que um motorista recebe no dia a dia por mês. Você pode ser capaz de detectar algum tipo de tendência. Talvez isso possa ajudá-lo a identificar drivers ruins ou outras tendências. Além disso, é bom apenas reportar ao motorista.

Eles poderiam facilmente alternar entre diferentes dias para ver quanto eles fizeram a partir de x dia do mês.

No entanto, o uso de uma função analítica em uma entrevista pode, às vezes, levar o entrevistador a perguntar se você conhece uma maneira diferente de resolver o problema. Não é que a sua resposta esteja errada, é apenas que eles podem estar interessados em ver o seu pensamento.

Usando uma associação própria

Outra opção para resolver o problema total em execução é usar uma auto-união. Uma auto-adesão refere-se a unir uma tabela a si mesma. Ao contrário da sua junção típica, onde você usará o "=" para todos os valores, ingressaremos em vários valores. Primeiramente, nós nos juntaremos ao ID do motorista e ao mês / ano normalmente e então nos uniremos na data da viagem. Quando nos juntarmos à data da viagem, usaremos o sinal> =. Isso unirá todas as datas de uma tabela que são maiores que as datas da outra tabela.

Então, se olharmos para a consulta abaixo, veremos essa estrutura básica. É um pouco mais complicado do que apenas usar uma função analítica. Se você fosse capaz de criar isso sozinho, ótimo trabalho!

 SELECIONAR 
dia
, driver_id
, SUM (Lucro)
DE
(
Selecione
t1.day
t2.day
t1.driver_id
t2.Profit
DE f_daily_rides t1
JOIN f_daily_rides t2
Em driver_id = driver_id
E t1.Mês = t2.Mês
E t1.ano = t2.ano
E t1.dia> = t2.dia
) t3
Agrupar por dia

Aqui está como seriam os dados na subconsulta. Se você notar que o campo t1.day tem valores repetidos para datas em que o t1.day é maior que o t2.day. Ao fazer isso, você pode agregar em t1.day e obter um total em execução.

Usando uma subconsulta na cláusula de seleção

Uma das últimas maneiras em que vimos pessoas calcularem um total em execução é usar uma subconsulta na instrução select. Isso tem algumas semelhanças com uma auto-junção, exceto que a auto-união ocorre dentro da instrução select. Isso geralmente não é recomendável porque o uso desse método geralmente força a consulta a varrer a tabela para cada linha existente na tabela original novamente. Isso tende a ser muito ineficiente, mas achamos que é sempre bom ter uma compreensão geral de todas as opções possíveis.

 - Para simplificar, não vamos nos concentrar no mês e ano 
Selecione
t1.day
t1.driver_id
, (Selecione soma (lucro) de f_daily_rides t2 onde t1.day> = t2.day e t1.driver_id = t2.driver_id)
DE f_daily_rides t1

Projetando Uma Tabela Para Rastrear Uma Soma Contínua

Além de usar consultas ad-hoc diretas, outra opção é projetar uma tabela para rastrear o total em execução. O que este design parece variará no objetivo. Mais do que provavelmente, o objetivo será preencher um aplicativo ou um painel. Com base nos requisitos, altera o design geral. Se fôssemos simples, como no exemplo acima, onde temos uma soma rotativa para um usuário por mês, basta inserir uma nova linha que acrescente o valor anterior, se for para o mesmo mês e ano.

Isso limitaria a facilidade de ser dinâmico. Por exemplo, e se você decidir que deseja ver o total em execução a cada três meses?

Alguma ideia?

Existem algumas maneiras de abordar isso. Incluindo alguns dos métodos acima. Vamos deixar você pensar sobre isso!

Por favor, sinta-se livre para entrar em contato com qualquer dúvida ou comentário. Se você tiver algum problema que você gostaria de resolver, ou novas soluções, em seguida, chegar até hoje.

Estamos nos concentrando em uma série de solução de problemas e gostaríamos de saber quais problemas você está tentando resolver ou se gostaria de ver resolvidos.

Texto original em inglês.