ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Группировка в Oracle
39 сообщений из 39, показаны все 2 страниц
  все  
Группировка в Oracle
    #39634126
alexsm73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexsm73
Гость
Никак не могу решить следующую задачу - может кто подскажет?
Есть таблица
с1 с2 с3 с4 с5 с6
1
1 2
1 3
1 4
1 5
1 6
1 7

Надо сделать группировку по ней по полю с1 что бы в результате вышло
с1 с2 с3 с4 с5 с6
1 2 3 4 5 6
1 7 3 4 5 6
...
Рейтинг: 0 / 0
21.04.2018, 14:35
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634127
alexsm73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexsm73
Гость
криво написал таблицу
таблица такая:
с1 с2 с3 с4 с5 с6
1 2 - - - -
1 - 3 - - -
1 - - 4 - -
1 - - - 5 -
1 - - - - 6
1 7 - - - -
...
Рейтинг: 0 / 0
21.04.2018, 14:39
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634143
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Код: 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.
with tbl as (
             select 1 c1,2 c2,null c3,null c4,null c5,null c6 from dual union all
             select 1,null,3,null,null,null from dual union all
             select 1,null,null,4,null,null from dual union all
             select 1,null,null,null,5,null from dual union all
             select 1,null,null,null,null,6 from dual union all
             select 1,7,null,null,null,null from dual
            ),
       t as (
             select  c1,
                     c2,
                     nvl(c3,0) + nvl(sum(case when c2 is null then c3 end) over(partition by c1),0) c3,
                     nvl(c4,0) + nvl(sum(case when c2 is null then c4 end) over(partition by c1),0) c4,
                     nvl(c5,0) + nvl(sum(case when c2 is null then c5 end) over(partition by c1),0) c5,
                     nvl(c6,0) + nvl(sum(case when c2 is null then c6 end) over(partition by c1),0) c6
               from  tbl
            )
select  *
  from  t
  where c2 is not null
  order by c1,
           c2
/

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          4          5          6
         1          7          3          4          5          6

SQL>



SY.
...
Рейтинг: 0 / 0
21.04.2018, 15:39
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634144
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Ну а если гарантированно у строк с C2 IS NOT NULL поля C3, C4, C5, C6 всегда NULL то упрощаем:

Код: 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.
with tbl as (
             select 1 c1,2 c2,null c3,null c4,null c5,null c6 from dual union all
             select 1,null,3,null,null,null from dual union all
             select 1,null,null,4,null,null from dual union all
             select 1,null,null,null,5,null from dual union all
             select 1,null,null,null,null,6 from dual union all
             select 1,7,null,null,null,null from dual
            ),
       t as (
             select  c1,
                     c2,
                     sum(case when c2 is null then c3 end) over(partition by c1) c3,
                     sum(case when c2 is null then c4 end) over(partition by c1) c4,
                     sum(case when c2 is null then c5 end) over(partition by c1) c5,
                     sum(case when c2 is null then c6 end) over(partition by c1) c6
               from  tbl
            )
select  *
  from  t
  where c2 is not null
  order by c1,
           c2
/

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          4          5          6
         1          7          3          4          5          6

SQL>



SY.
...
Рейтинг: 0 / 0
21.04.2018, 15:45
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634146
alexsm73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexsm73
Гость
А если у меня все значения c таблице кроме с1 не NUMBER? Как быть тогда?
...
Рейтинг: 0 / 0
21.04.2018, 15:58
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634150
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
alexsm73А если у меня все значения c таблице кроме с1 не NUMBER? Как быть тогда?

Замени SUM на MAX. Это если у строк с C2 IS NULL только одна где C3 IS NOT NULL, только одна где C4 IS NOT NULL, только одна где C5 IS NOT NULL, только одна где C6 IS NOT NULL. А если несколько, то что ты ожидаешь взад, например C3 = 'X' и C3 = 'Y'.

SY.
...
Рейтинг: 0 / 0
21.04.2018, 16:17
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634158
alexsm73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexsm73
Гость
Спасибо!То что надо!
...
Рейтинг: 0 / 0
21.04.2018, 16:45
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634278
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
И все-таки аналитика - это не совсем агрегация :)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with tbl as (
             select 1 c1,2 c2,null c3,null c4,null c5,null c6 from dual union all
             select 1,null,3,null,null,null from dual union all
             select 1,null,null,4,null,null from dual union all
             select 1,null,null,null,5,null from dual union all
             select 1,null,null,null,null,6 from dual union all
             select 1,7,null,null,null,null from dual
            )
--------------------
select c1,  c2.column_value c2, c3, c4, c5, c6
  from ( select c1, collect(distinct c2) c22
              , max(c3) c3, max(c4) c4, max(c5) c5, max(c6) c6
           from tbl
          group by c1
     ) t
     , table(c22) c2 ;


        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          4          5          6
         1          7          3          4          5          6
...
Рейтинг: 0 / 0
22.04.2018, 01:54
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634286
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
andrey_anonymousИ все-таки аналитика - это не совсем агрегация :)


Искусство ради искусства :) ?

SY.
...
Рейтинг: 0 / 0
22.04.2018, 04:06
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634297
alexsm73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexsm73
Гость
andrey_anonymous,
Спасибо этот способ подходет больше.
Но тут возникает проблема при создании процедуры:
При создании курсора на этот селект Oracle ругается ошибкой
ORA-30482: DISTINCT option not allowed for this function
Можно это обойти как-то? У меня
...
Рейтинг: 0 / 0
22.04.2018, 10:46
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634300
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
alexsm73DISTINCTset()
...
Рейтинг: 0 / 0
22.04.2018, 10:54
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634309
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
alexsm73Спасибо этот способ подход е т больше.Чудо, ты откудова выползло возомнить себя программистом?
...
Рейтинг: 0 / 0
22.04.2018, 11:22
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634343
alexsm73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexsm73
Гость
Elic,
Да учусь пока - поэтому и спрашиваю.
Проблема в том что мне надо что бы была группировка по с1 и уникальность по другим столбцам.
В этом варианте у меня вышло - но процедуру создать не могу
...
Рейтинг: 0 / 0
22.04.2018, 13:53
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634347
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
alexsm73Спасибо этот способ подходет больше.


Сравни:

Код: 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.
63.
64.
65.
66.
67.
68.
69.
70.
SQL> explain plan for
  2  select c1,  c2.column_value c2, c3, c4, c5, c6
  3    from ( select c1, collect(distinct c2) c22
  4                , max(c3) c3, max(c4) c4, max(c5) c5, max(c6) c6
  5             from tbl
  6            group by c1
  7       ) t
  8       , table(c22) c2 ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1588792982

-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |  8168 |   805K|    33   (4)| 00:00:01 |
|   1 |  NESTED LOOPS                      |      |  8168 |   805K|    33   (4)| 00:00:01 |
|   2 |   VIEW                             |      |     1 |    99 |     4  (25)| 00:00:01 |
|   3 |    SORT GROUP BY                   |      |     1 |     9 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL              | TBL  |     6 |    54 |     3   (0)| 00:00:01 |
|   5 |   COLLECTION ITERATOR PICKLER FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

12 rows selected.

SQL> explain plan for
  2  with   t as (
  3               select  c1,
  4                       c2,
  5                       sum(case when c2 is null then c3 end) over(partition by c1) c3,
  6                       sum(case when c2 is null then c4 end) over(partition by c1) c4,
  7                       sum(case when c2 is null then c5 end) over(partition by c1) c5,
  8                       sum(case when c2 is null then c6 end) over(partition by c1) c6
  9                 from  tbl
 10              )
 11  select  *
 12    from  t
 13    where c2 is not null
 14  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1123090974

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     6 |   468 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |      |     6 |   468 |     3   (0)| 00:00:01 |
|   2 |   WINDOW BUFFER     |      |     6 |    54 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TBL  |     6 |    54 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2" IS NOT NULL)

15 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
22.04.2018, 15:00
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634349
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
alexsm73Спасибо этот способ подходет больше.


Кстати, если у строк с C2 IS NOT NULL поля C3, C4, C5, C6 всегда NULL (как в твоем примере), то аналитическое решение можно упростить:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with   t as (
             select  c1,
                     c2,
                     max(c3) over(partition by c1) c3,
                     max(c4) over(partition by c1) c4,
                     max(c5) over(partition by c1) c5,
                     max(c6) over(partition by c1) c6
               from  tbl
            )
select  *
  from  t
  where c2 is not null
  order by c1,
           c2
/

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          4          5          6
         1          7          3          4          5          6

SQL>



SY.
...
Рейтинг: 0 / 0
22.04.2018, 15:14
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634382
alexsm73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexsm73
Гость
SY,
А как сделать что бы все стролбцы групировались по с1?
т.е если таблица
с1 с2 с3 с4 с5 с6
1 2 - - - -
1 - 3 - - -
1 - - 4 - -
1 - - - 5 -
1 - - - - 6
1 7 - - - -
1 - 8 ----
что выводилось бы
1 2 3 4 5 6
1 7 3 4 5 6
1 7 8 4 5 6
1 2 8 4 5 6
ну и так далее что бы в каждой строке было уникальное значение по всем столбцам?
...
Рейтинг: 0 / 0
22.04.2018, 19:02
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634403
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Никак - порядок строк в таблице просто нe существует. Порядок задается ORDER BY. Посему точто ты хочешь возможно только если в таблице есть поле задающее порядок строк. Например поле ID:

Код: 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.
with tbl as (
             select 1 id,1 c1,2 c2,null c3,null c4,null c5,null c6 from dual union all
             select 2,1,null,3,null,null,null from dual union all
             select 3,1,null,null,4,null,null from dual union all
             select 4,1,null,null,null,5,null from dual union all
             select 5,1,null,null,null,null,6 from dual union all
             select 6,1,7,null,null,null,null from dual union all
             select 7,1,null,8,null,null,null from dual
            )
select  c1,
        last_value(c2 ignore nulls) over(order by id) c2,
        nvl(last_value(c3 ignore nulls) over(order by id),last_value(c3 ignore nulls) over(order by id desc)) c3,
        nvl(last_value(c4 ignore nulls) over(order by id),last_value(c4 ignore nulls) over(order by id desc)) c4,
        nvl(last_value(c5 ignore nulls) over(order by id),last_value(c5 ignore nulls) over(order by id desc)) c5,
        nvl(last_value(c6 ignore nulls) over(order by id),last_value(c6 ignore nulls) over(order by id desc)) c6
  from  tbl
  order by id
/

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          4          5          6
         1          2          3          4          5          6
         1          2          3          4          5          6
         1          2          3          4          5          6
         1          2          3          4          5          6
         1          7          3          4          5          6
         1          7          8          4          5          6
SQL>



SY.
...
Рейтинг: 0 / 0
22.04.2018, 21:46
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634409
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Похоже я непрaвильно понял логику:

Код: 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.
with tbl as (
             select 1 c1,2 c2,null c3,null c4,null c5,null c6 from dual union all
             select 1,null,3,null,null,null from dual union all
             select 1,null,null,4,null,null from dual union all
             select 1,null,null,null,5,null from dual union all
             select 1,null,null,null,null,6 from dual union all
             select 1,7,null,null,null,null from dual union all
             selecT 1,null,8,null,null,null from dual
            ),
       t as (
             select  c1,
                     collect(c2) c2,
                     collect(c3) c3,
                     collect(c4) c4,
                     collect(c5) c5,
                     collect(c6) c6
               from  tbl
               group by c1
            )
select  c1,
        t2.column_value c2,
        t3.column_value c3,
        t4.column_value c4,
        t5.column_value c5,
        t6.column_value c6
  from  t,
        table(c2)(+) t2,
        table(c3)(+) t3,
        table(c4)(+) t4,
        table(c5)(+) t5,
        table(c6)(+) t6
  order by c1,
           c2,
           c3,
           c4,
           c5,
           c6
/

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          4          5          6
         1          2          8          4          5          6
         1          7          3          4          5          6
         1          7          8          4          5          6

SQL>



SY.
...
Рейтинг: 0 / 0
22.04.2018, 22:10
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39634991
alexsm73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexsm73
Гость
SY,
Спасибо большое! это как раз и есть то что надо было добиться в результате.
...
Рейтинг: 0 / 0
23.04.2018, 19:37
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635006
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
SYПохоже я непрaвильно понял логику:

Код: 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.
with tbl as (
             select 1 c1,2 c2,null c3,null c4,null c5,null c6 from dual union all
             select 1,null,3,null,null,null from dual union all
             select 1,null,null,4,null,null from dual union all
             select 1,null,null,null,5,null from dual union all
             select 1,null,null,null,null,6 from dual union all
             select 1,7,null,null,null,null from dual union all
             selecT 1,null,8,null,null,null from dual
            ),
       t as (
             select  c1,
                     collect(c2) c2,
                     collect(c3) c3,
                     collect(c4) c4,
                     collect(c5) c5,
                     collect(c6) c6
               from  tbl
               group by c1
            )
select  c1,
        t2.column_value c2,
        t3.column_value c3,
        t4.column_value c4,
        t5.column_value c5,
        t6.column_value c6
  from  t,
        table(c2)(+) t2,
        table(c3)(+) t3,
        table(c4)(+) t4,
        table(c5)(+) t5,
        table(c6)(+) t6
  order by c1,
           c2,
           c3,
           c4,
           c5,
           c6
/

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          4          5          6
         1          2          8          4          5          6
         1          7          3          4          5          6
         1          7          8          4          5          6

SQL>



SY.
из пушки по воробьям - так и память всю сожрать недолго.
сравни планы, убийца ))
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with tb1 as (
             select 1 c1,2 c2,null c3,null c4,null c5,null c6 from dual union all
             select 1,null,3,null,null,null from dual union all
             select 1,null,null,4,null,null from dual union all
             select 1,null,null,null,5,null from dual union all
             select 1,null,null,null,null,6 from dual union all
             select 1,7,null,null,null,null from dual union all
             selecT 1,null,8,null,null,null from dual
            )
select distinct t1.c1, t2.c2, t3.c3, t4.c4, t5.c5, t6.c6 from tb1 t1
left join (select c1, c2 from tb1 where c2 is not null) t2 on t2.c1 = t1.c1
left join (select c1, c3 from tb1 where c3 is not null) t3 on t3.c1 = t1.c1 
left join (select c1, c4 from tb1 where c4 is not null) t4 on t4.c1 = t1.c1 
left join (select c1, c5 from tb1 where c5 is not null) t5 on t5.c1 = t1.c1 
left join (select c1, c6 from tb1 where c6 is not null) t6 on t6.c1 = t1.c1  
where t1.c1 is not null -- это на всякий случай
...
Рейтинг: 0 / 0
23.04.2018, 20:01
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635029
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Fogelубийца ))
Тренируйтесь, парни:
 
Код: 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.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
SQL> create table dropme_t as
  2  with tb1 as (
  3               select 2 c2,null c3,null c4,null c5,null c6 from dual union all
  4               select null,3,null,null,null from dual union all
  5               select null,null,4,null,null from dual union all
  6               select null,null,null,5,null from dual union all
  7               select null,null,null,null,6 from dual union all
  8               select 7,null,null,null,null from dual union all
  9               selecT null,8,null,null,null from dual
 10              )
 11  , tb2 as (select mod(rownum,10) c1 from dual connect by level < 100000)
 12  select * from tb2, tb1
 13  ;

Table created.

SQL> create index dropme_t$c1 on dropme_t(c1); -- may be Fogel's version will use it?

Index created.

SQL> begin dbms_stats.gather_table_stats(user,'dropme_t',method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => true); end;
  2  /

PL/SQL procedure successfully completed.

SQL> set timing on;
SQL>
SQL> with t as (  select  c1
  2                    , collect(distinct c2) c2
  3                    , collect(distinct c3) c3
  4                    , collect(distinct c4) c4
  5                    , collect(distinct c5) c5
  6                    , collect(distinct c6) c6
  7                 from  dropme_t
  8                 group by c1
  9              )
 10  select     c1
 11       , t2.column_value c2
 12       , t3.column_value c3
 13       , t4.column_value c4
 14       , t5.column_value c5
 15       , t6.column_value c6
 16    from     t
 17       , table(c2)(+) t2
 18       , table(c3)(+) t3
 19       , table(c4)(+) t4
 20       , table(c5)(+) t5
 21       , table(c6)(+) t6
 22    order by c1, c2, c3, c4, c5, c6
 23  ;

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         0          2          3          4          5          6
         0          2          8          4          5          6
         0          7          3          4          5          6
         0          7          8          4          5          6
         1          2          3          4          5          6
         1          2          8          4          5          6
         1          7          3          4          5          6
         1          7          8          4          5          6
         2          2          3          4          5          6
         2          2          8          4          5          6
         2          7          3          4          5          6

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         2          7          8          4          5          6
         3          2          3          4          5          6
         3          2          8          4          5          6
         3          7          3          4          5          6
         3          7          8          4          5          6
         4          2          3          4          5          6
         4          2          8          4          5          6
         4          7          3          4          5          6
         4          7          8          4          5          6
         5          2          3          4          5          6
         5          2          8          4          5          6

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         5          7          3          4          5          6
         5          7          8          4          5          6
         6          2          3          4          5          6
         6          2          8          4          5          6
         6          7          3          4          5          6
         6          7          8          4          5          6
         7          2          3          4          5          6
         7          2          8          4          5          6
         7          7          3          4          5          6
         7          7          8          4          5          6
         8          2          3          4          5          6

        C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
         8          2          8          4          5          6
         8          7          3          4          5          6
         8          7          8          4          5          6
         9          2          3          4          5          6
         9          2          8          4          5          6
         9          7          3          4          5          6
         9          7          8          4          5          6

40 rows selected.

Elapsed: 00:00:00.54
SQL>


Ваших вариантов на этом наборе чот не дождался (~5мин, дальше лень).
Вариант SY сожрал к этому времени порядка 30 гиг в темпе, Fogel - 17 гиг.
...
Рейтинг: 0 / 0
23.04.2018, 20:50
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635032
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
andrey_anonymousВаших вариантов на этом наборе чот не дождался (~5мин, дальше лень).
Вариант SY сожрал к этому времени порядка 30 гиг в темпе, Fogel - 17 гиг.
Fogel - до финиша не дошел:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with tb1 as ( select  *
               from  dropme_t
)
select distinct t1.c1, t2.c2, t3.c3, t4.c4, t5.c5, t6.c6
  from tb1 t1
  left join (select c1, c2 from tb1 where c2 is not null) t2 on t2.c1 = t1.c1
  left join (select c1, c3 from tb1 where c3 is not null) t3 on t3.c1 = t1.c1
  left join (select c1, c4 from tb1 where c4 is not null) t4 on t4.c1 = t1.c1
  left join (select c1, c5 from tb1 where c5 is not null) t5 on t5.c1 = t1.c1
  left join (select c1, c6 from tb1 where c6 is not null) t6 on t6.c1 = t1.c1
 where t1.c1 is not null -- это на всякий случай
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP


SY побеждает в стиле СУМО, выпихнув Fogel из темпа - но все еще бежит, 37 гиг уже :) :) :)
...
Рейтинг: 0 / 0
23.04.2018, 20:59
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635033
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Ан нет - SY тоже дисквалифицирован :)
Код: 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.
SQL> with t as (
  2               select  c1,
  3                       collect(c2) c2,
  4                       collect(c3) c3,
  5                       collect(c4) c4,
  6                       collect(c5) c5,
  7                       collect(c6) c6
  8                 from  dropme_t
  9                 group by c1
 10              )
 11  select     c1,
 12          t2.column_value c2,
 13          t3.column_value c3,
 14          t4.column_value c4,
 15          t5.column_value c5,
 16          t6.column_value c6
 17    from     t,
 18          table(c2)(+) t2,
 19          table(c3)(+) t3,
 20          table(c4)(+) t4,
 21          table(c5)(+) t5,
 22          table(c6)(+) t6
 23    order by c1,
 24             c2,
 25             c3,
 26             c4,
 27             c5,
 28             c6
 29  ;
        table(c6)(+) t6
        *
ERROR at line 22:
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP


Elapsed: 00:11:28.48
...
Рейтинг: 0 / 0
23.04.2018, 21:02
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635037
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
andrey_anonymousАн нет - SY тоже дисквалифицирован :)


А что-еще ты ожидал? Законы комбинаторики еще никто не отмeнял . Если число строк для каждого C1 в пределах, то все будет пучком.

SY.
...
Рейтинг: 0 / 0
23.04.2018, 21:35
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635039
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SYandrey_anonymousАн нет - SY тоже дисквалифицирован :)

А что-еще ты ожидал?
Ну я как-то вот так примерно ожидал:
andrey_anonymous
Код: plsql
1.
2.
3.
40 rows selected.

Elapsed: 00:00:00.54



Комбинаторику-то не обманешь, это правда, но зачем же стулья ломать? :)
...
Рейтинг: 0 / 0
23.04.2018, 21:42
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635041
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
andrey_anonymousКомбинаторику-то не обманешь, это правда, но зачем же стулья ломать? :)
...что-то подозревать я начал на тесте с mod(rownum,1000) - т.е. по сотне значений на id, это ведь "в пределах", да?
...
Рейтинг: 0 / 0
23.04.2018, 21:44
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635049
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
andrey_anonymous
Код: plsql
1.
2.
3.
40 rows selected.

Elapsed: 00:00:00.54



Комбинаторику-то не обманешь, это правда, но зачем же стулья ломать? :)

Не понял с какого перепугу "40 rows selected". Select должен вернуть 699993 строк:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
SQL> select count(*) from dropme_t
  2  /

  COUNT(*)
----------
    699993

SQL>



SY.
...
Рейтинг: 0 / 0
23.04.2018, 22:08
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635050
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SYНе понял с какого перепугу "40 rows selected". Select должен вернуть 699993 строк:


Упс, пора перечитывать условия опять.

SY.
...
Рейтинг: 0 / 0
23.04.2018, 22:11
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635058
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SYУпс, пора перечитывать условия опять.


Похоже Андрей слепил "магию данных". Исходя из примера я решил что значения C2 - C6 уникальны a он слепил тест с повторяющимися значениями и скорее всего просто всунул DISTINCT в COLLECT:

Код: 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.
with   t as (
             select  c1,
                     collect(distinct c2) c2,
                     collect(distinct c3) c3,
                     collect(distinct c4) c4,
                     collect(distinct c5) c5,
                     collect(distinct c6) c6
               from  dropme_t
               group by c1
            )
select  c1,
        t2.column_value c2,
        t3.column_value c3,
        t4.column_value c4,
        t5.column_value c5,
        t6.column_value c6
  from  t,
        table(c2) t2,
        table(c3) t3,
        table(c4) t4,
        table(c5) t5,
        table(c6) t6
  order by c1,
           c2,
           c3,
           c4,
           c5,
           c6
/
...

40 rows selected.

Elapsed: 00:00:00.51
SQL>



SY.
...
Рейтинг: 0 / 0
23.04.2018, 22:48
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635061
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SYПохоже Андрей слепил "магию данных".
Не магию.
Контрпример.
Собственно, я зацепился глазом за эту тему, когда увидел решение агрегатной задачи на аналитике-фильтрации.
Затем - комментарий к решению на агрегате "искусство ради искусства".
Мои тараканы устроили митинг, на котором дружно заявили о недооценке спецэффектов, проистекающих от перерасхода памяти под промежуточные наборы данных (что весьма характерно для "аналитического" подхода к задаче агрегации).
Я устроил тараканам репрессии и от комментариев удержался.
Но тут влез Fogel и стало совсем смешно.
Как итог - я построил демонстрационный контр-пример, просто слегка размножив исходный набор.
Следует отметить, что эффект превзошел мои самые смелые ожидания.
...
Рейтинг: 0 / 0
23.04.2018, 23:13
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635076
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
andrey_anonymousСледует отметить, что эффект превзошел мои самые смелые ожидания.

Ну а я просто вижу эффект неполного определения условий задачи. Тут важное значение имеет уникальны ли c2 - c6.

SY.
...
Рейтинг: 0 / 0
24.04.2018, 01:05
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39635080
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SYТут важное значение имеет уникальны ли c2 - c6.


Поясню:

Код: 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.
SQL> explain plan for
  2  select  c1,
  3          c2,
  4          collect(c3) over(partition by c1) c3,
  5          collect(c4) over(partition by c1) c4,
  6          collect(c5) over(partition by c1) c5,
  7          collect(c6) over(partition by c1) c6
  8    from  dropme_t
  9  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3964081274

---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |   699K|  6835K|       |  3149   (2)| 00:00:01 |
|   1 |  WINDOW SORT       |          |   699K|  6835K|    18M|  3149   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DROPME_T |   699K|  6835K|       |   345   (3)| 00:00:01 |
---------------------------------------------------------------------------------------

9 rows selected.

SQL> explain plan for
  2  select  c1,
  3          c2,
  4          collect(distinct c3) over(partition by c1) c3,
  5          collect(distinct c4) over(partition by c1) c4,
  6          collect(distinct c5) over(partition by c1) c5,
  7          collect(distinct c6) over(partition by c1) c6
  8    from  dropme_t
  9  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2807215000

------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |   699K|  6835K|       | 11561   (2)| 00:00:01 |
|   1 |  WINDOW SORT          |          |   699K|  6835K|    18M| 11561   (2)| 00:00:01 |
|   2 |   WINDOW SORT         |          |   699K|  6835K|    18M| 11561   (2)| 00:00:01 |
|   3 |    WINDOW SORT        |          |   699K|  6835K|    18M| 11561   (2)| 00:00:01 |
|   4 |     WINDOW SORT       |          |   699K|  6835K|    18M| 11561   (2)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| DROPME_T |   699K|  6835K|       |   345   (3)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------

12 rows selected.

SQL>



А вот агрегация c DISTINCT "умнее":

Код: 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.
SQL> explain plan for
  2  select  c1,
  3          collect(distinct c2) c2,
  4          collect(distinct c3) c3,
  5          collect(distinct c4) c4,
  6          collect(distinct c5) c5,
  7          collect(distinct c6) c6
  8    from  dropme_t
  9    group by c1
 10  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 530221137

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    10 |   100 |   371  (10)| 00:00:01 |
|   1 |  SORT GROUP BY     |          |    10 |   100 |   371  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DROPME_T |   699K|  6835K|   345   (3)| 00:00:01 |
-------------------------------------------------------------------------------

9 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
24.04.2018, 01:32
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39636822
DenFrost
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DenFrost
Гость
Добрый день. Прошу совета как сделать, чтобы не плодить темы, решил написать здесь. Мне нужно сделать группировку.

дано

select null id, 32 d_id, 71 sn from dual
union
select null id, 62 d_id, 56 sn from dual
union
select 7 id, 62 d_id, 60 sn from dual
union
select null id, 60 d_id, 41 sn from dual
union
select null id, 82 d_id, 1 sn from dual
union
select 10 id, 82 d_id, 2 sn from dual
union
select 15 id, 82 d_id, 3 sn from dual

нужно вывести
id d_id sn
null 32 71
7 62 60
null 60 41
15 82 3

Заранее спасибо за ответ.

P.S. Нужно использовать аналитику?
...
Рейтинг: 0 / 0
26.04.2018, 15:42
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39636830
Фотография Egoр
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Egoр
Участник
Код: 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.
with дано as (
select null id, 32 d_id, 71 sn from dual
union
select null id, 62 d_id, 56 sn from dual
union
select 7 id, 62 d_id, 60 sn from dual
union
select null id, 60 d_id, 41 sn from dual
union
select null id, 82 d_id, 1 sn from dual
union
select 10 id, 82 d_id, 2 sn from dual
union
select 15 id, 82 d_id, 3 sn from dual
),
"нужно вывести"(id, d_id, sn) as (
select null, 32, 71 from dual
union
select 7, 62, 60 from dual
union
select null, 60, 41 from dual
union
select 15, 82, 3 from dual
)
select * from "нужно вывести"
...
Рейтинг: 0 / 0
26.04.2018, 15:49
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39636845
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Egoр,

Открой свою тему. Незачем "уводить" (не нашел лучшего перевода для hijack) чужую.

SY.
...
Рейтинг: 0 / 0
26.04.2018, 16:04
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39636881
Фотография Egoр
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Egoр
Участник
SYEgoр,

Открой свою тему. Незачем "уводить" (не нашел лучшего перевода для hijack) чужую.

SY.Вообще-то я товарищу DenFrost ответил
DenFrost Добрый день. Прошу совета как сделать, чтобы не плодить темы, решил написать здесь. Мне нужно сделать группировку.
...
Рейтинг: 0 / 0
26.04.2018, 16:47
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39636948
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
DenFrost,

а что не получается?
Код: 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.
SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2  select null id, 32 d_id, 71 sn from dual
  3  union all
  4  select null id, 62 d_id, 56 sn from dual
  5  union all
  6  select 7 id, 62 d_id, 60 sn from dual
  7  union all
  8  select null id, 60 d_id, 41 sn from dual
  9  union all
 10  select null id, 82 d_id, 1 sn from dual
 11  union all
 12  select 10 id, 82 d_id, 2 sn from dual
 13  union all
 14  select 15 id, 82 d_id, 3 sn from dual
 15  ),
 16  n(id, d_id, sn) as (
 17  select null, 32, 71 from dual
 18  union all
 19  select 7, 62, 60 from dual
 20  union all
 21  select null, 60, 41 from dual
 22  union all
 23  select 15, 82, 3 from dual
 24  )
 25  select max(id) id,d_id,max(sn) sn from n
 26* group by d_id
SQL> /

        ID       D_ID         SN
---------- ---------- ----------
                   32         71
         7         62         60
                   60         41
        15         82          3



....
stax
...
Рейтинг: 0 / 0
26.04.2018, 18:18
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39637100
DenFrost
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DenFrost
Гость
я наверное чуток не дописал,
вывести строчки с максимальным id по d_id и также строчки с id is null

дано
id d_id sn 3271 625676254 60 41 82110821115828

получить
id d_id sn 327176254 60 4115828
...
Рейтинг: 0 / 0
27.04.2018, 07:32
| Ответить | Цитировать | Написать  
Группировка в Oracle
    #39637107
DenFrost
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DenFrost
Гость
я придумал такой вариант, может есть проще

select * from (
select
a.*
, nvl(max (id) over (partition by d_id order by id desc nulls last), 0) max_id
from (
select null id, 32 d_id, 71 sn from dual
union
select null id, 62 d_id, 56 sn from dual
union
select 15 id, 82 d_id, 8 sn from dual
union
select 7 id, 62 d_id, 54 sn from dual
union
select null id, 60 d_id, 41 sn from dual
union
select null id, 82 d_id, 0 sn from dual
union
select 10 id, 82 d_id, 11 sn from dual
) a
)
where id = max_id or max_id = 0
...
Рейтинг: 0 / 0
27.04.2018, 07:50
| Ответить | Цитировать | Написать  
39 сообщений из 39, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Группировка в Oracle
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1884085]:
 0ms
get settings:
 9ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 76ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 83ms
get tp. blocked users:
 1ms
others: 233ms
total:  437ms
созд. / загр.: 437ms
x
x
Закрыть


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