1

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
Sudarshan kumar
  • 1,503
  • 4
  • 36
  • 83

1 Answers1

1

NTILE for MYSQL 5,7 And Mariadb 10.1

**and prior versions**

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

nbk
  • 45,398
  • 8
  • 30
  • 47
  • Can you please remove those IDS its working for me thank you so much ..I am not able to migrate to 5.8 Because i am using Aurora and in AWS its not released yet – Sudarshan kumar May 03 '21 at 04:55
  • 1
    i don't see why, nobody ca do anything with it anyway. Is there a good cause why you didin't upvte the answer? – nbk May 03 '21 at 08:33
  • Thanks nbk It took time for me to understand script so waited to test but all good thanks again – Sudarshan kumar May 03 '21 at 09:02