{"id":71580,"date":"2017-06-30T13:03:30","date_gmt":"2017-06-30T13:03:30","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71580"},"modified":"2021-09-29T16:21:12","modified_gmt":"2021-09-29T16:21:12","slug":"statistics-sql-mann-whitney-u-test","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/statistics-sql-mann-whitney-u-test\/","title":{"rendered":"Statistics in SQL: The Mann\u2013Whitney U test"},"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 Imagine you have some data. Maybe you’ve done a web redesign and you want to do some A\/B testing to see if by redesigning the page your visitors spend more time reading your content. You set up an experiment in which visitors are randomly sent to either the old or new design, and you measure how long they spend engaged with the content. (you try to ensure that all other factors besides the design of the website are ‘controlled’, meaning that they are equal for both samples. Is the new design significantly better than the old one? You need to disprove the ‘null hypothesis’ that the two samples, from those who got the new design and those who got the old one are from the same population.<\/p>\n

    There are several ways that you can test this, but nobody is going to argue with you if you use a Mann\u2013Whitney U test to test whether two samples come from the same distribution. It doesn’t require that the data has any particular type of distribution. It just requires that each observation is done by a different member of the population so that all the observations from both groups are independent of each other. It is really just a test of differences in mean-rank between two populations’ pooled ranking. To test this difference It has to be possible to compare any of the observations with any of the others and say which of the two are greater. Your objective is to disprove the assumption that The distributions of both populations are equal. Calculating a measure of the difference is simple, and was designed to be done easily by hand before computers. The probability that the observed difference occurred by chance is easily calculated for large samples because U then approximates to the normal distribution, but it is complex for small samples. Here, we have a small sample and are just interested in whether the two-tailed test is signifcant at the five percent level so we dodge the bullet by using a significance lookup table for the critical value of U.<\/p>\n

    SET NOCOUNT on\r\n\/* we'll start with our table of critical values for U for small values. Our test is two-tailed and we\r\nare interested in the 5% level so we just need that. *\/\r\nDECLARE @TwoTailedCriticalValues TABLE\r\n  (\r\n  UValue INT NOT NULL,\r\n  N1 INT NOT NULL,\r\n  N2 INT NOT NULL\r\n  );\r\nINSERT INTO @TwoTailedCriticalValues(UValue,N2,N1)\r\nvalues\r\n(0,8,2), (0,9,2), (0,10,2), (0,11,2), (1,12,2), (1,13,2), (1,14,2), (1,15,2), (1,16,2), (2,17,2),\r\n(2,18,2), (2,19,2), (2,20,2), (0,5,3), (1,6,3), (1,7,3), (2,8,3), (2,9,3), (3,10,3), (3,11,3), \r\n(4,12,3), (4,13,3), (5,14,3), (5,15,3), (6,16,3), (6,17,3), (7,18,3), (7,19,3), (8,20,3),(0,5,4),\r\n(1,6,4), (2,7,4), (3,8,4), (4,9,4), (4,10,4), (5,11,4), (6,12,4), (7,13,4), (9,14,4), (10,15,4), \r\n(11,16,4), (11,17,4), (12,18,4), (13,19,4), (14,20,4),(2,5,5), (3,6,5), (5,7,5), (6,8,5), (7,9,5),\r\n(8,10,5), (9,11,5), (11,12,5), (12,13,5), (13,14,5), (14,15,5), (15,16,5), (17,17,5), (18,18,5),\r\n(19,19,5), (20,20,5), (5,6,6), (6,7,6), (8,8,6), (10,9,6), (11,10,6), (13,11,6), (14,12,6),\r\n(16,13,6), (17,14,6), (19,15,6), (21,16,6), (22,17,6), (24,18,6), (25,19,6), (27,20,6),(8,7,7), \r\n(10,8,7), (12,9,7), (14,10,7), (16,11,7), (18,12,7), (20,13,7), (22,14,7), (24,15,7), (26,16,7),\r\n(28,17,7), (30,18,7), (32,19,7), (34,20,7),(13,8,8), (15,9,8), (17,10,8), (19,11,8), (22,12,8), \r\n(24,13,8), (26,14,8), (29,15,8), (31,16,8), (34,17,8), (36,18,8), (38,19,8), (41,20,8),(17,9,9), \r\n(20,10,9), (23,11,9), (26,12,9), (28,13,9), (31,14,9), (34,15,9), (36,16,9), (39,17,9), (42,18,9),\r\n(45,19,9), (48,20,9), (23,10,10), (26,11,10), (29,12,10), (33,13,10), (36,14,10), (39,15,10),\r\n(42,16,10), (45,17,10), (48,18,10), (52,19,10), (55,20,10), (30,11,11), (33,12,11), (37,13,11), \r\n(40,14,11), (44,15,11), (47,16,11), (51,17,11), (55,18,11), (55,19,11), (62,20,11),(37,12,12), \r\n(41,13,12), (45,14,12), (49,15,12), (53,16,12), (57,17,12), (61,18,12), (65,19,12), (69,20,12),\r\n(45,13,13), (50,14,13), (54,15,13), (59,16,13), (63,17,13), (67,18,13), (72,19,13), (76,20,13),\r\n(55,14,14), (59,15,14), (64,16,14), (69,17,14), (74,18,14), (78,19,14), (83,20,14),(64,15,15), \r\n(70,16,15), (75,17,15), (80,18,15), (85,19,15), (90,20,15), (75,16,16), (81,17,16), (86,18,16), \r\n(92,19,16), (98,20,16), (87,17,17), (93,18,17), (99,19,17), (105,20,17),(113,19,19),(119,20,19), \r\n(127,20,20)\r\n\r\n\/*\r\nNow we can add our data. here is the number of seconds spent on the page with either the old\r\ndesign (B) or the new design (A). The 'observation' is the number of seconds spent on the\r\npage. There is a fair range but not normally distributed. Some people get to the page and \r\nsoon realise it is not relevant for them, whereas others spend a long time on it. \r\nWith this test, the first thing you do is to rank these results so the actual measurement\r\nis irrelevant. You can do a U test on just ranked data for the two groups. The 'observation'\r\nis only used to achieve the ranking\r\n*\/\r\nDECLARE @ourResults TABLE --where we hold our results\r\n  (\r\n  TheGroup CHAR(1),\r\n  Observation INT NOT NULL\r\n  );\r\nINSERT INTO @ourResults\r\n  ( Observation,TheGroup)\r\nVALUES \r\n(3,'B'),\r\n(3,'A'),\r\n(3,'B'),\r\n(9,'B'),\r\n(9,'A'),\r\n(21,'B'),\r\n(25,'B'),\r\n(37,'A'),\r\n(45,'B'),\r\n(56,'B'),\r\n(75,'A'),\r\n(88,'B'),\r\n(101,'B'),\r\n(120,'A'),\r\n(120,'A'),\r\n(156,'B'),\r\n(206,'A'),\r\n(269,'A'),\r\n(269,'B'),\r\n(291,'B'),\r\n(308,'A'),\r\n(321,'A'),\r\n(346,'A'),\r\n(400,'A'),\r\n(421,'A'),\r\n(431,'A')\r\n\r\nDECLARE @tx NUMERIC(8, 2), @nx INT, @Ntotal INT, @UValue INT;\r\n\r\n--We now rank all the observations in order regardless of The group\r\n\r\nDECLARE @ourRanking TABLE\r\n  (\r\n  TheGroup CHAR(1),\r\n  Observation INT NOT NULL,\r\n  TheRank NUMERIC(8, 2) NOT NULL\r\n  );\r\n--insert into the temporary table the ranked observations\r\nINSERT INTO @ourRanking\r\n  (TheGroup, Observation, TheRank)\r\n  SELECT TheGroup, Observation, Rank() OVER (ORDER BY [@ourResults].Observation) AS TheRank\r\n  FROM @ourResults;\r\n--and remember how many observations there were in total\r\nSELECT @Ntotal = @@RowCount; \r\n--now deal with ties to give them equal shared rank so that joint first would both be 1.5\r\n--and three tied in first would all be 2\r\nUPDATE @ourRanking\r\n  SET TheRank = [@ourRanking].TheRank + ((ties.NumberOfTies - 1.00) \/ 2)\r\n  FROM\r\n  (SELECT Count(*) AS NumberOfTies, TheRank\r\n     FROM @ourRanking\r\n     GROUP BY TheRank\r\n  ) AS ties\r\n    INNER JOIN @ourRanking\r\n      ON [@ourRanking].TheRank = ties.TheRank;\r\nSELECT * FROM @ourRanking AS ORA\r\nSELECT TOP 1 @tx = Sum(TheRank), @nx = Count(*)\r\n  FROM @ourRanking\r\n  GROUP BY TheGroup\r\n  ORDER BY Sum(TheRank) DESC;\r\n\r\n--Now we can simply calculate the Mann Whitney U value\r\nSELECT @UValue = @nx * (@Ntotal - @nx) + @nx * ((@nx + 1) \/ 2.00) - @tx;\r\nIF @Ntotal - @nx<=20 AND @Ntotal - @nx<=20\r\n  --and see if it is significant...\r\n  SELECT 'The U score was '+CONVERT(varchar(5),@UValue)\r\n  +' from sample sizes of '+CONVERT(varchar(5),@nx)+' and '\r\n  + Convert(varchar(5),@Ntotal - @nx)\r\n  +' observations, the critical U score for 5% significance level was '\r\n  +CONVERT(varchar(5),CriticalValue)+', so the samples were'\r\n  + CASE WHEN CriticalValue<@UValue THEN ' NOT' ELSE '' END\r\n  +' from significantly different populations'\r\n  FROM (\r\n    SELECT @UValue AS UValue,\r\n      CASE WHEN @Ntotal - @nx > @Ntotal \/ 2 THEN\r\n        (SELECT UValue FROM @TwoTailedCriticalValues \r\n\t\t   WHERE N1 = @nx AND N2 = @Ntotal - @nx) \r\n      ELSE\r\n       (SELECT UValue FROM @TwoTailedCriticalValues \r\n\t       WHERE N1 = @Ntotal - @nx AND N2 = @nx) END\r\n\t  AS CriticalValue)f;\r\n  ELSE\r\n  SELECT 'The U score was '+CONVERT(varchar(5),@UValue)\r\n  +' from sample sizes of '+CONVERT(varchar(5),@nx)+' and '\r\n  + Convert(varchar(5),@Ntotal - @nx)\r\n  +' observations. You''ll need to calculate the Z and p values' ;\t\r\n\r\n\r\n\r\n<\/pre>\n
    This, when executed in SQL Server yields this result …<\/p>\n
    The U score was 42 from sample sizes of 14 and 12 observations, the critical U score for 5% significance level was 45, so the samples were from significantly different populations<\/pre>\n
    With all this series, I’m not giving you the cut n’ paste formula for doing production stats, The aim is to provide something you can experiment with, even if it just changing the figures and trying things out to see how easy it is to get from statistics theory to practice. It is much better to do this- even work things out on paper – before you get to crank the handle of a package such as R. There is too much magic in doing that if you don’t first get a feel for what is really being done, and what the assumptions are. The real expertise of statistics is in ensuring that the design of the test is right and in choosing the right statistical method for the task. Otherwise a tool like R merely gets you to the wrong answer quicker.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Imagine you have some data. Maybe you’ve done a web redesign and you want to do some A\/B testing to see if by redesigning the page your visitors spend more time reading your content. You set up an experiment in which visitors are randomly sent to either the old or new design, and you measure……<\/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":[2,143531],"tags":[5134,43637],"coauthors":[6813],"class_list":["post-71580","post","type-post","status-publish","format-standard","hentry","category-blogs","category-t-sql-programming-sql-server","tag-sql-prompt","tag-statisticsinsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71580","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=71580"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71580\/revisions"}],"predecessor-version":[{"id":71586,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71580\/revisions\/71586"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71580"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71580"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71580"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71580"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}