Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 красивее найти максимум по двум полям  [new]
Sintetik
Member

Откуда: SpB->Msk->...
Сообщений: 9270
есть таблица с тремя столбцами
HASHIDSEQUENCE_NUMBERfile_name
aaa1a1.csv
aaa2a1.csv
aaa3a1.csv
aaa1a2.csv
bbb1a1.csv
bbb2a1.csv
bbb1a1.csv
bbb2a3.csv


искомый результат, для каждого кода нужно найти максимальную комбинацию SEQUENCE_NUMBER,file_name
причем file_name старше, т.е. для каждого кода найти максимальный файл в котором он встречается,
а в нем найти максимальный SEQUENCE_NUMBER
HASHIDSEQUENCE_NUMBERfile_name
aaa 1 a2.csv
bbb 2 a3.csv


можно ли сделать элегантнее?
select * from (
select HASHID, 
       file_name,
       max(SEQUENCE_NUMBER) OVER (PARTITION BY HASHID, file_name) as max_sq,
       ROW_NUMBER() OVER (PARTITION BY HASHID ORDER BY file_name DESC) as rn
    from  base_data
   )hh
where rn=1
4 июн 19, 20:09    [21901900]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
Sintetik
Member

Откуда: SpB->Msk->...
Сообщений: 9270
сортировка по имени файла условная, в реальной задаче там идет парсинг имени, но для упрощения считаем что сортировка стрики дает нужный результат для поиска максимального значения
4 июн 19, 20:11    [21901901]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
xtender
Member

Откуда: Мск
Сообщений: 5119
Sintetik,

самая обычная группировка:
with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',1,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual union all
   select 'aaa',3,'a1.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a1.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual 
)
select hashid
      ,max(sequence_number)keep(dense_rank first order by file_name desc, sequence_number desc) as max_sequence_number
      ,max(file_name      )keep(dense_rank first order by file_name desc, sequence_number desc) as max_file_name
from base_data
group by hashid;
4 июн 19, 20:18    [21901905]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7854
with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',1,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual union all
   select 'aaa',3,'a1.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a1.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual 
)
select  hashid
      , sequence_number 
      , file_name 
from base_data
WHERE (hashid, file_name||'~'||sequence_number) IN 
         (SELECT hashid, MAX(file_name||'~'||sequence_number) 
          FROM base_data GROUP BY hashid)
order by hashid;
6 июн 19, 12:38    [21903374]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9468
SQL*Plus,

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',1,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual union all
   select 'aaa',3,'a1.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a1.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual 
)
select  DISTINCT hashid
      , sequence_number 
      , file_name 
from base_data
WHERE (hashid, file_name||'~'||sequence_number) IN 
         (SELECT hashid, MAX(file_name||'~'||sequence_number) 
          FROM base_data GROUP BY hashid)
order by hashid;


SY.
6 июн 19, 13:59    [21903496]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9468
Или через аналитику:

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',1,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual union all
   select 'aaa',3,'a1.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a1.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual
),
x as (
SELECT hashid,file_name,sequence_number,
       row_number() over(partition by hashid order by file_name desc,sequence_number desc) rn
FROM base_data)
SELECT hashid,file_name,sequence_number
FROM x
WHERE rn = 1
ORDER BY hashid
/


SY.
6 июн 19, 14:04    [21903508]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7854
SY,

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',1,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual union all
   select 'aaa',3,'a1.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a1.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual
)
select  DISTINCT hashid
      , sequence_number
      , file_name
from base_data
WHERE (hashid,file_name,sequence_number,1) IN
         (SELECT hashid,file_name,sequence_number,
                 row_number() over(partition by hashid order by file_name desc,sequence_number desc) rn
          FROM base_data)
order by hashid
/

:-)
6 июн 19, 14:15    [21903516]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9468
SQL*Plus,

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',1,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual union all
   select 'aaa',3,'a1.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a1.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual
),
x as (
SELECT hashid,file_name,sequence_number,
       row_number() over(partition by hashid order by file_name desc,sequence_number desc) rn
FROM base_data)
SELECT hashid,file_name,sequence_number
FROM x
WHERE rn = 1
ORDER BY hashid
/


SY.
6 июн 19, 14:17    [21903521]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7854
SY
SQL*Plus,

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',1,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual union all
   select 'aaa',3,'a1.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a1.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual
),
x as (
SELECT hashid,file_name,sequence_number,
       row_number() over(partition by hashid order by file_name desc,sequence_number desc) rn
FROM base_data)
SELECT hashid,file_name,sequence_number
FROM x
WHERE rn = 1
ORDER BY hashid
/


SY.
Да.
Вижу, что вы тоже быстро обнаружили свою неточность и исправили запрос... :-)
6 июн 19, 14:18    [21903528]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
xtender
Member

Откуда: Мск
Сообщений: 5119
группировка через аналитику? ....
6 июн 19, 14:33    [21903555]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9468
xtender
группировка через аналитику? ....


А в чем проблема? И чем KEEP не аналитика по DENSE_RANK + фильтр FIRST/LAST?

SY.
6 июн 19, 14:52    [21903592]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
Stax
Member

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

  1  with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
  2     select 'aaa',1,'a2.csv.txt' from dual union all
  3     select 'aaa',2,'a1.csv' from dual union all
  4     select 'aaa',3,'a1.csv' from dual union all
  5     select 'aaa',1,'a2.csv' from dual union all
  6     select 'bbb',1,'a1.csv' from dual union all
  7     select 'bbb',2,'a1.csv' from dual union all
  8     select 'bbb',1,'a1.csv' from dual union all
  9     select 'bbb',2,'a3.csv' from dual
 10  )
 11  /*
 12  select  hashid
 13        , sequence_number
 14        , file_name
 15  from base_data
 16  WHERE (hashid, file_name||'~'||sequence_number) IN
 17           (SELECT hashid, MAX(file_name||'~'||sequence_number)
 18            FROM base_data GROUP BY hashid)
 19  order by hashid
 20  */
 21  select * from (
 22  select HASHID,
 23         file_name,
 24         max(SEQUENCE_NUMBER) OVER (PARTITION BY HASHID, file_name) as max_sq,
 25         ROW_NUMBER() OVER (PARTITION BY HASHID ORDER BY file_name DESC) as rn
 26      from  base_data
 27     )hh
 28* where rn=1
SQL> /

HAS FILE_NAME      MAX_SQ         RN
--- ---------- ---------- ----------
aaa a2.csv.txt          1          1
bbb a3.csv              2          1

SQL> ed
Wrote file afiedt.buf

  1  with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
  2     select 'aaa',1,'a2.csv.txt' from dual union all
  3     select 'aaa',2,'a1.csv' from dual union all
  4     select 'aaa',3,'a1.csv' from dual union all
  5     select 'aaa',1,'a2.csv' from dual union all
  6     select 'bbb',1,'a1.csv' from dual union all
  7     select 'bbb',2,'a1.csv' from dual union all
  8     select 'bbb',1,'a1.csv' from dual union all
  9     select 'bbb',2,'a3.csv' from dual
 10  )
 11  select  hashid
 12        , sequence_number
 13        , file_name
 14  from base_data
 15  WHERE (hashid, file_name||'~'||sequence_number) IN
 16           (SELECT hashid, MAX(file_name||'~'||sequence_number)
 17            FROM base_data GROUP BY hashid)
 18* order by hashid
 19  /

HAS SEQUENCE_NUMBER FILE_NAME
--- --------------- ----------
aaa               1 a2.csv
bbb               2 a3.csv

SQL>


....
stax
6 июн 19, 14:59    [21903609]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9468
Stax,

Тут и проблема с числами:

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',10,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual
)
select  hashid
      , sequence_number
      , file_name
from base_data
WHERE (hashid, file_name||'~'||sequence_number) IN
         (SELECT hashid, MAX(file_name||'~'||sequence_number)
          FROM base_data GROUP BY hashid)
order by hashid
/

HAS SEQUENCE_NUMBER FILE_N
--- --------------- ------
aaa               2 a1.csv

SQL> 


Числа надо "выравнивать":

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',10,'a1.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual
)
select  hashid
      , sequence_number
      , file_name
from base_data
WHERE (hashid, file_name||'~'||to_char(sequence_number,'9999999999')) IN
         (SELECT hashid, MAX(file_name||'~'||to_char(sequence_number,'9999999999'))
          FROM base_data GROUP BY hashid)
order by hashid
/

HAS SEQUENCE_NUMBER FILE_N
--- --------------- ------
aaa              10 a1.csv

SQL> 


А '~' использовать нельзя (как ты и показал). Использовать надо CHR(0):

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',1,'a2.csv.txt' from dual union all
   select 'aaa',2,'a1.csv' from dual union all
   select 'aaa',3,'a1.csv' from dual union all
   select 'aaa',1,'a2.csv' from dual union all
   select 'bbb',1,'a1.csv' from dual union all
   select 'bbb',2,'a1.csv' from dual union all
   select 'bbb',10,'a3.csv' from dual union all
   select 'bbb',2,'a3.csv' from dual
)
select  DISTINCT hashid
      , sequence_number
      , file_name
from base_data
WHERE (hashid, file_name||chr(0)||to_char(sequence_number,'9999999999')) IN
         (SELECT hashid, MAX(file_name||chr(0)||to_char(sequence_number,'9999999999'))
          FROM base_data GROUP BY hashid)
order by hashid
/

HAS SEQUENCE_NUMBER FILE_NAME
--- --------------- ----------
aaa               1 a2.csv.txt
bbb              10 a3.csv

SQL> 


SY.
6 июн 19, 15:29    [21903661]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17347
SY
Использовать надо CHR(0)

Однажды наткнулся на клиента, в данных которого таки был chr(0) - имеет смысл проявлять некоторую осторожность :)
6 июн 19, 16:00    [21903699]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9468
andrey_anonymous
Однажды наткнулся на клиента, в данных которого таки был chr(0) - имеет смысл проявлять некоторую осторожность :)


В принципе - да, но в имeни файла вроде как нет :).

SY.
6 июн 19, 16:22    [21903723]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
xtender
Member

Откуда: Мск
Сообщений: 5119
SY
xtender
группировка через аналитику? ....


А в чем проблема? И чем KEEP не аналитика по DENSE_RANK + фильтр FIRST/LAST?

SY.
абсолютно разные алгоритмы, функции и выделение памяти - сравни планы
6 июн 19, 16:55    [21903757]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17347
Не все так однозначно...
SY
Числа надо "выравнивать":
with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',10,'a1.csv' from dual union all
   select 'aaa',9,'a2.csv' from dual union all
   select 'aaa',8,'a10.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual
)
6 июн 19, 17:15    [21903798]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1840
xtender
абсолютно разные алгоритмы


не могу догадатся почему ,max(file_name )keep(dense_rank first order by file_name desc, sequence_number desc) as max_file_name

простого max(file_name ) недостаточно?

.....
stax
6 июн 19, 17:19    [21903803]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1840
andrey_anonymous
Не все так однозначно...
SY
Числа надо "выравнивать":
with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',10,'a1.csv' from dual union all
   select 'aaa',9,'a2.csv' from dual union all
   select 'aaa',8,'a10.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual
)

в чем подвох (если считать что 21901900 отрабатывает правильно)?

....
stax
6 июн 19, 17:23    [21903813]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9468
xtender
абсолютно разные алгоритмы, функции и выделение памяти - сравни планы


Имплементация разная - логика примерно та-же. Что GROUP BY, что PARTITION BY разбивают set на группы и вычисляют DENSE_RANK (GROUP BY) или ROW_NUMBER (аналитикa c ORDER BY для FIRST или ORDER BY DESC для LAST). Дальше GROUP BY берет строки (а их может быть > 1) с FIRST или LAST DENSE_RANK и аггрегирует. С аналитикой мы делаем то-же через in-lion view/with clause фильтруя по ROW_NUMBER = 1.

SY.
6 июн 19, 17:25    [21903815]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9468
Stax
в чем подвох (если считать что 21901900 отрабатывает правильно)?

stax


Андрей о том что a10.csv должен быть > a2.csv. Hо это другая проблема. Если сравнивать надо как alphanumeric то это решается например регуляркой. Скажем numeric порции <= 10 цифр:

with base_data(HASHID,SEQUENCE_NUMBER,file_name) as (
   select 'aaa',10,'a1.csv' from dual union all
   select 'aaa',9,'a2.csv' from dual union all
   select 'aaa',8,'a10.csv' from dual union all
   select 'aaa',2,'a1.csv' from dual
),
x as (
SELECT hashid,file_name,sequence_number,
       row_number() over(
                         partition by hashid
                         order by regexp_replace(
                                                 regexp_replace(
                                                                file_name,
                                                                '(\d+)',
                                                                '000000000\1'
                                                               ),
                                                '\d*(\d{10})',
                                                '\1'
                                                ) desc,
                                  sequence_number desc
                        ) rn
FROM base_data)
SELECT hashid,file_name,sequence_number
FROM x
WHERE rn = 1
ORDER BY hashid
/

HAS FILE_NA SEQUENCE_NUMBER
--- ------- ---------------
aaa a10.csv               8

SQL> 


SY.
6 июн 19, 17:45    [21903848]     Ответить | Цитировать Сообщить модератору
 Re: красивее найти максимум по двум полям  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17347
SY
Имплементация разная - логика примерно та-же.

Аналитика по сути должна формировать (и часто материализовать в temp) весь rowset, который лишь на следующем шаге плана будет почитан (в т.ч. из temp), отфильтрован, агрегирован.
Агрегация же зачастую может себе позволить инкрементально копить агрегаты прямо из потока базового rowsource, что снижает требования к памяти и ресурсам на промежуточных шагах плана.
По этой причине лично я избегаю применения аналитики там, где достаточно агрегации.
6 июн 19, 18:14    [21903907]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить