Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Подскажите по collect(distinct)  [new]
Alex2001
Member

Откуда:
Сообщений: 186
Запрос такой -
select con_char( cast(collect(UNIQUE  CODE) as VARCHAR3)) from DATA 
group by source, id;
Функция con_char соединяет несколько строк в одну. Похоже как -то не так работает UNIQUE - ditinct - с итоге получаю соединение одинаковых строчек. оракл 11.1 solaris. Подскажите пожалуйста, в чем дело, заранее спасибо. На 10-ке express edition windows все нормально.
27 апр 10, 18:15    [8697959]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
Elic
Member

Откуда:
Сообщений: 29976
Alex2001
Похоже как-то не так работает UNIQUE - ditinct
select * from table((select collect(distinct mod(level, 3)) from dual connect by level <= 10));

 COLUMN_VALUE
-------------
            0
            1
            2
27 апр 10, 18:55    [8698204]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
Alex2001
Member

Откуда:
Сообщений: 186
Извините, не совсем понял, не подскажете, как изменить оригинальный запрос, чтобы не повторялись значения?
27 апр 10, 19:03    [8698258]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
Alex2001
Member

Откуда:
Сообщений: 186
Немножко упростил запрос
select cast(collect(UNIQUE  STUDY_CODE) as VARCHAR3) from DATA group by source, id;
уже без самописной функции - в рез-те получаю набор строк вида

ALEX.VARCHAR3('4','1')
ALEX.VARCHAR3('1','1')
ALEX.VARCHAR3('1','1','1','1')

Подскажите, где же тут уникальность, или я чего-то не понимаю :( ?

При этом БЕЗ группировки - все хорошо т.е. значения в строке уникальны

ALEX.VARCHAR3('1','4')

select cast(collect(UNIQUE  STUDY_CODE) as VARCHAR3) from DATA;
28 апр 10, 13:22    [8701894]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
Elic
Member

Откуда:
Сообщений: 29976
Alex2001
При этом БЕЗ группировки - все хорошо т.е. значения в строке уникальны
Это явно похоже на баг.
Distinct в collect не работает при группировке, а также при упорядочивании:
SQL> select    collect(distinct y                ) as yy from (select trunc(level/10) as x, mod(level, 3) as y from dual connect by level <= 11);

YY
----------------------------------------------------------------------------------------------------
SYSTPhpIti98xSNOfS+7yQ0L+eA==(0, 1, 2)

SQL> select    collect(distinct y order by y desc) as yy from (select trunc(level/10) as x, mod(level, 3) as y from dual connect by level <= 11);

YY
----------------------------------------------------------------------------------------------------
SYSTPhpIti98xSNOfS+7yQ0L+eA==(2, 2, 2, 2, 1, 1, 1, 1, 0, 0, 0)

SQL> select x, collect(distinct y                ) as yy from (select trunc(level/10) as x, mod(level, 3) as y from dual connect by level <= 11) group by x;

            X YY
------------- ----------------------------------------------------------------------------------------------------
            0 SYSTPhpIti98xSNOfS+7yQ0L+eA==(1, 2, 0, 2, 1, 0, 2, 0, 1)
            1 SYSTPhpIti98xSNOfS+7yQ0L+eA==(1, 2)

SQL> select x, collect(distinct y order by y desc) as yy from (select trunc(level/10) as x, mod(level, 3) as y from dual connect by level <= 11) group by x;

            X YY
------------- ----------------------------------------------------------------------------------------------------
            0 SYSTPhpIti98xSNOfS+7yQ0L+eA==(2, 2, 2, 1, 1, 1, 0, 0, 0)
            1 SYSTPhpIti98xSNOfS+7yQ0L+eA==(2, 1)
P.S. 10.2, 11.2
28 апр 10, 13:51    [8702108]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
-2-
Member

Откуда:
Сообщений: 15330
Alex2001,

Вы пользуетесь недокументированными особенностями
SQL Reference

COLLECT (column)

COUNT({ * | [ DISTINCT | ALL ] expr })
[ OVER (analytic_clause) ]

COUNT(DISTINCTCODE)    CAST(COLLECT(DISTINCTCODE)ASVARCHAR2_TABLE)     SET(CAST(COLLECT(CODE)ASVARCHAR2_TABLE))    
---------------------- ----------------------------------------------- --------------------------------------------
2                      VARCHAR2_TABLE('1','4','4','1')                 VARCHAR2_TABLE('1','4')              
2                      VARCHAR2_TABLE('1','4')                         VARCHAR2_TABLE('1','4')              
1                      VARCHAR2_TABLE('1')                             VARCHAR2_TABLE('1')                  

28 апр 10, 13:55    [8702137]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
Elic
Member

Откуда:
Сообщений: 29976
-2-
Вы пользуетесь недокументированными особенностями
А ведь точно. Задокументировано это только в 11gR2. Похоже, они эту фичу всё ещё допиливают :)
28 апр 10, 14:05    [8702217]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
-2-
Member

Откуда:
Сообщений: 15330
Elic
Задокументировано это только в 11gR2.
значит для 11.2 это уже баг.

Странно, что ORDER BY для COLLECT и LISTAGG сделан по разному. Закончились в оракле архитекторы или оставили COLLECT, как недокументированно работло в 11.1?
28 апр 10, 14:45    [8702529]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
Alex2001
Member

Откуда:
Сообщений: 186
Спасибо всем большое.
28 апр 10, 17:46    [8704437]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
Elic
А ведь точно. Задокументировано это только в 11gR2. Похоже, они эту фичу всё ещё допиливают :)


Before допиливать they better clearly define what COLLECT is and what it does. Oracle 10.2.0.4.0. According to docs COLLECT is classified as collection function - it creates a nested table of column values of rows fetched by the statement:

select 10,collect(sal) from emp where deptno = 10;

        10 X
---------- --------------------------------------------------
        10 SYSTP+F1j03raRIyzbE1zthU/3Q==(2450, 5000, 1300)

SQL> 

But look what happens when select list has multiple elements and element outside collect is column expression:

select deptno,collect(sal) x from emp where deptno = 10;
select deptno,collect(sal) x from emp where deptno = 10
       *
ERROR at line 1:
ORA-00937: not a single-group group function


select deptno,collect(sal) x from emp where deptno = 10 group by deptno;

    DEPTNO X
---------- --------------------------------------------------
        10 SYSTP+F1j03raRIyzbE1zthU/3Q==(2450, 5000, 1300)

SQL> 

So why the above did not fail if COLLECT is classified as collection function and not as aggregate function? And since the above did not fail, COLLECT must be also an aggregate function, which is quite logical but still should be documented. OK. Next thing is COLLECT (column). Why column and not expression? It works perfectly with expressions:

select deptno,collect(sal + comm) x from emp group by deptno
/

    DEPTNO X
---------- -------------------------------------------------------
        10 SYSTPj640K0VaQrmWyQlzWEpMFA==()
        20 SYSTPj640K0VaQrmWyQlzWEpMFA==()
        30 SYSTPj640K0VaQrmWyQlzWEpMFA==(1900, 1750, 1500, 2650)

SQL> 

Now see what happens when we throw analytic function into a mix:

select row_number() over(order by 1) x,collect(deptno) y from emp
/

          X Y
----------- ---------------------------------------------------------------------------------------------------------
          1 SYSTPSV5FnCsZSMidEIlbCW+oIw==(20, 30, 30, 20, 30, 30, 10, 20, 10, 30, 20, 30, 20, 10)

SQL>     

Good, row_number was calculated after COLLECT, but see what happens if we try to force COLLECT after row_number is calculated:

select collect(row_number() over(order by 1)) x,collect(deptno) y from emp
/

X                                        Y
---------------------------------------- --------------------------------------------------------------------------------------
SYSTPj640K0VaQrmWyQlzWEpMFA==()          SYSTPSV5FnCsZSMidEIlbCW+oIw==(20, 30, 30, 20, 30, 30, 10, 20, 10, 30, 20, 30, 20, 10)

SQL> 

Empty collection. But logically it should raise an error, same like:

SQL> select max((row_number() over(order by 1)) x,max(deptno) y from emp
  2  /
select max((row_number() over(order by 1)) x,max(deptno) y from emp
            *
ERROR at line 1:
ORA-30483: window  functions are not allowed here


SQL> 

Questions, questions, questions...

SY.
28 апр 10, 21:25    [8705276]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
-2-
Member

Откуда:
Сообщений: 15330
SY
And since the above did not fail, COLLECT must be also an aggregate function, which is quite logical but still should be documented.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i89203
The aggregate functions are:

AVG
COLLECT
CORR
CORR_*
COUNT
...
Но все равно, функция не от мира сего. А еще она недокументированно аналитическая, но на 11.1.0.7:
with t as (
   select level x from dual connect by level <= 3
)
select cast(collect(x) over() as number_table) 
from t
;

ОК

with t as (
   select level x from dual connect by level <= 3
)
select cast(collect(x) over(order by x) as number_table) 
from t
;

Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x40481BB, __intel_new_memcpy()+3547]
28 апр 10, 21:50    [8705331]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
Elic
Member

Откуда:
Сообщений: 29976
-2-
SY
but still should be documented.
The aggregate functions are:
COLLECT
+1 :)
Сам не пользуюсь :)
28 апр 10, 22:19    [8705372]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по collect(distinct)  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
-2-,

Thanks, I missed it in aggregate function list. More interesting observations on my 11.0.6.0:

create or replace
type NumList
as table of number
/
with t as (
   select level x from dual connect by level <= 3
)
select cast(collect(x) over(
                            order by 1
                           ) as NumList)
from t
/

CAST(COLLECT(X)OVER(ORDERBY1)ASNUMLIST)
----------------------------------------
NUMLIST(1, 2, 3)
NUMLIST(1, 2, 3)
NUMLIST(1, 2, 3)

with t as (
   select sal x from emp
)
select cast(collect(x) over(order by 1) as NumList)
from t
/
select cast(collect(x) over(order by 1) as NumList)
            *
ERROR at line 4:
ORA-22814: attribute or element value is larger than specified in type

create or replace
type NumList
as table of number(7,2)
/
with t as (
   select sal x from emp
)
select cast(collect(x) over(order by 1) as NumList)
from t
/

CAST(COLLECT(X)OVER(ORDERBY1)ASNUMLIST)
------------------------------------------------------------------------------------------
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)

CAST(COLLECT(X)OVER(ORDERBY1)ASNUMLIST)
------------------------------------------------------------------------------------------
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)

14 rows selected.

with t as (
   select sal x from emp
)
select cast(collect(x) over(partition by x order by x) as NumList)
from t
/
CAST(COLLECT(X)OVER(PARTITIONBYXORDERBYX)ASNUMLIST)
-----------------------------------------------------
NUMLIST(800)
NUMLIST(950)
NUMLIST(1100)
NUMLIST(1250, 1250)
NUMLIST(1250, 1250)
NUMLIST(1300)
NUMLIST(1500)
NUMLIST(1600)
NUMLIST(2450)
NUMLIST(2850)
NUMLIST(2975)

CAST(COLLECT(X)OVER(PARTITIONBYXORDERBYX)ASNUMLIST)
-----------------------------------------------------
NUMLIST(3000, 3000)
NUMLIST(3000, 3000)
NUMLIST(5000)

14 rows selected.

with t as (
   select sal x from emp
)
select cast(collect(x) over(order by 1 rows between unbounded preceding and current row) as NumList)
from t
/

ERROR:
ORA-29400: data cartridge error
ORA-00600: internal error code, arguments: [kokbCollTerminate], [13], [], [], [], [], [], []


This works, but does not order:

with t as (
   select sal x from emp
)
select cast(collect(x) over(order by 1 rows between unbounded preceding and unbounded following) as NumList)
from t
/


CAST(COLLECT(X)OVER(ORDERBY1ROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASNUMLIST)
--------------------------------------------------------------------------------------------------
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)

CAST(COLLECT(X)OVER(ORDERBY1ROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASNUMLIST)
--------------------------------------------------------------------------------------------------
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)
NUMLIST(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300)

14 rows selected.

And finally:

with t as (
   select sal x from emp
)
select cast(collect(x) over(order by x range between unbounded preceding and unbounded following) as NumList)
from t
/


CAST(COLLECT(X)OVER(ORDERBYXRANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASNUMLIST)
-------------------------------------------------------------------------------------------
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)

CAST(COLLECT(X)OVER(ORDERBYXRANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASNUMLIST)
-------------------------------------------------------------------------------------------
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)
NUMLIST(800, 950, 1100, 1250, 1250, 1300, 1500, 1600, 2450, 2850, 2975, 3000, 3000, 5000)

14 rows selected.

SQL> 

SY.

Сообщение было отредактировано: 28 апр 10, 23:04
28 апр 10, 23:01    [8705441]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить