ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выбрать первые 3 позиции в группировке
25 сообщений из 35, страница 1 из 2
  1  все
Выбрать первые 3 позиции в группировке
    #39454037
Петров Андрей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петров Андрей
Участник
Приветствую.
Имеются данные:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select type,
       data,
       sum(cc)
from (
select 'a' type, 1 cc, '1111' data from dual union all
select 'a' type, 5 cc, '1112' data from dual union all
select 'a' type, 2 cc, '1111' data from dual union all
select 'b' type, 3 cc, '1114' data from dual union all
select 'b' type, 4 cc, '1113' data from dual union all
select 'b' type, 5 cc, '1115' data from dual union all
select 'b' type, 7 cc, '1112' data from dual) t
group by type, data
order by 1, 2, 3 desc



Нужно получить:
1 a 1111 3
2 a 1112 5
3 b 1112 7
4 b 1113 4
5 b 1114 3

Т.е. в запросе оставить только 1-е 3 топовые позиции по cc.
Надо бы подкрутить аналитическую функцию :-) но я не знаю как, помогите.
...
Рейтинг: 0 / 0
16.05.2017, 17:57
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454041
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
Петров Андрей,

row_number() ?

Версия субд какая?
...
Рейтинг: 0 / 0
16.05.2017, 17:59
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454042
Петров Андрей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петров Андрей
Участник
Немного подправил запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select type,
       data,
       sum(cc)
from (
select 'a' type, 1 cc, '1111' data from dual union all
select 'a' type, 5 cc, '1112' data from dual union all
select 'a' type, 2 cc, '1111' data from dual union all
select 'b' type, 3 cc, '1114' data from dual union all
select 'b' type, 4 cc, '1113' data from dual union all
select 'b' type, 5 cc, '1115' data from dual union all
select 'b' type, 7 cc, '1112' data from dual) t
group by type, data
order by 1, 3 desc
...
Рейтинг: 0 / 0
16.05.2017, 17:59
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454043
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
Петров Андрей,

для 12c посмотри эту тему
...
Рейтинг: 0 / 0
16.05.2017, 18:01
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454048
Петров Андрей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петров Андрей
Участник
А для 11 только так?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select *
from (
select type,
       data,
       sum(cc),
       row_number() over(partition by type order by sum(cc) desc) rn
from (
select 'a' type, 1 cc, '1111' data from dual union all
select 'a' type, 5 cc, '1112' data from dual union all
select 'a' type, 2 cc, '1111' data from dual union all
select 'b' type, 3 cc, '1114' data from dual union all
select 'b' type, 4 cc, '1113' data from dual union all
select 'b' type, 5 cc, '1115' data from dual union all
select 'b' type, 7 cc, '1112' data from dual) t
group by type, data
order by 1, 3 desc) tt
where tt.rn <= 3
...
Рейтинг: 0 / 0
16.05.2017, 18:05
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454101
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
envПетров Андрей,

для 12c посмотри эту тему Поскольку ТСу не надо делать соединений - аналитика вполне подходящее решение.
В той теме суть в том, что благодаря lateral можно сортировать только соединяемые данные для каждой строки, а не весь recordset после соединения.
Если еще углубиться в ту тему, можно сначала сортировать все соединяемые данные, потом фильтровать, потом соединять, но можно придумать данные когда lateral будет эффективнее.

Итого, представим, что во внутренней таблице 10 строк, во внешней 10 млн (по миллиону на строку главной), при этом нас интересуют ТОП 100 только для трех строк из главной. В зависимости от подхода будет примерно следующая последовательность действий

Код: plaintext
1.
2.
row_number + join сортировка 10 млн, фильтр 1000, 300 после соединения 
join + row_number 3 млн после соединения, сортировка 3 млн, фильтр 300
lateral           3 раза сортировка по 1 млн, фильтр 300
...
Рейтинг: 0 / 0
16.05.2017, 19:00
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454386
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
dbms_photoshop,

Отсылка туда была на случай, если ТС привёл вырожденный случай, чтобы потом было меньше вопросов вида "а если мне надо первые 10 из другой таблицы".

С твоим стилем изложения можно следующую книгу будет по комментариям в темах собирать, практически без редакторской правки.
...
Рейтинг: 0 / 0
17.05.2017, 10:17
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454461
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
dbms_photoshop
lateral 3 раза сортировка по 1 млн, фильтр 300
каким образом получаете 3 из десяти?


ps
сложилось впечатление что результат lateral может зависеть от order by в главном
аля выполняется с учетом сортировки

select * from t,lateral(select * from tt where t.x=tt.x) order by t.y
....
stax
...
Рейтинг: 0 / 0
17.05.2017, 11:19
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454476
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop
Код: plaintext
lateral           3 раза сортировка по 1 млн, фильтр 300
К слову CBO умеет трансформировать laterals так что в некоторых случаях может оказаться что будет один хэшджойн
...
Рейтинг: 0 / 0
17.05.2017, 11:35
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454482
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
и еще: https://hourim.wordpress.com/2017/03/25/de-correlated-lateral-view-vw_dcl_mmm/
...
Рейтинг: 0 / 0
17.05.2017, 11:39
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454485
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Код: plsql
1.
2.
3.
4.
5.
6.
SQL> @hint correl

NAME                           SQL_FEATURE                         CLASS                          VERSION              INVERSE
------------------------------ ----------------------------------- ------------------------------ -------------------- ------------------------------
DECORRELATE                    QKSFM_DECORRELATE                   DECORRELATE                    12.1.0.1             NO_DECORRELATE
NO_DECORRELATE                 QKSFM_DECORRELATE                   DECORRELATE                    12.1.0.1             DECORRELATE
...
Рейтинг: 0 / 0
17.05.2017, 11:39
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454616
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
stax..dbms_photoshoplateral 3 раза сортировка по 1 млн, фильтр 300
каким образом получаете 3 из десяти?dbms_photoshopпри этом нас интересуют ТОП 100 только для трех строк из главнойЕсли следующим вопросом будет почему я выдумал эту тройку, то ответ: чтоб обратить внимание, что будет выполнятся сортировка разного числа строк для первых двух случаев при наличии фильтра по главной.
...
Рейтинг: 0 / 0
17.05.2017, 13:42
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454626
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
dbms_photoshopstax..пропущено...

каким образом получаете 3 из десяти?dbms_photoshopпри этом нас интересуют ТОП 100 только для трех строк из главнойЕсли следующим вопросом будет почему я выдумал эту тройку, то ответ: чтоб обратить внимание, что будет выполнятся сортировка разного числа строк для первых двух случаев при наличии фильтра по главной.
вопрос был не в етом
1 вариант) select * from (select * from t10 where rownum<4), lateral (select * from t100000 ...FIRST 100 ROWS)
2 вариант) select * from t10, lateral (select * from t100000 ...FIRST 100 ROWS) ... FIRST 3 ROWS
3) ....?
4) ....?

.....
stax
...
Рейтинг: 0 / 0
17.05.2017, 13:51
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454646
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtenderК слову CBO умеет трансформировать laterals так что в некоторых случаях может оказаться что будет один хэшджойнНу я писал в своей pdf, что переписывание ansi в native совершенствуется и запросы, которые всегда порождали lateral в десятке могут быть без оной в 11-ке
(это же высказывание верно если взять 11 и 12 для иного типа запросов).
xtenderи еще: https://hourim.wordpress.com/2017/03/25/de-correlated-lateral-view-vw_dcl_mmm/ Тут важно что оно может быть декоррелировано только если оно и так не скоррелировано логически. :)

Иными словами, нормальный разработчик не будет писать
Код: plsql
1.
2.
3.
select *
from t1
, lateral (select * from t2 where t1.id = t2.id)(+)


вместо
Код: plsql
1.
2.
3.
select *
from t1
, t2 where t1.id = t2.id(+)


Но если это написано, то оно будет "декоррелировано" с применением HASH JOIN и в 11.
Это же применимо к запросам порождаемым CBO при переписывании ANSI в native.

Если же lateral логически нужен, то Оракл от него никак не избавится.
Возвращаясь к моему примеру с lateral и сортировками я сомневаюсь что там может быть что-то кроме "сортировка набора получаемого из дочерней
для каждой строки главной".
...
Рейтинг: 0 / 0
17.05.2017, 14:03
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454660
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
stax..dbms_photoshopпропущено...
пропущено...
Если следующим вопросом будет почему я выдумал эту тройку, то ответ: чтоб обратить внимание, что будет выполнятся сортировка разного числа строк для первых двух случаев при наличии фильтра по главной.
вопрос был не в етом
1 вариант) select * from (select * from t10 where rownum<4), lateral (select * from t100000 ...FIRST 100 ROWS)
2 вариант) select * from t10, lateral (select * from t100000 ...FIRST 100 ROWS) ... FIRST 3 ROWS
3) ....?
4) ....?

.....
staxОткуда это все?
У меня третий пример с lateral, первые два - обычные соединения и аналитика.
...
Рейтинг: 0 / 0
17.05.2017, 14:11
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454686
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Раз
(кроме того, что я написал Оракл наверное может сделать NL + window pushed rank)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select t10000000.*
  from (select row_number() over(partition by t10000000.t10_id order by t10000000.value) rn,
               t10000000.*
          from t10000000) t10000000
  join t10
    on t10.id = t10000000.t10_id
 where t10.sign = 1
   and t10000000.rn <= 100


Два
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select t10000000.*
  from (select row_number() over(partition by t10000000.t10_id order by t10000000.value) rn,
               t10000000.*
          from t10
          join t10000000
            on t10.id = t10000000.t10_id
         where t10.sign = 1)
 where rn <= 100


Три ансишный синтаксис (тут будет NL + stopkey)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select t10000000.*
  from t10
 cross apply (select *
                from (select *
                        from t10000000
                       where t10.id = t10000000.t10_id
                       order by value)
               where rownum <= 100) t10000000
 where t10.sign = 1


Три оракловый синтаксис
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select t10000000.*
  from t10,
       lateral (select *
                  from (select *
                          from t10000000
                         where t10.id = t10000000.t10_id
                         order by value)
                 where rownum <= 100) t10000000
 where t10.sign = 1



PS. Предполагается что фильтр "t10.sign = 1" возвращает три строки главной. Кардинальности взяты с потолка - не надо искать в них глубокий смысл.

PPS. Я уже когда-то писал, что синтаксический сахар для TOP N - это больше маркетинговый ход (типа в Оракле такое тоже есть) и для людей плохо владеющих SQL типа бизнес аналитиков. Для разработчиков это не стоит рассматривать всерьез.
...
Рейтинг: 0 / 0
17.05.2017, 14:29
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454694
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshopИными словами, нормальный разработчик не будет писать
Код: plsql
1.
2.
3.
select *
from t1
, lateral (select * from t2 where t1.id = t2.id)(+)



вместо
Код: plsql
1.
2.
3.
select *
from t1
, t2 where t1.id = t2.id(+)


Ну я тогда не "нормальный разработчик" Дело в том, что я сталкивался со случаем, когда у
Код: plsql
1.
select ... from view1, view2 where view1.x = view2.a

и
Код: plsql
1.
select ... from view1, view3 where view1.y = view3.b

нормально пушились предикаты в view2, view3(там были джойны и группировки внутри), а в
Код: plsql
1.
select ... from view1, view2, view3 where view1.x = view2.a and view1.y = view3.b

пушились либо в view2 либо в view3, но вместе никак не хотели работать. И именно в такой ситуации дело решалось как раз lateral:
Код: plsql
1.
2.
3.
select ... from view1
,lateral(select ... from view2 where view1.x = view2.a)
,lateral(select ... from view3 where view1.y = view3.b)


Поэтому я советую в сложных динамических запросах добавлять не через "join x on ....", а "lateral(select .... from x where ...)

dbms_photoshopЕсли же lateral логически нужен, то Оракл от него никак не избавится.вообще это не cost-based трансформация, а эвристическая, поэтому тут сложно сказать какие конкретно у него правила
...
Рейтинг: 0 / 0
17.05.2017, 14:32
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454704
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtenderИ именно в такой ситуации дело решалось как раз lateralПравильно я понял, что они пропушились и метод соединения был HJ чего нельзя было добиться без lateral?
В таком случае я бы назвал это workaround для косяка оптимизатора а не best practice.
xtenderсложно сказать какие конкретно у него правилаУ эвристических трансформаций правило простое - применять всегда когда может быть применено. Другое дело, что в некоторых случаях применено быть не может. :)
...
Рейтинг: 0 / 0
17.05.2017, 14:40
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454721
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Нет, кажись я неправильно понял.
Лучше конкретный пример какого плана нельзя добиться без lateral (для которого легко можно вынести предикат с корреляцией наружу).
Профит в стоимости вторичен.
...
Рейтинг: 0 / 0
17.05.2017, 14:50
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454722
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshopУ эвристических трансформаций правило простое - применять всегда когда может быть применено.ты путаешь с rule-based: Их не два типа(CBQT и Heurustic), а 3:
Путь иерархии ТипALL -> COMPILATION -> TRANSFORMATION Rule basedALL -> COMPILATION -> TRANSFORMATION -> CBQT CostedALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC Heuristic basedps. путь иерархии отсюда: http://blog.tanelpoder.com/2013/04/01/understanding-what-a-hint-affects-using-the-vsql_feature-views/
...
Рейтинг: 0 / 0
17.05.2017, 14:50
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454723
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshopПравильно я понял, что они пропушились и метод соединения был HJ чего нельзя было добиться без lateral?нет, NL был в обоих случаях, просто с lateral предикаты пропушились, а не считалась сначала агрегация вся и потом фильтровалась
...
Рейтинг: 0 / 0
17.05.2017, 14:51
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454730
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
dbms_photoshopstax..пропущено...

вопрос был не в етом
1 вариант) select * from (select * from t10 where rownum<4), lateral (select * from t100000 ...FIRST 100 ROWS)
2 вариант) select * from t10, lateral (select * from t100000 ...FIRST 100 ROWS) ... FIRST 3 ROWS
3) ....?
4) ....?

.....
staxОткуда это все?
У меня третий пример с lateral, первые два - обычные соединения и аналитика.
вот я про третий и спрашиваю, как он выглядит?


ps
для меня необычные и первые два (вернее не знаю как латерал заменить обычным соеденением), но ето не важно
зыы
с FIRST 3 ROWS очепятался
.....
stax
...
Рейтинг: 0 / 0
17.05.2017, 15:00
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454742
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtenderdbms_photoshopУ эвристических трансформаций правило простое - применять всегда когда может быть применено.ты путаешь с rule-based: Их не два типа(CBQT и Heurustic), а 3:
Путь иерархии ТипALL -> COMPILATION -> TRANSFORMATION Rule basedALL -> COMPILATION -> TRANSFORMATION -> CBQT CostedALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC Heuristic basedps. путь иерархии отсюда: http://blog.tanelpoder.com/2013/04/01/understanding-what-a-hint-affects-using-the-vsql_feature-views/ Я подразумеваю, что речь идет про CBO и использую терминологию из
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-general-query-optimization-10gr-130948.pdf Oracle has implemented a wide range of SQL transformations. These broadly
fall into two categories:
heuristic query transformations : These transformations are applied to
incoming SQL statements whenever possible. These transformations
always provide equivalent or better query performance, so that Oracle
knows that applying these transformations will not degrade performance.
cost-based query transformations : Oracle uses a cost-based approach for
several classes of query transformations. Using this approach, the
transformed query is compared to the original query, and Oracle’s
optimizer then selects the best execution strategyЕще раз подчеркну, что не всегда возможно применить эвристическую трансформацию и получить эквивалентный запрос - это именно то, что я пытался сказать.

PS. Всякие view merging могут применяться для RBO, но это не то о чем интересно разговаривать в виду практической бесполезности в наши дни.
...
Рейтинг: 0 / 0
17.05.2017, 15:08
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454746
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
stax..вот я про третий и спрашиваю, как он выглядит?Я надеюсь ты просколлил до моего следующего ответа и увидел, что я имел в виду совершенно иные запросы.
...
Рейтинг: 0 / 0
17.05.2017, 15:08
| Ответить | Цитировать | Написать  
Выбрать первые 3 позиции в группировке
    #39454750
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtenderdbms_photoshopПравильно я понял, что они пропушились и метод соединения был HJ чего нельзя было добиться без lateral?нет, NL был в обоих случаях, просто с lateral предикаты пропушились, а не считалась сначала агрегация вся и потом фильтроваласьЕсть подозрение, что нужного эффекта можно было бы добиться если создать дополнительную inline view и в ней соединять с view2, а снаружи с view3.

Создавать lateral view для которого можно элементарно вынести предикат с корреляцией наружу имхо весьма misleading и я однозначно старался бы этого избежать за исключением случая если иными способами нужного плана достичь невозможно.
...
Рейтинг: 0 / 0
17.05.2017, 15:12
| Ответить | Цитировать | Написать  
25 сообщений из 35, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выбрать первые 3 позиции в группировке
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=162&tid=1885922]:
 0ms
get settings:
 8ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 68ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 1ms
others: 241ms
total:  412ms
созд. / загр.: 412ms
x
x
Закрыть


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