{"id":70534,"date":"2017-04-06T15:36:50","date_gmt":"2017-04-06T15:36:50","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=70534"},"modified":"2021-09-29T16:21:16","modified_gmt":"2021-09-29T16:21:16","slug":"statistics-sql-pearsons-correlation","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/statistics-sql-pearsons-correlation\/","title":{"rendered":"Statistics in SQL: Pearson’s Correlation"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. Statistics in SQL: Pearson\u2019s Correlation<\/a><\/li>\n
  2. Statistics in SQL: Kendall\u2019s Tau Rank Correlation<\/a><\/li>\n
  3. Statistics in SQL: Simple Linear Regressions<\/a><\/li>\n
  4. Statistics in SQL: The Kruskal\u2013Wallis Test<\/a><\/li>\n
  5. Statistics in SQL: The Mann\u2013Whitney U Test<\/a><\/li>\n
  6. Statistics in SQL: Student's T Test<\/a><\/li>\n<\/ol>\n\n

    When you have two variables, one of the first questions you\u2019d probably like to ask is, \u2018how closely are they related?\u2019 These variables could be attributes whose values come from measurements, scores or other types of quantity. Are you eager to puzzle out what factors are related the number of purchases in your website per day, for example? You want some sort of measure of how the various attributes relate together in their values. You have two choices, depending on whether your data is \u2018parametric\u2019 or not. A variable is \u2018parametric\u2019 if it is representative of a normally-distributed (bell-curve) population. If it isn\u2019t, then you\u2019d want to use something like Kendall\u2019s rank correlation or Spearman\u2019s rank correlation<\/span><\/a> coefficient, but if your measurements are parametric, then Pearson\u2019s Product-moment Correlation Coefficient is best.<\/p>\n

    Karl Pearson was an odd cove who had some very weird ideas about eugenics. He and Francis Galton were out to distinguish clear differences of racial types, using scientific measurement. He was a gifted mathematician whose work influenced Einstein, and which founded the science of Psychology, but his delusions about eugenics were to have ghastly consequences in the twentieth century. \u00a0\u00a0<\/p>\n

    Pearson\u2019s Product-moment Correlation Coefficient gives a measurement from -1 for a perfect negative correlation (as one variable goes up, the other goes down) to 1 for a perfect correlation (as one variable goes up, the other goes up). a correlation of 0 means that there is no relationship between the two. Pearson’s correlation coefficient is calculated as the covariance of the two variables divided by the product of their standard deviations.<\/p>\n

    You can go further to calculate the probability that this correlation occurred by chance. This only gives a meaningful figure if both variables are normally distributed.<\/p>\n

    Using SQL, here is how one might calculate Pearson’s correlation coefficient when applied to a sample. It is usually represented by the letter r, and is the sample correlation coefficient. We will use the formula<\/p>\n

    \"pearsonsCorrelation\"<\/p>\n

    \u2026which is easier to do in SQL, though not entirely easy on the eye for a non-statistician. \u00a0My expression in the following SQL merely translates this into something any SQL programmer will greet with a grin of recognition. That big squiggly E means \u2018sum()\u2019 in SQL, and N is count(*). Later, I’ll show an easier way of calculating it, using the built-in aggregation functions (StDev() and StDevP()) designed for this sort of job.<\/p>\n

    \n

    DECLARE<\/span> @OurData<\/span> TABLE<\/span><\/p>\n

    \u00a0<\/span>(<\/span><\/p>\n

    \u00a0x<\/span> NUMERIC<\/span>(<\/span>18,<\/span> 6)<\/span> NOT<\/span> NULL,<\/span><\/p>\n

    \u00a0y<\/span> NUMERIC<\/span>(<\/span>18,<\/span> 6)<\/span> NOT<\/span> NULL<\/span><\/p>\n

    \u00a0);<\/span><\/p>\n

    \u00a0<\/p>\n

    INSERT<\/span> INTO<\/span> @OurData<\/span><\/p>\n

    \u00a0<\/span>(<\/span>x<\/span>,<\/span> y<\/span>)<\/span><\/p>\n

    VALUES<\/span><\/p>\n

    \u00a0<\/span>(<\/span>10,<\/span> 7),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>9,<\/span> 13),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>13,<\/span> 11),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>9,<\/span> 8),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>12,<\/span> 13),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>9,<\/span> 11),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>10,<\/span> 11),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>10,<\/span> 12),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>9,<\/span> 8),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>11,<\/span> 9),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>13,<\/span> 13),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>10,<\/span> 11),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>14,<\/span> 15),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>11,<\/span> 13),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>8,<\/span> 8),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>13,<\/span> 13),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>13,<\/span> 11),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>12,<\/span> 10),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>10,<\/span> 12),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>12,<\/span> 7),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>10,<\/span> 12),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>10,<\/span> 12),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>7,<\/span> 9),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>10,<\/span> 11),<\/span><\/p>\n

    \u00a0<\/span>(<\/span>11,<\/span> 8);<\/span><\/p>\n

    \u00a0<\/p>\n

    \u00a0<\/p>\n

    SELECT <\/span>((<\/span>SUM<\/span>(<\/span>x<\/span> *<\/span> y<\/span>)<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0 –<\/span> (<\/span>SUM<\/span>(<\/span>x<\/span>)<\/span> *<\/span> SUM<\/span>(<\/span>y<\/span>))<\/span> \/<\/span> COUNT<\/span>(*)<\/span><\/p>\n

    \u00a0\u00a0\u00a0 )<\/span><\/p>\n

    \u00a0\u00a0 )<\/span><\/p>\n

    \u00a0\u00a0 \/<\/span> (<\/span>SQRT<\/span>(<\/span>SUM<\/span>(<\/span>x<\/span> *<\/span> x<\/span>)<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 –<\/span> (<\/span>SUM<\/span>(<\/span>x<\/span>)<\/span> *<\/span> SUM<\/span> (<\/span>x<\/span>))<\/span> \/<\/span> COUNT<\/span>(*)<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0 *<\/span> SQRT<\/span>(<\/span>SUM<\/span>(<\/span>y<\/span> *<\/span> y<\/span>)<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 –<\/span> (<\/span>SUM<\/span>(<\/span>y<\/span>)<\/span> *<\/span> SUM<\/span>(<\/span>y<\/span>))<\/span> \/<\/span> COUNT<\/span>(*)<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0 )<\/span> AS<\/span> ‘Pearsons r’<\/span><\/p>\n

    \u00a0FROM<\/span> @OurData<\/span>;<\/span><\/p>\n<\/div>\n

    … giving …<\/p>\n

    \n

    (25 row(s) affected)<\/span><\/p>\n

    Pearsons r<\/span><\/p>\n

    0.428590216844083<\/span><\/p>\n

    \u00a0<\/p>\n

    (1 row(s) affected)<\/span><\/p>\n

    \u00a0<\/p>\n<\/div>\n

    SQL Server has a built-in function for calculating the population variance \u00a0that makes it simpler to calculate<\/p>\n

    \n

    \/*or more shortly, using the built-in StDevP function giving the<\/p>\n

    \u00a0statistical standard deviation for the population for all values *\/<\/span><\/p>\n

    \u00a0<\/span>SELECT <\/span> (<\/span>Avg<\/span>(<\/span>x<\/span> *<\/span> y<\/span>)<\/span> –<\/span> (<\/span>Avg<\/span>(<\/span>x<\/span>)<\/span> *<\/span> Avg<\/span>(<\/span>y<\/span>)))<\/span> \/<\/span> (<\/span>StDevP<\/span>(<\/span>x<\/span>)<\/span> *<\/span> StDevP<\/span>(<\/span>y<\/span>))<\/span> AS<\/span> ‘Pearsons r’<\/span><\/span><\/p>\n

    \u00a0FROM<\/span> @ourData<\/span><\/span><\/p>\n<\/div>\n

    … giving …<\/p>\n

    \n

    Pearsons r<\/span><\/p>\n

    0.428590216844083<\/span><\/p>\n

    \u00a0<\/p>\n

    (1 row(s) affected)<\/span><\/p>\n

    \u00a0<\/p>\n<\/div>\n

     <\/p>\n

    You\u2019d probably just want it to a couple of decimal places.<\/p>\n

    \u00a0For a good introduction to using correlation in SQL see Causation, Correlation and Crackpots,<\/a> by Joe Celko.<\/div>\n","protected":false},"excerpt":{"rendered":"

    Some people will assure you that you can’t do any serious statistical calculations in SQL. In the first of a series of articles, Phil factor aims to prove them wrong by explaining how easy it is to calculate Pearson’s Product Moment Correlation.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,2,143531],"tags":[43637],"coauthors":[6813],"class_list":["post-70534","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-blogs","category-t-sql-programming-sql-server","tag-statisticsinsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70534","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=70534"}],"version-history":[{"count":13,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70534\/revisions"}],"predecessor-version":[{"id":71973,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70534\/revisions\/71973"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=70534"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=70534"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=70534"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=70534"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}