Window function IGNORE NULLS workaround for PostgreSQL [duplicate]
With the following query I can use the LAG() function to repeat the last non null value of c column:
Getting the following result:
But I need to repeat the value of the c column while the current column value is null. I see that if PostgreSQL supports IGNORE NULLS attribute on window functions this would be solved. How to solve this without IGNORE NULLS ?
1 Answer 1
LAG() doesn’t repeat the last non null value.
Quoted from docs
returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value)
But you can set a partition depending on one column value and then use firts_value() function.
-
Featured on Meta
Linked
Related
Hot Network Questions
Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.9.6.43612
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.
How to ignore nulls in PostgreSQL window functions? or return the next non-null value in a column
I need another column indicating the next non-null COL1 value for each row, so the result would look like the below:
would work but I’m using PostgreSQL which doesn’t support the ignore nulls clause.
Any suggested workarounds?
5 Answers 5
You can still do it with windowing function if you add a case when criteria in the order by like this:
This will use non null values first.
However performance will probably not be great compared to skip nulls because the database will have to sort on the additional criteria.
Обработка NULL значений
Часто задают вопрос, как ведут себя агрегатные оконные функции с NULL значениями. Разобьем вопрос на два:
- Как обрабатываются NULL значения при вычислении значения?
- Как учитываются NULL значения при разделении данных на группы в PARTITION BY ?
Если отвечать коротко, то так же, как и в обычных агрегатных функциях.
NULL при вычислении значения
Все агрегатные функции, кроме count(*) игнорируют NULL значения.
Выведем сколько магазинов в каждом городе и для скольки из них заданы телефоны:
| # | 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 ).
Для номера телефона выведем в скольки городах он используется:
| # | 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 не реализована 🙁
С помощью какой команды можно произвести автозамену null результата оконной функции в postgresql
Оконные функции дают возможность выполнять вычисления с набором строк, каким-либо образом связанным с текущей строкой запроса. Вводную информацию об этом можно получить в Разделе 3.5, а подробнее узнать о синтаксисе можно в Подразделе 4.2.8.
Встроенные оконные функции перечислены в Таблице 9.56. Заметьте, что эти функции должны вызываться именно как оконные, т. е. при вызове необходимо использовать предложение OVER .
В дополнение к этим функциям в качестве оконных можно использовать любые встроенные или пользовательские обычные (но не сортирующие и не гипотезирующие) агрегатные функции (встроенные функции перечислены в Разделе 9.20). Агрегатные функции работают как оконные, только когда за их вызовом следует предложение OVER ; в противном случае они останутся обычными агрегатными.
Таблица 9.56. Оконные функции общего назначения
| Функция | Тип результата | Описание |
|---|---|---|
| row_number() | bigint | номер текущей строки в её разделе, начиная с 1 |
| rank() | bigint | ранг текущей строки с пропусками; то же, что и row_number для первой родственной ей строки |
| dense_rank() | bigint | ранг текущей строки без пропусков; эта функция считает группы родственных строк |
| percent_rank() | double precision | относительный ранг текущей строки: ( rank — 1) / (общее число строк — 1) |
| cume_dist() | double precision | относительный ранг текущей строки: (число строк, предшествующих или родственных текущей) / (общее число строк) |
| ntile( число_групп integer ) | integer | ранжирование по целым числам от 1 до значения аргумента так, чтобы размеры групп были максимально близки |
| lag( значение anyelement [, смещение integer [, по_умолчанию anyelement ]]) | тип аргумента значение | возвращает значение для строки, положение которой задаётся смещением от текущей строки к началу раздела; если такой строки нет, возвращается значение по_умолчанию (оно должно иметь тот же тип, что и значение ). Оба параметра смещение и по_умолчанию вычисляются для текущей строки. Если они не указываются, то смещение считается равным 1, а по_умолчанию — NULL |
| lead( значение anyelement [, смещение integer [, по_умолчанию anyelement ]]) | тип аргумента значение | возвращает значение для строки, положение которой задаётся смещением от текущей строки к концу раздела; если такой строки нет, возвращается значение по_умолчанию (оно должно иметь тот же тип, что и значение ). Оба параметра смещение и по_умолчанию вычисляются для текущей строки. Если они не указываются, то смещение считается равным 1, а по_умолчанию — NULL |
| first_value( значение any ) | тип аргумента значение | возвращает значение , вычисленное для первой строки в рамке окна |
| last_value( значение any ) | тип аргумента значение | возвращает значение , вычисленное для последней строки в рамке окна |
| nth_value( значение any , n integer ) | тип аргумента значение | возвращает значение , вычисленное в н-ой строке в рамке окна (считая с 1), или NULL, если такой строки нет |
Результат всех функций, перечисленных в Таблице 9.56, зависит от порядка сортировки, заданного предложением ORDER BY в определении соответствующего окна. Строки, которые являются одинаковыми с точки зрения сортировки ORDER BY , считаются родственными; четыре функции, вычисляющие ранг, реализованы так, что их результат будет одинаковым для любых двух родственных строк.
Заметьте, что функции first_value , last_value и nth_value рассматривают только строки в « рамке окна » , которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей. Поэтому результаты last_value и иногда nth_value могут быть не очень полезны. В таких случаях можно переопределить рамку, добавив в предложение OVER подходящее указание ( RANGE или ROWS ). Подробнее эти указания описаны в Подразделе 4.2.8.
Когда в качестве оконной функции используется агрегатная, она обрабатывает строки в рамке текущей строки. Агрегатная функция с ORDER BY и определением рамки окна по умолчанию будет вычисляться как « бегущая сумма » , что может не соответствовать желаемому результату. Чтобы агрегатная функция работала со всем разделом, следует опустить ORDER BY или использовать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING . Используя другие указания в определении рамки, можно получить и другие эффекты.
Примечание
В стандарте SQL определены параметры RESPECT NULLS или IGNORE NULLS для функций lead , lag , first_value , last_value и nth_value . В PostgreSQL такие параметры не реализованы: эти функции ведут себя так, как положено в стандарте по умолчанию (или с подразумеваемым параметром RESPECT NULLS ). Также функция nth_value не поддерживает предусмотренные стандартом параметры FROM FIRST и FROM LAST : реализовано только поведение по умолчанию (с подразумеваемым параметром FROM FIRST ). (Получить эффект параметра FROM LAST можно, изменив порядок ORDER BY на обратный.)