Только выбрать Supersets

У меня есть две таблицы (наряду с некластеризованным индексом), которые могут быть созданы с помощью следующих команд:

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

Хотя технически мои таблицы немного разные, и я присоединяюсь к нескольким другим таблицам, это подходящий прокси для моей ситуации.

  • Я хотел бы выделить все GroupKeys, которые не являются подмножествами другого GroupKey.
  • Для данного надмножества я хотел бы получить максимальный GroupScore всех его подмножеств (включая сам).
  • В случае, когда GroupKey содержит тот же самый точный RecordKeys в качестве другого GroupKey(s), тогда только один из этих GroupKeys схвачен (неважно, какой из них).
  • Любой GroupKey, который имеет тот же самый точный RecordKeys в качестве другого GroupKey(s) также будет иметь тот же GroupScore.
  • Не связанный GroupKeys может иметь одинаковую оценку.

Ниже приведен пример, иллюстрирующий то, что я прошу:

               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

Я хотел бы, чтобы результат был следующим:

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

GroupTable имеет около 75M строк и RecordTable имеет около 115M строк; однако после объединений и предиката WHERE в определенный день обычно существует около 20 тыс. строк.

Прошу прощения, если этот вопрос тривиален, но по какой-то причине я действительно борюсь с ним.

10 голосов | спросил basketballfan22 3 MaramFri, 03 Mar 2017 08:20:23 +03002017-03-03T08:20:23+03:0008 2017, 08:20:23

1 ответ


7
  

Я хотел бы, чтобы результат был следующим:

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

Использование коррелированных подзапросов - это один из способов получения нужного результата.

  
  • В случае, когда GroupKey содержит те же точные RecordKeys, что и другие GroupKey (s), тогда захватывается только один из этих GroupKeys (неважно, какой из них).
  •   

Я возвращаю группу с самым низким GroupKey, когда есть совпадение, но это произвольно, как вы говорите, это не имеет значения.

тестовые данные:

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

запрос:

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

Подзапрос в SELECT получает наивысший GroupScore только из тех групп, которые являются подмножествами этой группы ('g1'). Это достигается путем подсчета UNION из RecordKey для набора 'g1' и каждого 'g2'. Если UNION больше, чем набор 'g1', в наборе 'g2' должен быть хотя бы один RecordKey без соответствующего ---- +: = 6 =: + ---- для набора 'g1', поэтому набор 'g2' не является подмножеством и не должен рассматриваться для этой строки.

В предложении WHERE для фильтрации необходимо учитывать два случая. В любом случае набор 'g1' фильтруется только в том случае, если все 'g1' RecordKey s также присутствуют в наборе 'g3' ; и эта проверка выполняется путем подсчета объединения снова (в соответствии с предложением SELECT).

Два случая: ① набор 'g1' имеет меньше RecordKey s (RecordKey; в этом случае мы фильтруем), а ② набор 'g1' идентичен набору 'g3' (g3.RecordCount>g1.RecordCount; в этом случае мы произвольно выбираем набор с более низким g3.RecordCount=g1.RecordCount)

выход:

GroupKey

dbfiddle здесь

ответил Jack Douglas 3 MarpmFri, 03 Mar 2017 15:08:34 +03002017-03-03T15:08:34+03:0003 2017, 15:08:34

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132