Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
zorlo
Member

Откуда:
Сообщений: 237
Добрый день!
Ранее писал уже тему, ссылка ниже.
[url=]https://www.sql.ru/forum/1336132/vyvesti-znachenie-pri-maksimalnom-value-i-prosummirovat-vse-ostalnye-value[/url]

Напомню пример:

with t as (
   select 2 gr, 1 value, 22 tr from dual union all
  select 2 gr, 0 value, 33 tr from dual union all
  select 2 gr, 120 value, 41 tr from dual union all
  select 5 gr, 2 value, 22 tr from dual union all
  select 5 gr, 3 value, 33 tr from dual union all
  select 5 gr, 56 value, 34 tr from dual union all
  select 9 gr, 20 value, 33 tr from dual union all
  select 9 gr, 2 value, 34 tr from dual union all
 select 9 gr, 12 value, 35 tr from dual
 )
 select gr,sum(value) sv,max(tr)  KEEP (DENSE_RANK last ORDER BY value) mtr
 from t group by gr
 order by 1



Можно ли здесь уйти от группировки и от max() KEEP (DENSE_RANK), заменив это на аналитическую функцию, к примеру over?
8 июн 21, 15:44    [22332959]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19237
zorlo
Можно ли здесь уйти от группировки и от max() KEEP (DENSE_RANK), заменив это на аналитическую функцию, к примеру over?

Можно, но зачем?
 select gr
      , sum(value) over(partition by gr) sv
      , last_value(tr) over(partition by gr ORDER BY value rows between unbounded preceding and unbounded following) mtr
 from t
8 июн 21, 16:19    [22332978]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
=nomad=
Member

Откуда:
Сообщений: 235
А я не понял сути вопроса: вам сгруппированные строки нужны или именно все строки? Ну если второе (как в последнем ответе), то добавьте тогда over(partition by ... ) в сам KEEP (DENSE_RANK...)
8 июн 21, 16:56    [22332994]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3070
zorlo,

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2     select 2 gr, 1 value, 22 tr from dual union all
  3    select 2 gr, 0 value, 33 tr from dual union all
  4    select 2 gr, 120 value, 41 tr from dual union all
  5    select 5 gr, 2 value, 22 tr from dual union all
  6    select 5 gr, 3 value, 33 tr from dual union all
  7    select 5 gr, 56 value, 34 tr from dual union all
  8    select 9 gr, 20 value, 31 tr from dual union all
  9    select 9 gr, 20 value, 33 tr from dual union all
 10    select 9 gr, 2 value, 34 tr from dual union all
 11   select 9 gr, 12 value, 35 tr from dual
 12   )
 13   select gr,sum(value) sv,to_number(substr(max(to_char(gr,'9990')||to_char(value,'9990')||to_char(tr,'9990')),11)) mtr
 14   from t group by gr
 15*  order by 1
SQL> /

        GR         SV        MTR
---------- ---------- ----------
         2        121         41
         5         61         34
         9         54         33


.....
stax

Сообщение было отредактировано: 8 июн 21, 17:09
8 июн 21, 17:16    [22333008]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
zorlo
Member

Откуда:
Сообщений: 237
=nomad=,

Цель была уйти от групповой функции к аналитической
9 июн 21, 10:03    [22333158]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
zorlo
Member

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

Так работает, но получаются дубли, я тоже думал про это решение, но как уйти от дублей не знаю.

К сообщению приложен файл. Размер - 4Kb
9 июн 21, 10:07    [22333159]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
zorlo
Member

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

Работает, но tr у меня текстовое поле, плохой пример привёл((

with t as (
   select 2 gr, 1 value, 'te22' tr from dual union all
  select 2 gr, 0 value, 'te33' tr from dual union all
  select 2 gr, 120 value, 'te41' tr from dual union all
  select 5 gr, 2 value, 'te22' tr from dual union all
  select 5 gr, 3 value, 'te33' tr from dual union all
  select 5 gr, 56 value, 'te34' tr from dual union all
  select 9 gr, 20 value, 'te33' tr from dual union all
  select 9 gr, 2 value, 'te34' tr from dual union all
 select 9 gr, 12 value, 'te35' tr from dual
 )
 select gr,sum(value) sv,to_number(substr(max(to_char(gr,'9990')||to_char(value,'9990')||to_char(tr,'9990')),11)) mtr
 from t group by gr
 order by 1
9 июн 21, 10:15    [22333162]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
zorlo
Member

Откуда:
Сообщений: 237
Вот так попробовал, но это лишний подзапрос, нужно уложить в один запрос

 with t as (
  select 2 gr, 1 value, 'te22' tr from dual union all
  select 2 gr, 0 value, 'te33' tr from dual union all
  select 2 gr, 120 value, 'te41' tr from dual union all
  select 5 gr, 2 value, 'te22' tr from dual union all
  select 5 gr, 3 value, 'te33' tr from dual union all
  select 5 gr, 56 value, 'te34' tr from dual union all
  select 9 gr, 20 value, 'te33' tr from dual union all
  select 9 gr, 2 value, 'te34' tr from dual union all
 select 9 gr, 12 value, 'te35' tr from dual
 )
 --select * from t
 select gr, sum(value), tr from (
 select gr,value, last_value(tr) over (partition by GR order by value rows  BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as tr-- sv,max(tr)  KEEP (DENSE_RANK last ORDER BY value) mtr
 from t group by gr, tr, value
 order by 1)
 group by gr, tr
 order by gr
9 июн 21, 10:19    [22333163]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3070
zorlo,
SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2     select 2 gr, 1 value, 'te22' tr from dual union all
  3    select 2 gr, 0 value, 'te33' tr from dual union all
  4    select 2 gr, 120 value, 'te41' tr from dual union all
  5    select 5 gr, 2 value, 'te22' tr from dual union all
  6    select 5 gr, 3 value, 'te33' tr from dual union all
  7    select 5 gr, 56 value, 'te34' tr from dual union all
  8    select 9 gr, 20 value, 'tz33' tr from dual union all
  9    select 9 gr, 2 value, 'ty34' tr from dual union all
 10   select 9 gr, 12 value, 'tx35' tr from dual
 11   )
 12   select gr,sum(value) sv,substr(max(to_char(gr,'9990')||to_char(value,'9990')||tr),11) mtr
 13   from t group by gr
 14*  order by 1
SQL> /

        GR         SV MTR
---------- ---------- ----
         2        121 te41
         5         61 te34
         9         34 tz33

SQL>

......
stax
9 июн 21, 11:22    [22333182]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заменить конструкцию max() KEEP (DENSE_RANK) на аналитическу функцию over  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
with t as (
  select 2 gr, 1 value, 'te22' tr from dual union all
  select 2 gr, 0 value, 'te33' tr from dual union all
  select 2 gr, 120 value, 'te41' tr from dual union all
  select 5 gr, 2 value, 'te22' tr from dual union all
  select 5 gr, 3 value, 'te33' tr from dual union all
  select 5 gr, 56 value, 'te34' tr from dual union all
  select 9 gr, 20 value, 'te33' tr from dual union all
  select 9 gr, 2 value, 'te34' tr from dual union all
 select 9 gr, 12 value, 'te35' tr from dual
 )
select  gr,
        value,
        tr
  from  t
  match_recognize(
                  partition by gr
                  order by value
                  measures
                    sum(value) value,
                    last(tr) tr
                  one row per match
                  pattern(p+)
                  define p as 1 = 1
                 )
  order by gr
/

        GR      VALUE TR
---------- ---------- ----
         2        121 te41
         5         61 te34
         9         34 te33

SQL>


SY.
9 июн 21, 12:39    [22333235]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить