COUNT DISTINCT В ОКОННЫХ ФУНКЦИЯХ

Количество уникальных значений нельзя посчитать в оконных функциях через использование DISTINCT. То есть использование ключевого слова DISTINCT не допускается с over.

Для решения задач, где все-таки необходимо посчитать количество уникальных значений, можно использовать подзапрос или DENSE_RANK()

Задача: давайте посчитаем для каждого района (neighborhood) данные:

• сумму количества проданного товара (quantityavailable) по дням;
• количество уникальных магазинов за все время.

У задачи есть 2 способа решений: 

С использованием подзапроса:

with cte as 
(
    select
        date,
        storename,
        neighborhood,
        sum(quantityavailable) over (partition by neighborhood, date) as total_quantity
    from
        supermarket_inventory
    left join 
        supermarket_sales
        using(storeid)
)

select 
    date,
    storename,
    neighborhood,
    total_quantity,
    (
        select count(distinct storename) from cte as t2 where t1.neighborhood = t2.neighborhood
    ) as unique_stores
from 
    cte as t1
order by 
    unique_stores desc
    

С использованием DENSE_RANK():

with cte as 
(
    select
        date,
        storename,
        neighborhood,
        sum(quantityavailable) over (partition by neighborhood, date) as total_quantity,
        dense_rank() over (partition by neighborhood order by storename) as rank_store
    from
        supermarket_inventory
    left join 
        supermarket_sales
        using(storeid)
)

select
    date,
    neighborhood,
    total_quantity,
    max(rank_store) as unique_stores
from
    cte
group by
    date,
    storename,
    neighborhood,
    total_quantity
order by 
    unique_stores desc