with
-- emulation of test table
virt_table( id, val ) as (
select 1, 1 from dual union all
select 2, 2 from dual union all
select 3, null from dual union all
select 4, null from dual union all
select 5, 5 from dual
),
-- test data without nulls
clear_nulls as (
select id, nvl(val, 0) val from virt_table
),
-- comparison of test data
compare( count_rows, count_val, sum_val, avg1_val, avg2_val, avg3_val, commentary ) as (
-- with nulls
select
count(*),
count(val),
sum(val),
avg(val),
sum(val) / count(val),
sum(val) / count(*),
'with nulls' commentary
from virt_table
union all
-- without nulls
select
count(*),
count(nvl(val, 0)),
sum(nvl(val, 0)),
avg(nvl(val, 0)),
sum(nvl(val, 0)) / count(nvl(val, 0)),
sum(nvl(val, 0)) / count(*),
'manual cleaning of nulls' commentary
from virt_table
union all
select
count(*),
count(val),
sum(val),
avg(val),
sum(val) / count(val),
sum(val) / count(*),
'general cleaning of nulls' commentary
from clear_nulls
)
select * from compare
Приведу для примера и поведение аналитических функций с пустыми значениями.
with
-- emulation of test table
virt_table( id, val ) as (
select 1, 1 from dual union all
select 2, 2 from dual union all
select 3, null from dual union all
select 4, null from dual union all
select 5, 5 from dual
),
-- test data without nulls
clear_nulls as (
select id, nvl(val, 0) val from virt_table
),
-- for testing analytical functions with nulls
test( count_rows, count_val, sum_val, avg_val ) as (
select
count(*) over (),
count(val) over (),
sum(val) over (),
avg(val) over ()
from virt_table
union all
select
count(*) over (),
count(val) over (),
sum(val) over (),
avg(val) over ()
from clear_nulls
)
select * from test
Для удобства результаты аналитических функций разделены красной линией (верхняя часть - с пустыми значениями, нижняя - без пустых значений). Разница налицо. Поэтому общий вывод таков, необходимо помнить о таких аномалиях и быть очень внимательными с такого рода неопределенного состояния полями. Стоит также отметить, что наличие таких полей в выборках определенных запросов также может приводить к невозможности выполнения последующих подзапросов. Вот один из классических примеров, который можно встретить при работе с иерархическими таблицами. Рассмотрим следующий запрос
with
-- emulation of test table
nodes( node, name, parent ) as (
select 1, 'root', null from dual union all
select 2, 'node2', 1 from dual union all
select 3, 'node3', 2 from dual union all
select 4, 'node4', 3 from dual union all
select 5, 'node5', 4 from dual
),
-- get path of nodes -- /root/node2/node3/...
get_nodes_path as (
select level, sys_connect_by_path ( name, '/' ) path
from nodes
connect by prior node = parent
start with parent is null
),
-- get just root
get_root as (
select node, name, parent
from nodes
where parent is null
),
-- get just leaf -- wrong variant
get_leaf_wrong as (
select node, name, parent
from nodes
where node not in (
select parent
from nodes
)
),
-- get just leaf -- correct variant
get_leaf_correct as (
select node, name, parent
from nodes
where node not in (
select parent
from nodes
where parent is not null
)
)
select * from get_nodes_path
Как видно, мы имеем дело с простым деревом. Теперь о классическом примере. Допустим, нам нужно найти все концевые узлы нашего дерева, точнее - листья. В приведенном выше запросе для этого предусмотрены два вложенных представления - get_leaf_wrong и get_leaf_correct. Вроде бы, если обратиться к представлению get_leaf_wrong то мы должны получить ожидаемые листья дерева, но здесь есть нюанс, так как в выборке
select parent
from nodes
содержится значение NULL (присущее корневому узлу), которое нельзя сравнивать с другими значениями, на выходе мы получим пустой результат. Если же исключить строку с данным неопределенным значением, что сделано в представлении - get_leaf_correct, тогда будет получен правильный ответ
