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

Откуда:
Сообщений: 40
Здравствуйте!
Из таблицы tmp_br2336_device для каждого msisdn нужно найти крайние (по дате) os_type и terminal_type. Написал следующий запрос. Но некоторым msisdn сопоставляются по несколько пар os_type и terminal_type.
create table tmp_br2336_smartphone
as
select distinct dv.msisdn,
    first_value(dv.os_type) over (partition by dv.msisdn order by dv.msisdn_last_updt DESC) os_type,
    first_value(dv.terminal_type) over (partition by dv.msisdn order by dv.msisdn_last_updt DESC) terminal_type  
from tmp_br2336_device dv


Подскажите, пожалуйста, что делаю неправильно?

Заранее благодарен!

Сообщение было отредактировано: 25 ноя 21, 16:17
25 ноя 21, 16:16    [22400794]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
create table tmp_br2336_smartphone
as
select dv.msisdn
     , max(dv.os_type) keep(dense_rank last order by dv.msisdn_last_updt) os_type
     , max(dv.terminal_type) keep(dense_rank last order by dv.msisdn_last_updt) terminal_type  
from tmp_br2336_device dv
group by dv.msisdn
25 ноя 21, 16:20    [22400798]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
SergiiW
Member

Откуда: СССР
Сообщений: 70
ssCandal,
Попробуйте так.
select distinct msisdn, os_type, terminal_type FROM (
select dv.msisdn,
    first_value(dv.os_type) over (partition by dv.msisdn order by dv.msisdn_last_updt DESC) os_type,
    first_value(dv.terminal_type) over (partition by dv.msisdn order by dv.msisdn_last_updt DESC) terminal_type  
from tmp_br2336_device dv)
25 ноя 21, 16:21    [22400799]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10337
create table tmp_br2336_smartphone
  as
    select  dv.msisdn,
            max(dv.os_type) keep(dense_rank last order by dv.msisdn_last_updt,dv.rowid) os_type,
            max(dv.terminal_type) keep(dense_rank last order by dv.msisdn_last_updt,dv.rowid) terminal_type  
      from  tmp_br2336_device dv
      group by partition by dv.msisdn
/


SY.
25 ноя 21, 16:23    [22400802]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
SergiiW
Попробуйте так.

Просто интересно - на какой эффект Вы рассчитываете, предлагая данное "так"?
SY
group by partition by dv.msisdn

По ходу я опять чего-то не знаю...

Сообщение было отредактировано: 25 ноя 21, 16:25
25 ноя 21, 16:23    [22400803]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
SY
Member

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

order by dv.msisdn_last_updt - не детерминирован (разве только поле msisdn_last_updt уникально).

SY.
25 ноя 21, 16:31    [22400805]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
SY
Member

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

Просто интересно - на какой эффект Вы рассчитываете, предлагая данное "так"?
SY
group by partition by dv.msisdn

По ходу я опять чего-то не знаю...


Упс - cut & paste. Конечно partition by лишний:

create table tmp_br2336_smartphone
  as
    select  dv.msisdn,
            max(dv.os_type) keep(dense_rank last order by dv.msisdn_last_updt,dv.rowid) os_type,
            max(dv.terminal_type) keep(dense_rank last order by dv.msisdn_last_updt,dv.rowid) terminal_type  
      from  tmp_br2336_device dv
      group by dv.msisdn
/


SY.
25 ноя 21, 16:34    [22400807]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
SY
andrey_anonymous,
order by dv.msisdn_last_updt - не детерминирован (разве только поле msisdn_last_updt уникально).

Полагаю, в рамках задачи в пределах группы его можно принять уникальным.
Но доп. сортировка по rowid, конечно, делает решение более общим.
25 ноя 21, 16:36    [22400811]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
ssCandal
Member

Откуда:
Сообщений: 40
Работает не так, как нужно мне
SergiiW
ssCandal,
Попробуйте так.
select distinct msisdn, os_type, terminal_type FROM (
select dv.msisdn,
    first_value(dv.os_type) over (partition by dv.msisdn order by dv.msisdn_last_updt DESC) os_type,
    first_value(dv.terminal_type) over (partition by dv.msisdn order by dv.msisdn_last_updt DESC) terminal_type  
from tmp_br2336_device dv)


Запрос
select count(*), count(distinct msisdn) from tmp_br2189_smartphone

выдал
COUNT(*)	COUNT(DISTINCTMSISDN)
--------	---------------------
31314   	22337                

А хотелось бы получить
COUNT(*)	COUNT(DISTINCTMSISDN)
--------	---------------------
22337   	22337                


Сообщение было отредактировано: 25 ноя 21, 18:44
25 ноя 21, 18:36    [22400870]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
ssCandal
Member

Откуда:
Сообщений: 40
msisdn_last_updt уникальны для каждого конкретного msisdn. По крайне мере, я так думаю...
andrey_anonymous
SY
andrey_anonymous,
order by dv.msisdn_last_updt - не детерминирован (разве только поле msisdn_last_updt уникально).

Полагаю, в рамках задачи в пределах группы его можно принять уникальным.
Но доп. сортировка по rowid, конечно, делает решение более общим.


Сообщение было отредактировано: 25 ноя 21, 19:01
25 ноя 21, 18:52    [22400878]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
ssCandal
Работает не так, как нужно мне

Вам уже два человека показали как сделать правильно.
Но в работу Вы взяли изначально неверный вариант.
Продолжайте в том же духе.
25 ноя 21, 19:05    [22400884]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
ssCandal
Member

Откуда:
Сообщений: 40
Другие предложенные варианты я тоже испробовал. Просто ищу вариант без group by.
andrey_anonymous
ssCandal
Работает не так, как нужно мне

Вам уже два человека показали как сделать правильно.
Но в работу Вы взяли изначально неверный вариант.
Продолжайте в том же духе.


А так, оказалось, что пара (dv.msisdn, dv.msisdn_last_updt) не уникальна, хотя я рассчитывал на это.

Сообщение было отредактировано: 25 ноя 21, 19:15
25 ноя 21, 19:14    [22400886]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10337
SergiiW
ssCandal,
Попробуйте так.


Если хочешь аналитику:

with t as (
           select  msisdn,
                   os_type,
                   terminal_type,
                   row_number() over (partition by msisdn order by msisdn_last_updt desc) rn
             from  tmp_br2336_device
          )
select  msisdn,
        os_type,
        terminal_type
  from  t
  where rn = 1
/


или

select  msisdn,
        os_type,
        terminal_type
  from  tmp_br2336_device
  order by row_number() over (partition by msisdn order by msisdn_last_updt desc)
  fetch first 1 row with ties
/


Кстати, аналитика дает преимущество так как позволяет вывести любое количество строк с любым рангом (например первые по имени 3 сотрудника из каждого отдела с 5-ой по величине зарплатой.

SY.

Сообщение было отредактировано: 25 ноя 21, 19:57
25 ноя 21, 19:18    [22400889]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
ssCandal
Member

Откуда:
Сообщений: 40
SY,
Спасибо!
25 ноя 21, 19:26    [22400897]     Ответить | Цитировать Сообщить модератору
 Re: не получается уникально, distinct, partition by  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
ssCandal
Другие предложенные варианты я тоже испробовал. Просто ищу вариант без group by.
...
А так, оказалось, что пара (dv.msisdn, dv.msisdn_last_updt) не уникальна, хотя я рассчитывал на это.


Вариант без group by - зачем, если под задачу лучше подходит именно он?

По уникальности - тогда вариация от SY, добавьте в order by вторым компонентом rowid, это предотвратит возможный косяк, когда ostype возьмется от одной записи, а тип терминала - от другой.


SY
Если хочешь аналитику:
                   row_number() over (partition by dv.msisdn order by dv.msisdn_last_updt desc) rn


В означенных условиях мне больше по душе вариация от Elic
lag(null,1,'1')over (partition by dv.msisdn order by dv.msisdn_last_updt desc) f

которая метит крайнюю в порядке сортировки запись, а не нумерует всю выборку.
...только лучше
lead(null,1,'1')over (partition by dv.msisdn order by dv.msisdn_last_updt) f

а то наступал не так давно на баг, связанный с desc-сортировкой
25 ноя 21, 19:28    [22400899]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить