Обработка NULL значений

Часто задают вопрос, как ведут себя агрегатные оконные функции с NULL значениями. Разобьем вопрос на два:

1. Как обрабатываются NULL значения при вычислении значения?
2. Как учитываются NULL значения при разделении данных на группы в PARTITION BY?

Если отвечать коротко, то так же, как и в обычных агрегатных функциях.

NULL при вычислении значения

Все агрегатные функции, кроме count(*) игнорируют NULL значения.

Выведем сколько магазинов в каждом городе и для скольки из них заданы телефоны:

SELECT sa.city_id,
       sa.phone,
       count(sa.phone) over (PARTITION BY sa.city_id) AS count_phones_in_city,
       count(*) over (PARTITION BY sa.city_id) AS count_rows_in_city
  FROM store_address sa
 WHERE sa.city_id IN (1, 2, 6)
 ORDER BY sa.city_id,
          sa.phone NULLS LAST

NULL в PARTITION BY

В условиях WHERE два NULL значения считаются различными. Но при группировке строк PARTITION BY NULL значения считаются идентичными и объединяются в одну группу (как и при исключении повторяющихся строк DISTINCT).

Для номера телефона выведем в скольки городах он используется:

SELECT sa.phone,
       sa.city_id,
       count(sa.city_id) over (PARTITION BY sa.phone) AS count_cities
  FROM store_address sa
 WHERE sa.city_id IN (1, 2, 6)
 ORDER BY sa.phone NULLS LAST,
          sa.city_id

P.S. Если внимательно посмотреть на первые две строки результата

то видно, что город на самом деле один, а не два, как мы получили. Функция count(значение) считает количество заполненных значений, а не количество уникальных значений. Чтобы получить количество уникальных значений, хотелось бы воспользоваться count (DISTINCT значение), но такая возможность в PostgreSQL не реализована :(

SELECT sa.phone,
       sa.city_id,
       count(DISTINCT sa.city_id) over (PARTITION BY sa.phone) AS count_cities
  FROM store_address sa
 WHERE sa.city_id IN (1, 2, 6)
 ORDER BY sa.phone NULLS LAST,
          sa.city_id

error: DISTINCT is not implemented for window functions