CORREL_q

Updated 2023-10-31 15:56:38.723000

Syntax

SELECT [westclintech].[wct].[CORREL_q] (
   <@Known_y_Known_x_RangeQuery, nvarchar(4000),>)

Description

Use the scalar function CORREL_q to calculate the correlation coefficient between two datasets. The equation for the correlation coefficient is

r_{xy} = \frac{\sum(x-\bar{x})(y-\bar{y})}{\sqrt{\sum(x-\bar{x})^2\sum(y-\bar{y})^2}

Arguments

@Known_y_Known_x_RangeQuery

the select statement, as text, used to determine the known y- and x-values to be used in the CORREL_q calculation.

Return Type

float

Remarks

If the number of known-x data points is different than the number of known-y data points, CORREL_q will return an error

If the standard deviation of either set of data points is zero, then CORREL_q will return an error.

This function works with normalized and de-normalized tables.

No GROUP BY is required for this function even though it produces aggregated results.

Examples

To determine the correlation between grades in math and grades in science in a normalized table:

CREATE TABLE #s1

(

    [Student] [nvarchar](50) NOT NULL,

    [subject] [nvarchar](50) NOT NULL,

    [grade] [float] NULL

);

INSERT INTO #S1

VALUES

('Student 01', 'Math', 97.19);

INSERT INTO #S1

VALUES

('Student 02', 'Math', 94.07);

INSERT INTO #S1

VALUES

('Student 03', 'Math', 92.78);

INSERT INTO #S1

VALUES

('Student 04', 'Math', 91.86);

INSERT INTO #S1

VALUES

('Student 05', 'Math', 91.12);

INSERT INTO #S1

VALUES

('Student 06', 'Math', 90.48);

INSERT INTO #S1

VALUES

('Student 07', 'Math', 89.9);

INSERT INTO #S1

VALUES

('Student 08', 'Math', 89.36);

INSERT INTO #S1

VALUES

('Student 09', 'Math', 88.86);

INSERT INTO #S1

VALUES

('Student 10', 'Math', 88.37);

INSERT INTO #S1

VALUES

('Student 11', 'Math', 87.89);

INSERT INTO #S1

VALUES

('Student 12', 'Math', 87.42);

INSERT INTO #S1

VALUES

('Student 13', 'Math', 86.95);

INSERT INTO #S1

VALUES

('Student 14', 'Math', 86.47);

INSERT INTO #S1

VALUES

('Student 15', 'Math', 85.98);

INSERT INTO #S1

VALUES

('Student 16', 'Math', 85.46);

INSERT INTO #S1

VALUES

('Student 17', 'Math', 84.92);

INSERT INTO #S1

VALUES

('Student 18', 'Math', 84.32);

INSERT INTO #S1

VALUES

('Student 19', 'Math', 83.65);

INSERT INTO #S1

VALUES

('Student 20', 'Math', 82.86);

INSERT INTO #S1

VALUES

('Student 21', 'Math', 81.85);

INSERT INTO #S1

VALUES

('Student 22', 'Math', 80.32);

INSERT INTO #S1

VALUES

('Student 01', 'Science', 97.64);

INSERT INTO #S1

VALUES

('Student 02', 'Science', 95.02);

INSERT INTO #S1

VALUES

('Student 03', 'Science', 93.94);

INSERT INTO #S1

VALUES

('Student 04', 'Science', 93.17);

INSERT INTO #S1

VALUES

('Student 05', 'Science', 92.54);

INSERT INTO #S1

VALUES

('Student 06', 'Science', 92);

INSERT INTO #S1

VALUES

('Student 07', 'Science', 91.51);

INSERT INTO #S1

VALUES

('Student 08', 'Science', 91.06);

INSERT INTO #S1

VALUES

('Student 09', 'Science', 90.64);

INSERT INTO #S1

VALUES

('Student 10', 'Science', 90.23);

INSERT INTO #S1

VALUES

('Student 11', 'Science', 89.83);

INSERT INTO #S1

VALUES

('Student 12', 'Science', 89.43);

INSERT INTO #S1

VALUES

('Student 13', 'Science', 89.04);

INSERT INTO #S1

VALUES

('Student 14', 'Science', 88.64);

INSERT INTO #S1

VALUES

('Student 15', 'Science', 88.22);

INSERT INTO #S1

VALUES

('Student 16', 'Science', 87.79);

INSERT INTO #S1

VALUES

('Student 17', 'Science', 87.33);

INSERT INTO #S1

VALUES

('Student 18', 'Science', 86.83);

INSERT INTO #S1

VALUES

('Student 19', 'Science', 86.26);

INSERT INTO #S1

VALUES

('Student 20', 'Science', 85.6);

INSERT INTO #S1

VALUES

('Student 21', 'Science', 84.75);

INSERT INTO #S1

VALUES

('Student 22', 'Science', 83.47);

INSERT INTO #S1

VALUES

('Student 01', 'Foreign Language', 96.52);

INSERT INTO #S1

VALUES

('Student 02', 'Foreign Language', 92.64);

INSERT INTO #S1

VALUES

('Student 03', 'Foreign Language', 91.05);

INSERT INTO #S1

VALUES

('Student 04', 'Foreign Language', 89.91);

INSERT INTO #S1

VALUES

('Student 05', 'Foreign Language', 88.99);

INSERT INTO #S1

VALUES

('Student 06', 'Foreign Language', 88.19);

INSERT INTO #S1

VALUES

('Student 07', 'Foreign Language', 87.47);

INSERT INTO #S1

VALUES

('Student 08', 'Foreign Language', 86.81);

INSERT INTO #S1

VALUES

('Student 09', 'Foreign Language', 86.18);

INSERT INTO #S1

VALUES

('Student 10', 'Foreign Language', 85.58);

INSERT INTO #S1

VALUES

('Student 11', 'Foreign Language', 84.99);

INSERT INTO #S1

VALUES

('Student 12', 'Foreign Language', 84.4);

INSERT INTO #S1

VALUES

('Student 13', 'Foreign Language', 83.82);

INSERT INTO #S1

VALUES

('Student 14', 'Foreign Language', 83.22);

INSERT INTO #S1

VALUES

('Student 15', 'Foreign Language', 82.61);

INSERT INTO #S1

VALUES

('Student 16', 'Foreign Language', 81.97);

INSERT INTO #S1

VALUES

('Student 17', 'Foreign Language', 81.29);

INSERT INTO #S1

VALUES

('Student 18', 'Foreign Language', 80.55);

INSERT INTO #S1

VALUES

('Student 19', 'Foreign Language', 79.72);

INSERT INTO #S1

VALUES

('Student 20', 'Foreign Language', 78.74);

INSERT INTO #S1

VALUES

('Student 21', 'Foreign Language', 77.49);

INSERT INTO #S1

VALUES

('Student 22', 'Foreign Language', 75.6);

INSERT INTO #S1

VALUES

('Student 01', 'History', 98.09);

INSERT INTO #S1

VALUES

('Student 02', 'History', 95.97);

INSERT INTO #S1

VALUES

('Student 03', 'History', 95.09);

INSERT INTO #S1

VALUES

('Student 04', 'History', 94.47);

INSERT INTO #S1

VALUES

('Student 05', 'History', 93.96);

INSERT INTO #S1

VALUES

('Student 06', 'History', 93.52);

INSERT INTO #S1

VALUES

('Student 07', 'History', 93.13);

INSERT INTO #S1

VALUES

('Student 08', 'History', 92.77);

INSERT INTO #S1

VALUES

('Student 09', 'History', 92.42);

INSERT INTO #S1

VALUES

('Student 10', 'History', 92.09);

INSERT INTO #S1

VALUES

('Student 11', 'History', 91.77);

INSERT INTO #S1

VALUES

('Student 12', 'History', 91.45);

INSERT INTO #S1

VALUES

('Student 13', 'History', 91.13);

INSERT INTO #S1

VALUES

('Student 14', 'History', 90.8);

INSERT INTO #S1

VALUES

('Student 15', 'History', 90.47);

INSERT INTO #S1

VALUES

('Student 16', 'History', 90.12);

INSERT INTO #S1

VALUES

('Student 17', 'History', 89.74);

INSERT INTO #S1

VALUES

('Student 18', 'History', 89.34);

INSERT INTO #S1

VALUES

('Student 19', 'History', 88.88);

INSERT INTO #S1

VALUES

('Student 20', 'History', 88.34);

INSERT INTO #S1

VALUES

('Student 21', 'History', 87.66);

INSERT INTO #S1

VALUES

('Student 22', 'History', 86.62);

INSERT INTO #S1

VALUES

('Student 01', 'Literature', 98.09);

INSERT INTO #S1

VALUES

('Student 02', 'Literature', 95.97);

INSERT INTO #S1

VALUES

('Student 03', 'Literature', 95.09);

INSERT INTO #S1

VALUES

('Student 04', 'Literature', 94.47);

INSERT INTO #S1

VALUES

('Student 05', 'Literature', 93.96);

INSERT INTO #S1

VALUES

('Student 06', 'Literature', 93.52);

INSERT INTO #S1

VALUES

('Student 07', 'Literature', 93.13);

INSERT INTO #S1

VALUES

('Student 08', 'Literature', 92.77);

INSERT INTO #S1

VALUES

('Student 09', 'Literature', 92.42);

INSERT INTO #S1

VALUES

('Student 10', 'Literature', 92.09);

INSERT INTO #S1

VALUES

('Student 11', 'Literature', 91.77);

INSERT INTO #S1

VALUES

('Student 12', 'Literature', 91.45);

INSERT INTO #S1

VALUES

('Student 13', 'Literature', 91.13);

INSERT INTO #S1

VALUES

('Student 14', 'Literature', 90.8);

INSERT INTO #S1

VALUES

('Student 15', 'Literature', 90.47);

INSERT INTO #S1

VALUES

('Student 16', 'Literature', 90.12);

INSERT INTO #S1

VALUES

('Student 17', 'Literature', 89.74);

INSERT INTO #S1

VALUES

('Student 18', 'Literature', 89.34);

INSERT INTO #S1

VALUES

('Student 19', 'Literature', 88.88);

INSERT INTO #S1

VALUES

('Student 20', 'Literature', 88.34);

INSERT INTO #S1

VALUES

('Student 21', 'Literature', 87.66);

INSERT INTO #S1

VALUES

('Student 22', 'Literature', 86.62);

select wct.CORREL_q('Select a.grade, b.grade

      from #s1 a, #s1 b

      where a.subject = ' + Char(39) + 'Math' + Char(39) + ' and

      b.subject = ' + Char(39) + 'Science' + Char(39) + 'and

      a.student = b.student');

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.999999494275124"}]}

To determine the correlation between grades in math and grades in science on a de-normalized table:

CREATE TABLE #s2

(

    [student] [nvarchar](50) NOT NULL,

    [math] [float] NULL,

    [science] [float] NULL,

    [f_language] [float] NULL,

    [history] [float] NULL,

    [literature] [float] NULL

);

insert into #s2

values

('Student 01', 97.19, 97.64, 96.52, 98.09, 98.09);

insert into #s2

values

('Student 02', 94.07, 95.02, 92.64, 95.97, 95.97);

insert into #s2

values

('Student 03', 92.78, 93.94, 91.05, 95.09, 95.09);

insert into #s2

values

('Student 04', 91.86, 93.17, 89.91, 94.47, 94.47);

insert into #s2

values

('Student 05', 91.12, 92.54, 88.99, 93.96, 93.96);

insert into #s2

values

('Student 06', 90.48, 92, 88.19, 93.52, 93.52);

insert into #s2

values

('Student 07', 89.9, 91.51, 87.47, 93.13, 93.13);

insert into #s2

values

('Student 08', 89.36, 91.06, 86.81, 92.77, 92.77);

insert into #s2

values

('Student 09', 88.86, 90.64, 86.18, 92.42, 92.42);

insert into #s2

values

('Student 10', 88.37, 90.23, 85.58, 92.09, 92.09);

insert into #s2

values

('Student 11', 87.89, 89.83, 84.99, 91.77, 91.77);

insert into #s2

values

('Student 12', 87.42, 89.43, 84.4, 91.45, 91.45);

insert into #s2

values

('Student 13', 86.95, 89.04, 83.82, 91.13, 91.13);

insert into #s2

values

('Student 14', 86.47, 88.64, 83.22, 90.8, 90.8);

insert into #s2

values

('Student 15', 85.98, 88.22, 82.61, 90.47, 90.47);

insert into #s2

values

('Student 16', 85.46, 87.79, 81.97, 90.12, 90.12);

insert into #s2

values

('Student 17', 84.92, 87.33, 81.29, 89.74, 89.74);

insert into #s2

values

('Student 18', 84.32, 86.83, 80.55, 89.34, 89.34);

insert into #s2

values

('Student 19', 83.65, 86.26, 79.72, 88.88, 88.88);

insert into #s2

values

('Student 20', 82.86, 85.6, 78.74, 88.34, 88.34);

insert into #s2

values

('Student 21', 81.85, 84.75, 77.49, 87.66, 87.66);

insert into #s2

values

('Student 22', 80.32, 83.47, 75.6, 86.62, 86.62);

SELECT wct.CORREL_q('SELECT math, science from #s2');

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.999999494275124"}]}