Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка (аналитика) / 24 сообщений из 24, страница 1 из 1
17.01.2018, 14:29
    #39585833
grok
grok
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
есть набор номеров строк, номера могут быть NULL, а также могут иметь пропуски
нужно отсортировать, а вместо NULL в конце вывести по порядку

я написал такое вот решение

 

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with x(a) as
(
select 1 from dual union all
select 2 from dual union all
select 4 from dual union all
select 9 from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual
)
select nvl(a, max(a) over(order by a nulls last) + sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum  ) )
from x
order by 1




кто-нибудь может оценить адекватность ?
т.к. на этих данных норм, но я не уверен что на всех возможных данных

ну и если кто придумает еще варианты, интересно посмотреть
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.01.2018, 14:35
    #39585837
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
grok
Код: plsql
1.
max(a) over(order by a nulls last) + sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum  )

Код: plsql
1.
max(a) over () + row_number() over (order by a nulls first)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.01.2018, 09:39
    #39586226
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
grok,

в надежде что для over сортировать будет раз

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
with x(a) as
(
select 1 from dual union all
select 2 from dual union all
select 4 from dual union all
select 9 from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual
)
select nvl(a,
  nvl(last_value(a ignore nulls) over(order by a nulls last, rownum),0)
+ sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum  ) ) x
from x
order by 1
/



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.01.2018, 09:54
    #39586232
grok
grok
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
Staxgrok,

в надежде что для over сортировать будет раз

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
with x(a) as
(
select 1 from dual union all
select 2 from dual union all
select 4 from dual union all
select 9 from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual
)
select nvl(a,
  nvl(last_value(a ignore nulls) over(order by a nulls last, rownum),0)
+ sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum  ) ) x
from x
order by 1
/



.....
stax

т.е. это будет быстрее ?
(не могу так ценить на взгляд)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.01.2018, 09:54
    #39586233
grok
grok
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
Elicgrok
Код: plsql
1.
max(a) over(order by a nulls last) + sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum  )

Код: plsql
1.
max(a) over () + row_number() over (order by a nulls first)



спасибо, как-то не подумал про row_number в таком виде
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.01.2018, 11:04
    #39586295
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
grokт.е. это будет быстрее ?Не парься. Количество window-шагов в плане одинаково в обоих вариантах. Только один из вариантов читабельнее другого.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 16:27
    #39633296
Zahar H.
Zahar H.
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
sum(NVL2(a,0,1)) is a bit shorter than sum(case ...)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 16:45
    #39633315
Zahar H.
Zahar H.
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
--Slightly modified solution (no need to use cumulative MAX function)
with x(a) as
(
select 1 from dual union all
select 2 from dual union all
select 4 from dual union all
select 9 from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual
)
select nvl(a, max(a) over() + sum(NVL2(a,0,1)) over(order by a nulls last, rownum ) )
from x
order by 1

--Another approach:

with x(a) as
(
select 1 from dual union all
select 2 from dual union all
select 4 from dual union all
select 9 from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual
)
SELECT NVL(a,MAX(a)OVER()) +
NVL2(a,0,ROW_NUMBER()OVER(ORDER BY a NULLS LAST)-COUNT(a)OVER()) b
FROM x

it is also doable with RECURSIVE CTE or MODEL clause
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 16:51
    #39633321
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
grok,

Код: 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.
with x(a) as
(
select 1 from dual union all
select 2 from dual union all
select 4 from dual union all
select 9 from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual
)
select a, 

  nvl2(a, 0, count(a)over() ) + rownum MaximaXXL,
  
  max(a) over () + row_number() over (order by a nulls first) Elic,
  
  nvl(a,
  nvl(last_value(a ignore nulls) over(order by a nulls last, rownum),0)
+ sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum  ) ) Stax,

  nvl(a, max(a) over(order by a nulls last) + sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum  ) ) grok 
  
from x
order by 1


A	MaximaXXL Elic	Stax	Grok
1	1	  15	1	1
2	2	  16	2	2
4	3	  17	4	4
9	4	  18	9	9
 - 	9	  10	10	10
 - 	10	  13	11	11
 - 	11	  12	12	12
 - 	12	  11	13	13
 - 	13	  14	14	14
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 16:58
    #39633326
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
MaximaXXL,

Чтоб совсем совпало
Код: plsql
1.
  nvl(a,nvl2(a, 0, count(a)over()+1 ) + rownum) MaximaXXL,
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:06
    #39633332
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
grok,

Ну и если исключительно для сортировки, можно упростить до
Код: plsql
1.
  nvl(a, count(a) over() + 1 + rownum) MaximaXXL,
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:17
    #39633340
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
MaximaXXLgrok,

Ну и если исключительно для сортировки, можно упростить до
Код: plsql
1.
  nvl(a, count(a) over() + 1 + rownum) MaximaXXL,


Код: 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.
  1  with x(a) as
  2  (
  3  select 1 from dual union all
  4  select 2 from dual union all
  5  select 4 from dual union all
  6  select 19 from dual union all
  7  select null from dual union all
  8  select null from dual union all
  9  select null from dual union all
 10  select null from dual union all
 11  select null from dual
 12  )
 13  select
 14  a
 15  --nvl(a, max(a) over(order by a nulls last) + sum(case wh
 16  ,nvl(a, count(a) over() + 1 + rownum) MaximaXXL
 17  from x
 18* order by 1
SQL> /

         A  MAXIMAXXL
---------- ----------
         1          1
         2          2
         4          4
        19         19
                   13
                   12
                   11
                   14
                   10



....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:19
    #39633342
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
MaximaXXL
Код: plsql
1.
  max(a) over () + row_number() over (order by a nulls first) Elic,

Инсинуация.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:21
    #39633344
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
MaximaXXL
Код: plsql
1.
+ rownum

Поставь null-ы в начало, наивный чудак.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:24
    #39633345
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
Stax,

Так для сортировки жеж

Код: 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.
with x(a) as
(
select 1 from dual union all
select 2 from dual union all
select 4 from dual union all
select 19 from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual
)
select a, 

  nvl(a, count(a) over() + 1 + rownum) MaximaXXL
  
from x
order by a, MaximaXXL


A	MaximaXXL
1	1
2	2
4	4
19	19
 - 	10
 - 	11
 - 	12
 - 	13
 - 	14



Все прекрасно сортирует
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:26
    #39633348
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
ElicMaximaXXL
Код: plsql
1.
  max(a) over () + row_number() over (order by a nulls first) Elic,

Инсинуация.

Я к тому что row_number() over (order by a nulls first) - пересортирует, или я не прав?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:32
    #39633350
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
Похоже я жестко туплю, и не понял задачу до конца =(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:35
    #39633352
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
MaximaXXLЯ к томуА я к тому, что ты не понял моего первоначального ответа.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:46
    #39633358
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
ElicMaximaXXLЯ к томуА я к тому, что ты не понял моего первоначального ответа.

я действительно не понял
Код: 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.
  1  with x(a) as
  2  (
  3  select 1 from dual union all
  4  select 2 from dual union all
  5  select 4 from dual union all
  6  select 19 from dual union all
  7  select null from dual union all
  8  select null from dual union all
  9  select null from dual union all
 10  select null from dual union all
 11  select null from dual
 12  )
 13  select
 14  a
 15  --,nvl(a, max(a) over(order by a nulls last) + sum(case when a is null then 1 else 0 end
 16  --,nvl(a, count(a) over() + 1 + rownum) MaximaXXL
 17  ,max(a) over () + row_number() over (order by a nulls first) elic
 18  ,max(a) over(order by a nulls last)
 19   + sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum)  glok
 20  from x
 21* order by 1,2
SQL> /

         A       ELIC       GLOK
---------- ---------- ----------
         1         25          1
         2         26          2
         4         27          4
        19         28         19
                   20         20
                   21         23
                   22         22
                   23         21
                   24         24

9 rows selected.



....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:55
    #39633361
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
Stax,

Я просто не до конца понял задачу, а Elic правильно предложил пересчитать сначала null по порядку и прибавить к max(a) значению

в итоге будет:

Код: 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.
with x(a) as
(
select 1 from dual union all
select 2 from dual union all
select 4 from dual union all
select 19 from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual union all
select null from dual
)
select a, 
--,nvl(a, max(a) over(order by a nulls last) + sum(case when a is null then 1 else 0 end
--,nvl(a, count(a) over() + 1 + rownum) MaximaXXL
nvl(a,max(a) over () + row_number() over (order by a nulls first)) elic
,max(a) over(order by a nulls last)
+ sum(case when a is null then 1 else 0 end) over(order by a nulls last, rownum)  glok

from x
order by 1

A	ELIC	GLOK
1	1	1
2	2	2
4	4	4
19	19	19
 - 	20	20
 - 	23	21
 - 	22	22
 - 	21	23
 - 	24	24
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 17:55
    #39633362
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
StaxElicА я к тому, что ты не понял моего первоначального ответа.я действительно не понялНу так перечитай его внимательно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2018, 08:18
    #39633503
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
MaximaXXL,

дя я знаю что от Elic-а удочка, а не рыба
я ж ничего сразу не написал о его посте, мол сами догадайтесь что нужен еще nvl/decode/case
у Elic-а две сортировки, поетому и дописал к своему варіанту "в надежде что для over сортировать будет раз"

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2018, 10:19
    #39633580
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
Staxу Elic-а две сортировки, поетому и дописал к своему варіанту "в надежде что для over сортировать будет разWindow buffer- это не сортировка, и в варианте Elic-а этот шаг "поглощается" шагом window sort. Таким образом, сортировка будет одна, если не считать последнюю кляузу order by 1 после from.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2018, 12:28
    #39633694
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка (аналитика)
AmKadStaxу Elic-а две сортировки, поетому и дописал к своему варіанту "в надежде что для over сортировать будет разWindow buffer- это не сортировка, и в варианте Elic-а этот шаг "поглощается" шагом window sort. Таким образом, сортировка будет одна, если не считать последнюю кляузу order by 1 после from.
я считаю и с последней, так как она не совпадает с over сортировкой

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка (аналитика) / 24 сообщений из 24, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 423ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1884118]:
 0ms
get settings:
 11ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 69ms
get tp. blocked users:
 2ms
others: 265ms
total:  423ms
x
x
Закрыть


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