Início / dba / Perguntas / 195004
Accepted
IronicMuffin
Asked: 2018-01-10 10:53:26 +0800 CST

Existe uma maneira eficiente de combinar vários cabeçalhos que tenham o mesmo conjunto de detalhes?

  • 772

Aqui está um exemplo do que estou perguntando:

Tabela de detalhes:

HeaderId | DetailId
    1          100
    1          101
    2          100
    2          101
    3          101
    3          102
    3          103

Estou procurando uma estratégia de consulta que me forneça todos os IDs de cabeçalho com o mesmo conjunto de IDs de detalhes para cada um.

Então, no exemplo, eu gostaria que HeaderId 1 e 2 se unissem porque eles têm os mesmos dois registros de detalhes, mas 103 não corresponderia porque tem um terceiro item no conjunto.

A estratégia que estou adotando até agora é usar a STUFFtécnica para criar uma string separada por vírgulas de valores de detalhes, fazer a soma de verificação dessa string e, em seguida, unir o resultado da soma de verificação. Parece estar funcionando, mas não sei como otimizá-lo. Em um conjunto de cerca de 7.000 cabeçalhos, ele retorna em cerca de 6 a 7 segundos.

Aqui está a consulta:

with Details as 
(   
    select distinct t2.HeaderId, 
    checksum(stuff((
        select
            ',' + convert(varchar(15), t2.DetailId)
        from
            DetailTable t2
        where
            t2.HeaderId = t2.HeaderId
        for xml path('')
    ),1,1,'')) as ChkSum
from
      DetailTable t1
)
select
    *
from
    Details t1
        join Details t2
            on t2.ChkSum = t1.ChkSum
            and t2.HeaderId <> t1.HeaderId -- To avoid matching the same record

Então - esta é a abordagem correta? E se for, como posso otimizar? O plano de consulta não tem nada pulando para mim. O maior peso é dado a um carretel de mesa. Além disso, estou tentando fazer disso uma função ou proc, se isso ajudar.

Edit: comecei a pesquisar a divisão relacional, e acho que isso é relevante aqui, mas talvez não no contexto em que estou pensando. Para dar mais contexto, aqui está o caso de negócios que estou tentando resolver.

Eu tenho um conjunto de Promoções que podem ter qualquer número de UPCs neles. Estou tentando encontrar promoções que tenham exatamente o mesmo conjunto de UPCs. Muitas das soluções que estou vendo dependem do uso do count(*). Então - apenas algum contexto para quem está olhando para isso. Obrigado!

sql-server t-sql

4 respostas

  1. Aqui está uma maneira de usar PIVOTe T-SQL que pode funcionar se você tiver < 255 unique DetailIDs. Eu encontrei limitação na CONCATfunção (2012+) depois de escrever a coisa e testá-la. Ele roda muito bem, <5 segundos em 20k cabeçalhos em 40k linhas, com 254 chaves de detalhes exclusivas e muita correspondência. Se o seu conjunto pode se encaixar nessa limitação, pode valer a pena dar uma olhada.

    DECLARE @sql varchar(MAX)
DECLARE @d varchar(MAX)
SET @d = stuff((
        SELECT ',' + QUOTENAME(DetailId)
        FROM (SELECT DetailId FROM DetailTable GROUP BY DetailId) d
        for xml path('')
    ),1,1,'')
DECLARE @tbl TABLE (H int, D varchar(254))
INSERT INTO @tbl
EXEC(
'SELECT HeaderId,CONCAT('+@d+') Details --'+@d+'
FROM 
(
    SELECT HeaderId, DetailId, 1 o
    FROM DetailTable
) as s
PIVOT
(
    COUNT(o)
    FOR DetailId IN ('+@d+')
) as pvt'
--Possible subquery and JOIN?
)

SELECT t1.H, t2.H H2  --matches
FROM @tbl t1 JOIN @tbl t2 ON t1.D = t2.D AND t1.H < t2.H

    Você deve ser capaz de contornar a CONCATlimitação 254 substituindo o select por apenas @d, envolvendo o PIVOT em uma subconsulta e preenchendo outro @dj para um JOIN.

    • 2

  2. Você pode tentar usar checksum_agg em vez de concatenação xml.

    with c as (
  select
    h = headerid,
    g = checksum_agg(d) 
           over(partition by headerid)
  from detailtable
)

select distinct main, copy
from (
select
    main = min(l.h) over(partition by l.g), 
    copy = l.h
from c as l
) x
where x.main < x.copy

    violino: http://sqlfiddle.com/#!6/df56a/16

    • 2 
  3. with cte1 as 
select headerID, count(*) as cnt 
from Detail 
group by headerID, 

cte2 as 
select h1.headerID as ID1, h2.headerID as ID2, count(*) as cnt 
from detail h1 
join detail h2 
 on h1.headerID < h2.headerID 
and h1.DetailId = h2.DetailId 
group by h1.headerID, h2.headerID

select cte2.* 
from cte2 
join cte1 as cte1a
  on cte1a.headerID = cte2.ID1  
 and cte1a.cnt      = cte2.cnt 
join cte1 as cte1b
  on cte1b.headerID = cte2.ID2  
 and cte1b.cnt      = cte2.cnt

    Aposto que uma junção externa completa e procurar por null seria mais simples, mas não testei. Acho que também seria menos eficiente.

    • 2
  4. Best Answer

    Então, o comentário de @Neil McGuigan sobre a divisão relacional me levou a este artigo .

    Achei o exemplo de "Todd's Division - Dwain.C 1" com bom desempenho e me deu os resultados que eu estava procurando.

    Este é o exemplo do artigo que eu usei literalmente, exceto para nomes de campos/tabelas:

    -- Todd's Division - Dwain.C 1
SELECT j.ProjectID, s.ResourceID
FROM #ProjectTasks j
JOIN #ResourceTasks s ON j.TaskID = s.TaskID
JOIN
(
    SELECT ProjectID, c_res=COUNT(*)
    FROM #ProjectTasks
    GROUP BY ProjectID
) c ON j.ProjectID = c.ProjectID
GROUP BY j.ProjectID, ResourceID
HAVING COUNT(*) = MAX(c_res)
ORDER BY j.ProjectID, ResourceID;

    Obrigado pelo resto das sugestões, elas não me levaram lá, mas finalmente encontrei uma solução.

    • 2

relate perguntas