Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server Stirling Number of the Second Kind function


STIRLING2
Updated: 21 July 2017

Use the SQL Server scalar function STIRLING2 to calculate the Stirling numbers of the second kind. The Stirling numbers of the second kind count the number of ways to partition n elements into k nonempty sets:

Where is the binomial coefficient (see BICO).

Stirling numbers of the second kind exhibit the following recursive relationship.

Syntax
SELECT [wct].[STIRLING2] (
   <@n, int,>
  ,<@k, int,>)
Arguments
Input NameDefinition
@nNumber of items
@kNumber chosen
Return Type
float
Remarks
  • If @n <= 0 then NULL is returned.
  • If @k < 0 then NULL is returned.
  • If @k > n then NULL is returned.
  • S(0,0) = 1
  • S(n,0) = 0
  • Available in XLeratorDB / statistics 2008 only
Examples
Example #1

Let’s say that we have a fair die and we roll it 10 times. We can use the Stirling numbers of the second kind to predict the probability that the result set will contain 1, 2, 3, 4, 5 or 6 distinct values.

SELECT
    k.SeriesValue as [N],
    wct.BICO(6,k.seriesvalue)*
    wct.STIRLING2(10,k.seriesvalue)*
    wct.FACT(k.SeriesValue)*
    wct.POWER(6,-10) as p
FROM   
    wct.Seriesint(1,6,NULL,NULL,NULL)k

This produces the following result.

Example #2

A lower triangular representation of the first few Stirling numbers of the second kind.

SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
FROM (
    SELECT
         n.SeriesValue as n
        ,k.seriesValue as k
        ,cast(wct.STIRLING2(n.SeriesValue,k.SeriesValue) as int) as S1
    FROM
        wct.SeriesInt(1,10,NULL,NULL,NULL)n
    CROSS APPLY
        wct.SeriesInt(1,n.seriesvalue,NULL,NULL,NULL)k
    )d   
PIVOT (MAX(S1) FOR k IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10]))pvt
ORDER BY
    n

This produces the following result.

See Also


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service