COUNT DISTINCT и оконные функции
Мы без проблем можем посчитать общее количество ПК для каждого производителя, а также количество уникальных моделей данного производителя в таблице PC:
select maker, count(*) models, count(distinct pc.model) unique_models
from product p join pc on p.model=pc.model
group by maker
order by maker;mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |
| maker | models | unique_models |
|---|---|---|
| A | 8 | 2 |
| B | 3 | 1 |
| E | 1 | 1 |
Если нам требуется получить детальную информацию о каждой модели, наряду с их общим количеством для каждого производителя, то можно использовать оконную функцию:
select maker, pc.model,pc.price,
count(*) over(partition by maker) models
from product p join pc on p.model=pc.model
order by maker, pc.model;mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |
| 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 |
Теперь представим, что нам требуется дополнить эту информацию количеством уникальных моделей. Естественная попытка
select maker, pc.model,pc.price,
count(*) over(partition by maker) models,
count(distinct pc.model) over(partition by maker) unique_models
from product p join pc on p.model=pc.model
order by maker, pc.model;mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |
Использование ключевого слова DISTINCT не допускается с предложением OVER.
Сообщение об ошибке ясно описывает проблему. Вопрос в том, как её обойти.
Использование подзапроса
with cte as
(select maker, pc.model,pc.price,
count(*) over(partition by maker) models
from product p join pc on p.model=pc.model)
select maker, model, models,
(select count(distinct model)
from cte t where t.maker=cte.maker) unique_models
from cte
order by maker,model;mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |
| 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
with cte as
(select maker, pc.model,pc.price,
count(*) over(partition by maker) models,
DENSE_RANK() over(partition by maker order by pc.model) drnk
from product p join pc on p.model=pc.model)
select maker, model, price, models,
max(drnk) over(partition by maker) unique_models from cte
order by maker, model;mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |
Здесь мы воспользовались тем фактом, что последнее ранговое значение - max(drnk) - оказывается равным числу уникальных моделей.