keep+over+nls_lang = bag или фича?

Vint
Дата: 24.03.2011 11:02:25
стандратные настройки базы nls_language = 'AMERICAN'
with t as (
select '01' idn from dual union all
select '11' idn from dual union all
select '29' idn from dual union all
select '20' idn from dual union all
select 'A0' idn from dual union all
select 'AA' idn from dual
)
select t.*,row_number() over (order by idn), max(idn) keep(dense_rank last order by idn) over()
from t order by idn


alter session set nls_language = 'RUSSIAN'
и еще раз запрос
with t as (
select '01' idn from dual union all
select '11' idn from dual union all
select '29' idn from dual union all
select '20' idn from dual union all
select 'A0' idn from dual union all
select 'AA' idn from dual
)
select t.*,row_number() over (order by idn), max(idn) keep(dense_rank last order by idn) over()
from t order by idn
в результате keep`у на изменившиеся настройки nls_language положить или это меня так старательно глючит?
проверял на 10.2.0.4 и 11.2.0.1
Vint
Дата: 24.03.2011 11:10:15
понятнее было бы вместо row_number() подставить
LAST_value(idn) over (order by idn rows between unbounded preceding and unbounded following)
-2-
Дата: 24.03.2011 11:14:25
keep работает внутри группы max- или min- значений.
alter session set nls_sort=binary;

select t.*,row_number() over (order by idn), max(idn) keep(dense_rank last order by idn) over()
, max(idn) over()
from t order by idn;

IDN ROW_NUMBER()OVER(ORDERBYIDN) MAX(IDN)KEEP(DENSE_RANKLASTORDERBYIDN)OVER() MAX(IDN)OVER() 
--- ---------------------------- -------------------------------------------- -------------- 
01  1                            AA                                           AA             
11  2                            AA                                           AA             
20  3                            AA                                           AA             
29  4                            AA                                           AA             
A0  5                            AA                                           AA             
AA  6                            AA                                           AA             


alter session set nls_sort=russian;

select t.*,row_number() over (order by idn), max(idn) keep(dense_rank last order by idn) over()
, max(idn) over()
from t order by idn;

IDN ROW_NUMBER()OVER(ORDERBYIDN) MAX(IDN)KEEP(DENSE_RANKLASTORDERBYIDN)OVER() MAX(IDN)OVER() 
--- ---------------------------- -------------------------------------------- -------------- 
AA  1                            AA                                           AA             
A0  2                            AA                                           AA             
01  3                            AA                                           AA             
11  4                            AA                                           AA             
20  5                            AA                                           AA             
29  6                            AA                                           AA             

Vint
Дата: 24.03.2011 11:18:55
мда.. надо было сразу писать с first_value пример))
+
--alter session set nls_language = 'RUSSIAN'
with t as (
select '01' idn from dual union all
select '11' idn from dual union all
select '29' idn from dual union all
select '20' idn from dual union all
select 'A0' idn from dual union all
select 'AA' idn from dual
)
select t.*,row_number() over (order by idn), max(idn) keep(dense_rank first order by idn) over(),
first_value(idn) over (order by idn rows between unbounded preceding and unbounded following)
from t order by idn
	AA	1	01	AA
	A0	2	01	AA
	01	3	01	AA
	11	4	01	AA
	20	5	01	AA
	29	6	01	AA

как обьяснить такой результат?)
-2-
Дата: 24.03.2011 11:28:24
Vint
как обьяснить такой результат?)
max/min используют сравнение (
-2-
Дата: 24.03.2011 11:28:55
Vint
как обьяснить такой результат?)
max/min используют сравнение (nls_comp), а не сортировку (nls_sort)
Vint
Дата: 24.03.2011 11:55:10
Все еще не совсем понимаю:
1.создаёт окно - over()
2. ранжирует по сортировке (dense_rank last order by idn)
3. от max/min(idn) уже ничего не зависит.
ткните доку или где почитать для просветления)
AmKad
Дата: 24.03.2011 11:57:36
Vint
3.

На твоем примере да.
Vint
Дата: 24.03.2011 12:01:57
AmKad,

то есть keep сортирует в разделе (dense_rank last order by idn) по параметру nls_comp а over (order by .... сортирует по nls_sort? имхо нелогично.
-2-
Дата: 24.03.2011 13:41:48
Vint
имхо нелогично.
keep
DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle Database will aggregate over only those rows with the minimum (FIRST) or the maximum (LAST) dense rank (also called olympic rank).

мое имхо - неоднознаное восприятие ключевой фразы order by в keep, но это решаемо:
with t as (
select '01' idn from dual union all
select '11' idn from dual union all
select '29' idn from dual union all
select '20' idn from dual union all
select 'A0' idn from dual union all
select 'AA' idn from dual
)
select t.*,row_number() over (order by idn), max(idn) keep(dense_rank last крайняк by idn) over()
, max(idn) over()
from t order by idn

IDN ROW_NUMBER()OVER(ORDERBYIDN) MAX(IDN)KEEP(DENSE_RANKLASTКРАЙНЯКBYIDN)OVER() MAX(IDN)OVER() 
--- ---------------------------- ---------------------------------------------- -------------- 
AA  1                            AA                                             29             
A0  2                            AA                                             29             
01  3                            AA                                             29             
11  4                            AA                                             29             
20  5                            AA                                             29             
29  6                            AA                                             29             

6 rows selected
Vint
имхо нелогично.
keep
DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle Database will aggregate over only those rows with the minimum (FIRST) or the maximum (LAST) dense rank (also called olympic rank).

мое имхо - неоднознаное восприятие ключевой фразы order by в keep, но это решаемо:
with t as (
select '01' idn from dual union all
select '11' idn from dual union all
select '29' idn from dual union all
select '20' idn from dual union all
select 'A0' idn from dual union all
select 'AA' idn from dual
)
select t.*,row_number() over (order by idn), max(idn) keep(dense_rank last крайняк by idn) over()
, max(idn) over()
from t order by idn

IDN ROW_NUMBER()OVER(ORDERBYIDN) MAX(IDN)KEEP(DENSE_RANKLASTКРАЙНЯКBYIDN)OVER() MAX(IDN)OVER() 
--- ---------------------------- ---------------------------------------------- -------------- 
AA  1                            AA                                             29             
A0  2                            AA                                             29             
01  3                            AA                                             29             
11  4                            AA                                             29             
20  5                            AA                                             29             
29  6                            AA                                             29             

6 rows selected