Início / dba / Perguntas / 163557
Accepted
Pரதீப்
Asked: 2017-02-09 00:55:39 +0800 CST

Redefinir o total em execução com base em outra coluna

  • 772

Estou tentando calcular o total em execução. Mas deve ser redefinido quando a soma cumulativa for maior que outro valor de coluna

create table #reset_runn_total
(
id int identity(1,1),
val int, 
reset_val int,
grp int
)

insert into #reset_runn_total
values 
(1,10,1),
(8,12,1),(6,14,1),(5,10,1),(6,13,1),(3,11,1),(9,8,1),(10,12,1)


SELECT Row_number()OVER(partition BY grp ORDER BY id)AS rn,*
INTO   #test
FROM   #reset_runn_total

Detalhes do índice:

CREATE UNIQUE CLUSTERED INDEX ix_load_reset_runn_total
  ON #test(rn, grp)

dados de amostra

+----+-----+-----------+-----+
| id | val | reset_val | Grp |
+----+-----+-----------+-----+
|  1 |   1 |        10 | 1   |
|  2 |   8 |        12 | 1   |
|  3 |   6 |        14 | 1   |
|  4 |   5 |        10 | 1   |
|  5 |   6 |        13 | 1   |
|  6 |   3 |        11 | 1   |
|  7 |   9 |         8 | 1   |
|  8 |  10 |        12 | 1   |
+----+-----+-----------+-----+

Resultado esperado

+----+-----+-----------------+-------------+
| id | val |    reset_val    | Running_tot |
+----+-----+-----------------+-------------+
|  1 |   1 | 10              |       1     |  
|  2 |   8 | 12              |       9     |  --1+8
|  3 |   6 | 14              |       15    |  --1+8+6 -- greater than reset val
|  4 |   5 | 10              |       5     |  --reset 
|  5 |   6 | 13              |       11    |  --5+6
|  6 |   3 | 11              |       14    |  --5+6+3 -- greater than reset val
|  7 |   9 | 8               |       9     |  --reset -- greater than reset val 
|  8 |  10 | 12              |      10     |  --reset
+----+-----+-----------------+-------------+

Consulta:

Eu obtive o resultado usando Recursive CTE. A pergunta original está aqui https://stackoverflow.com/questions/42085404/reset-running-total-based-on-another-column

;WITH cte
     AS (SELECT rn,id,
                val,
                reset_val,
                grp,
                val                   AS running_total,
                Iif (val > reset_val, 1, 0) AS flag
         FROM   #test
         WHERE  rn = 1
         UNION ALL
         SELECT r.*,
                Iif(c.flag = 1, r.val, c.running_total + r.val),
                Iif(Iif(c.flag = 1, r.val, c.running_total + r.val) > r.reset_val, 1, 0)
         FROM   cte c
                JOIN #test r
                  ON r.grp = c.grp
                     AND r.rn = c.rn + 1)
SELECT *
FROM   cte

Existe alguma alternativa melhor T-SQLsem usar CLR.?

sql-server sql-server-2012

4 respostas

  1. Best Answer

    Eu olhei para problemas semelhantes e nunca consegui encontrar uma solução de função de janela que faça uma única passagem pelos dados. Eu não acho que seja possível. As funções de janela precisam poder ser aplicadas a todos os valores em uma coluna. Isso torna os cálculos de redefinição muito difíceis, porque uma redefinição altera o valor de todos os valores a seguir.

    Uma maneira de pensar sobre o problema é que você pode obter o resultado final desejado se calcular um total básico de corrida, desde que possa subtrair o total de corrida da linha anterior correta. Por exemplo, em seus dados de amostra, o valor para id4 é o running total of row 4 - the running total of row 3. O valor para id6 é running total of row 6 - the running total of row 3porque uma reinicialização ainda não aconteceu. O valor para id7 é o running total of row 7 - the running total of row 6e assim por diante.

    Eu abordaria isso com T-SQL em um loop. Eu me empolguei um pouco e acho que tenho uma solução completa. Para 3 milhões de linhas e 500 grupos, o código terminou em 24 segundos na minha área de trabalho. Estou testando com o SQL Server 2016 Developer Edition com 6 vCPU. Estou aproveitando as inserções paralelas e a execução paralela em geral, então talvez seja necessário alterar o código se estiver em uma versão mais antiga ou tiver limitações de DOP.

    Abaixo o código que usei para gerar os dados. Os intervalos em VALe RESET_VALdevem ser semelhantes aos seus dados de amostra.

    drop table if exists reset_runn_total;

create table reset_runn_total
(
id int identity(1,1),
val int, 
reset_val int,
grp int
);

DECLARE 
@group_num INT,
@row_num INT;
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    SET @group_num = 1;
    WHILE @group_num <= 50000 
    BEGIN
        SET @row_num = 1;
        WHILE @row_num <= 60
        BEGIN
            INSERT INTO reset_runn_total WITH (TABLOCK)
            SELECT 1 + ABS(CHECKSUM(NewId())) % 10, 8 + ABS(CHECKSUM(NewId())) % 8, @group_num;

            SET @row_num = @row_num + 1;
        END;
        SET @group_num = @group_num + 1;
    END;
    COMMIT TRANSACTION;
END;

    O algoritmo é o seguinte:

    1) Comece inserindo todas as linhas com um total de execução padrão em uma tabela temporária.

    2) Em um loop:

    2a) Para cada grupo, calcule a primeira linha com um total em execução acima do valor_redefinido restante na tabela e armazene o id, o total em execução que era muito grande e o total em execução anterior que era muito grande em uma tabela temporária.

    2b) Exclua linhas da primeira tabela temporária em uma tabela temporária de resultados que tenha um IDvalor menor ou igual a IDna segunda tabela temporária. Use as outras colunas para ajustar o total em execução conforme necessário.

    3) Após a exclusão não processar mais as linhas, execute um adicional DELETE OUTPUTna tabela de resultados. Isso é para linhas no final do grupo que nunca excedem o valor de redefinição.

    Vou passar por uma implementação do algoritmo acima em T-SQL passo a passo.

    Comece criando algumas tabelas temporárias. #initial_resultsmantém os dados originais com o total em execução padrão, #group_bookkeepingé atualizado a cada loop para descobrir quais linhas podem ser movidas e #final_resultscontém os resultados com o total em execução ajustado para redefinições.

    CREATE TABLE #initial_results (
id int,
val int, 
reset_val int,
grp int,
initial_running_total int
);

CREATE TABLE #group_bookkeeping (
grp int,
max_id_to_move int,
running_total_to_subtract_this_loop int,
running_total_to_subtract_next_loop int,
grp_done bit, 
PRIMARY KEY (grp)
);

CREATE TABLE #final_results (
id int,
val int, 
reset_val int,
grp int,
running_total int
);

INSERT INTO #initial_results WITH (TABLOCK)
SELECT ID, VAL, RESET_VAL, GRP, SUM(VAL) OVER (PARTITION BY GRP ORDER BY ID) RUNNING_TOTAL
FROM reset_runn_total;

CREATE CLUSTERED INDEX i1 ON #initial_results (grp, id);

INSERT INTO #group_bookkeeping WITH (TABLOCK)
SELECT DISTINCT GRP, 0, 0, 0, 0
FROM reset_runn_total;

    Eu crio o índice clusterizado na tabela temporária depois para que a inserção e a construção do índice possam ser feitas em paralelo. Fez uma grande diferença na minha máquina, mas pode não fazer na sua. Criar um índice na tabela de origem não pareceu ajudar, mas pode ajudar na sua máquina.

    O código abaixo é executado no loop e atualiza a tabela de contabilidade. Para cada grupo, precisamos encontrar o máximo IDque deve ser movido para a tabela de resultados. Precisamos do total acumulado dessa linha para que possamos subtraí-lo do total acumulado inicial. A grp_donecoluna é definida como 1 quando não há mais trabalho a fazer para um arquivo grp.

    WITH UPD_CTE AS (
        SELECT 
        #grp_bookkeeping.GRP
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) max_id_to_update
        , MIN(#group_bookkeeping.running_total_to_subtract_next_loop) running_total_to_subtract_this_loop
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN initial_running_total ELSE NULL END) additional_value_next_loop
        , CASE WHEN MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) IS NULL THEN 1 ELSE 0 END grp_done
        FROM #group_bookkeeping 
        INNER JOIN #initial_results IR ON #group_bookkeeping.grp = ir.grp
        WHERE #group_bookkeeping.grp_done = 0
        GROUP BY #group_bookkeeping.GRP
    )
    UPDATE #group_bookkeeping
    SET #group_bookkeeping.max_id_to_move = uv.max_id_to_update
    , #group_bookkeeping.running_total_to_subtract_this_loop = uv.running_total_to_subtract_this_loop
    , #group_bookkeeping.running_total_to_subtract_next_loop = uv.additional_value_next_loop
    , #group_bookkeeping.grp_done = uv.grp_done
    FROM UPD_CTE uv
    WHERE uv.GRP = #group_bookkeeping.grp
OPTION (LOOP JOIN);

    Realmente não sou fã da LOOP JOINdica em geral, mas essa é uma consulta simples e foi a maneira mais rápida de conseguir o que eu queria. Para realmente otimizar o tempo de resposta, eu queria junções de loop aninhadas paralelas em vez de junções de mesclagem DOP 1.

    O código abaixo é executado no loop e move os dados da tabela inicial para a tabela de resultados finais. Observe o ajuste no total inicial.

    DELETE ir
OUTPUT DELETED.id,  
    DELETED.VAL,  
    DELETED.RESET_VAL,  
    DELETED.GRP ,
    DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
INTO #final_results
FROM #initial_results ir
INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP AND ir.ID <= tb.max_id_to_move
WHERE tb.grp_done = 0;

    Para sua comodidade, segue abaixo o código completo:

    DECLARE @RC INT;
BEGIN
SET NOCOUNT ON;

CREATE TABLE #initial_results (
id int,
val int, 
reset_val int,
grp int,
initial_running_total int
);

CREATE TABLE #group_bookkeeping (
grp int,
max_id_to_move int,
running_total_to_subtract_this_loop int,
running_total_to_subtract_next_loop int,
grp_done bit, 
PRIMARY KEY (grp)
);

CREATE TABLE #final_results (
id int,
val int, 
reset_val int,
grp int,
running_total int
);

INSERT INTO #initial_results WITH (TABLOCK)
SELECT ID, VAL, RESET_VAL, GRP, SUM(VAL) OVER (PARTITION BY GRP ORDER BY ID) RUNNING_TOTAL
FROM reset_runn_total;

CREATE CLUSTERED INDEX i1 ON #initial_results (grp, id);

INSERT INTO #group_bookkeeping WITH (TABLOCK)
SELECT DISTINCT GRP, 0, 0, 0, 0
FROM reset_runn_total;

SET @RC = 1;
WHILE @RC > 0 
BEGIN
    WITH UPD_CTE AS (
        SELECT 
        #group_bookkeeping.GRP
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) max_id_to_move
        , MIN(#group_bookkeeping.running_total_to_subtract_next_loop) running_total_to_subtract_this_loop
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN initial_running_total ELSE NULL END) additional_value_next_loop
        , CASE WHEN MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) IS NULL THEN 1 ELSE 0 END grp_done
        FROM #group_bookkeeping 
        CROSS APPLY (SELECT ID, RESET_VAL, initial_running_total FROM #initial_results ir WHERE #group_bookkeeping.grp = ir.grp ) ir
        WHERE #group_bookkeeping.grp_done = 0
        GROUP BY #group_bookkeeping.GRP
    )
    UPDATE #group_bookkeeping
    SET #group_bookkeeping.max_id_to_move = uv.max_id_to_move
    , #group_bookkeeping.running_total_to_subtract_this_loop = uv.running_total_to_subtract_this_loop
    , #group_bookkeeping.running_total_to_subtract_next_loop = uv.additional_value_next_loop
    , #group_bookkeeping.grp_done = uv.grp_done
    FROM UPD_CTE uv
    WHERE uv.GRP = #group_bookkeeping.grp
    OPTION (LOOP JOIN);

    DELETE ir
    OUTPUT DELETED.id,  
        DELETED.VAL,  
        DELETED.RESET_VAL,  
        DELETED.GRP ,
        DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
    INTO #final_results
    FROM #initial_results ir
    INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP AND ir.ID <= tb.max_id_to_move
    WHERE tb.grp_done = 0;

    SET @RC = @@ROWCOUNT;
END;

DELETE ir 
OUTPUT DELETED.id,  
    DELETED.VAL,  
    DELETED.RESET_VAL,  
    DELETED.GRP ,
    DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
    INTO #final_results
FROM #initial_results ir
INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP;

CREATE CLUSTERED INDEX f1 ON #final_results (grp, id);

/* -- do something with the data
SELECT *
FROM #final_results
ORDER BY grp, id;
*/

DROP TABLE #final_results;
DROP TABLE #initial_results;
DROP TABLE #group_bookkeeping;

END;
    • 6

  2. Usando um CURSOR:

    ALTER TABLE #reset_runn_total ADD RunningTotal int;

DECLARE @id int, @val int, @reset int, @acm int, @grp int, @last_grp int;
SET @acm = 0;

DECLARE curRes CURSOR FAST_FORWARD FOR 
SELECT id, val, reset_val, grp
FROM #reset_runn_total
ORDER BY grp, id;

OPEN curRes;
FETCH NEXT FROM curRes INTO @id, @val, @reset, @grp;
SET @last_grp = @grp;

WHILE @@FETCH_STATUS = 0  
BEGIN
    IF @grp <> @last_grp SET @acm = 0;
    SET @last_grp = @grp;
    SET @acm = @acm + @val;
    UPDATE #reset_runn_total
    SET RunningTotal = @acm
    WHERE id = @id;
    IF @acm > @reset SET @acm = 0;
    FETCH NEXT FROM curRes INTO @id, @val, @reset, @grp;
END

CLOSE curRes;
DEALLOCATE curRes;

+----+-----+-----------+-------------+
| id | val | reset_val | RunningTotal|
+----+-----+-----------+-------------+
| 1  | 1   | 10        |     1       |
+----+-----+-----------+-------------+
| 2  | 8   | 12        |     9       |
+----+-----+-----------+-------------+
| 3  | 6   | 14        |     15      |
+----+-----+-----------+-------------+
| 4  | 5   | 10        |     5       |
+----+-----+-----------+-------------+
| 5  | 6   | 13        |     11      |
+----+-----+-----------+-------------+
| 6  | 3   | 11        |     14      |
+----+-----+-----------+-------------+
| 7  | 9   | 8         |     9       |
+----+-----+-----------+-------------+
| 8  | 10  | 12        |     10      |
+----+-----+-----------+-------------+

    Confira aqui: http://rextester.com/WSPLO95303

    • 4

  3. Não em janela, mas em versão SQL pura:

    WITH x AS (
    SELECT TOP 1 id,
           val,
           reset_val,
           val AS running_total,
           1 AS level 
      FROM reset_runn_total
    UNION ALL
    SELECT r.id,
           r.val,
           r.reset_val,
           CASE WHEN x.running_total < x.reset_val THEN x.running_total + r.val ELSE r.val END,
           level = level + 1
      FROM x JOIN reset_runn_total AS r ON (r.id > x.id)
) SELECT
  *
FROM x
WHERE NOT EXISTS (
        SELECT 1
        FROM x AS x2
        WHERE x2.id = x.id
        AND x2.level > x.level
    )
ORDER BY id, level DESC
;

    Não sou especialista no dialeto do SQL Server. Esta é uma versão inicial para PostrgreSQL (se entendi bem não consigo usar LIMIT 1 / TOP 1 na parte recursiva no SQL Server):

    WITH RECURSIVE x AS (
    (SELECT id, val, reset_val, val AS running_total
       FROM reset_runn_total
      ORDER BY id
      LIMIT 1)
    UNION
    (SELECT r.id, r.val, r.reset_val,
            CASE WHEN x.running_total < x.reset_val THEN x.running_total + r.val ELSE r.val END
       FROM x JOIN reset_runn_total AS r ON (r.id > x.id)
      ORDER BY id
      LIMIT 1)
) SELECT * FROM x;
    • 3

  4. Parece que você tem várias consultas/métodos para atacar o problema, mas não nos forneceu - ou sequer considerou? - os índices na tabela.

    Quais índices existem na tabela? É um heap ou tem um índice clusterizado?

    Eu tentaria as várias soluções sugeridas depois de adicionar este índice:

    (grp, id) INCLUDE (val, reset_val)

    Ou apenas altere (ou faça) o índice clusterizado para ser (grp, id).

    Ter um índice que tenha como alvo a consulta específica deve melhorar a eficiência - da maioria, se não de todos os métodos.

    • 1

relate perguntas