Divide two numbers to a range without using parts-CodePudding

I have to divide two numbers based on the increment provided by the users, I found an answer which is based on the parts.

I can not use parts as it effects numbers of rows in result, rather I have to pass increment.

Here is my query using parts.

declare @min numeric(18,0)
declare @max numeric(18,0)
declare @parts numeric(18,0)

select @min = 100 ,
@max = 204,
@parts = 10

declare @increment int = (@max - @min) / @parts 
while @max >= @min
begin
    declare @newMin numeric(18,0) = @min   @increment
    print convert(varchar, @min)   ' - '   convert(varchar, @newMin) select @min = @newMin   1
end

Expected output, as you can see in the query my input is min and max with parts based on which the increments are calculating but I have to fix increment like 10 or 100.

CodePudding user response：

There are 2 answers here, 1 based on the original version of the question (where To could be larger than @max) and that your goal is that @parts is the value of numbers in you want in the bucket ( 1). The latter is that you want to split the range into that many buckets, and the last bucket is shrunk if the upper value is larger than the @max value. Both use a Tally function here, which I include the definition of:

CREATE FUNCTION [fn].[Tally] (@End bigint, @StartAtOne bit) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @StartAtOne = 0
        UNION ALL
        SELECT TOP (@End)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7, N N8)
    SELECT I
    FROM Tally;
GO

DECLARE @min numeric(18,0) = 100,
        @max numeric(18,0) = 304,
        @parts numeric(18,0) = 10;

SELECT @min   (T.I * (@parts 1)),
       @min   ((T.I 1) * (@parts 1))-1
FROM fn.Tally((@max - @min)/(@parts 1),0) T;

SELECT @min   (T.I * CEILING(((@max - @min)/@parts))),
       CASE WHEN @min   ((T.I 1)* CEILING(((@max - @min)/@parts)))-1 > @max THEN @max ELSE @min   ((T.I 1)* CEILING(((@max - @min)/@parts)))-1 END
FROM fn.Tally(@parts-1,0) T

db<>fiddle

CodePudding user response：

The question is unclear and doesn't describe the actual problem that needs solving. The code shows a way to partition a continuous range of numbers 100-204 into N partitions, in this case specified by @parts. It's not very efficient but it works.

Partitioning a range into parts is a popular SQL puzzle so there are a lot of articles over the past 30-40 years that show how to do it for different databases, using different features and trying to get the best performance. In the simple form there are two ways to partition :

By part count, what you have
By part size

If you don't want by part count, you probably want by part size. Doing this isn't complicated either and doesn't require slow loops.

Assuming we have a Tally table named Numbers, with all numbers up to eg 1M, the query to partition a range would be :

declare @start int=100
declare @end int=204
declare @size int=25

;with parts as (
    select Number,(Number-@start)/@size as part_id
    from numbers where number between @start and @end
)
select part_id,min(number) as [Start],max(number) as [End]
from parts
group by part_id


----------------------
part_id Start   End
0   100 124
1   125 149
2   150 174
3   175 199
4   200 204

First, the Number is divided by the size we want using integer division to determine the part it belongs to. The results are then grouped by the Part ID and the range limits are the minimum and maximum Number in that group.

Creating a Numbers table is cheap. I used this script to generate a table with 1M numbers which takes about 11MB only:

DECLARE @UpperBound INT = 1000000;

;WITH cteN(Number) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
)
SELECT [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= @UpperBound;

CREATE UNIQUE CLUSTERED INDEX CIX_Number ON dbo.Numbers([Number])
WITH 
(
  FILLFACTOR = 100,  
  DATA_COMPRESSION = ROW
);

Data compression is available in all SQL Server versions and editions since SQL Server 2016 SP1.

The same technique can be used to partition a range into N parts, using the NTILE function this time :

declare @parts int=10
;with parts as (
    select number, NTILE(@parts) over(order by number) as part_id
    from numbers where number between @start and @end
)
select part_id,min(number) as [Start],max(number) as [End]
from parts
group by part_id

In real business cases NTILE is used to partition results into "buckets"