Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / row_number vs first_value / 9 сообщений из 9, страница 1 из 1
20.11.2017, 19:45
    #39556375
sqlbeginer
sqlbeginer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
Коллеги, насколько верно предположение, что row_number=1 соответствует first_value того же partition?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
create table Z_EMP
(
  id   NUMBER(10)  ,
  name VARCHAR2(400) not null,
  constraint  PK_EMP  primary key (ID)
);
create table Z_CONTACT
(
  id        NUMBER(14) not null,
  constraint PK_CONTACT primary key (ID),
  client_id NUMBER(10) not null,
  c_type    NUMBER,--1 -тлф, 2-email
  c_info    VARCHAR2(100 CHAR) not null,
  c_created DATE,
  active    CHAR(1)
);

create table Z_ADDRESSES
(
  id        NUMBER(14) not null,
  constraint PK_ADDR primary key (ID),
  client_id NUMBER not null,
  a_type    NUMBER,
  city      VARCHAR2(250 CHAR),
  street    VARCHAR2(250 CHAR),
  house     VARCHAR2(10),
  flat      VARCHAR2(10),
  created   DATE,
  active    CHAR(1)
);



ну и нужно получить наилучшие данные сотрудника
получаем
но вычитая из первого множества второе (и наоборот) посредством оператора MINUS, получаем не 0 rows как ожидаем, а определённый набор строк

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
select name,address,phone,email from (
  select 
    c.name,
    row_number() over (partition by a.client_id order by a.a_type desc,                      
                                                         decode(null, a.city,  -1,1) +
                                                         decode(null, a.street,-1,1) +
                                                         decode(null, a.house, -1,1) +
                                                         decode(null, a.flat,  -1,1) desc,
                                                         a.created desc)     as rn_addr,
    trim(BOTH ',' from a.city||','||a.street||','||a.house||','||a.flat)     as address,                                                   
    row_number() over (partition by cp.client_id order by cp.c_created desc ) as rn_phone,
    cp.c_info as phone,
    row_number() over (partition by ce.client_id order by ce.c_created asc ) as rn_email,
    ce.c_info as email
  from z_emp c,
       z_addresses a,
       z_contact  cp,
       z_contact  ce
 where 
 a.client_id (+)=c.id 
   and a.active    (+)='Y' 
   and cp.client_id(+)=c.id 
   and cp.active   (+)='Y' 
   and cp.c_type   (+)=1
   and ce.client_id(+)=c.id 
   and ce.active   (+)='Y' 
   and ce.c_type   (+)=2
   )
where rn_addr =1 
  and rn_phone=1
  and rn_email=1
minus 
select t.name,
  trim(BOTH ',' from a.city||','||a.street||','||a.house||','||a.flat)  as address,                                                   
  p.c_info as phone, 
  e.c_info as email    
from (
  select 
    c.name,
    (select unique first_value(a.id)  over (partition by a.client_id order by a.a_type desc, 
                                                                               decode(null, a.city,  -1,1) +
                                                                               decode(null, a.street,-1,1) +
                                                                               decode(null, a.house, -1,1) +
                                                                               decode(null, a.flat,  -1,1) desc,
                                                                             a.created desc)
      from z_addresses a where a.client_id=c.id and a.active='Y') as addr_id ,
    (select unique first_value(cp.id) over (partition by cp.client_id order by cp.c_created desc ) from z_contact  cp where cp.client_id=c.id and cp.active='Y' and cp.c_type=1) id_phone,--61500000709934
    (select unique first_value(ce.id) over (partition by ce.client_id order by ce.c_created asc ) from z_contact  ce where ce.client_id=c.id and ce.active='Y' and ce.c_type=2) id_email
  from z_emp c
  ) t,
  z_addresses a, 
  z_contact p, 
  z_contact e
where a.id(+)= t.addr_id
  and p.id(+)= t.id_phone
  and e.id(+)= t.id_email;


И наоборот,
На сайте оракла читаем что first_value по дефолту ставит в окно i f you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . Ок, ставим range between unbounded preceding and unbounded following.
Но нулла в итоге снова нет.
Запросы что ли неверны.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2017, 19:50
    #39556377
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
sqlbeginer
Код: plsql
1.
(select unique …(…) over

Говнокод.
Остальной поток сознания не заслуживает внимания.

RTFM test-case.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2017, 19:57
    #39556378
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
sqlbeginer,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
with t(id, value) as
(select 2, 'A' from dual
union all select 1, 'A' from dual
union all select 3, 'C' from dual)
select id
  from (select t.*, row_number() over (order by value) rn from t)
 where rn = 1;

        ID
----------
         2

with t(id, value) as
(select 2, 'A' from dual
union all select 1, 'A' from dual
union all select 3, 'C' from dual)
select unique first_value(id) over (order by value) id from t;

        ID
----------
         1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2017, 23:25
    #39556457
sqlbeginer
sqlbeginer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
Elic,

прокомментируйте пожалуйста результаты тест-кейса другого автора.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2017, 23:52
    #39556464
respect / ignore
respect / ignore
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
sqlbeginerпрокомментируйте пожалуйста результаты тест-кейса другого автора.результаты ничего не показывают, так как недетерминизм сортировки является недерминизмом для обоих функций.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.11.2017, 01:22
    #39556479
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
sqlbeginerElic,

прокомментируйте пожалуйста результаты тест-кейса другого автора.
1. Недетерминизм сортировки один из примеров когда дельта может быть не пустой.
2. sqlbeginer if you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
это может быть важно для last value desc, не для first value asc
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
SQL> with t(id, value) as
  2  (select 2, 'B' from dual
  3  union all select 1, 'A' from dual
  4  union all select 3, 'C' from dual)
  5  select t.*, first_value(id) over (order by value
  6  --range between unbounded preceding and unbounded following
  7  ) id from t;

        ID V         ID
---------- - ----------
         1 A          1
         2 B          1
         3 C          1

SQL>
SQL> with t(id, value) as
  2  (select 2, 'B' from dual
  3  union all select 1, 'A' from dual
  4  union all select 3, 'C' from dual)
  5  select t.*, first_value(id) over (order by value
  6    range between unbounded preceding and unbounded following
  7  ) id from t;

        ID V         ID
---------- - ----------
         1 A          1
         2 B          1
         3 C          1

SQL>
SQL> with t(id, value) as
  2  (select 2, 'B' from dual
  3  union all select 1, 'A' from dual
  4  union all select 3, 'C' from dual)
  5  select t.*, last_value(id) over (order by value desc
  6  --range between unbounded preceding and unbounded following
  7  ) id from t;

        ID V         ID
---------- - ----------
         3 C          3
         2 B          2
         1 A          1

SQL>
SQL> with t(id, value) as
  2  (select 2, 'B' from dual
  3  union all select 1, 'A' from dual
  4  union all select 3, 'C' from dual)
  5  select t.*, last_value(id) over (order by value desc
  6    range between unbounded preceding and unbounded following
  7  ) id from t;

        ID V         ID
---------- - ----------
         3 C          1
         2 B          1
         1 A          1


3. В твою простыню мало кому интересно вникать. Как минимум, вызывает недоумение, что фильтр по active='Y' в первом случае post-join, а во втором pre-join.
4. Про "группировку" аналитикой уже сказали.
...
n. Вообще вызывает недоумение почему бы самому не упростить запрос, убрать дополнительные соединения и/или добавить фильтры. Посмотреть что именно отличается.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2017, 17:31
    #39558496
sqlbeginer
sqlbeginer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
dbms_photoshop1. Недетерминизм сортировки один из примеров когда дельта может быть не пустой.
Ещё и моя ошибка с тремя r_n=1, однако не совсем понял, Недетерминизм имеется в виду в том смысле, что fst_value поднял дублируемое value выше в соответствии с меньшим id, или что-то другое - какая-то более-менее известная истина? Я нашёл только вот The value returned by an analytic function with a logical offset is always deterministic.
2. sqlbeginer if you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
это может быть важно для last value desc, не для first value asc
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
SQL> with t(id, value) as
  2  (select 2, 'B' from dual
  3  union all select 1, 'A' from dual
  4  union all select 3, 'C' from dual)
  5  select t.*, first_value(id) over (order by value
  6  --range between unbounded preceding and unbounded following
  7  ) id from t;

        ID V         ID
---------- - ----------
         1 A          1
         2 B          1
         3 C          1

SQL>
SQL> with t(id, value) as
  2  (select 2, 'B' from dual
  3  union all select 1, 'A' from dual
  4  union all select 3, 'C' from dual)
  5  select t.*, first_value(id) over (order by value
  6    range between unbounded preceding and unbounded following
  7  ) id from t;

        ID V         ID
---------- - ----------
         1 A          1
         2 B          1
         3 C          1

SQL>
SQL> with t(id, value) as
  2  (select 2, 'B' from dual
  3  union all select 1, 'A' from dual
  4  union all select 3, 'C' from dual)
  5  select t.*, last_value(id) over (order by value desc
  6  --range between unbounded preceding and unbounded following
  7  ) id from t;

        ID V         ID
---------- - ----------
         3 C          3
         2 B          2
         1 A          1

SQL>
SQL> with t(id, value) as
  2  (select 2, 'B' from dual
  3  union all select 1, 'A' from dual
  4  union all select 3, 'C' from dual)
  5  select t.*, last_value(id) over (order by value desc
  6    range between unbounded preceding and unbounded following
  7  ) id from t;

        ID V         ID
---------- - ----------
         3 C          1
         2 B          1
         1 A          1



4. Про "группировку" аналитикой уже сказали.
В чём криминал п.4? в distinct?
Если нет, то п.2., в котором видна надёжность fst_value, разве не опровергает п.4.?
Если всё равно нет, то такая конструкция - тоже гомнокод или легальна
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select max(f_value) value from
(
with t(id, value,d ) as
(select 2, 'A', sysdate-1  from dual union all 
 select 1, 'A', sysdate-1  from dual union all 
 select 3, 'C', sysdate  from dual)
select first_value(value) over (order by d) f_value from t
)group by f_value;

?
n. Вообще вызывает недоумение почему бы самому не упростить запрос, убрать дополнительные соединения и/или добавить фильтры. Посмотреть что именно отличается.
Фильтры пробовал, но после того, как увидел, что внешний запрос "переделывает" 1 из трёх rn подзапроса, понял, что неожиданностей многовато для одного раза, и решил обратиться с вопросом к доброжелателям. :)

Модератор: Вложение удалено.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2017, 17:33
    #39558499
sqlbeginer
sqlbeginer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
Это был пример подзапроса, вот результат внешнего запроса

Модератор: Вложение удалено.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2017, 17:37
    #39558505
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
row_number vs first_value
sqlbeginerВ чём криминал п.4? в distinct?
Если нет, то п.2., в котором видна надёжность fst_value, разве не опровергает п.4.?
Если всё равно нет, то такая конструкция - тоже гомнокод или легальнаНе нужна аналитика, чтобы группировать.
RTFM FIRST/LAST
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / row_number vs first_value / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 367ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1884861]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 52ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 31ms
get tp. blocked users:
 1ms
others: 248ms
total:  367ms
x
x
Закрыть


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