COUNT DISTINCT - scrappyCoco/SQL-Note GitHub Wiki

В MS SQL есть аггрегирующая функция COUNT с возможностью подссчета уникального количества записей по одному столбцу. Но иногда возникает необходимость получить уникальное количество по нескольким столбцам. Для этого придется применять небольшой трюк с оконной функцией DENSE_RANK.

В качестве примера предоставлена история изменений сотрудников:

DECLARE @EmployeeHistory TABLE (
  EmployeeId INT,
  Name       VARCHAR(100),
  Department VARCHAR(100),
  City       VARCHAR(100),
  Salary     DECIMAL(10, 2)
);

INSERT
INTO @EmployeeHistory (EmployeeId, Name, Department, City, Salary)
VALUES
  (0, 'Abraham', 'IT department', 'Moscow', 2000),
  (0, 'Abraham', 'IT department', 'Moscow', 2500),
  (0, 'Abraham', 'IT department', 'London', 5000),
  (1, 'Daniel', 'IT department', 'Moscow', 1000),
  (1, 'Daniel', 'IT department', 'London', 1000),
  (1, 'Daniel', 'Salary', 'London', 4000);

Для получения количество изменений зарплат по сотрудникам можно воспользоваться аггрегирующей функцией COUNT DISTINCT:

SELECT
  Name,
  SalaryChanges = COUNT(DISTINCT Salary)
FROM @EmployeeHistory
GROUP BY Name;

Name	SalaryChanges
Abraham	3
Daniel	2

Из данной таблицы видно, что у Abraham возрастала 3 раза, а у Daniel 2.

При возникновении необходимость подссчитать сколько раз менялось местонахождения или отдел, то хотелось бы написать следующий код:

-- Сколько раз менялся местонахождение и отдел.
/*
  SELECT
  Name,
  SalaryChanges = COUNT(DISTINCT Department, City)
FROM @EmployeeHistory
GROUP BY Name;
*/

К сожаленияю, COUNT DISTINCT не принимает несколько аргументов. Но не стоит отчаиваться, ведь есть более извращенный способ с применением оконной функции:

SELECT DISTINCT
  Name,
  ChangesCount
FROM (
  SELECT *,
         ChangesCount = DENSE_RANK() OVER (PARTITION BY EmployeeId ORDER BY Department, City) +
                        DENSE_RANK() OVER (PARTITION BY EmployeeId ORDER BY Department DESC, City DESC) - 1
  FROM @EmployeeHistory
) AS DistinctData

Name	ChangesCount
Abraham	2
Daniel	3

Как видно, у Abraham 2 раза менялись офис/отдел, у Daniel - 3.

Также можно воспользоваться стандартной аггрегирующей функцией COUNT DISTINCT, передав ей сконкатенированную строку:

SELECT
  Name,
  ChangesCount = COUNT(DISTINCT Department + '#' + City)
FROM @EmployeeHistory
GROUP BY Name;