Hi this query i am trying to run in MySQL to get well distribution over my ID but looks like some issue in syntax .
select min(ID), max(ID),count(*), nt from ( select ID, ntile(16) over (order by ID) nt from table) group by nt order by nt;
This is working in Oracle but not MySQL may be looks like its not available in MySQL 5.7 . How else can we get this data ?
Basically i have UUID application generated which can be ordered and in need to organize and group and then divide in 16 segment .
Expected output
MIN(ID) MAX(ID) COUNT(*) NT
00000000-ebc5-4d19-9d7b 0a360b83-6d9a-17d7-9b67 36282227 1
0a360b83-6d9a-17d7-9b67 0a360b85-6ebb-1bbc-9bbb 36282227 2
It takes a bit of logic and if you like you can debug it
The first is my application, the second is the mysql 80 version of your query for comparison
I would still recommend upgrading your mysql version
The main part is
@mod:=countr % 16, @div:=countr DIV 16
Where you determine which number of tiles you need
SELECT MIN(ID), MAX(ID), COUNT(*), nt FROM (SELECT `ID`, IF(@countr < @div2, @ntile, @ntile:=@ntile + 1) AS nt, IF(@countr < @div2, @countr:=@countr + 1, @countr:=1) c1, IF(@ntile <= CAST(@mod AS UNSIGNED), @div2:=@div + 1, @div2:=@div) div2 FROM (SELECT ID, @mod:=countr % 16, @div:=countr DIV 16, @div2:=@div FROM table1, (SELECT COUNT(*) countr FROM table1, (SELECT @ntile:=1, @countr:=0, @div2:=0) t3) t2) t1 ORDER BY ID) t1 GROUP BY nt ORDER BY CAST(nt AS UNSIGNED);
select min(ID) , max(ID) ,count(*) , nt from ( select ID , ntile(16) over (order by ID) nt from table1) t1 group by nt order by nt;
db<>fiddle here