Оконные функции

Обработка 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_idphonecount_phones_in_citycount_rows_in_city
117(495)312‒03‒0822
217(495)312‒03‒0822
327(812)700‒03‒0312
42NULL12
56NULL02
66NULL02

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
#phonecity_idcount_cities
17(495)312‒03‒0812
27(495)312‒03‒0812
37(812)700‒03‒0321
4NULL23
5NULL63
6NULL63

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

#phonecity_idcount_cities
17(495)312‒03‒0812
27(495)312‒03‒0812

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