Início / dba / Perguntas / 166062
Accepted
philomathic_life
Asked: 2017-03-03 21:20:23 +0800 CST

Apenas selecione superconjuntos

  • 772

Eu tenho duas tabelas (juntamente com um índice não clusterizado) que podem ser criadas com os comandos abaixo:

CREATE TABLE GroupTable
(
  GroupKey int NOT NULL PRIMARY KEY, 
  RecordCount int NOT NULL,
  GroupScore float NOT NULL
);

CREATE TABLE RecordTable
(
  RecordKey varchar(10) NOT NULL, 
  GroupKey int NOT NULL,
  PRIMARY KEY(RecordKey, GroupKey)
);

CREATE UNIQUE INDEX ixGroupRecord ON RecordTable(GroupKey, RecordKey);

Embora tecnicamente minhas tabelas sejam um pouco diferentes e eu esteja participando de algumas outras, esse é um proxy adequado para minha situação.

  • Eu gostaria de selecionar todos os GroupKeysque não são subconjuntos de outro GroupKey.
  • Para um determinado superconjunto, gostaria de obter o máximo GroupScorede todos os seus subconjuntos (incluindo ele mesmo).
  • No caso em que a GroupKeycontém exatamente o mesmo RecordKeysque outro GroupKey(s), apenas um deles GroupKeysé capturado (não importa qual).
  • Qualquer GroupKeyum que tenha exatamente o mesmo RecordKeysque outro GroupKey(s)também terá o mesmo GroupScore.
  • Não relacionados GroupKeyspodem ter a mesma pontuação também.

O seguinte é um exemplo para ilustrar o que estou perguntando:

              GroupTable                          RecordTable

GroupKey    RecordCount   GroupScore         RecordKey    GroupKey
------------------------------------         ---------------------
  1              3            6.2                A          1
  29             2            9.8                A          29
  95             3            6.2                A          95
  192            4            7.1                A          192
                                                 B          1
                                                 B          29
                                                 B          95
                                                 B          192
                                                 C          1
                                                 C          95
                                                 D          192
                                                 E          192

Eu gostaria que a saída fosse a seguinte:

GroupKey    RecordCount    GroupScore
-------------------------------------
  1              3             9.8
  192            4             9.8

GroupTabletem cerca de 75 milhões de linhas e RecordTablecerca de 115 milhões de linhas; no entanto, após as junções e o WHEREpredicado, tende a haver cerca de 20 mil linhas em um determinado dia.

Peço desculpas se esta pergunta é trivial, mas por algum motivo estou realmente lutando com isso.

sql-server t-sql

1 respostas

  1. Best Answer

    Eu gostaria que a saída fosse a seguinte:

     GroupKey    RecordCount    GroupScore
 -------------------------------------
   1              3             9.8
   192            4             7.1

    Usar subconsultas correlacionadas é uma maneira de obter a saída desejada.

    • No caso em que uma GroupKey contém exatamente as mesmas RecordKeys que outras GroupKeys, apenas uma dessas GroupKeys é capturada (não importa qual).

    Estou retornando o Grupo com o GroupKey mais baixo quando há uma correspondência, mas isso é arbitrário, pois você diz que não importa.

    dados de teste:

    INSERT INTO RecordTable(RecordKey,GroupKey)
VALUES ('A',1)
     , ('A',29)
     , ('A',95)
     , ('A',192)
     , ('B',1)
     , ('B',29)
     , ('B',95)
     , ('B',192)
     , ('C',1)
     , ('C',95)
     , ('D',192)
     , ('E',192);

INSERT INTO GroupTable(GroupKey,RecordCount,GroupScore)
VALUES (1,3,6.2)     -- ABC
     , (29,2,9.8)    -- AB
     , (95,3,6.2)    -- ABC
     , (192,4,7.1);  -- ABDE
GO

    consulta:

    SELECT GroupKey
     , RecordCount
     , GroupScore = ( SELECT max(GroupScore)
                      FROM GroupTable g2 
                      WHERE ( SELECT count(*)
                              FROM ( SELECT RecordKey
                                     FROM RecordTable
                                     WHERE GroupKey=g1.GroupKey
                                     UNION
                                     SELECT RecordKey
                                     FROM RecordTable
                                     WHERE GroupKey=g2.GroupKey ) z
                            )=g1.RecordCount )
FROM GroupTable g1
WHERE NOT EXISTS ( SELECT *
                   FROM GroupTable g3
                   WHERE ( SELECT count(*)
                           FROM ( SELECT RecordKey
                                  FROM RecordTable 
                                  WHERE GroupKey=g1.GroupKey 
                                  UNION
                                  SELECT RecordKey 
                                  FROM RecordTable 
                                  WHERE GroupKey=g3.GroupKey ) z )=g3.RecordCount
                         AND ( g3.RecordCount>g1.RecordCount 
                               OR ( g3.RecordCount=g1.RecordCount 
                                    AND g3.GroupKey<g1.GroupKey ) ) );
GO

    A subconsulta no SELECT obtém o valor mais alto GroupScoreapenas dos grupos que são subconjuntos deste grupo ('g1'). Ele consegue isso contando a UNIÃO dos RecordKey's para o conjunto 'g1' e cada conjunto 'g2'. Se UNION for maior que o conjunto 'g1', deve haver pelo menos um RecordKeyno conjunto 'g2' sem um correspondente RecordKeypara o conjunto 'g1', então o conjunto 'g2' não é um subconjunto e não deve ser considerado para esta linha.

    Na cláusula WHERE, há dois casos a serem considerados para filtragem. Em ambos os casos, o conjunto 'g1' só é filtrado se todos os 'g1' RecordKeys também estiverem presentes no conjunto 'g3'; e essa verificação é feita contando a união novamente (conforme a cláusula SELECT).

    Os dois casos são: ① o conjunto 'g1' tem menos RecordKeys ( g3.RecordCount>g1.RecordCount; nesse caso filtramos), e ② o conjunto 'g1' é idêntico ao conjunto 'g3' ( g3.RecordCount=g1.RecordCount; nesse caso escolhemos arbitrariamente o conjunto com o inferior GroupKey)

    resultado:

    /*
|GroupKey|RecordCount|GroupScore|
|-------:|----------:|---------:|
|       1|          3|       9.8|
|     192|          4|       9.8|
*/

    dbfiddle aqui

    • 7

relate perguntas