Обработка 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
# city_id phone count_phones_in_city count_rows_in_city
1 1 7(495)312‒03‒08 2 2
2 1 7(495)312‒03‒08 2 2
3 2 7(812)700‒03‒03 1 2
4 2 NULL 1 2
5 6 NULL 0 2
6 6 NULL 0 2

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
# phone city_id count_cities
1 7(495)312‒03‒08 1 2
2 7(495)312‒03‒08 1 2
3 7(812)700‒03‒03 2 1
4 NULL 2 3
5 NULL 6 3
6 NULL 6 3

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

# phone city_id count_cities
1 7(495)312‒03‒08 1 2
2 7(495)312‒03‒08 1 2

то видно, что город на самом деле один, а не два, как мы получили. Функция 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

11.6 Агрегатные оконные функции

11.8 Нарастающий итог SUM + ORDER BY