loading..
Русский    English
17:52
листать

COUNT DISTINCT и оконные функции

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

Консоль
Выполнить
  1. SELECT maker, COUNT(*) models, COUNT(DISTINCT pc.model) unique_models 
  2. FROM product p JOIN pc ON p.model=pc.model
  3. GROUP BY maker
  4. ORDER BY maker;

maker    models    unique_models
A    8    2
B    3    1
E    1    1

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

Консоль
Выполнить
  1. SELECT maker, pc.model,pc.price, 
  2. COUNT(*) over(partition BY maker) models 
  3. FROM product p JOIN pc ON p.model=pc.model
  4. ORDER BY maker, pc.model;

maker    model    price    models
A    1232    600,00    8
A    1232    400,00    8
A    1232    350,00    8
A    1232    350,00    8
A    1233    600,00    8
A    1233    950,00    8
A    1233    980,00    8
A    1233    970,00    8
B    1121    850,00    3
B    1121    850,00    3
B    1121    850,00    3
E    1260    350,00    1

Теперь представим, что нам требуется дополнить эту информацию количеством уникальных моделей. Естественная попытка

Консоль
Выполнить
  1. SELECT maker, pc.model,pc.price, 
  2. COUNT(*) over(partition BY maker) models, 
  3. COUNT(DISTINCT pc.model) over(partition BY maker) unique_models 
  4. FROM product p JOIN pc ON p.model=pc.model
  5. ORDER BY maker, pc.model;
терпит неудачу:

Использование ключевого слова DISTINCT не допускается с предложением OVER.

Сообщение об ошибке ясно описывает проблему. Вопрос в том, как её обойти.

Использование подзапроса

Консоль
Выполнить
  1. WITH cte AS
  2. (SELECT maker, pc.model,pc.price, 
  3. COUNT(*) over(partition BY maker) models
  4.  FROM product p JOIN pc ON p.model=pc.model)
  5. SELECT maker, model, models, 
  6. (SELECT COUNT(DISTINCT model) 
  7.      FROM cte t WHERE t.maker=cte.maker) unique_models 
  8. FROM cte
  9. ORDER BY maker,model;

maker    model    models    unique_models
A    1232    8    2
A    1232    8    2
A    1232    8    2
A    1232    8    2
A    1233    8    2
A    1233    8    2
A    1233    8    2
A    1233    8    2
B    1121    3    1
B    1121    3    1
B    1121    3    1
E    1260    1    1

Использование DENSE_RANK

Консоль
Выполнить
  1. WITH cte AS
  2. (SELECT maker, pc.model,pc.price, 
  3. COUNT(*) over(partition BY maker) models, 
  4. DENSE_RANK()  over(partition BY maker ORDER BY pc.model) drnk 
  5. FROM product p JOIN pc ON p.model=pc.model)
  6. SELECT maker, model, price, models,
  7. MAX(drnk) over(partition BY maker) unique_models FROM cte
  8. ORDER BY maker, model;


Здесь мы воспользовались тем фактом, что последнее ранговое значение - max(drnk) - оказывается равным числу уникальных моделей.
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
Характеристики трещин в плитах
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.