(8.0.22)
drop table tab1;
create table tab1(col1 int);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i bigint;
set i = 1;
while i <= 100 do
insert into tab1 values(floor(rand() * 1000)+1);
set i = i + 1;
end while;
end
//
delimiter ;
call proc1();
select col1, ntile(5) over (order by col1 ) tile
from tab1
;
select t.tile ,min(t.col1),max(t.col1),count(t.col1)
from (
select col1, ntile(5) over (order by col1 ) tile
from tab1
) t
group by t.tile
order by t.tile
;
(19c)
drop table tab1 purge;
create table tab1(col1 int);
declare
begin
for i in 1..100 loop
insert into tab1 values( floor(dbms_random.value(1, 1001) ) );
end loop;
commit;
end;
/
select col1, ntile(5) over (order by col1 ) tile
from tab1
;
select t.tile ,min(t.col1),max(t.col1),count(t.col1)
from (
select col1, ntile(5) over (order by col1 ) tile
from tab1
) t
group by t.tile
order by t.tile
;
(13)
drop table tab1;
create table tab1(col1 int);
do $$
declare
begin
for i in 1..100 loop
insert into tab1 values(floor(random() * 1000)+1);
end loop;
end
$$ language plpgsql;
select col1, ntile(5) over (order by col1 ) tile
from tab1
;
select t.tile ,min(t.col1),max(t.col1),count(t.col1)
from (
select col1, ntile(5) over (order by col1 ) tile
from tab1
) t
group by t.tile
order by t.tile
;
(2019)
drop table tab1;
create table tab1(col1 int);
declare @i integer;
set @i = 1;
while @i <= 100
begin
insert into tab1 values(floor(rand() * 1000)+1);
set @i = @i + 1;
end
select col1, ntile(5) over (order by col1 ) tile
from tab1
;
select t.tile ,min(t.col1),max(t.col1),count(t.col1)
from (
select col1, ntile(5) over (order by col1 ) tile
from tab1
) t
group by t.tile
order by t.tile
;
