Различают две колонки, группирующие по другой колонке

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

Ниже приведен пример сценария, над которым я работаю.

    DECLARE @mytable TABLE (CampName varchar(10),ID VARCHAR(10),ListName varchar(10))
    INSERT INTO @mytable
            ( CampName, ID, ListName )
    VALUES  ( 'A',   'X',   'Y' ), ( 'A',   'X',   'Y' ), 
            ( 'A',   'Y',   'Z' ), ( 'A',   'Y',   'Z' ),
            ( 'A',   'Y',   'Z' ), ( 'A',   'P',   'Q' ),
            ( 'B',   'X',   'Y' ), ( 'B',   'X',   'Y' ), 
            ( 'B',   'Y',   'Z' ), ( 'B',   'Y',   'Z' ),
            ( 'B',   'Y',   'Z' ), ( 'B',   'P',   'Q' ),
            ( 'B',   'R',   'S' ), ( 'B',   'R',   'S' )

Это приведет к следующей таблице.

 CampName   ID  ListName
-------------------------------------
      A     X     Y
      A     X     Y -- Duplicate Record
      A     Y     Z
      A     Y     Z -- Duplicate Record
      A     Y     Z -- Duplicate Record
      A     P     Q
      B     X     Y 
      B     X     Y -- Duplicate Record
      B     Y     Z
      B     Y     Z -- Duplicate Record
      B     Y     Z -- Duplicate Record
      B     P     Q
      B     R     S
      B     R     S -- Duplicate Record

Мне нужен вывод следующим образом:

CampName   dupcount
-------------------
A            3
B            4

В принципе, мне нужно выяснить количество дубликатов (ID, ListName) для каждого имени CampName независимо от того, что представляют собой повторяющиеся значения.

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

Ответ 1

Вы можете использовать следующий запрос:

SELECT CampName, SUM(cnt) AS dupcount
FROM (
  SELECT CampName, COUNT(*) - 1 AS cnt
  FROM @mytable
  GROUP BY CampName, ID, ListName
  HAVING COUNT(*) > 1) AS t
GROUP BY CampName

Внутренний запрос использует предложение HAVING для фильтрации недвойственных записей. Он также вычисляет количество дубликатов записей за ID, ListName. Внешний запрос просто суммирует количество дубликатов.

Ответ 2

Вот простой способ получить нужные результаты:

select t.campname, count(*) - count(distinct t.listname) as num_duplicates
from @mytable t
group by t.campname;

Логика заключается в том, что count(*) подсчитывает все строки. count(distinct) подсчитывает количество отдельных списков. Разница заключается в количестве дубликатов.

EDIT:

Джорджиос делает хороший момент. Однако данные выглядят так: id и name содержат одну и ту же информацию, поэтому требуется только одна. Если вам нужно использовать оба варианта, многие базы данных позволят вам сделать:

select t.campname, count(*) - count(distinct t.id, t.listname) as num_duplicates
from @mytable t
group by t.campname;

Но не SQL Server. Вместо этого объедините их вместе:

select t.campname,
       count(*) - count(distinct concat(t.id, ':', t.listname)) as num_duplicates
from @mytable t
group by t.campname;

Ответ 3

Я считаю, что для получения правильного результата необходимо вычесть четное количество комбинаций ID и ListName из общего количества для каждой группы CampName.

SELECT t.CampName,
       COUNT(*) - COUNT(DISTINCT 'ColOne' + ID + 'ColTwo' + ListName) AS dupcount
FROM yourTable t
GROUP BY CampName

В этом запросе используется трюк, который объединяет столбцы ID и ListName, которые являются как текстом, так и эффективно формируют псевдогруппу. Потребность в этом состоит в том, что DISTINCT работает только в одном столбце, но у вас есть два столбца, которые необходимо учитывать.

Ссылка: Quora: В SQL, как мне подсчитать DISTINCT по нескольким столбцам?

Ответ 4

В вопросе есть немного неопределенность.

Если вы считаете, что все ваши комбинации ID и ListName всегда равны, ниже выполняется запрос ниже:

Вы можете просто сделать это, используя DISTINCT внутри COUNT

SELECT CampName, COUNT(DISTINCT ListName) UniqueCount
FROM @mytable
GROUP BY CampName

Если вы подозреваете, что комбинация может быть не одинаковой все время, вам нужно рассмотреть возможность подсчета комбинации столбцов ID и ListName.

Это предполагает оператор конкатенации |, который не будет присутствовать в любой из двух столбцов.

SELECT CampName, COUNT(DISTINCT ID+'|'+ListName) UniqueCount
FROM @mytable
GROUP BY CampName

Если вы обеспокоены подсчетом повторяющегося количества строк

SELECT CampName, COUNT(*) - COUNT(DISTINCT ID+'|'+ListName) dupCount
FROM @mytable
GROUP BY CampName

Альтернатива, я думаю,

;WITH Temp AS
(
    SELECT CampName, ID, ListName, COUNT(*) UniqueCount 
    FROM @mytable
    GROUP BY CampName, ID, ListName
)
SELECT CampName, COUNT(UniqueCount) count 
FROM Temp
GROUP BY CampName

Ответ 5

Вы также можете получить тот же результат с помощью CONCAT более надежным

SELECT CampName, 
    COUNT(ListName)-COUNT(DISTINCT CONCAT(id,ListName)) tot 
FROM #tmp 
GROUP BY CampName

Ответ 6

Попробуйте что-нибудь подобное: проанализируйте инструкцию SELECT, предложение WITH не важно для логики:

WITH input_data AS (
  SELECT 'X' AS x, 'Y' AS y FROM DUAL
  UNION ALL
  SELECT 'X' AS x, 'Y' AS y FROM DUAL
  UNION ALL
  SELECT 'X' AS x, 'A' AS y FROM DUAL
)
SELECT input_data.*, COUNT(*) OVER (PARTITION BY x, y) - 1 AS numer_duplicates
FROM input_data
;