Quebra por percentuais - SQL Server

06/04/2021

6

Boa noite,

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
Responder

Posts

06/04/2021

Matheus

Boa noite Carlos,

Pode explicar melhor o que esta fazendo no sql server? Está importando um excel com que estrutura? ou é uma tabela que ja existe?
Responder
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

total de quê? de onde vieram os valores 280.000, 70.000, 42.000, etc?
Responder

07/04/2021

Carlos Castro

Bom dia,

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
Responder
deixa eu ver se eu entendi....

A consulta abaixo conta o número de clientes em cada perfil...
SELECT
   PERFIL, COUNT(*) CLIENTES
FROM
   TABELA
GROUP BY
   PERFIL
...e pode ser utilizada como uma subconsulta para apresentar o resultado final
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
   PERFIL
Responder
se você vai 'rankear' os clientes, será baseado em quê? no ID?
Responder

07/04/2021

Carlos Castro

se você vai 'rankear' os clientes, será baseado em quê? no ID?


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.
Responder
tente algo assim:
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_CLI

ou 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
Responder

08/04/2021

Carlos Castro

Boa noite,

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
Responder
veja a 2a opção funciona 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
) TAB
ORDER BY
    PERFIL, ID_CLI

Responder

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários. Para saber mais sobre o uso de cookies,
consulte nossa política de privacidade. Ao continuar navegando em nosso site, você concorda com a nossa política.

Aceitar