С помощью какой команды можно произвести автозамену null результата оконной функции в postgresql

оконная функция, которая игнорирует NULL

То есть окно нужно наложить на test_id, начать считать run_id в этом окне, причём run_id is NULL в расчёт брать не нужно. Пробовал делать через FIRST_VALUE(run_id) , но всё равно сводится к тому, что надо оборачивать в ещё один подзапрос или CTE.

Отслеживать
задан 8 июн 2020 в 17:55
347 1 1 серебряный знак 11 11 бронзовых знаков

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

select (case when r.RUN_ID is not null then row_number() over (partition by (case when r.RUN_ID is not null then t.test_id else 0 end) order by r.run_id ) end) as urun

Отслеживать
ответ дан 8 июн 2020 в 18:16
347 1 1 серебряный знак 11 11 бронзовых знаков

sql
sql-server

Важное на Мете

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Сравнение с NULL¶

Любое сравнение произвольного значения с NULL возвращает NULL (неопределенное значение):

denis=# select coalesce((1 <> null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((1 = null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((1  null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((1 > null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((null = null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((null <> null)::varchar, ''); coalesce ---------- (1 row)

Поэтому выражения/переменные, которые могут принимать неопределенные значения, надо осторожно использовать в сравнениях. Если в выражении попадётся неопределенное значение, то результат всего выражения может быть очень неожиданным.

Использование неопределенного значения в сложном сравнении с использование булевых операторов AND, OR, NOT тоже не несет в себе какого-нибудь позитива — хотя и результаты логических операция не противоречат болевой логики, но их сложно анализировать разработчикам.

denis=# select coalesce((null or true)::varchar, ''); coalesce ---------- true (1 row) denis=# select coalesce((null and true)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((null and false)::varchar, ''); coalesce ---------- false (1 row) denis=# select coalesce((null or false)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((not null)::varchar, ''); coalesce ---------- (1 row)

Для сравнения на равенство двух значение, которые могут принимать неопределенные значения, можно использовать конструкцию IS DISTINCT FROM, которая возвращает True, если значения отличаются друг от друга:

denis=# select coalesce((null is distinct from null)::varchar, ''); coalesce ---------- false (1 row) denis=# select coalesce((null is distinct from 1)::varchar, ''); coalesce ---------- true (1 row) denis=# select coalesce((1 is distinct from 1)::varchar, ''); coalesce ---------- false (1 row)

При использовании операторов is distinct from и is not distinct from в запросе индексы задействованы не будут.

denis=# explain select * from customers where id is not distinct from 222; QUERY PLAN ------------------------------------------------------------ Seq Scan on customers (cost=0.00..803.00 rows=1 width=17) Filter: (NOT (id IS DISTINCT FROM 222)) (2 rows)

denis=# explain select * from customers where id = 222; QUERY PLAN -------------------------------------------------------------------------------- Index Scan using customers_pkey on customers (cost=0.00..8.28 rows=1 width=17) Index Cond: (id = 222) (2 rows)

Это частично решает проблему сравнения неопределенных значений. Однако осталась проблема сравнений на больше-меньше. Это можно решить через функцию COALESCE:

denis=# select coalesce(1  null, false); coalesce ---------- f (1 row) denis=# select coalesce(2 > null, true); coalesce ---------- t (1 row)

При разработке разработке хранимых процедур на pl/pgsql вышеописанная проблема так же сохраняется, но не так остро. Хотя остаётся не менее коварной:

DECLARE _str text; _int int4; BEGIN _str := NULL; _int := NULL; IF _str <> '' THEN RAISE NOTICE 'str: True'; ELSE RAISE NOTICE 'str: False'; END IF; IF _int > 0 THEN RAISE NOTICE 'int: True'; ELSE RAISE NOTICE 'int: False'; END IF; IF NULL THEN RAISE NOTICE 'NULL: True'; ELSE RAISE NOTICE 'NULL: False'; END IF; RAISE NOTICE 'end'; END

NOTICE: str: False NOTICE: int: False NOTICE: NULL: False NOTICE: end

Неопределенное значение результата операции сравнения с NULL интерпретируется конструкцией IF NULL THEN ELSE END как False. Поэтому при простом сравнении переменной (которая может принимать NULL) с какой-либо константой можно проверки на NULL не делать:

IF _var IS NOT NULL AND _var > 0 THEN -- можно заменить на IF _var > 0 THEN IF _var IS NOT NULL AND _var = 'token' THEN -- можно за менить на IF _var = 'token' THEN .

В общем, будьте внимательны.

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

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

Как обрабатываются NULL значения при вычислении значения?
Как учитываются 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

Разбираем магию оконных функций (на примере PostgreSQL)

Всем привет! Рассмотрим очень полезный и невероятно интересный функционал реляционных БД – оконные функции.

Примеры работают в PostgreSQL, однако мы основное внимание уделим логике работы, которая заложена в сам принцип работы оконных функций и применяется в других SQL-диалектах – поэтому вы без труда сможете применять полученные знания практически в любой БД, делая поправку на синтаксис используемого диалекта. Также отметим — так как это вводная статья, мы решили ограничиться описанием базовых оконных функций, которые, вероятно, покроют 90% задач, в которых эти функции необходимы. Во второй статье углубимся в код, рассмотрим оконные функции с фреймами, а также познакомимся с другими оконными конструкциями, нередко помогающими в работе аналитику.

На первый взгляд может показаться, что оконные функции — это как group by. Вот отличие – конструкция group by собирает агрегат таблицы (изменяет количество строк в результирующем наборе данных, группирует строки), а оконные функции не группируют строки, а добавляют новые атрибуты, результат которых рассчитывает оконная функция.

Для удобства изучения мы решили сначала визуально показать, что из себя представляют оконные функции, дальше немного углубимся в логику и код. Итак, посмотрите на эту таблицу:

Здесь мы выделили атрибуты, относящиеся к источнику данных (первоначальной таблице, блок «Исходная таблица»), а также атрибуты, которые рассчитываются с помощью базовых оконных функций (блок «Оконные функции»). Мы умышленно в каждое последующее окно поместили на один элемент больше, чтобы можно было невооруженным взглядом понять суть оконной функции, то, как изменяется ее значение. Зависимости показали красными линиями – то есть на результат оконной функции sum() влияет только атрибут «PRICE», а оконные конструкции count() и row_number() используют количество строк (для примера мы сослались на атрибут «ID»).

Теперь стало понятнее? Отлично. Давайте разберем детально каждую из трех оконных функций.

Оконная конструкция SUM()

Сразу пишем код, потом разбираем, что делает каждый символ: