Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running RSQ function


RunningRSQ

Updated: 31 Oct 2012


Use the RunningRSQ function to calculate the square of the Pearson product moment correlation coefficient through data points in y- and x-values within a resultant table or partition, without the need for a self-join. The correlation coefficient is calculated from the first row of the resultant table or partition through to the current row. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [Example].[wct].[RunningRSQ](
  <@Y, float,>
 ,<@X, float,>
 ,<@RowNum, int,>
 ,<@Id, tinyint,>)
GO
Arguments
@Y
the y-value passed into the function. @Y is an expression of type float or of a type that can be implicitly converted to float.
@X
the x-value passed into the function. @X is an expression of type float or of a type that can be implicitly converted to float.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the RunningRSQ calculation. @Id allows you to specify multiple RunningRSQ calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Remarks
·         If @Id is NULL then @Id = 0.
·         @RowNum must be in ascending order.
·         To calculate the square of the correlation coefficient over a window of x- and y-values use the MovingRSQ function.
·         If @RowNum = 1 then RunningRSQ is NULL.
·         To calculated a single square of the correlation coefficient value for a set of x- and y-values, use the RSQ function.
·         There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.
Example
In this example we will calculate the square of the correlation coefficient in the relationship between square footage and house prices. We will create a temporary table, #se, populate it with some data and then run the SELECT.
SET NOCOUNT ON
--Create the temporary table
CREATE TABLE #se(
      rn                int,
      id_lot            int,
      amt_sqft    int,
      amt_price   int,
      PRIMARY KEY (rn)
      )
--Put some date in the table
INSERT INTO #se VALUES (1,21783,1147,393918)
INSERT INTO #se VALUES (2,94729,1313,470479)
INSERT INTO #se VALUES (3,33028,1433,512474)
INSERT INTO #se VALUES (4,59446,1724,610477)
INSERT INTO #se VALUES (5,97646,1162,388196)
INSERT INTO #se VALUES (6,44823,1813,636916)
INSERT INTO #se VALUES (7,88397,1105,374348)
INSERT INTO #se VALUES (8,13588,1555,559149)
INSERT INTO #se VALUES (9,13891,1775,623900)
INSERT INTO #se VALUES (10,90957,1585,563947)
INSERT INTO #se VALUES (11,44167,1510,529806)
INSERT INTO #se VALUES (12,75533,1628,592533)
INSERT INTO #se VALUES (13,56812,1145,408634)
INSERT INTO #se VALUES (14,12897,1632,589522)
INSERT INTO #se VALUES (15,93826,1850,668852)
INSERT INTO #se VALUES (16,74510,1867,633400)
INSERT INTO #se VALUES (17,17262,1587,552178)
INSERT INTO #se VALUES (18,30929,1809,633141)
INSERT INTO #se VALUES (19,49030,1521,555713)
INSERT INTO #se VALUES (20,33431,1195,434542)
--Calculate RSQ
SELECT rn
,id_lot
,amt_sqft
,amt_price
,wct.RunningRSQ(amt_price,amt_sqft,ROW_NUMBER() OVER (ORDER BY rn),NULL) as RSQ
FROM #se
--Clean up
DROP TABLE #se
 
This produces the following result.
         rn      id_lot    amt_sqft   amt_price                    RSQ
----------- ----------- ----------- ----------- ----------------------
          1       21783        1147      393918                   NULL
          2       94729        1313      470479                      1
          3       33028        1433      512474      0.994508459443581
          4       59446        1724      610477      0.993395382191858
          5       97646        1162      388196      0.988456339599465
          6       44823        1813      636916      0.991135178527575
          7       88397        1105      374348      0.992548648834574
          8       13588        1555      559149      0.991549672349918
          9       13891        1775      623900      0.992331371968798
         10       90957        1585      563947      0.992357626842498
         11       44167        1510      529806      0.992343210114028
         12       75533        1628      592533      0.990249419721381
         13       56812        1145      408634       0.98989235167382
         14       12897        1632      589522      0.989566123744144
         15       93826        1850      668852      0.990925385225506
         16       74510        1867      633400      0.984082874500432
         17       17262        1587      552178       0.98361449791164
         18       30929        1809      633141      0.984148573328568
         19       49030        1521      555713      0.981801120674846
         20       33431        1195      434542      0.981670150999315


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service