Início / dba / Perguntas / 187311
Accepted
John Eisbrener
Asked: 2017-09-30 12:41:56 +0800 CST

Soma por Grupo Implícito

  • 772

Tenho certeza de que estou perdendo uma solução óbvia para isso, mas estou tentando resumir valores para grupos definidos por um número de grupo explícito e uma ordenação implícita. Tenho certeza de que isso não torna isso muito mais claro, então digamos que eu tenha esta tabela de heap de origem de exemplo:

GroupID     Value
----------- -----------
1           5
1           5
1           3
2           4
2           1
1           4
2           3
2           5
2           2
1           1

Gostaria de uma consulta que me fornecesse os seguintes resultados:

GroupID     Values
----------- -----------
1           13
2           5
1           4
2           10
1           1

A ordenação implícita é o desafio que eu simplesmente não encontrei uma maneira de contornar... ainda. Qualquer ajuda seria apreciada.

Eu esperava poder criar uma ordem de linha determinística com uma consulta semelhante à seguinte:

SELECT    * 
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum  
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

Espero que isso force uma varredura de ordem de alocação que me daria uma ordem de linha determinística.

Infelizmente, estou preso com os dados como estão. Não tenho outros indicadores aqui, como data, etc., que forneçam qualquer ordem definida. Minha esperança era que o truque descrito acima fosse suficiente, mas não estou totalmente certo de que será.

EDIT: Apenas para fechar isso, pois sei que havia perguntas sobre por que eu estava perguntando isso, eu tinha uma série de tabelas de heap, nomeadas por mês / ano, que continham valores de itens de linha que a empresa queria resumir por dia (que eles correlacionaram com os grupos implícitos na minha pergunta). Como não parece viável fazer isso de forma eficaz, decidimos agregar no nível do mês (por exemplo, tabela), então este post me ajudou a justificar a alteração nos requisitos de negócios. Obrigado pela contribuição de todos!

sql-server order-by

2 respostas

  1. Best Answer

    O grupo "implícito" que você menciona parece ser baseado na ordem das linhas. Ao contrário de uma planilha ou arquivo de texto, uma tabela relacional é logicamente um conjunto não ordenado de linhas, independentemente de estar armazenada como um heap ou ter um índice clusterizado. Não será possível escrever uma consulta para fornecer os resultados desejados, a menos que você tenha outra coluna para facilitar o agrupamento.

    Eu esperava poder criar uma ordem de linha determinística com uma consulta semelhante à seguinte:

    SELECT      * 
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum  
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

    Desculpe ser o portador de más notícias, mas ordenar por um literal não garantirá ordenação determinística (e mesmo que isso aconteça, você precisaria de uma ORDER BYcláusula lá). Se parece fazê-lo, é apenas por acaso. Qualquer coluna incremental funcionará, no entanto.

    Espero que isso force uma varredura de ordem de alocação que me daria uma ordem de linha determinística.

    Uma varredura ordenada por alocação não é mais determinística do que qualquer outra implementação; você está apenas (inseguramente) confiando em um comportamento observado diferente.

    • 7

  2. Se você realmente deseja usar %%physloc%%, aqui está uma solução:

    declare @t table(GroupID int, Value int);
insert into @t values
(1,           5),
(1,           5),
(1,           3),
(2,           4),
(2,           1),
(1,           4),
(2,           3),
(2,           5),
(2,           2),
(1,           1);

create table #t(id int identity, GroupID int, Value int);

insert into #t (GroupID, Value)
select GroupID, Value
from @t
     cross apply (select sys.fn_PhysLocFormatter (%%physloc%%) as loc) a 
     cross apply (select charindex(':', loc) as first)a1
     cross apply (select charindex(':', loc, first + 1) as second)a2
     cross apply (select cast(SUBSTRING(loc, 2, first - 2) as int)as file_,
       cast(SUBSTRING(loc, first + 1, second - 4) as bigint)as page_,
       cast(SUBSTRING(loc, second + 1, len(loc) - second - 1)as int) as slot_)a4
order by a4.file_, a4.page_, a4.slot_;

with cte as
(
select id, GroupID, Value,
       row_number() over(order by id) - row_number() over(order by GroupID, id) as grp 
from #t
)

,cte1 as
(
select GroupID, grp, sum(Value) as Value, min(id) as id 
from cte
group by GroupID, grp
)

select GroupID, Value
from cte1
order by id;

    %%physloc%%é uma função de localizador de registro físico, você pode ler sobre isso aqui: SQL Server 2008: Nova função de localizador de linha física (não documentada)

    ATUALIZAR:

    Como ypercubeᵀᴹ sugeriu, a ordem por %%physloc%%está incorreta, precisamos extrair o arquivo, paginar um slot e ordenar por eles

    e o que acontece quando há exclusões na tabela? Não podemos presumir que as inserções depois disso terão RIDs físicos mais altos, pois podem preencher as lacunas

    Esta pergunta deve ser dirigida ao OP, não a mim. Minha solução é para heap estático que foi apresentado no Original Post.

    Se o autor souber que este heap pode mudar, ele deve copiar este heap não para uma tabela temporária com coluna de identidade, mas para uma permanente.

    • 1

relate perguntas