ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка (аналитика)
24 сообщений из 24, страница 1 из 1
Задачка (аналитика)
    #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:29
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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
17.01.2018, 14:35
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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:39
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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, 09:54
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #39586295
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
grokт.е. это будет быстрее ?Не парься. Количество window-шагов в плане одинаково в обоих вариантах. Только один из вариантов читабельнее другого.
...
Рейтинг: 0 / 0
18.01.2018, 11:04
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #39633296
Zahar H.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zahar H.
Гость
sum(NVL2(a,0,1)) is a bit shorter than sum(case ...)
...
Рейтинг: 0 / 0
19.04.2018, 16:27
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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:45
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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:51
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #39633326
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
MaximaXXL,

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

Ну и если исключительно для сортировки, можно упростить до
Код: plsql
1.
  nvl(a, count(a) over() + 1 + rownum) MaximaXXL,
...
Рейтинг: 0 / 0
19.04.2018, 17:06
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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:17
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #39633342
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
MaximaXXL
Код: plsql
1.
  max(a) over () + row_number() over (order by a nulls first) Elic,

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

Поставь null-ы в начало, наивный чудак.
...
Рейтинг: 0 / 0
19.04.2018, 17:21
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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:24
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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:26
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #39633350
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
Похоже я жестко туплю, и не понял задачу до конца =(
...
Рейтинг: 0 / 0
19.04.2018, 17:32
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #39633352
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
MaximaXXLЯ к томуА я к тому, что ты не понял моего первоначального ответа.
...
Рейтинг: 0 / 0
19.04.2018, 17:35
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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:46
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #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
19.04.2018, 17:55
| Ответить | Цитировать | Написать  
Задачка (аналитика)
    #39633503
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
MaximaXXL,

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

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

.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 12:28
| Ответить | Цитировать | Написать  
24 сообщений из 24, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка (аналитика)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39633580&tid=1884118]:
 0ms
get settings:
 11ms
get forum list:
 18ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 69ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 81ms
get tp. blocked users:
 2ms
others: 307ms
total:  508ms
созд. / загр.: 508ms
x
x
Закрыть


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