Wybierz tylko nadzbiory

Mam dwie tabele (wraz z indeksem nieklastrowanym), które można utworzyć za pomocą poniższych poleceń:

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);

Chociaż technicznie moje tabele są nieco inne i dołączam do kilku innych tabel, jest to odpowiedni serwer proxy dla mojej sytuacji.

• Chciałbym wybrać wszystkie GroupKeys, które nie są podzbiorami innego GroupKey.
• Dla danego supersetu chciałbym pobrać maksimum GroupScorewszystkich jego podzbiorów (w tym siebie).
• W przypadku, gdy a GroupKeyzawiera dokładnie to samo RecordKeysco inne GroupKey(s), wówczas GroupKeyspobierany jest tylko jeden z nich (nie ma znaczenia, który z nich).
• Każdy, GroupKeyktóry ma taki sam dokładny RecordKeysjak inny, GroupKey(s)będzie miał również to samo GroupScore.
• Niepowiązane GroupKeysmogą mieć również ten sam wynik.

Oto przykład ilustrujący to, o co proszę:

              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

Chciałbym, aby wynik był następujący:

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

GroupTablema około 75 RecordTablemilionów wierszy i około 115 milionów wierszy; jednak po sprzężeniach i WHEREpredykacie w danym dniu jest zwykle około 20 000 wierszy.

Przepraszam, jeśli to pytanie jest trywialne, ale z jakiegoś powodu naprawdę mam z tym problem.

Chciałbym, aby wynik był następujący:

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

Używanie skorelowanych podkwerend jest jednym ze sposobów uzyskania pożądanego wyniku.

• W przypadku, gdy GroupKey zawiera te same dokładne klucze RecordKey, co inne klucze GroupKey, wówczas tylko jeden z tych kluczy GroupKey jest pobierany (nie ma znaczenia, który z nich).

Zwracam grupę z najniższym kluczem GroupKey, gdy jest dopasowanie, ale jest to arbitralne, jak mówisz, nie ma znaczenia.

dane testowe:

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

pytanie:

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

Podkwerenda w SELECT pobiera najwyższą wartość GroupScoretylko z tych grup, które są podzestawami tej grupy („g1”). Osiąga to poprzez zliczenie UNII RecordKeydla zestawu „g1” i każdego zestawu „g2”. Jeśli UNION jest większy niż zestaw „g1”, musi być co najmniej jeden RecordKeyw zestawie „g2” bez odpowiadającego RecordKeymu zestawu „g1”, więc zestaw „g2” nie jest podzbiorem i nie powinien być brany pod uwagę dla ten rząd.

W klauzuli WHERE istnieją dwa przypadki, które należy rozważyć w celu filtrowania. W obu przypadkach zestaw „g1” jest filtrowany tylko wtedy, gdy wszystkie „g1” RecordKeysą również obecne w zestawie „g3”; i ta kontrola jest osiągana przez ponowne zliczenie unii (zgodnie z klauzulą ​​SELECT).

Dwa przypadki to: ① zestaw „g1” ma mniej RecordKeys ( g3.RecordCount>g1.RecordCount; w takim przypadku filtrujemy), i ② zestaw „g1” jest identyczny z zestawem „g3” ( g3.RecordCount=g1.RecordCount; w którym to przypadku arbitralnie wybieramy zestaw z niższa GroupKey)

wynik:

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

dbfiddle tutaj