Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / спецификация окна в listagg / 23 сообщений из 23, страница 1 из 1
03.11.2016, 15:54
    #39340938
bhr
bhr
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
Всем здрасти.

with a as (select 1 id, 1 srt,'txt1' txt
from dual
union all
select 1 , 2 ,'txt2'
from dual
union all
select 1 , 3 ,'txt3'
from dual
union all
select 1 , 4 ,'txt4'
from dual
union all
select 1 , 5 ,'txt5'
from dual
union all
select 1 , 6 ,'txt6'
from dual)
select a.*,
listagg(a.txt,', ') within group(order by a.srt) over (partition by id ) lstagg,
sum(a.srt) over(partition by id order by a.srt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) sm

from a

в функции sum спецификацию окна могу задать. а в листагг - никак.
Так задумано или я чегото не знаю?
и еще... как задать спецификацию окна чтоб в агрегации участвовали первые 10 строк партиции?
наверно, не у меня одного такая проблема - ограничить количество сцепляемых строк listagg первыми n строками.
Я, конечно, извратился и нашел решение. Но хотелось бы стандартными способами. без извратов.
Буду благодарен любому совету.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 16:12
    #39340953
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
bhr,

Тут ноги растут прежде всего из факта, что склейка строк не коммутативная операция в отличие от других.
То есть a || b <> b || a в отличие, например, от суммы или среднего.

Соответственно listagg позволяет указывать сортировку результата, но не сортировку окна.
С другой стороны stragg позволяет сортировать в окне, но сортировку в результирующей строке указать невозможно. Это вполне закономерно, поскольку в UDAG не предусмотрена возможность учитывать некоммутативность операции.

Возвращаясь к listagg, не совсем понятно почему не сделали возможность указывать order by (и distinct, но это другая проблема и более легко решаемая).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 16:18
    #39340963
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
bhr,

"как задать спецификацию окна чтоб в агрегации участвовали первые 10 строк партиции? "

Как вариант - пронумеровать сначало row_number()
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 16:30
    #39340975
bhr
bhr
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
dbms_photoshopbhr,

Тут ноги растут прежде всего из факта, что склейка строк не коммутативная операция в отличие от других.
То есть a || b <> b || a в отличие, например, от суммы или среднего.

Соответственно listagg позволяет указывать сортировку результата, но не сортировку окна.
С другой стороны stragg позволяет сортировать в окне, но сортировку в результирующей строке указать невозможно. Это вполне закономерно, поскольку в UDAG не предусмотрена возможность учитывать некоммутативность операции.

Возвращаясь к listagg, не совсем понятно почему не сделали возможность указывать order by (и distinct, но это другая проблема и более легко решаемая).
то есть сделать это невозможно?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 16:35
    #39340983
bhr
bhr
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
ora601bhr,

"как задать спецификацию окна чтоб в агрегации участвовали первые 10 строк партиции? "

Как вариант - пронумеровать сначало row_number()
и?
ограничить выборку первыми десятью строчками партиции?
мне нужны все строчки. и чтоб у двадцатой строки была агрегация первых 10 сточек.
я конечно использовал row_number() и использовал listagg group by/ а потом основную таблицу связывал с этой агрегацией.
Но хотелось бы через аналитические функции.
И еще.... фиг с ней с листагг. неужели нет простого способа задать окно "первые десять строчек"?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 16:41
    #39340991
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
bhrora601bhr,

"как задать спецификацию окна чтоб в агрегации участвовали первые 10 строк партиции? "

Как вариант - пронумеровать сначало row_number()

ограничить выборку первыми десятью строчками партиции?

sum() over(order by rn range between 1 AND 10) .

Простого способа нет)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 16:44
    #39340994
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
bhr,

Для твоей задачи просто создай и используй stragg, там можно указывать определения окна по строкам.

Если же стоит цель, например аналитическая склейка и результат в обратном порядке (столбец col в примере ниже), то тут никак listagg/stragg не помогут.
Решается встроенными функциями через склейку всего + regexp или через PL/SQL
Про решения через model и recursive subquery factoring речь не идет ибо баловство.

Код: plsql
1.
2.
3.
4.
5.
6.
select a.*,
       f(cast(collect(cast(a.txt as varchar2(100))) over (partition by id) as sys.odcivarchar2list), srt) col,
       cast(str_agg(a.txt || ' ') over (partition by id order by a.srt) as varchar2(4000)) stragg,
       listagg(a.txt, ', ') within group (order by a.srt desc) over (partition by id) lstagg
  from a
order by srt



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
        ID        SRT TXT  COL                                 STRAGG                              LSTAGG                             
---------- ---------- ---- ----------------------------------- ----------------------------------- -----------------------------------
         1          1 txt1 txt1                                txt1                                txt6, txt5, txt4, txt3, txt2, txt1 
         1          2 txt2 txt2 txt1                           txt1 txt2                           txt6, txt5, txt4, txt3, txt2, txt1 
         1          3 txt3 txt3 txt2 txt1                      txt1 txt2 txt3                      txt6, txt5, txt4, txt3, txt2, txt1 
         1          4 txt4 txt4 txt3 txt2 txt1                 txt1 txt2 txt3 txt4                 txt6, txt5, txt4, txt3, txt2, txt1 
         1          5 txt5 txt5 txt4 txt3 txt2 txt1            txt1 txt2 txt3 txt4 txt5            txt6, txt5, txt4, txt3, txt2, txt1 
         1          6 txt6 txt6 txt5 txt4 txt3 txt2 txt1       txt1 txt2 txt3 txt4 txt5 txt6       txt6, txt5, txt4, txt3, txt2, txt1 

6 rows selected.

 
f
Код: plsql
1.
2.
3.
4.
5.
6.
create or replace function f(c in sys.odcivarchar2list, n in number) return varchar2 as
result varchar2(4000);
begin
  for i in reverse 1..n loop result := result || c(i) || ' '; end loop;
  return result;
end;

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 16:51
    #39341006
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
ora601bhrпропущено...


ограничить выборку первыми десятью строчками партиции?

sum() over(order by rn range between 1 AND 10) .

Простого способа нет)

Точнее :
Код: plsql
1.
select t.*, sum(CASE WHEN rn>10 THEN 0 ELSE a.srt END) over (order by n rows between unbounded preceding and unbounded following) FROM t
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 16:53
    #39341008
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
ora601,

ТС спрашивает про склейку строк, а ты ему про сумму вещаешь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 17:08
    #39341027
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
dbms_photoshopora601,

ТС спрашивает про склейку строк, а ты ему про сумму вещаешь.
Я ему вещаю про первые 10 строк в партиции, если что)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 17:16
    #39341032
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
ora601 Я ему вещаю про первые 10 строк в партиции, если что)

И что? LISTAGG не имеет windowing и посему не хавает ROWS/RANGE PRECEDING/FOLLOWING.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 17:17
    #39341033
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
ora601dbms_photoshopora601,

ТС спрашивает про склейку строк, а ты ему про сумму вещаешь.
Я ему вещаю про первые 10 строк в партиции, если что)
Ему вроде как надо склейку, но даже если речь про сумму, то нет смысла указывать то, в чем нет надобности
ora601Точнее :
Код: plaintext
1.
select t.*, sum(CASE WHEN rn>10 THEN 0 ELSE a.srt END) over (partition by id order by n rows between unbounded preceding and unbounded following) FROM t
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 17:18
    #39341035
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
SYora601 Я ему вещаю про первые 10 строк в партиции, если что)

И что? LISTAGG не имеет windowing и посему не хавает ROWS/RANGE PRECEDING/FOLLOWING.

SY.

Я говорю за work-around, а не за решение в лоб. Тем более там вопрос не только по listagg, но и по аналитике.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 17:25
    #39341040
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
bhr,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
select t.*, regexp_replace(lstagg || ' ', '^([^ ]+ ){' || to_char(srt - 1) || '}(([^ ]+ ){1,3})(.*)', '\2') moving_3
  from (select a.*,
               listagg(a.txt, ' ') within group (order by a.srt) over (partition by id) lstagg,
               listagg(case when srt <= 3 then a.txt end, ' ') within group (order by a.srt) over (partition by id) first_3,
               str_agg(a.txt || ' ') over (partition by id order by a.srt rows between current row and 2 following) stragg
          from a
        order by srt) t



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
        ID        SRT TXT  LSTAGG                              FIRST_3         STRAGG          MOVING_3       
---------- ---------- ---- ----------------------------------- --------------- --------------- ---------------
         1          1 txt1 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt1 txt2 txt3  txt1 txt2 txt3 
         1          2 txt2 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt2 txt3 txt4  txt2 txt3 txt4 
         1          3 txt3 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt3 txt4 txt5  txt3 txt4 txt5 
         1          4 txt4 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt4 txt5 txt6  txt4 txt5 txt6 
         1          5 txt5 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt5 txt6       txt5 txt6      
         1          6 txt6 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt6            txt6
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 17:48
    #39341060
bhr
bhr
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
в теме действительно два вопроса. не хотел создавать две темы.
Спасибо за наводку по поводу ограничения первых 10 строк.
Listagg - пока не понял. пошел по указанной ссылке (asktom). понял, что создается собственная аналитическая ф-я.
Я никогда своих аналитических функций не создавал. Посоветуйте, что почитать (желательно ссылку).
Спасибо
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 18:15
    #39341077
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
bhr,

Тебе так сложно взять непонятное слово и вбить в гугл?
Например, "ODCIAggregateInitialize oracle magazine" добавив oracle magazine чтоб было понятно "для самых маленьких" без лишних технических деталей.

Первой ссылкой ты увидишь Build Custom Aggregate Functions
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 18:23
    #39341087
bhr
bhr
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
dbms_photoshopbhr,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
select t.*, regexp_replace(lstagg || ' ', '^([^ ]+ ){' || to_char(srt - 1) || '}(([^ ]+ ){1,3})(.*)', '\2') moving_3
  from (select a.*,
               listagg(a.txt, ' ') within group (order by a.srt) over (partition by id) lstagg,
               listagg(case when srt <= 3 then a.txt end, ' ') within group (order by a.srt) over (partition by id) first_3,
               str_agg(a.txt || ' ') over (partition by id order by a.srt rows between current row and 2 following) stragg
          from a
        order by srt) t



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
        ID        SRT TXT  LSTAGG                              FIRST_3         STRAGG          MOVING_3       
---------- ---------- ---- ----------------------------------- --------------- --------------- ---------------
         1          1 txt1 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt1 txt2 txt3  txt1 txt2 txt3 
         1          2 txt2 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt2 txt3 txt4  txt2 txt3 txt4 
         1          3 txt3 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt3 txt4 txt5  txt3 txt4 txt5 
         1          4 txt4 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt4 txt5 txt6  txt4 txt5 txt6 
         1          5 txt5 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt5 txt6       txt5 txt6      
         1          6 txt6 txt1 txt2 txt3 txt4 txt5 txt6       txt1 txt2 txt3  txt6            txt6



спасибо.
listagg(case when srt <= 3 then a.txt end, ' ,') within group (order by a.srt) over (partition by id) first_3
именно то что нужно!!! этого и пытался добиться.

regexp_replace(lstagg || ' ', '^([^ ]+ ){' || to_char(srt - 1) || '}(([^ ]+ ){1,3})(.*)', '\2') moving_3
не то
задача возникла изза ошибки оракла при попытке применить listagg к партиции размером в 5000 строк.
нужно было ввести ограничение. причем сцепить и потом отрезать не получится, ибо оракл выдает ошибку в момент сцепки.
а case внутри listagg - очень красиво. Чето я ступил в свое время. Не додумкал.
спасибо еще раз. возьму на вооружение. Просто и изящно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 18:35
    #39341100
bhr
bhr
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
dbms_photoshopbhr,

Тебе так сложно взять непонятное слово и вбить в гугл?
Например, "ODCIAggregateInitialize oracle magazine" добавив oracle magazine чтоб было понятно "для самых маленьких" без лишних технических деталей.

Первой ссылкой ты увидишь Build Custom Aggregate Functions
а как я мог догадаться, что сии названия функций - предопределенные, а не придуманные сиюминутно автором статьи?
За наводку - спасибо. Готов согласиться, что я дурак, лишь бы получить нужные знания.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 18:56
    #39341118
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
Рекурсивный CTE:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
with e as (
           select  ename,
                   null ename_list,
                   row_number() over(order by ename) rn,
                   -2 window_start,
                   2 window_end
             from  emp
         ),
     r(
       ename,
       ename_list,
       rn,
       window_start,
       window_end
      ) as (
             select  *
               from  e
            union all
             select  r.ename,
                     case
                       when e.ename is null then r.ename_list
                       when r.ename_list is null then e.ename
                       else r.ename_list || ',' || e.ename
                     end,
                     r.rn,
                     r.window_start + 1,
                     r.window_end
               from  r,
                     e
               where e.rn(+) = r.rn + r.window_start
                 and r.window_start <= r.window_end
           )
select  ename,
        ename_list
  from  r
  where window_start = window_end + 1
  order by rn
/

ENAME      ENAME_LIST
---------- ---------------------------------
ADAMS      ADAMS,ALLEN,BLAKE
ALLEN      ADAMS,ALLEN,BLAKE,CLARK
BLAKE      ADAMS,ALLEN,BLAKE,CLARK,FORD
CLARK      ALLEN,BLAKE,CLARK,FORD,JAMES
FORD       BLAKE,CLARK,FORD,JAMES,JONES
JAMES      CLARK,FORD,JAMES,JONES,KING
JONES      FORD,JAMES,JONES,KING,MARTIN
KING       JAMES,JONES,KING,MARTIN,MILLER
MARTIN     JONES,KING,MARTIN,MILLER,SCOTT
MILLER     KING,MARTIN,MILLER,SCOTT,SMITH
SCOTT      MARTIN,MILLER,SCOTT,SMITH,TURNER

ENAME      ENAME_LIST
---------- ---------------------------------
SMITH      MILLER,SCOTT,SMITH,TURNER,WARD
TURNER     SCOTT,SMITH,TURNER,WARD
WARD       SMITH,TURNER,WARD

14 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 19:24
    #39341129
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
А если LISTAGGируемые значения уникальны и ename не содержит спецсимволов regexp:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
with e as (
           select  ename,
                   listagg(ename,',') within group(order by ename) over() ename_list
             from  emp
         )
select  ename,
        regexp_substr(
                      ename_list,
                      '([^,]+,){0,2}' || ename || '(,[^,]+){0,2}'
                     ) ename_list
  from  e
/

ENAME      ENAME_LIST
---------- ----------------------------------
ADAMS      ADAMS,ALLEN,BLAKE
ALLEN      ADAMS,ALLEN,BLAKE,CLARK
BLAKE      ADAMS,ALLEN,BLAKE,CLARK,FORD
CLARK      ALLEN,BLAKE,CLARK,FORD,JAMES
FORD       BLAKE,CLARK,FORD,JAMES,JONES
JAMES      CLARK,FORD,JAMES,JONES,KING
JONES      FORD,JAMES,JONES,KING,MARTIN
KING       JAMES,JONES,KING,MARTIN,MILLER
MARTIN     JONES,KING,MARTIN,MILLER,SCOTT
MILLER     KING,MARTIN,MILLER,SCOTT,SMITH
SCOTT      MARTIN,MILLER,SCOTT,SMITH,TURNER

ENAME      ENAME_LIST
---------- ----------------------------------
SMITH      MILLER,SCOTT,SMITH,TURNER,WARD
TURNER     SCOTT,SMITH,TURNER,WARD
WARD       SMITH,TURNER,WARD

14 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 19:42
    #39341141
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
SY,

А вы читаете сообщения в топике, кроме своих?
Я уже вроде как сообщил что CTE - баловство и привел решения с regexp и PL/SQL.
С объяснением сопутствующих деталей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 20:37
    #39341163
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
dbms_photoshopЯ уже вроде как сообщил что CTE - баловство

И что? Я же не привел STRAGG еще раз. В допoлнение я привел CTE. Кроме того довольно затратно сортировать в ODCIAggregateMerge.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 20:51
    #39341178
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
спецификация окна в listagg
SY,

Если имеется хотя бы 100к строк, то для этой задачи recursive subquery factoring не жилец в сравнении с другими альтернативами.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / спецификация окна в listagg / 23 сообщений из 23, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 537ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1887086]:
 0ms
get settings:
 8ms
get forum list:
 19ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 167ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 74ms
get tp. blocked users:
 1ms
others: 248ms
total:  537ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]