SeriesFloat
Updated: 17 August 2010
Use SeriesFloat to generate a range of floating point values.
Syntax
SELECT * FROM [Example].[wct].[SeriesFloat] (
<@StartValue, float,>
,<@StopValue, float,>
,<@StepValue, float,>
,<@MaxIterations, float,>
,<@SeriesType, nvarchar(4000),>)
Arguments
@StartValue
the lower (or upper) limit of the floating point range. The first value may be the maximum or the minimum value, depending on the @StepValue. @StartValue must be of the type float or of a type that implicitly converts to float.
@StopValue
the upper (or lower) limit of the floating point range. The last value may be the maximum or the minimum value, depending on the @StepValue. @StopValue must be of the type float or of a type that implicitly converts to float.
@StepValue
the increment (or decrement) used to determine the next value in a linear series (see @SeriesType). In a random series, this is the multiple used to calculate a random number between the @StartValue and the @StopValue. @StepValue must be of the type float or of a type that implicitly converts to float.
@MaxIterations
the number of results to return. For linear series (see @SeriesType), a value of NULL will return all the values between the @StartValue and the @StopValue specified by the @StepValue. For random series, NULL is not permitted. @MaxIterations must be of the type float or of a type that implicitly converts to float. Non-integer values are truncated to integer.
@SeriesType
the letter ‘L’ or ‘l’ for linear series, or the letter ‘R’ or ‘r’ for random series. A linear series will generate numbers from the @StartValue incremented by the @StepValue until either the number of iterations is equal to @MaxIterations or the greatest value less than or equal to the @StopValue has been reached.
A random series will randomly generate numbers that are multiples of @StepValue until @MaxIterations is reached.
Return Types
RETURNS TABLE (
[Seq] [int] NULL,
[SeriesValue] [int] NULL
)
Remarks
· To generate a series for integer values use the SeriesInt function
· To generate a series for date values use the SeriesDate function
· If the @StepValue > @StopValue and @SeriesType = ‘L’, then only one row will be returned with SeriesValue equal to @StartValue.
· If the @StepValue > @StopValue and @SeriesType = ‘R’, then @MaxIterations rows will be returned with SeriesValue equal to the lesser of @StartValue and @StopValue.
· If @SeriesType is NULL, then @SeriesType is set to ‘L’.
· If @StartValue is NULL, it will be calculated from @StopValue, @MaxIterations and @StepValue.
· If @StopValue is NULL, it will be calculated from @StartValue, @MaxIterations and @StepValue.
· If @StepValue is NULL and @SeriesType is ‘L’ and @StopValue > @StartValue then @StepValue is set to 1.
· If @StepValue is NULL and @SeriesType is ‘L’ and @StopValue < @StartValue then @StepValue is set to -1.
· If @SeriesType is ‘L’ and SIGN(@StepValue) <> SIGN(@StopValue - @StartValue) then only one row will be returned.
· If @SeriesType is ‘R’, @MaxIterations must be greater than 0 and not equal to NULL.
· If @SeriesType is ‘R’, @StartValue cannot be NULL
· If @SeriesType is ‘R’, @StopValue cannot be NULL.
Examples
Generate a list of numbers from .1 to 10.
SELECT * FROM wct.SeriesFloat (0.1, 10.0, 0.1, NULL, 'L')
Here is a partial list of the resultant table:
Seq SeriesValue
----------- ----------------------
1 0.1
2 0.2
3 0.3
4 0.4
5 0.5
6 0.6
7 0.7
8 0.8
9 0.9
10 1
11 1.1
12 1.2
13 1.3
14 1.4
15 1.5
Generate a series of numbers from 10 to -10 in decremements of 3.3333.
SELECT * FROM wct.SeriesFloat (10, -10, -3.3333, NULL, 'L')
Here is the resultant table:
Seq SeriesValue
----------- -----------
1 10.0000
2 6.6667
3 3.3334
4 0.0001
5 -3.3332
6 -6.6665
7 -9.9998
Generate 10 random numbers, between 0 and 1. Your results will vary.
SELECT * FROM wct.SeriesFloat (0, 1, '', 10, 'R')
This produces the following results.
Seq SeriesValue
----------- ----------------------
1 0.65459289618516
2 0.0511420620843498
3 0.937456301850014
4 0.252113381518104
5 0.310297649032575
6 0.845395081138888
7 0.968246823627617
8 0.0648803282831238
9 0.362198921554815
10 0.769764599748778
(10 row(s) affected)
Generate 10 random numbers, between 1 and 100 in multiples of .03125. Your results will vary.
SELECT * FROM wct.SeriesFloat (1,100, .03125, 10, 'R')
This produces the following results.
Seq SeriesValue
----------- ----------------------
1 20.09375
2 25.6875
3 24.125
4 46.34375
5 54.6875
6 39
7 31.1875
8 7.375
9 59.75
10 31.375
(10 row(s) affected)
You can use the SeriesFloat function in conjunction with other functions to generate random numbers consistent with known statistical distributions. For example, we could randomly generate 15 numbers from the standard normal distribution with the following statement:
SELECT seq
,wct.NORMSINV(seriesvalue)
FROM wct.SeriesFloat(0,1,'',15,'R')
This produces the following result:
seq
----------- ----------------------
1 -0.275834479530695
2 -2.30220946652677
3 -0.0622709645857857
4 -0.491481485330579
5 -0.624316374420031
6 0.731172113848871
7 -1.80390709713252
8 -2.21012209513013
9 -1.21468657472561
10 -0.709569022616488
11 -1.99210370527231
12 1.76669987779836
13 0.334494017269453
14 -1.01297426483473
15 0.942903565144487
(15 row(s) affected)
We could generate 15 random numbers for a normal distribution with a mean of 100 and a standard deviation of 15 with the following statement:
SELECT seq
,wct.NORMINV(seriesvalue, 100, 15)
FROM wct.SeriesFloat(0,1,'',15,'R')
This produces the following result.
seq
----------- ----------------------
1 104.111666822153
2 104.600267746498
3 103.41236161729
4 97.196247601099
5 105.060578145366
6 127.119056123846
7 69.5989882195887
8 112.212992947044
9 103.994894764519
10 120.900060110402
11 85.138122798331
12 123.688514699804
13 121.229480665641
14 117.687111952413
15 95.5299030979285
We could generate 15 random numbers for a gamma distribution with a shape parameter of 9 with the following statement:
SELECT seq
,wct.GAMMAINV(seriesvalue, 9, 1)
FROM wct.SeriesFloat(0,1,'',15,'R')
This produces the following result.
seq
----------- ----------------------
1 5.39579796067116
2 9.28042459738811
3 9.57009471496562
4 8.39155629502876
5 8.31532406711638
6 8.62589656632907
7 10.8307931736274
8 6.67109040457896
9 6.08872748306008
10 8.43729352363099
11 12.291385310717
12 8.44690443208271
13 6.66271670676902
14 9.12164224048701
15 13.1666978695753
(15 row(s) affected)