Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выбрать первые 3 позиции в группировке / 25 сообщений из 35, страница 1 из 2
  1  все
16.05.2017, 17:57
    #39454037
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
Приветствую.
Имеются данные:
Код: 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:59
    #39454041
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
Петров Андрей,

row_number() ?

Версия субд какая?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2017, 17:59
    #39454042
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
Немного подправил запрос:
Код: 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, 18:01
    #39454043
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
Петров Андрей,

для 12c посмотри эту тему
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2017, 18:05
    #39454048
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
А для 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, 19:00
    #39454101
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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
| Ответить | Цитировать | Написать  
17.05.2017, 10:17
    #39454386
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
dbms_photoshop,

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

С твоим стилем изложения можно следующую книгу будет по комментариям в темах собирать, практически без редакторской правки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2017, 11:19
    #39454461
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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:35
    #39454476
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
dbms_photoshop
Код: plaintext
lateral           3 раза сортировка по 1 млн, фильтр 300
К слову CBO умеет трансформировать laterals так что в некоторых случаях может оказаться что будет один хэшджойн
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2017, 11:39
    #39454482
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
и еще: https://hourim.wordpress.com/2017/03/25/de-correlated-lateral-view-vw_dcl_mmm/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2017, 11:39
    #39454485
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
Код: 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, 13:42
    #39454616
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
stax..dbms_photoshoplateral 3 раза сортировка по 1 млн, фильтр 300
каким образом получаете 3 из десяти?dbms_photoshopпри этом нас интересуют ТОП 100 только для трех строк из главнойЕсли следующим вопросом будет почему я выдумал эту тройку, то ответ: чтоб обратить внимание, что будет выполнятся сортировка разного числа строк для первых двух случаев при наличии фильтра по главной.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2017, 13:51
    #39454626
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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, 14:03
    #39454646
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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:11
    #39454660
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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:29
    #39454686
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
Раз
(кроме того, что я написал Оракл наверное может сделать 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:32
    #39454694
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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:40
    #39454704
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
xtenderИ именно в такой ситуации дело решалось как раз lateralПравильно я понял, что они пропушились и метод соединения был HJ чего нельзя было добиться без lateral?
В таком случае я бы назвал это workaround для косяка оптимизатора а не best practice.
xtenderсложно сказать какие конкретно у него правилаУ эвристических трансформаций правило простое - применять всегда когда может быть применено. Другое дело, что в некоторых случаях применено быть не может. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2017, 14:50
    #39454721
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
Нет, кажись я неправильно понял.
Лучше конкретный пример какого плана нельзя добиться без lateral (для которого легко можно вынести предикат с корреляцией наружу).
Профит в стоимости вторичен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2017, 14:50
    #39454722
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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:51
    #39454723
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
dbms_photoshopПравильно я понял, что они пропушились и метод соединения был HJ чего нельзя было добиться без lateral?нет, NL был в обоих случаях, просто с lateral предикаты пропушились, а не считалась сначала агрегация вся и потом фильтровалась
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2017, 15:00
    #39454730
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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:08
    #39454742
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
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
    #39454746
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
stax..вот я про третий и спрашиваю, как он выглядит?Я надеюсь ты просколлил до моего следующего ответа и увидел, что я имел в виду совершенно иные запросы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2017, 15:12
    #39454750
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выбрать первые 3 позиции в группировке
xtenderdbms_photoshopПравильно я понял, что они пропушились и метод соединения был HJ чего нельзя было добиться без lateral?нет, NL был в обоих случаях, просто с lateral предикаты пропушились, а не считалась сначала агрегация вся и потом фильтроваласьЕсть подозрение, что нужного эффекта можно было бы добиться если создать дополнительную inline view и в ней соединять с view2, а снаружи с view3.

Создавать lateral view для которого можно элементарно вынести предикат с корреляцией наружу имхо весьма misleading и я однозначно старался бы этого избежать за исключением случая если иными способами нужного плана достичь невозможно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выбрать первые 3 позиции в группировке / 25 сообщений из 35, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 461ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1885922]:
 0ms
get settings:
 6ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 177ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 212ms
total:  461ms
x
x
Закрыть


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