Quebra por percentuais - SQL Server
06/04/2021
0
Atualmente realizo uma tratativa na empresa através do Excel, porém, o volume de clientes para essa tratativa chegou em 1 milhão e não consigo mais fazer da maneira a qual estava habituado. Estou iniciando no SQL Server e necessito desenvolver um código para realizar essa quebra, sendo uma base de dados com 1 milhão de clientes, e esses clientes possuindo um perfil que varia entre 1 e 10. Necessito realizar as quebras por percentuais, considerando o percentual no perfil, não o total geral, conforme exemplo abaixo:
Das 10 quebras que necessito realizar, primeira quebra é de 25% do total de cada perfil, a segunda 15%, a terceira 10% e assim por diante, até completar o total de clientes:
Perfil # Clientes 1 - 25% 2 - 15% 3 - 10% 4 - 7% 5 - 6% 6 - 5% 7 - 13% 8 - 10% 9 - 5% 10 - 4%
1 280.000 70.000 42.000 28.000 19.600 16.800 14.000 36.400 28.000 14.000 11.200
2 190.000 47.500 28.500 19.000 13.300 11.400 9.500 24.700 19.000 9.500 7.600
3 170.000 42.500 25.500 17.000 11.900 10.200 8.500 22.100 17.000 8.500 6.800
4 75.000 18.750 11.250 7.500 5.250 4.500 3.750 9.750 7.500 3.750 3.000
5 50.000 12.500 7.500 5.000 3.500 3.000 2.500 6.500 5.000 2.500 2.000
6 40.000 10.000 6.000 4.000 2.800 2.400 2.000 5.200 4.000 2.000 1.600
7 30.000 7.500 4.500 3.000 2.100 1.800 1.500 3.900 3.000 1.500 1.200
8 35.000 8.750 5.250 3.500 2.450 2.100 1.750 4.550 3.500 1.750 1.400
9 80.000 20.000 12.000 8.000 5.600 4.800 4.000 10.400 8.000 4.000 3.200
10 50.000 12.500 7.500 5.000 3.500 3.000 2.500 6.500 5.000 2.500 2.000
Total 1.000.000 250.000 150.000 100.000 70.000 60.000 50.000 130.000 100.000 50.000 40.000
Caso não seja muito incômodo, poderiam me auxiliar a estruturar um código que realize essa quebra, considerando os percentuais de acordo com cada perfil, por gentileza?
Desde já agradeço.
At,
Carlos Castro
Carlos Castro
Posts
06/04/2021
Matheus
Pode explicar melhor o que esta fazendo no sql server? Está importando um excel com que estrutura? ou é uma tabela que ja existe?
07/04/2021
Emerson Nascimento
total de quê? de onde vieram os valores 280.000, 70.000, 42.000, etc?
07/04/2021
Carlos Castro
Peço desculpas, acabei não me expressando da maneira correta.
Eu importo um arquivo para o SQL contendo os dados cadastrais dos clientes, porém, irei necessitar apenas do ID que é a PK dos clientes e o perfil que varia de 1 a 10, onde meu objetivo é realizar a quebra por perfil, de acordo com os percentuais que forem informados, para no final exportar um arquivo com as colunas ID, Perfil e Quebra, o qual eu possa realizar a importação no sistema.
Os valores enviados no post anterior, foram valores fictícios em que tentei explicar a quebra, onde por exemplo dos 1.000.000 de clientes, 280.000 estão distribuídos no Perfil 1 e assim por diante nas linhas, aí a primeira quebra seria de 25%, representando 70.000 de Perfil 1 que deveriam retornar na quebra coluna de quebra como 1 para os clientes do Perfil 1, já a segunda quebra seria de 15%, representando 42.000 do Perfil 1, que deveriam retornar na quebra como 2 na sequência de clientes do Perfil 1 e assim por diante.
A consulta deveria retornar os valores mais ou menos conforme abaixo, com o ID do cliente, Perfil e a Quebra, considerando o volume informado para cada quebra baseado no percentual, de acordo com cada perfil. É possível realizar essa tratativa através de uma consulta?
ID_CLI PERFIL QUEBRA 000000001 1 1 000000002 1 1 000000003 1 1 000000004 2 1 000000005 2 1 000000006 3 1 000000007 4 1 000000008 5 1 000000009 6 1 000000010 7 1 000000011 2 2 000000012 2 2 000000013 5 1 000000014 5 2 000000015 4 2 000000016 2 3 000000017 3 2 000000018 1 2 000000019 9 1 000000020 10 1 000000021 2 4 000000022 5 3 000000023 7 2 000000024 8 1 000000025 8 2 000000026 9 2 000000027 9 3 000000028 10 2 000000029 1 3
Desde já agradeço.
At,
Carlos Castro
07/04/2021
Emerson Nascimento
A consulta abaixo conta o número de clientes em cada perfil...
SELECT PERFIL, COUNT(*) CLIENTES FROM TABELA GROUP BY PERFIL
SELECT
PERFIL, CLIENTES,
(CLIENTES * 0.25) '01 - 25%',
(CLIENTES * 0.15) '02 - 15%',
(CLIENTES * 0.10) '03 - 10%',
(CLIENTES * 0.07) '04 - 07%',
(CLIENTES * 0.06) '05 - 06%',
(CLIENTES * 0.05) '06 - 05%',
(CLIENTES * 0.13) '07 - 13%',
(CLIENTES * 0.10) '08 - 10%',
(CLIENTES * 0.05) '09 - 05%',
(CLIENTES * 0.04) '10 - 04%'
FROM
(
SELECT
PERFIL, COUNT(*) CLIENTES
FROM
TABELA
GROUP BY
PERFIL
) TAB
ORDER BY
PERFIL07/04/2021
Emerson Nascimento
07/04/2021
Carlos Castro
Emerson,
Sim, seria um ranking através do ID, baseado no tipo de perfil do cliente, porém, de acordo com os parâmetros de percentuais informados.
O exemplo que eu havia dado é exatamente conforme a consulta que você descreveu, ela retornou o total de clientes por perfil, informando a quantidade de clientes para cada quebra nas colunas ao lado.
08/04/2021
Emerson Nascimento
SELECT
T1.ID_CLI, T1.PERFIL,
CASE
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.96) THEN 10
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.91) THEN 9
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.81) THEN 8
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.68) THEN 7
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.63) THEN 6
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.57) THEN 5
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.50) THEN 4
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.40) THEN 3
WHEN RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) > (CLIENTES * 0.25) THEN 2
ELSE
1
END QUEBRA
FROM
TABELA T1
INNER JOIN
(
SELECT
PERFIL, COUNT(*) CLIENTES
FROM
TABELA
GROUP BY
PERFIL
) T2 ON T2.PERFIL = T1.PERFIL
ORDER BY
T1.PERFIL, T1.ID_CLIou assim:
SELECT ID_CLI, PERFIL, CASE WHEN RANKING > (CLIENTES * 0.96) THEN 10 WHEN RANKING > (CLIENTES * 0.91) THEN 9 WHEN RANKING > (CLIENTES * 0.81) THEN 8 WHEN RANKING > (CLIENTES * 0.68) THEN 7 WHEN RANKING > (CLIENTES * 0.63) THEN 6 WHEN RANKING > (CLIENTES * 0.57) THEN 5 WHEN RANKING > (CLIENTES * 0.50) THEN 4 WHEN RANKING > (CLIENTES * 0.40) THEN 3 WHEN RANKING > (CLIENTES * 0.25) THEN 2 ELSE 1 END QUEBRA FROM ( SELECT T1.ID_CLI, T1.PERFIL, RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) RANKING FROM TABELA T1 INNER JOIN ( SELECT PERFIL, COUNT(*) CLIENTES FROM TABELA GROUP BY PERFIL ) T2 ON T2.PERFIL = T1.PERFIL ORDER BY T1.PERFIL, T1.ID_CLI ) TAB
o que for mais performático
08/04/2021
Carlos Castro
Emerson,
O código 1 rodou perfeitamente e em 10s, retornando os dados exatamente da maneira que eu precisava, já o segundo código deu um erro no ORDER BY:
Mensagem 1033, Nível 15, Estado 1, Linha 294 A cláusula ORDER BY é inválida em exibições, funções embutidas, tabelas derivadas, subconsultas e expressões de tabela comuns, a menos que TOP, OFFSET ou FOR XML também esteja especificado.
Irei manter o código 1 nas tratativas que estou realizando para a base de dados e irá ajudar muito.
Muito obrigado pela força e pela atenção.
At,
Carlos Castro
09/04/2021
Emerson Nascimento
SELECT
ID_CLI, PERFIL,
CASE
WHEN RANKING > (CLIENTES * 0.96) THEN 10
WHEN RANKING > (CLIENTES * 0.91) THEN 9
WHEN RANKING > (CLIENTES * 0.81) THEN 8
WHEN RANKING > (CLIENTES * 0.68) THEN 7
WHEN RANKING > (CLIENTES * 0.63) THEN 6
WHEN RANKING > (CLIENTES * 0.57) THEN 5
WHEN RANKING > (CLIENTES * 0.50) THEN 4
WHEN RANKING > (CLIENTES * 0.40) THEN 3
WHEN RANKING > (CLIENTES * 0.25) THEN 2
ELSE
1
END QUEBRA
FROM
( SELECT
T1.ID_CLI, T1.PERFIL,
RANK() OVER (PARTITION BY T1.PERFIL ORDER BY T1.ID_CLI) RANKING
FROM
TABELA T1
INNER JOIN
(
SELECT
PERFIL, COUNT(*) CLIENTES
FROM
TABELA
GROUP BY
PERFIL
) T2 ON T2.PERFIL = T1.PERFIL
) TAB
ORDER BY
PERFIL, ID_CLIClique aqui para fazer login e interagir na Comunidade :)
