Início / dba / Perguntas / 170730
Accepted
user2676140
Asked: 2017-04-12 07:12:07 +0800 CST

Função para realizar cálculos com base na variável

  • 772

Eu vi uma resposta de @ypercubeᵀᴹ que deu uma ótima resposta sobre como calcular o que parecia ser uma quantidade ordenada por 3 e dar respostas de números inteiros. O que me fez pensar, como você lidaria com uma situação se houvesse um número variável de vezes para dividir?

Significando que este era o OP onde você sempre dividiria por 3 ( Pôster Original

Create Table #Orders 
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,partid varchar(100) NOT NULL
    ,qtyordered int DEFAULT '0'
    ,orderedby varchar(100) NOT NULL
    ,ordereddate date DEFAULT GETDATE()
) ;

Insert Into #Orders (partid, qtyordered, orderedby) VALUES
('SS100', 10, 'James'), ('RR200', 5, 'Bob'), ('NN300', 3, 'Jake'), ('OO400', 5, 'Blue') ;

SELECT 
   partid,
   qtyordered,
   [First], 
   [Second],
   [Third] 
FROM 
    #Orders 
  CROSS APPLY
    ( SELECT [Third]  = (qtyordered)                      / 3 )  AS q3
  CROSS APPLY
    ( SELECT [Second] = (qtyordered - [Third])            / 2 )  AS q2
  CROSS APPLY
    ( SELECT [First]  = (qtyordered - [Third] - [Second]) / 1 )  AS q1;

dbfiddle aqui

No entanto, e se, em vez de sempre dividir por 3, você tivesse uma variável int que indicasse quantas vezes dividir, digamos o mesmo DDL, mas em vez de dividir por 3 você usa

Declare @TTS int = 5

E agora você divide cada cenário de 5 maneiras em vez de 3. Basicamente uma função reutilizável que pode dividir "on-the-fly" com base em uma variável?

sql-server sql-server-2008-r2

2 respostas

  1. Basicamente, estamos apenas procurando distribuir o resto da divisão pelos valores. Este procedimento faz exatamente isso; ele retornará uma tabela com os vals de resultado da divisão. Suponho que não queremos retornar um número variável de colunas (dependendo dos divisores que recebemos), e decidi que gerar o texto "Primeiro", "Segundo" etc. então estou retornando cada valor em sua própria linha.

    CREATE PROCEDURE split_into_ints(@dividend int, @divisor int)
AS
BEGIN
    WITH result_rows AS
         (SELECT TOP (@divisor) ROW_NUMBER() OVER (ORDER BY o1.object_id)
                 as result_num
            FROM sys.objects o1 CROSS JOIN sys.objects o2
         )
        ,base_vals AS
         (SELECT @dividend / @divisor as base_result
                ,@dividend % @divisor as remainder
         )
    SELECT r.result_num
          ,CASE WHEN r.result_num <= remainder THEN 1 ELSE 0 END
           + v.base_result as result_val
      FROM base_vals v CROSS JOIN result_rows r
     ORDER BY result_num
END;
GO

    A seguir:

    EXECUTE split_into_ints 10,3;
EXECUTE split_into_ints 49,6;
EXECUTE split_into_ints 2000,11;

    Gera estes resultados:

    result_num           result_val
-------------------- -----------
1                    4
2                    3
3                    3

result_num           result_val
-------------------- -----------
1                    9
2                    8
3                    8
4                    8
5                    8
6                    8

result_num           result_val
-------------------- -----------
1                    182
2                    182
3                    182
4                    182
5                    182
6                    182
7                    182
8                    182
9                    182
10                   181
11                   181

    Como alternativa, você pode tornar isso uma função com valor de tabela inline:

    CREATE FUNCTION fn_split_into_ints(@dividend int, @divisor int)
RETURNS TABLE
AS 
RETURN
(
    WITH result_rows AS
         (SELECT TOP (@divisor) ROW_NUMBER() OVER (ORDER BY o1.object_id)
                 as result_num
            FROM sys.objects o1 CROSS JOIN sys.objects o2
         )
        ,base_vals AS
         (SELECT @dividend / @divisor as base_result
                ,@dividend % @divisor as remainder
         )
    SELECT r.result_num
          ,CASE WHEN r.result_num <= remainder THEN 1 ELSE 0 END
           + v.base_result as result_val
      FROM base_vals v CROSS JOIN result_rows r
);
GO

    Você executa as mesmas consultas acima assim:

    SELECT * FROM fn_split_into_ints(10,3);
SELECT * FROM fn_split_into_ints(49,6);
SELECT * FROM fn_split_into_ints(2000,11);

    Os resultados são os mesmos. Para responder a um comentário: isso permitiria que você puxesse result_valuma variável:

    SELECT @result_val = result_val
  FROM fn_split_into_ints(49,6)
 WHERE result_num = 6
;

    EDIT: Nome da função alterado, para que função e procedimento possam coexistir (se você quiser, por algum motivo).

    • 5
  2. Best Answer

    ATUALIZAR

    Eu usei uma função recursiva para calcular cada item int e uma consulta dinâmica para dinamizar o resultado.

    Declare @TTS int = 5

--= Add divisors 
;with AddDiv as
(
    SELECT partid, qtyordered, t.divisions
    FROM   #Orders
    CROSS APPLY (SELECT TOP (@TTS) ROW_NUMBER() OVER (ORDER BY S.[object_id]) [divisions]
                 FROM    sys.all_objects S) t
)
    --= Recursively calc every int item
    , CalDiv as
    (
        SELECT partid, qtyordered, divisions, 
               part = qtyordered / divisions, rest = qtyordered - (qtyordered/divisions)
        FROM   AddDiv WHERE divisions = @tts
        UNION ALL
        SELECT ad.partid, ad.qtyordered, ad.divisions,
               part = rest / ad.divisions, rest = rest - (rest / ad.divisions) 
        FROM   AddDiv ad 
        INNER JOIN CalDiv cd 
        ON ad.partid = cd.partid
        WHERE ad.divisions = cd.divisions - 1
    )
    SELECT * 
    INTO   Results
    FROM   CalDiv;

    E agora o PIVOT dinâmico:

    DECLARE @col AS nvarchar(MAX),
        @cmd AS nvarchar(MAX);

SET @col = STUFF((SELECT distinct ',' + QUOTENAME(divisions) 
            FROM Results
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'')

SET @cmd =   'SELECT partid, qtyordered, ' + @col
           + ' FROM'  
           + ' (SELECT partid, qtyordered, divisions, part'
           + ' FROM Results) src'
           + ' PIVOT (MAX(part) FOR divisions IN (' + @col + ')) pvt';
    
EXECUTE (@cmd);

    E este é o resultado final:

    partid | qtyordered | 1 | 2 | 3 | 4 | 5
:----- | ---------: | :- | :- | :- | :- | :-
NN300 | 3 | 1 | 1 | 1 | 0 | 0
OO400 | 5 | 1 | 1 | 1 | 1 | 1
SS100 | 10 | 2 | 2 | 2 | 2 | 2
RR200 | 15 | 3 | 3 | 3 | 3 | 3

    dbfiddle aqui

    Esta é minha primeira resposta, ele usa uma função para construir uma string delimitada por vírgula com todos os itens int.

    Create Table #Orders 
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,partid varchar(100) NOT NULL
    ,qtyordered int DEFAULT '0'
    ,dividedBy int DEFAULT 1
    ,orderedby varchar(100) NOT NULL
    ,ordereddate date DEFAULT GETDATE()
) ;

Insert Into #Orders (partid, qtyordered, dividedBy, orderedby) VALUES
('SS100', 10, 3, 'James'), ('RR200', 15, 6, 'Bob'), ('NN300', 3, 2, 'Jake'), ('OO400', 5, 4, 'Blue') ;
GO

    CREATE FUNCTION fnCF(@Qty decimal, @TTS int)
RETURNS varchar(1024)
AS
BEGIN
    DECLARE @step int = 0;
    DECLARE @sv varchar(1024) = '';
    DECLARE @Q0 int;
    DECLARE @Q1 decimal;
    
    SET @step = @TTS
    SET @Q1 = @Qty

    WHILE  @step > 0
    BEGIN
        SET @Q0 = @Q1 / @step;
        SELECT @sv = @sv + CAST(@Q0 AS varchar(100)) 
        SET @step = @step -1
        SET @Q1 = @Q1 - @Q0
        IF @step > 0 
        SET @sv = @sv + ', '
    END
    
    return @sv;
END

GO

    ✓

    SELECT 
   partid,
   qtyordered,
   dividedBy,
   dbo.fnCF(qtyordered, dividedBy) divInt
FROM 
    #Orders;
GO

    partid | qtyordered | divididoPor | divInt          
:----- | ---------: | --------: | :---------------
SS100 | 10 | 3 | 3, 3, 4         
RR200 | 15 | 6 | 2, 2, 2, 3, 3, 3
NN300 | 3 | 2 | 1, 2            
OO400 | 5 | 4 | 1, 1, 1, 2

    dbfiddleaqui

    • 3

relate perguntas