April 25, 2013 at 3:42 pm
Hi,
I want to divide a range values into equal parts with t-sql
If i have a range of valuessay 1-100
If i enter Min(Value) as 1 and Max(value) as 100
(Here i am taking it as 5 parts) but i want to pass the value while running the querry.
the output should come as
1-20
21-40
41-60
61-80
81-100
My ranges are in millions Min(value)= 5000000000000480 and max(Value)=5000001000000690
Thanks in advance
April 25, 2013 at 4:04 pm
declare @min-2 numeric(18,0)
declare @max-2 numeric(18,0)
declare @parts numeric(18,0)
select @min-2 = 102201011472463 ,
@max-2 = 102201354392808,
@parts = 3480
declare @increment int = (@max - @min-2) / @parts while @max-2 >= @min-2
begin
declare @newMin numeric(18,0) = @min-2 + @increment
print convert(varchar, @min-2) + ' - ' + convert(varchar, @newMin) select @min-2 = @newMin + 1
end
April 25, 2013 at 4:06 pm
You can do this with a tally table too.
declare @min-2 int = 1, @max-2 int = 100, @NumParts int = 5
select ((N * (@Max - @min-2) / @NumParts) + 1) - ((@Max - @min-2) / @NumParts),
(N * (@Max - @min-2) / @NumParts) + 1
from Tally
where N <= @NumParts
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2013 at 4:30 pm
I think mine is very similar to Sean's:
declare @Parts int = 5,
@InitialValue bigint = 5000000000000480,
@FinalValue bigint = 5000001000000690;
with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), -- 10 rows
e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows
e4(n) as (select 1 from e2 a cross join e2 b), -- 10000 rows
eTally(n) as (select row_number() over (order by (select null)) from e4 a cross join e4 b) -- 100,000,000 rows
select top (@Parts)
n,
@InitialValue + ((n - 1) * (@FinalValue - @InitialValue + 1) / @Parts),
@InitialValue + (((n) * (@FinalValue - @InitialValue + 1) / @Parts) - 1),
cast(@InitialValue + ((n - 1) * (@FinalValue - @InitialValue + 1) / @Parts) as varchar(30)) +
'-' +
cast(@InitialValue + (((n) * (@FinalValue - @InitialValue + 1) / @Parts) - 1) as varchar(30))
from
eTally;
April 25, 2013 at 4:41 pm
Sqlism (4/25/2013)
declare @min-2 numeric(18,0)declare @max-2 numeric(18,0)
declare @parts numeric(18,0)
select @min-2 = 102201011472463 ,
@max-2 = 102201354392808,
@parts = 3480
declare @increment int = (@max - @min-2) / @parts while @max-2 >= @min-2
begin
declare @newMin numeric(18,0) = @min-2 + @increment
print convert(varchar, @min-2) + ' - ' + convert(varchar, @newMin) select @min-2 = @newMin + 1
end
Actually, no loops required. Just look up.
April 27, 2013 at 5:14 pm
This is pretty much what the NTILE() windowed function is for - equally dividing a results set into x number of pieces. Unlike throwing calculations at the min and max values, it keeps sets equal even when there are gaps in the range. Works like ROW_NUMBER(), except the value it returns is between 1 and the number of separate pieces you specify.
To get the beginning and end values of each range, just group by the NTILE() value, and grab the min() and max() values for each group.
DECLARE @SegmentCount int = 0;
SELECT @SegmentCount = 5;
;WITH vals(val) AS (
SELECT 5000000000000 + convert(bigint, n) AS [val]
FROM dbo.Nums
WHERE n BETWEEN 480 AND 1000690
)
SELECT [TheTile] AS [TheRange], min(val) AS RangeStart, max(val) AS [RangeEnd]
FROM (
SELECT NTILE(@SegmentCount) OVER (ORDER BY val) AS [TheTile], val
FROM vals
) a
GROUP BY [TheTile]
ORDER BY [TheTile];
TheRange RangeStart RangeEnd
-------- --------------- --------------
1 5000000000480 5000000200522
2 5000000200523 5000000400564
3 5000000400565 5000000600606
4 5000000600607 5000000800648
5 5000000800649 5000001000690
Eddie Wuerch
MCM: SQL
April 27, 2013 at 5:24 pm
Eddie Wuerch (4/27/2013)
This is pretty much what the NTILE() windowed function is for - equally dividing a results set into x number of pieces. Unlike throwing calculations at the min and max values, it keeps sets equal even when there are gaps in the range. Works like ROW_NUMBER(), except the value it returns is between 1 and the number of separate pieces you specify.To get the beginning and end values of each range, just group by the NTILE() value, and grab the min() and max() values for each group.
DECLARE @SegmentCount int = 0;
SELECT @SegmentCount = 5;
;WITH vals(val) AS (
SELECT 5000000000000 + convert(bigint, n) AS [val]
FROM dbo.Nums
WHERE n BETWEEN 480 AND 1000690
)
SELECT [TheTile] AS [TheRange], min(val) AS RangeStart, max(val) AS [RangeEnd]
FROM (
SELECT NTILE(@SegmentCount) OVER (ORDER BY val) AS [TheTile], val
FROM vals
) a
GROUP BY [TheTile]
ORDER BY [TheTile];
TheRange RangeStart RangeEnd-------- --------------- --------------
1 5000000000480 5000000200522
2 5000000200523 5000000400564
3 5000000400565 5000000600606
4 5000000600607 5000000800648
5 5000000800649 5000001000690
Glad to see you got it to work. I'll have to figure out what I was doing wrong because everything came out in the first NTILE for me. Really messed up if you ask me.
April 29, 2013 at 7:53 am
Eddie Wuerch (4/27/2013)
This is pretty much what the NTILE() windowed function is for - equally dividing a results set into x number of pieces. Unlike throwing calculations at the min and max values, it keeps sets equal even when there are gaps in the range. Works like ROW_NUMBER(), except the value it returns is between 1 and the number of separate pieces you specify.To get the beginning and end values of each range, just group by the NTILE() value, and grab the min() and max() values for each group.
NTILE didn't even cross my mind. 😛
Just for kicks I put together the NTILE version that would produce the same results as my original query. Much to my surprise the query plans are absolutely identical.
declare @min-2 int = 1, @max-2 int = 100, @NumParts int = 5
select ((N * (@Max - @min-2) / @NumParts) + 1) - ((@Max - @min-2) / @NumParts) as RangeStart,
(N * (@Max - @min-2) / @NumParts) + 1 as RangeEnd
, NTILE(@NumParts) over (order by N) as Segment
from Tally
where N <= @NumParts
select MIN(val) as RangeStart, MAX(val) as RangeEnd, Segment
from
(
select
NTILE(@NumParts) over (order by N) as Segment, N as val
from Tally
where N <= @max-2
) x
group by Segment
order by Segment
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2013 at 9:17 am
Eddie Wuerch (4/27/2013)
This is pretty much what the NTILE() windowed function is for - equally dividing a results set into x number of pieces. Unlike throwing calculations at the min and max values, it keeps sets equal even when there are gaps in the range. Works like ROW_NUMBER(), except the value it returns is between 1 and the number of separate pieces you specify.To get the beginning and end values of each range, just group by the NTILE() value, and grab the min() and max() values for each group.
DECLARE @SegmentCount int = 0;
SELECT @SegmentCount = 5;
;WITH vals(val) AS (
SELECT 5000000000000 + convert(bigint, n) AS [val]
FROM dbo.Nums
WHERE n BETWEEN 480 AND 1000690
)
SELECT [TheTile] AS [TheRange], min(val) AS RangeStart, max(val) AS [RangeEnd]
FROM (
SELECT NTILE(@SegmentCount) OVER (ORDER BY val) AS [TheTile], val
FROM vals
) a
GROUP BY [TheTile]
ORDER BY [TheTile];
TheRange RangeStart RangeEnd-------- --------------- --------------
1 5000000000480 5000000200522
2 5000000200523 5000000400564
3 5000000400565 5000000600606
4 5000000600607 5000000800648
5 5000000800649 5000001000690
Brilliant!
-- Itzik Ben-Gan 2001
April 29, 2013 at 9:24 am
Now, if we could just get people to use the semicolon statement terminitor correctly instead of using it as a begininator at the start of a CTE definition. 😉
April 29, 2013 at 7:25 pm
Lynn Pettis (4/29/2013)
Now, if we could just get people to use the semicolon statement terminitor correctly instead of using it as a begininator at the start of a CTE definition. 😉
Heh... I'll tell you when it's really going to matter and that's when MS goes all Oracle on us by requiring it's use in all the right places. What's really going to tick me off is when the errors come up that clearly and correctly identify where they are needed instead of just assuming they're there like they do now with no error. :sick:
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2019 at 12:01 pm
Recursion works well for this:
set nocount on
declare @GroupCount int = 5
declare @MinValue bigint = 5000000000000480
declare @MaxValue bigint = 5000001000000690
declare @RowsPerGroup bigint = ((@MaxValue - @MinValue) / @GroupCount)
;with src(val,rm) as (
select @MaxValue, @MaxValue - @RowsPerGroup union all
select rm-1, case when rm-1 > @MinValue + @RowsPerGroup then rm-1 - @RowsPerGroup else @MinValue end from src where rm-1 >= @MinValue
)
select rm, val from src order by rm asc
option(maxrecursion 0)
January 23, 2019 at 12:39 pm
kgresham - Wednesday, January 23, 2019 12:01 PMRecursion works well for this:set nocount on
declare @GroupCount int = 5
declare @MinValue bigint = 5000000000000480
declare @MaxValue bigint = 5000001000000690declare @RowsPerGroup bigint = ((@MaxValue - @MinValue) / @GroupCount)
;with src(val,rm) as (
select @MaxValue, @MaxValue - @RowsPerGroup union all
select rm-1, case when rm-1 > @MinValue + @RowsPerGroup then rm-1 - @RowsPerGroup else @MinValue end from src where rm-1 >= @MinValue
)
select rm, val from src order by rm asc
option(maxrecursion 0)
That's too expensive. I suggest that you review the previous solutions that would be more efficient than using recursion.
January 25, 2019 at 9:50 am
kgresham - Wednesday, January 23, 2019 12:01 PMRecursion works well for this:set nocount on
declare @GroupCount int = 5
declare @MinValue bigint = 5000000000000480
declare @MaxValue bigint = 5000001000000690declare @RowsPerGroup bigint = ((@MaxValue - @MinValue) / @GroupCount)
;with src(val,rm) as (
select @MaxValue, @MaxValue - @RowsPerGroup union all
select rm-1, case when rm-1 > @MinValue + @RowsPerGroup then rm-1 - @RowsPerGroup else @MinValue end from src where rm-1 >= @MinValue
)
select rm, val from src order by rm asc
option(maxrecursion 0)
I'll stick with the tally version of the code.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply