Início / dba / Perguntas / 231866
Accepted
Dan
Asked: 2019-03-12 11:30:48 +0800 CST

Comparando dois pares de colunas entre duas tabelas e retornando a coluna de uma terceira tabela

  • 772

Tenho três tabelas:

Tabela 1

table1pode ter de 50 mil a 800 mil linhas exclusivas. Com isso, quero dizer que a combinação de todos os valores em cada linha é única, embora às vezes algumas colunas correspondam. É POSSÍVEL que uma linha inteira corresponda, exceto a NAMEcoluna, mas é muito, muito improvável. A coluna NAME será sempre exclusiva.

A NAMEcoluna é do tipo varchar(20). O restante das colunas para cada registro são todos do tipo varchar(6), onde cada duas colunas é um par de conjuntos e a tabela tem até 21 pares de conjuntos (ou seja, 43 colunas no total). Aqui está um exemplo table1mostrando um par definido para 4 registros (usei caracteres únicos aqui para facilitar, mas não esqueça que eles são type varchar(6)):

table1

NAME        pair1_1     pair1_2     ...up to pair21_1 - pair21_2
00001A      A           B       
00002A      A           A       
00003A      B           C       
00004A      A           B       
…up to 800k rows

Mesa 2

table2é configurado exatamente da mesma maneira, exceto que contém registros completamente diferentes do mesmo tipo exato (aqui pode haver de 1 a 200 linhas)

table2

NAME        pair1_1    pair1_2    ...up to pair21_1 - pair21_2
1234B       A          B
5678B       A          A
9101B       C          C
1213B       A          B        
…up to 200 rows

Tabela 3

table3está associado a uma única linha em table2e pode ser caracterizado como todo resultado possível de uma comparação entre essa única linha em table2qualquer linha possível que PODERIA ESTAR em table1. Provavelmente é melhor chamá-lo pelo NAMEin table2pertencente a ele (vamos usar apenas o primeiro e chamá-lo table1234Bporque se houver 200 linhas na tabela2, haverá 200 table3s associados diferentes).

Esta 3ª tabela conterá 4 linhas. Tem uma NAMEcoluna que é a varchar(20)e 21 conjuntos de 7 colunas (cada uma associada a um par diferente de table1e table2). As colunas adicionais res1_1são res1_2…res21_7type decimal(30,7).

Aqui está o que parece:

table1234B

NAME        res1_1  res1_2  res1_3  res1_4  res1_5  res1_6  res1_7  ….res21_7
1234B       12.30   1.000   0.2500  1.000   2.000   2.10    25.00   ….

Eu quero usar a combinação de dados compartilhados nos pares de colunas de table1e table2(ou seja, como eles combinam) como a condição para selecionar os dados table1234B(mostrarei um pouco disso abaixo). Apenas pares coincidentes serão comparados. pair1_1e pair1_2from table1será comparado com pair1_1e pair1_2de table2, o pair2s só será comparado com pair2s, pair3s apenas com pair3s etc. Portanto, não são feitas comparações de pares cruzados (por exemplo pair1, nunca serão comparados com pair2)

Neste exemplo abaixo, os campos pair1_1de cada tabela correspondem (A) e os campos pair1_2de cada tabela correspondem (B), mas os campos não correspondem entre as colunas de cada tabela.

NAME        Pair1_1     Pair1_2
00001A      A           B       (from table1)
1234B       A           B       (from table2)

Então eu quero SELECT dizer pair1_4de table1234Be associá-lo com a comparação entre os registros 00001A e 1234B.

Se as tabelas fossem assim, podemos ver que todos os 4 campos correspondem.

NAME        pair1_1     pair1_2
00001A      A           A       (from table1)
1234B       A           A       (from table2)

Neste caso, talvez eu queira escolher pair1_1entretable1234B

Aqui podemos ver que pair1_1em table1combina ambos os campos de table2mas pair1_2de table1não corresponde a nada.

NAME        pair1_1     pair1_2
00001A      A           B       (from table1)
1234B       A           A       (from table2)

Então eu quero escolher dizer pair1_4detable1234B

As opções acima são apenas 3 das 14 maneiras possíveis de compartilhar os dados nos pares de colunas entre as tabelas (há várias maneiras de não compartilhar dados entre as tabelas também), mas apenas 7 colunas possíveis para cada par selecionar table1234B.

Eu quero selecionar TODOS os valores table1234Bque atendem aos critérios estabelecidos de todos os possíveis compartilhamentos entre as colunas em cada conjunto de 2 de table1e table2. Será algo assim quando concluído:

1234BResult

NAME        RESULTPair1     …up to Resultpair21
00001A      12.30       (res1_1 from table3)        
00002B      1.000       (res1_2 from table3)                
00003C      25.00       (res1_7 from table3)        
00004A      1.000       (res1_4 from table3)
…up to 800K rows

Aqui está minha consulta editada com a qual estou começando.

SELECT t1.NAME as NAME, t3.pair1_4 as RESULTPair1
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON (t1.pair1_1 = t2.pair1_1 AND t1.pair1_1 <> t1.pair1_2 AND t1.pair1_2 = t2.pair1_2 AND t2.pair1_1 <> t2.pair1_2)
LEFT OUTER JOIN table1234B t3
ON t2.NAME = t3.NAME

… e o resultado dessa consulta

NAME        RESULTPair1 
00001A      1.000       (res1_4 from table3)
00002B      NULL
00003A      NULL
00004A      1.000       (res1_4 from table3)

Isso está mais próximo do que procuro, pois deixa os NAMEs de table2com diferentes compartilhamentos abertos a outros valores de table1234B. Pretendo que a lógica restante, uma vez incorporada, retorne os outros resultados apropriados.

O problema é que isso só me dá o resultado de qualquer situação que seja AB, AB. Eu preciso expandir isso para 21 pares e potencialmente 200 table3s, incluindo todos os diferentes resultados possíveis, table3se aplicável. Eu posso lidar com toda a lógica de compartilhamento (ou seja , pair1_1 = pairt2_2ou pair1_1 <> pair2_2) expandindo-a para as situações restantes e potencialmente mais registros dos table2quais estou perdido. Eu preciso obter resultados para todos os 800k registros table1para todos os 21 pares.

Se você ficou comigo tanto tempo e entendeu toda aquela coisinha de galinha, minhas perguntas são:

  1. Como eu editaria minha consulta acima para trabalhar com um JOINem vez de um WHERE?
  2. Haveria uma maneira de expandir isso para incluir eficientemente a lógica dos cenários adicionais possíveis de compartilhamento entre os campos table1e table2para que eu possa ver ou armazenar em uma nova tabela todos os resultados de 50 a 800k da table1linha única em table2comparação?
  3. Como diabos posso expandi-lo para os 20 pares adicionais?

EDIT Depois de ser questionado sobre a conexão com a table3 percebi que a coluna precisava de uma edição para que isso funcionasse corretamente. Ainda estou tendo problemas para descobrir como fazer a consulta funcionar corretamente. Editei a consulta também. Ainda estou tendo um pouco de dificuldade para expandi-lo para várias colunas. Foi sugerido o uso de INTERSECT + EXCEPT ou NOT EXISTS. Não consegui que o INTERSECT retornasse nada, enquanto a consulta com a edição para incluir as junções à esquerda faz.

sql-server-2008

1 respostas

  1. Best Answer

    Eu tenho uma consulta de trabalho. Ele une 21 tabelas derivadas (uma para cada um dos 21 pares comparados). Elas são unidas com 20 FULL OUTER JOINs em vez de LEFT JOINs, cada uma com conjuntos de 14 subconsultas combinadas com UNION ALLs, então darei apenas uma forma geral com alguns comentários. Ele faz 67.000 comparações em 5 segundos.

    SELECT COALESCE (t4.NAME, t5.NAME,...t25.NAME) AS NAME,
                t4.RESULTPair1, t5.RESULTPair2,...t25.RESULTPair21           

FROM ((SELECT t1.NAME AS NAME, t3.RESULTPair1_1 AS RESULTPair1
     FROM table1 t1
     INNER JOIN table2 t2
     ON (t1.pair1_1 = t1.pair1_2 AND    ---logic between table1 and table2 for AAAA
         t1.pair1_1 = t2.pair1_1 AND
         t1.pair1_1 = t2.pair1_2)
     INNER JOIN table1234B t3
     ON t2.NAME = t3.NAME) 

     UNION ALL

     (SELECT t1.NAME AS NAME, t3.RESULTPair1_2 AS RESULTPair1
     FROM table1 t1
     INNER JOIN table2 t2
     ON (---LOGIC FOR AAAB---)  ---I wont bore you with the rest of the matching logic unless someone is really interested
     INNER JOIN table1234B t3
     ON t2.NAME = t3.NAME) 

     ------Repeat the above query for each set of logic AAAB, AABA, ABAA, BAAA etc. with 13
     ------total UNION ALL (each set of logics will take care of every possible situation of
     ------sharing between the tables and each t1.NAME is different so UNION ALL will be more
     ------efficient, COALESCE for the NAME field returns a full column for NAME)

    UNION ALL

    (SELECT t1.NAME AS NAME, t3.RESULTPair1_7 AS RESULTPair1
    FROM OFFENDERS t1
    INNER JOIN table2 t2
    ON  (---LOGIC FOR AABB---)
    INNER JOIN table1234B t3
    ON t2.NAME = t3.NAME)) t4 --aabb

     ——-now move through the remaining result pairs up to 21 (ie derived table25)
FULL OUTER JOIN
     ——-subqueries for result set2
ON t4.NAME = t5.NAME
     ——-subqueries for resultsets 3-20
ON t23.NAME = t24.NAME
FULL OUTER JOIN

    ((SELECT t1.NAME AS NAME, t3.RESULTPair21_1 AS RESULTPair21
    FROM table1 t1
    INNER JOIN table2 t2
    ON  (---LOGIC FOR AAAA---)
    INNER JOIN table1234B t3
    ON t2.NAME = t3.NAME)  ---aaaa

    UNION ALL

    -------remaining sets of logic for RESULTPair21

    SELECT t1.NAME AS NAME, t3.RESULTPair21_7 AS RESULTPair21
    FROM table1 t1
    INNER JOIN table2 t2
    ON  (---LOGIC FOR AABB---)
    INNER JOIN table3 t3
    ON t2.NAME = t3.NAME)) t25 --aabb

ON t24.NAME = t25.NAME

    É um trabalho em andamento, então se houver algo que eu possa fazer para melhorá-lo, me avise! Como eu disse nos comentários acima, acho que vou configurar outra tabela para indexar os campos varchar(6) para inteiros e usar os inteiros para comparação. Então agora table1 e table2 ficarão assim:

    table1
NAME        pair1_1     pair1_2     ...up to pair21_1 - pair21_2
00001A      1           2       
00002A      1           1       
00003A      2           3       
00004A      1           2       
…up to 800k rows 

table2
NAME        pair1_1    pair1_2    ...up to pair21_1 - pair21_2
1234B       1          2
5678B       1          1
9101B       3          3
1213B       1          2        
…up to 200 rows

    ...e o índice adicional para tornar a comparação mais rápida e caso eu queira recuperar os conjuntos reais de dados:

    intIndex
pairs    code
A        1
B        2
C        3
D        4
...
up to all possible varchar(6) character strings

    Eu adicionei a tabela de índice e agora tenho comparações de inteiros em vez das colunas varchar(6). A consulta de 67.000 linhas table1 produz 21 colunas em 5 segundos para as colunas varchar(6) versus 4 segundos para as colunas inteiras. 1,4 milhões de cálculos. Alguém tem alguma outra sugestão que possa me ajudar?

    • 0

relate perguntas