Sabir Khan Sabir Khan - 1 month ago 13
SQL Question

Is it possible to get partitioned data using SQL?

I have a RDBMS table with a column

BIGINT
type and values are not sequential. I have a java program where I want each thread to get data as per
PARTITION_SIZE
i.e. I want a pair of column values like after doing
ORDER BY
on result,

Column_Value at Row 0 , Column_Value at Row `PARTITION_SIZE`

Column_Value at Row `PARTITION_SIZE+1` , Column_Value at Row `2*PARTITION_SIZE`

Column_Value at Row `2*PARTITION_SIZE+1` , Column_Value at Row `3*PARTITION_SIZE`


Eventually, I will pass above value ranges in a SELECT query's
BETWEEN
clause to get divided data for each thread.

Currently, I am able to do this partitioning via Java by putting all values in a
List
( after getting all values from DB ) and then getting values at those specific indices -
{0,PARTITION_SIZE},{PARTITION_SIZE+1,2*PARTITION_SIZE}
..etc but problem there is that
List
might have millions of records and is not advisable to store in memory.

So I was wondering if its possible to write such a query using SQL itself which would return me those ranges like below?


row-1 -> minId , maxId

row-2 -> minId , maxId

....


Database is DB2.

For example,

For table column values
1,2,12,3,4,5,20,30,7,9,11
,result of SQL query for a partition size =2 should be
{1,2},{3,4} ,{5,7},{9,11},{12,20},{30}
.

Answer

In my eyes the mod() function would solve your problem and you could choose a dynamic number of partitions with it.

WITH numbered_rows_temp as ( 
SELECT rownumber() over () as rownum,
       col1,
       ...
       coln
  FROM table
  ORDER BY col1)

SELECT * FROM numbered_rows_temp
  WHERE mod(rownum, <numberofpartitions>) = 0  

Fill in the appropriate and change the result from 0 to - 1 in your queries.