Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
 Что у вас показывает sum( X )over(order by X), когда в столбце 'X' есть NULL'ы ?  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
hi all

Дано: таблица `t2` с двумя int-полями:
CREATE TABLE T2(ID INT, X INT);
COMMIT;
INSERT INTO T2 (ID, X) VALUES (1, 1);
INSERT INTO T2 (ID, X) VALUES (2, NULL);
INSERT INTO T2 (ID, X) VALUES (3, 0);
INSERT INTO T2 (ID, X) VALUES (4, NULL);
INSERT INTO T2 (ID, X) VALUES (5, 2);
INSERT INTO T2 (ID, X) VALUES (6, NULL);
INSERT INTO T2 (ID, X) VALUES (7, 1);
INSERT INTO T2 (ID, X) VALUES (8, NULL);
INSERT INTO T2 (ID, X) VALUES (9, 0);
INSERT INTO T2 (ID, X) VALUES (10, NULL);
COMMIT;
Запрос-1:
select id,x,sum(x)over(order by  x  desc) from t2 order by x;

Запрос-2:
select id,x,sum(x)over(order by  id ) from t2 order by x;


Результат запроса-1 в Oracle 11.2.g по неведомым причинам содержит NULL'ы в sum()over():
        ID          X SUM(X)OVER(ORDERBYXDESC)
---------- ---------- ------------------------
9 0 4
3 0 4
1 1 4
7 1 4
5 2 2
4 ***null*** ***null***
6 ***null*** ***null***
2 ***null*** ***null***
8 ***null*** ***null***
10 ***null*** ***null***

Результат запроса-2 этого уже не содержит:
+
        ID          X SUM(X)OVER(ORDERBYID)
---------- ---------- ---------------------
9 0 4
3 0 1
1 1 1
7 1 4
5 2 3
6 ***null*** 3
4 ***null*** 1
8 ***null*** 4
2 ***null*** 1
10 ***null*** 4


Для сравнения, результаты запроса-1 в других СУБД:
Firebird 3.0:
+
SQL> select id,x,sum(x)over(order by x desc) from t2 order by x nulls last,id;

ID X SUM
============ ============ =====================
3 0 4
9 0 4
1 1 4
7 1 4
5 2 2
2 <null> 4
4 <null> 4
6 <null> 4
8 <null> 4
10 <null> 4

MS SQL 2008:
+
with 
r as(select 1 id union all select r.id+1 from r where r.id<10)
,t as(select id,iif(id%2<>0,id%3,null) x from r)
select id,x,sum(x)over(order by x desc) s from t
order by isnull(x, 999999),id
idxs
304
904
114
714
522
2NULL4
4NULL4
6NULL4
8NULL4
10NULL4
Что показывает запрос-1 в других СУБД, поддерживающих оконные функции ?
12 мар 14, 17:50    [15712396]     Ответить | Цитировать Сообщить модератору
 Re: Что у вас показывает sum( X )over(order by X), когда в столбце 'X' есть NULL'ы ?  [new]
qwwq
Member

Откуда:
Сообщений: 2894
Таблоид,

postgresql 9.3
select id,x,sum(x)over(order by  x desc NULLS LAST ) from t2 order by x ;

304
904
714
114
522
84
104
24
44
64


select id,x,sum(x)over(order by  x desc /*NULLS LAST*/ ) from t2 order by x ;

304
904
714
114
522
10
8
6
4
2

наверное разное дефолтное NULLS FIRST|LAST в разных субд
12 мар 14, 17:59    [15712458]     Ответить | Цитировать Сообщить модератору
 Re: Что у вас показывает sum( X )over(order by X), когда в столбце 'X' есть NULL'ы ?  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Таблоид,

+ А почему не должен?
with t as (
 select 1 id, 1 x from dual
  union all
 select 2 id, null x from dual
  union all
 select 3 id, 0 x from dual
  union all
 select 4 id, null x from dual
  union all
 select 5 id, 2 x from dual
)  
 select id,x,sum(x)over(order by  x desc nulls first) s
   from t t2
  order by x;

with t as (
 select 1 id, 1 x from dual
  union all
 select 2 id, null x from dual
  union all
 select 3 id, 0 x from dual
  union all
 select 4 id, null x from dual
  union all
 select 5 id, 2 x from dual
)  
 select id,x,sum(x)over(order by  x desc nulls last) s
  from t t2
  order by x;


а в документации написано:NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

12 мар 14, 18:05    [15712511]     Ответить | Цитировать Сообщить модератору
 Re: Что у вас показывает sum( X )over(order by X), когда в столбце 'X' есть NULL'ы ?  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
я сравнил еще результаты без всякого упоминания nulls first / last.
Вот для sum(x)over(order by x ASCENDING):
over(order by x asc)QueryIDXS
Firebird 3.0with recursive2nullnull
r as(select 1 id from rdb$database union all select r.id+1 from r where r.id<10)4nullnull
,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)6nullnull
select id,x,sum(x)over(order by x) s from t8nullnull
order by x,id;10nullnull
300
900
112
712
524
MS SQL 2008with idxs
r as(select 1 id union all select r.id+1 from r where r.id<10)2NULLNULL
,t as(select id,iif(id%2<>0,id%3,null) x from r)4NULLNULL
select id,x,sum(x)over(order by x ) s from t 6NULLNULL
order by x,id8NULLNULL
10NULLNULL
300
900
112
712
524
ORA 11.2 gwithIDXS
r as(select level+1 id from dual connect by level<10)300
,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)900
select id,x,sum(x)over(order by x) s from t 711
order by x,id;523
2***null***3
4***null***3
6***null***3
8***null***3
10***null***3

И вот для sum(x)over(order by x DESCENDING):
over(order by x desc)QueryIDXS
Firebird 3.0with recursive2null4
r as(select 1 id from rdb$database union all select r.id+1 from r where r.id<10)4null4
,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)6null4
select id,x,sum(x)over(order by x desc) s from t8null4
order by x,id;10null4
304
904
114
714
522
MS SQL 2008with idxs
r as(select 1 id union all select r.id+1 from r where r.id<10)2NULL4
,t as(select id,iif(id%2<>0,id%3,null) x from r)4NULL4
select id,x,sum(x)over(order by x desc) s from t 6NULL4
order by x,id8NULL4
10NULL4
304
904
114
714
522
ORA 11.2 gwithIDXS
r as(select level+1 id from dual connect by level<10)303
,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)903
select id,x,sum(x)over(order by x desc) s from t 713
order by x,id;522
2***null******null***
4***null******null***
6***null******null***
8***null******null***
10***null******null***

Чё-то настораживает, что в Оракле не сходится ни с чем: ни с MS, ни с PG, ни с FB...
12 мар 14, 18:30    [15712643]     Ответить | Цитировать Сообщить модератору
 Re: Что у вас показывает sum( X )over(order by X), когда в столбце 'X' есть NULL'ы ?  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
Пардон, в предыдущем посте ошибка в оракловом варианте запроса. в топку его.
Вопрос на самом деле вот какой: почему результаты в ФБ и в Оракле хотя и совпадают, выглядят "перевёрнутыми" друг отн. друга ?
DBMSQueryIDXSDBMSIDXS
Firebird 3.0with recursive2null4ORA 11.2 gwith300
order by x asc nulls lastr as(select 1 id from rdb$database union all select r.id+1 from r where r.id<10)4null4order by x asc nulls lastr as(select level id from dual connect by level<=10)900
,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)6null4,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)112
select id,x,sum(x)over(order by x nulls last) s from t8null4select id,x,sum(x)over(order by x nulls last) s from t712
order by x,id;10null4order by x,id;524
3002***null***4
9004***null***4
1126***null***4
7128***null***4
52410***null***4
Firebird 3.0with recursiveIDXSORA 11.2 gwithIDXS
order by x asc, nulls firstr as(select 1 id from rdb$database union all select r.id+1 from r where r.id<10)2nullnullorder by x asc, nulls firstr as(select level id from dual connect by level<=10)300
,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)4nullnull,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)900
select id,x,sum(x)over(order by x nulls first) s from t6nullnullselect id,x,sum(x)over(order by x nulls first) s from t112
order by x,id;8nullnullorder by x,id;712
10nullnull524
3002***null******null***
9004***null******null***
1126***null******null***
7128***null******null***
52410***null******null***
Firebird 3.0with recursiveIDXSORA 11.2 gwithIDXS
order by x desc, nulls lastr as(select 1 id from rdb$database union all select r.id+1 from r where r.id<10)2null4order by x desc, nulls lastr as(select level id from dual connect by level<=10)304
,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)4null4,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)904
select id,x,sum(x)over(order by x desc nulls last) s from t6null4select id,x,sum(x)over(order by x desc nulls last) s from t114
order by x,id;8null4order by x,id;714
10null4522
3042***null***4
9044***null***4
1146***null***4
7148***null***4
52210***null***4
Firebird 3.0with recursiveIDXSORA 11.2 gwithIDXS
order by x desc, nulls firstr as(select 1 id from rdb$database union all select r.id+1 from r where r.id<10)2nullnullorder by x desc, nulls firstr as(select level id from dual connect by level<=10)304
,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)4nullnull,t as(select id,case when mod(id,2)<>0 then mod(id,3) else null end x from r)904
select id,x,sum(x)over(order by x desc nulls first) s from t6nullnullselect id,x,sum(x)over(order by x desc nulls first) s from t114
order by x,id;8nullnullorder by x,id;714
10nullnull522
3042***null******null***
9044***null******null***
1146***null******null***
7148***null******null***
52210***null******null***
12 мар 14, 18:57    [15712778]     Ответить | Цитировать Сообщить модератору
 Re: Что у вас показывает sum( X )over(order by X), когда в столбце 'X' есть NULL'ы ?  [new]
dimitr
Member

Откуда: PNZ
Сообщений: 7008
Таблоид,

ты читаешь, что тебе выше пишут? Сергей Арсеньев вроде ответил уже.
12 мар 14, 19:09    [15712854]     Ответить | Цитировать Сообщить модератору
 Re: Что у вас показывает sum( X )over(order by X), когда в столбце 'X' есть NULL'ы ?  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
dimitr
ты читаешь, что тебе выше пишут? Сергей Арсеньев вроде ответил уже.
не увидел ответы, сидел "внутри себя" :-)
Вроде всё ясно, вопрос закрыт.
12 мар 14, 19:19    [15712888]     Ответить | Цитировать Сообщить модератору
Все форумы / Сравнение СУБД Ответить