add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- original 1
with sample_data as
(select
rownum as id,
case when rownum <= 50 then 0 else rownum end as value
from xmltable('1 to 100')
)
select
id,
value,
ntile(50) over (order by value) as ntile_v1,
case when value = 0 then 0 else ntile(50) over (order by value) end as ntile_v2
from sample_data
order by id;
ID VALUE NTILE_V1 NTILE_V2
1 0 1 0
2 0 25 0
3 0 2 0
4 0 2 0
5 0 3 0
6 0 3 0
7 0 4 0
8 0 4 0
9 0 5 0
10 0 5 0
11 0 6 0
12 0 6 0
13 0 7 0
14 0 7 0
15 0 8 0
16 0 8 0
17 0 9 0
18 0 9 0
19 0 10 0
20 0 10 0
21 0 11 0
22 0 11 0
23 0 12 0
24 0 12 0
25 0 13 0
26 0 13 0
27 0 14 0
28 0 14 0
29 0 15 0
30 0 15 0
31 0 16 0
32 0 16 0
33 0 17 0
34 0 17 0
35 0 18 0
36 0 18 0
37 0 19 0
38 0 19 0
39 0 20 0
40 0 20 0
41 0 21 0
42 0 21 0
43 0 22 0
44 0 22 0
45 0 23 0
46 0 23 0
47 0 24 0
48 0 24 0
49 0 25 0
50 0 1 0
51 51 26 26
52 52 26 26
53 53 27 27
54 54 27 27
55 55 28 28
56 56 28 28
57 57 29 29
58 58 29 29
59 59 30 30
60 60 30 30
61 61 31 31
62 62 31 31
63 63 32 32
64 64 32 32
65 65 33 33
66 66 33 33
67 67 34 34
68 68 34 34
69 69 35 35
70 70 35 35
71 71 36 36
72 72 36 36
73 73 37 37
74 74 37 37
75 75 38 38
76 76 38 38
77 77 39 39
78 78 39 39
79 79 40 40
80 80 40 40
81 81 41 41
82 82 41 41
83 83 42 42
84 84 42 42
85 85 43 43
86 86 43 43
87 87 44 44
88 88 44 44
89 89 45 45
90 90 45 45
91 91 46 46
92 92 46 46
93 93 47 47
94 94 47 47
95 95 48 48
96 96 48 48
97 97 49 49
98 98 49 49
99 99 50 50
100 100 50 50
-- original 2
with sample_data as
(select
rownum as id,
case when rownum <= 50 then 0 else rownum end as value
from xmltable('1 to 100')
)
, sample_data_not_zero as
(select
id,
value,
ntile(50) over (order by value) as ntile_v3
from sample_data
where value <> 0
)
select
sd.id,
sd.value,
nvl(sdnz.ntile_v3, 0) as ntile_v3
from
sample_data sd
left outer join
sample_data_not_zero sdnz on sd.id = sdnz.id
order by id;
ID VALUE NTILE_V3
1 0 0
2 0 0
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0
12 0 0
13 0 0
14 0 0
15 0 0
16 0 0
17 0 0
18 0 0
19 0 0
20 0 0
21 0 0
22 0 0
23 0 0
24 0 0
25 0 0
26 0 0
27 0 0
28 0 0
29 0 0
30 0 0
31 0 0
32 0 0
33 0 0
34 0 0
35 0 0
36 0 0
37 0 0
38 0 0
39 0 0
40 0 0
41 0 0
42 0 0
43 0 0
44 0 0
45 0 0
46 0 0
47 0 0
48 0 0
49 0 0
50 0 0
51 51 1
52 52 2
53 53 3
54 54 4
55 55 5
56 56 6
57 57 7
58 58 8
59 59 9
60 60 10
61 61 11
62 62 12
63 63 13
64 64 14
65 65 15
66 66 16
67 67 17
68 68 18
69 69 19
70 70 20
71 71 21
72 72 22
73 73 23
74 74 24
75 75 25
76 76 26
77 77 27
78 78 28
79 79 29
80 80 30
81 81 31
82 82 32
83 83 33
84 84 34
85 85 35
86 86 36
87 87 37
88 88 38
89 89 39
90 90 40
91 91 41
92 92 42
93 93 43
94 94 44
95 95 45
96 96 46
97 97 47
98 98 48
99 99 49
100 100 50
with sample_data as
(select
rownum as id,
case when rownum <= 50 then 0 else rownum end as value
from xmltable('1 to 100')
)
select
id,
value,
ntile(50) over (order by value) as ntile_v1,
case when value = 0 then 0 else ntile(50) over (order by value) end as ntile_v2,
case when value = 0 then 0 else ntile(50) over (partition by case when value = 0 then 0 else 1 end order by value) end as ntile_v3
from sample_data
order by id;
ID VALUE NTILE_V1 NTILE_V2 NTILE_V3
1 0 1 0 0
2 0 1 0 0
3 0 2 0 0
4 0 2 0 0
5 0 3 0 0
6 0 3 0 0
7 0 4 0 0
8 0 4 0 0
9 0 5 0 0
10 0 5 0 0
11 0 6 0 0
12 0 6 0 0
13 0 7 0 0
14 0 7 0 0
15 0 8 0 0
16 0 8 0 0
17 0 9 0 0
18 0 9 0 0
19 0 10 0 0
20 0 10 0 0
21 0 11 0 0
22 0 11 0 0
23 0 12 0 0
24 0 12 0 0
25 0 13 0 0
26 0 13 0 0
27 0 14 0 0
28 0 14 0 0
29 0 15 0 0
30 0 15 0 0
31 0 16 0 0
32 0 16 0 0
33 0 17 0 0
34 0 17 0 0
35 0 18 0 0
36 0 18 0 0
37 0 19 0 0
38 0 19 0 0
39 0 20 0 0
40 0 20 0 0
41 0 21 0 0
42 0 21 0 0
43 0 22 0 0
44 0 22 0 0
45 0 23 0 0
46 0 23 0 0
47 0 24 0 0
48 0 24 0 0
49 0 25 0 0
50 0 25 0 0
51 51 26 26 1
52 52 26 26 2
53 53 27 27 3
54 54 27 27 4
55 55 28 28 5
56 56 28 28 6
57 57 29 29 7
58 58 29 29 8
59 59 30 30 9
60 60 30 30 10
61 61 31 31 11
62 62 31 31 12
63 63 32 32 13
64 64 32 32 14
65 65 33 33 15
66 66 33 33 16
67 67 34 34 17
68 68 34 34 18
69 69 35 35 19
70 70 35 35 20
71 71 36 36 21
72 72 36 36 22
73 73 37 37 23
74 74 37 37 24
75 75 38 38 25
76 76 38 38 26
77 77 39 39 27
78 78 39 39 28
79 79 40 40 29
80 80 40 40 30
81 81 41 41 31
82 82 41 41 32
83 83 42 42 33
84 84 42 42 34
85 85 43 43 35
86 86 43 43 36
87 87 44 44 37
88 88 44 44 38
89 89 45 45 39
90 90 45 45 40
91 91 46 46 41
92 92 46 46 42
93 93 47 47 43
94 94 47 47 44
95 95 48 48 45
96 96 48 48 46
97 97 49 49 47
98 98 49 49 48
99 99 50 50 49
100 100 50 50 50