Consulta SQL com vários case com vários campos para rotular determinada condições. É a melhor forma ?
20/05/2021
0
Bom dia a Todos!
Sou novo aqui e preciso da ajuda de vcs para verificar se a forma que estou fazendo a consulta esta correta ou se existe uma outra forma ou ferramenta para me ajudar a chegar em uma solução.
Criei uma consulta SQL com case para rotular algumas características de compras do cliente durante sua permanência na base. (Ruim, Sem dados, Em analise e ótimo). Em uma tabela com mais de 1 milhão de registro.
Por exemplo:
A base tem registro de compras desde 2019 e preciso rotular a característica do cliente através da suas compras durante todo o período. Ou seja, durante sua permanência na base suas características são alteradas.
Vamos supor que um cliente que gasta acima de 22 reais por mês é rotulado como ótimo e aquele que gasta menos é ruim ou o cliente que nos últimos 3 meses não gastou nada é sem dados ou aquele que gastou no mês jan mais que 20, em fev não gastou e em março gastou menos que 20 fica em analise. Ou seja, preciso rotular todos os clientes e suas compras desde 20019, todos os valores gastos ou sem gastos tem que entrar na condição.
Segue abaixo o script para criação da base e a consulta que realizo com case.
script de criação da tabela
CREATE TABLE [teste](
[cod_cliente] [nchar](10) NULL,
[data_compra] [date] NULL,
[gasto_122020] [nchar](5) NULL,
[gasto_012021] [numeric](4, 0) NULL,
[gasto_022021] [int] NULL,
[gasto_032021] [int] NULL,
[gasto_042021] [int] NULL
) ON [PRIMARY]
Consultas:
select cod.cliente,
case
When gasto_122020 > = 20 and gasto_012021 >=20 and gasto_012021 >=20 and gasto_022021 >= 20 and gasto_032021 >= 20 then 'otimo'
When gasto_122020 > = 20 and gasto_012021 is null and gasto_012021 is null and gasto_022021 is null and gasto_032021 >= 20 then 'em analise'
When (gasto_122020 > = 20 or gasto_122020 is null) and (gasto_012021 >= 20 or gasto_012021 is null) and
gasto_012021 >=20 and gasto_022021 >= 20 and gasto_032021 >= 20 then 'otimo'
When (gasto_122020 > = 20 or gasto_122020 is null) and (gasto_012021 >= 20 or gasto_012021 is null) and
gasto_012021<=20 and gasto_022021 <= 20 and gasto_032021 <= 20 then 'Ruim'
else 'Sem dados'
end
'Status compra'
from teste
Enfim, eu fiz dessa forma, para inserir todos os meses e gastos na condição, hoje a consulta da produção tem mais de 60 when para realizar essa classificação, mas não sei se é a melhor forma ou se existe uma maneira melhor de fazer, pois todos os meses preciso inserir na tabela os dados do mês atual e alterar a consulta para adicionar esse mês nas condições. Um amigo aconselhou em executar a consulta e criar outra tabela com os cod. clientes e os rotulos e começar de novo, mas também nao sei se é a melhor solução, pois teria que ficar adicionando a nova coluna do mês atual e alterando a consulta.
Ficarei muito grato se algum dos amigos me orientar a melhor forma de realizar a consulta ou de informar se com outra ferramenta eu consigo chegar a solução de forma mais fácil.
Obrigado a todos.
Sou novo aqui e preciso da ajuda de vcs para verificar se a forma que estou fazendo a consulta esta correta ou se existe uma outra forma ou ferramenta para me ajudar a chegar em uma solução.
Criei uma consulta SQL com case para rotular algumas características de compras do cliente durante sua permanência na base. (Ruim, Sem dados, Em analise e ótimo). Em uma tabela com mais de 1 milhão de registro.
Por exemplo:
A base tem registro de compras desde 2019 e preciso rotular a característica do cliente através da suas compras durante todo o período. Ou seja, durante sua permanência na base suas características são alteradas.
Vamos supor que um cliente que gasta acima de 22 reais por mês é rotulado como ótimo e aquele que gasta menos é ruim ou o cliente que nos últimos 3 meses não gastou nada é sem dados ou aquele que gastou no mês jan mais que 20, em fev não gastou e em março gastou menos que 20 fica em analise. Ou seja, preciso rotular todos os clientes e suas compras desde 20019, todos os valores gastos ou sem gastos tem que entrar na condição.
Segue abaixo o script para criação da base e a consulta que realizo com case.
script de criação da tabela
CREATE TABLE [teste](
[cod_cliente] [nchar](10) NULL,
[data_compra] [date] NULL,
[gasto_122020] [nchar](5) NULL,
[gasto_012021] [numeric](4, 0) NULL,
[gasto_022021] [int] NULL,
[gasto_032021] [int] NULL,
[gasto_042021] [int] NULL
) ON [PRIMARY]
Consultas:
select cod.cliente,
case
When gasto_122020 > = 20 and gasto_012021 >=20 and gasto_012021 >=20 and gasto_022021 >= 20 and gasto_032021 >= 20 then 'otimo'
When gasto_122020 > = 20 and gasto_012021 is null and gasto_012021 is null and gasto_022021 is null and gasto_032021 >= 20 then 'em analise'
When (gasto_122020 > = 20 or gasto_122020 is null) and (gasto_012021 >= 20 or gasto_012021 is null) and
gasto_012021 >=20 and gasto_022021 >= 20 and gasto_032021 >= 20 then 'otimo'
When (gasto_122020 > = 20 or gasto_122020 is null) and (gasto_012021 >= 20 or gasto_012021 is null) and
gasto_012021<=20 and gasto_022021 <= 20 and gasto_032021 <= 20 then 'Ruim'
else 'Sem dados'
end
'Status compra'
from teste
Enfim, eu fiz dessa forma, para inserir todos os meses e gastos na condição, hoje a consulta da produção tem mais de 60 when para realizar essa classificação, mas não sei se é a melhor forma ou se existe uma maneira melhor de fazer, pois todos os meses preciso inserir na tabela os dados do mês atual e alterar a consulta para adicionar esse mês nas condições. Um amigo aconselhou em executar a consulta e criar outra tabela com os cod. clientes e os rotulos e começar de novo, mas também nao sei se é a melhor solução, pois teria que ficar adicionando a nova coluna do mês atual e alterando a consulta.
Ficarei muito grato se algum dos amigos me orientar a melhor forma de realizar a consulta ou de informar se com outra ferramenta eu consigo chegar a solução de forma mais fácil.
Obrigado a todos.
Alex
Curtir tópico
+ 0
Responder
Posts
20/05/2021
Emerson Nascimento
você analisa um período específico (sempre últimos 3 meses, sempre último ano) ou é um período aberto, escolhido pelo usuário?
é possível fazer a consulta em tempo-real (sem pré-gravação de tabela), ou ter uma tabela de acumulados, como você fez, só que de forma diferente:
crie uma tabela de acumulados do cliente, que seja assim:
[cod_cliente] [nchar](10) NOT NULL,
[anomes] [nchar](6) NOT NULL,
[valor] [numeric](4, 0) NOT NULL
) ON [PRIMARY]
dessa forma. todo mês fechado será acumulado na tabela. exemplo de dados gravados:
assim você pode não precisa refazer a tabela. basta acumular os valores em novas linhas; você não estará 'amarrado' à colunas.
é possível fazer a consulta em tempo-real (sem pré-gravação de tabela), ou ter uma tabela de acumulados, como você fez, só que de forma diferente:
crie uma tabela de acumulados do cliente, que seja assim:
[cod_cliente] [nchar](10) NOT NULL,
[anomes] [nchar](6) NOT NULL,
[valor] [numeric](4, 0) NOT NULL
) ON [PRIMARY]
dessa forma. todo mês fechado será acumulado na tabela. exemplo de dados gravados:
cod_cliente anomes valor 123456 202012 0 123456 202101 8.5 123456 202102 13 345678 202012 2.8 345678 202101 8 345678 202102 0
Responder
20/05/2021
Alex
você analisa um período específico (sempre últimos 3 meses, sempre último ano) ou é um período aberto, escolhido pelo usuário?
é possível fazer a consulta em tempo-real (sem pré-gravação de tabela), ou ter uma tabela de acumulados, como você fez, só que de forma diferente:
crie uma tabela de acumulados do cliente, que seja assim:
[cod_cliente] [nchar](10) NOT NULL,
[anomes] [nchar](6) NOT NULL,
[valor] [numeric](4, 0) NOT NULL
) ON [PRIMARY]
dessa forma. todo mês fechado será acumulado na tabela. exemplo de dados gravados:
assim você pode não precisa refazer a tabela. basta acumular os valores em novas linhas; você não estará 'amarrado' à colunas.
Da
é possível fazer a consulta em tempo-real (sem pré-gravação de tabela), ou ter uma tabela de acumulados, como você fez, só que de forma diferente:
crie uma tabela de acumulados do cliente, que seja assim:
[cod_cliente] [nchar](10) NOT NULL,
[anomes] [nchar](6) NOT NULL,
[valor] [numeric](4, 0) NOT NULL
) ON [PRIMARY]
dessa forma. todo mês fechado será acumulado na tabela. exemplo de dados gravados:
cod_cliente anomes valor 123456 202012 0 123456 202101 8.5 123456 202102 13 345678 202012 2.8 345678 202101 8 345678 202102 0
Da
Boa noite Emerson, Obrigado pela atenção.
Vou responder os seus questionamentos
Eu preciso sempre analisar toda a base, para ter um indicador de quantos clientes se enquadram em cada característica. por exemplo, se um cliente não gasta desde Dez/2020, ele fica com o status de sem dados para validar. Nesse caso entra a a equipe de MKT para verificar o pq desse cliente parou de gastar.
Não consigo realizar essa consulta na base de produção, eu criei uma base com o cod. do cliente e com os meses e o valor que ele gastou em cada mês;
Sobre a sua questão da criação da tabela, achei fantástico, não pensei nisso, pensei em apenas criar as colunas com os meses e adicionando os valores.
No caso, eu preciso no final realizar um fechamento , por exemplo, o cliente entrou na base no mês 12/2020
e só posso contar ele uma vez. Dessa forma, como eu ja tenho um campo data chamado de entrada, eu posso adicionar ele nessa nova tabela e repetir a data de entrada igual vc repetiu a o cod. cliente, assim não terei problema de contar o cliente duas vezes, ou mais de acordo com os meses que ele gastou. certo ?
Muito obrigado.
Vou refazer aqui e testar.
Abraços.
Responder
21/05/2021
Emerson Nascimento
Você não precisa criar esse campo de "data de entrada" na nova tabela se houver meios de fazer um join com a tabela que já possui o campo.
Responder
21/05/2021
Alex
Você não precisa criar esse campo de "data de entrada" na nova tabela se houver meios de fazer um join com a tabela que já possui o campo.
, Bom dia Emerson!Acho que vou ter que fazer, pois caso contrario terei que trabalhar com a tabela antiga na hora de entrar com os novos clientes a ideia e trabalhar com uma tabela só.
No caso da nova tabela, vc acha melhor eu usar o case ou o if para rotular os clientes com as características especificas.
Por exemplo
cod_cliente anomes valor
123456 202012 0
123456 202101 8.5
123456 202102 13
345678 202012 2.8
345678 202101 8
345678 202102 0
No caso dos dados acima, de acordo com a regra o cliente 345678 gastou nos dois primeiros meses e nao gastou no ultimo, nesse caso, o cliente 345678 = (202012 <= 20 or 202012 is null) and (202101 <= 20 or 202101 is null) and (202101 <= 20 or 202121 is null) = 'Ruim'
Lembrando que vou ter que fazer isso com todos os cliente e com todos os meses de gasto depois da sua entrada na base. se ele entrou em 2019, terei que realizar a condição verificando todos os meses.
Se ele gastou >= 20 em todos os meses de 2019 e não gastou mais em 2020 e 2021 ele é rotulado hoje com 'sem dados'
Mais uma vez obrigado.
Responder
Clique aqui para fazer login e interagir na Comunidade :)