SQL Server LOGITPROB Function
Updated 2023-11-01 20:52:29.500000
Description
Use theaggregate function LOGITPROB to calculate the probability that Y = 1 given a set of coefficients from a logistic regression and a set of x-values. The probability is estimated as:
p = \hat{\pi} = \frac{e^{\beta_0 + \beta_1x_1+...+\beta_nx_n}}{1 + e^{\beta_0 + \beta_1x_1 + ... +\beta_nx_n}
The coefficients and x-values are passed into the function as pairs, which requires passing a 1 (for the intercept) into the function for ß0 coefficient.
Syntax
SELECT [westclintech].[wct].[LOGITPROB] (
<@B, float,>
,<@x, float,>)
Arguments
@B
the coefficients from a logit regression. @B must be of the type float or of a type that implicitly converts to float.
@x
the x-value associated with coefficient. @x should be consistent with the independent variables used in the logit regression. @x must be of the type float or of a type that implicitly converts to float.
Return Type
float
Remarks
You should pass a 1 with the ß0 coefficient.
Examples
We will run a logistic regression on the following data and then compare the observed y-value to the predicted y-value using the LOGITPROB function. We will use the LOGITSUM function to calculate the coefficients.
| Temp | Water | Male | Female |
|---|---|---|---|
| 20 | 0 | 21 | 0 |
| 21 | 0 | 90 | 6 |
| 22 | 0 | 91 | 23 |
| 23 | 0 | 61 | 73 |
| 24 | 0 | 11 | 41 |
| 25 | 0 | 4 | 28 |
| 20 | 1 | 18 | 4 |
| 21 | 1 | 75 | 9 |
| 22 | 1 | 68 | 21 |
| 23 | 1 | 59 | 65 |
| 24 | 1 | 17 | 46 |
| 25 | 1 | 7 | 22 |
--Put the data into a table
SELECT IDENTITY(INT, 1, 1) as rn,
*,
male / CAST(male + female as float) as y_obs
INTO #t
FROM ( VALUES (20, 0, 21, 0),
(21, 0, 90, 6),
(22, 0, 91, 23),
(23, 0, 61, 73),
(24, 0, 11, 41),
(25, 0, 4, 28),
(20, 1, 18, 4),
(21, 1, 75, 9),
(22, 1, 68, 21),
(23, 1, 59, 65),
(24, 1, 17, 46),
(25, 1, 7, 22)) n (temp, water, male, female);
--Perform the regression
SELECT *
INTO #coef
FROM wct.LOGITSUM('SELECT temp,water,male,female FROM #T', 3, 4);
--Put the new x-values into 3rd normal form
SELECT #t.rn,
n.idx,
n.x
INTO #newx
FROM #t
CROSS APPLY ( VALUES (0, 1),
(1, temp),
(2, water)) n (idx, x);
--Calculate the predicted y-values and compare
--to the observed y-values
SELECT t.temp,
t.water,
t.y_obs,
wct.LOGITPROB(a.stat_val, b.x) as y_pred
FROM #newx b
JOIN #coef a
ON b.idx = a.idx
JOIN #t t
ON b.rn = t.rn
WHERE a.stat_name = 'b'
GROUP BY b.rn,
t.temp,
t.water,
t.y_obs
ORDER BY b.rn;
This produces the following result.
| temp | water | y_obs | y_pred |
|---|---|---|---|
| 20 | 0 | 1 | 0.962666164888472 |
| 21 | 0 | 0.9375 | 0.898550857816897 |
| 22 | 0 | 0.798245614035088 | 0.752621841432599 |
| 23 | 0 | 0.455223880597015 | 0.511014157141803 |
| 24 | 0 | 0.211538461538462 | 0.264148436577182 |
| 25 | 0 | 0.125 | 0.109769453268386 |
| 20 | 1 | 0.818181818181818 | 0.961211464524059 |
| 21 | 1 | 0.892857142857143 | 0.89487074873382 |
| 22 | 1 | 0.764044943820225 | 0.745149498986974 |
| 23 | 1 | 0.475806451612903 | 0.501081697117896 |
| 24 | 1 | 0.26984126984127 | 0.25649731606484 |
| 25 | 1 | 0.241379310344828 | 0.105946142147757 |
Using the data from Example #2 in the LOGIT documentation as input into a table called #mydata, we calculate the coefficients using the following the SQL which stores the results in table called #mylogit.
SELECT *
INTO #mydata
FROM ( VALUES (1, 0, 380, 3.61, 3),
(2, 1, 660, 3.67, 3),
(3, 1, 800, 4, 1),
(4, 1, 640, 3.19, 4),
(5, 0, 520, 2.93, 4),
(6, 1, 760, 3, 2),
(7, 1, 560, 2.98, 1),
(8, 0, 400, 3.08, 2),
(9, 1, 540, 3.39, 3),
(10, 0, 700, 3.92, 2),
(11, 0, 800, 4, 4),
(12, 0, 440, 3.22, 1),
(13, 1, 760, 4, 1),
(14, 0, 700, 3.08, 2),
(15, 1, 700, 4, 1),
(16, 0, 480, 3.44, 3),
(17, 0, 780, 3.87, 4),
(18, 0, 360, 2.56, 3),
(19, 0, 800, 3.75, 2),
(20, 1, 540, 3.81, 1),
(21, 0, 500, 3.17, 3),
(22, 1, 660, 3.63, 2),
(23, 0, 600, 2.82, 4),
(24, 0, 680, 3.19, 4),
(25, 1, 760, 3.35, 2),
(26, 1, 800, 3.66, 1),
(27, 1, 620, 3.61, 1),
(28, 1, 520, 3.74, 4),
(29, 1, 780, 3.22, 2),
(30, 0, 520, 3.29, 1),
(31, 0, 540, 3.78, 4),
(32, 0, 760, 3.35, 3),
(33, 0, 600, 3.4, 3),
(34, 1, 800, 4, 3),
(35, 0, 360, 3.14, 1),
(36, 0, 400, 3.05, 2),
(37, 0, 580, 3.25, 1),
(38, 0, 520, 2.9, 3),
(39, 1, 500, 3.13, 2),
(40, 1, 520, 2.68, 3),
(41, 0, 560, 2.42, 2),
(42, 1, 580, 3.32, 2),
(43, 1, 600, 3.15, 2),
(44, 0, 500, 3.31, 3),
(45, 0, 700, 2.94, 2),
(46, 1, 460, 3.45, 3),
(47, 1, 580, 3.46, 2),
(48, 0, 500, 2.97, 4),
(49, 0, 440, 2.48, 4),
(50, 0, 400, 3.35, 3),
(51, 0, 640, 3.86, 3),
(52, 0, 440, 3.13, 4),
(53, 0, 740, 3.37, 4),
(54, 1, 680, 3.27, 2),
(55, 0, 660, 3.34, 3),
(56, 1, 740, 4, 3),
(57, 0, 560, 3.19, 3),
(58, 0, 380, 2.94, 3),
(59, 0, 400, 3.65, 2),
(60, 0, 600, 2.82, 4),
(61, 1, 620, 3.18, 2),
(62, 0, 560, 3.32, 4),
(63, 0, 640, 3.67, 3),
(64, 1, 680, 3.85, 3),
(65, 0, 580, 4, 3),
(66, 0, 600, 3.59, 2),
(67, 0, 740, 3.62, 4),
(68, 0, 620, 3.3, 1),
(69, 0, 580, 3.69, 1),
(70, 0, 800, 3.73, 1),
(71, 0, 640, 4, 3),
(72, 0, 300, 2.92, 4),
(73, 0, 480, 3.39, 4),
(74, 0, 580, 4, 2),
(75, 0, 720, 3.45, 4),
(76, 0, 720, 4, 3),
(77, 0, 560, 3.36, 3),
(78, 1, 800, 4, 3),
(79, 0, 540, 3.12, 1),
(80, 1, 620, 4, 1),
(81, 0, 700, 2.9, 4),
(82, 0, 620, 3.07, 2),
(83, 0, 500, 2.71, 2),
(84, 0, 380, 2.91, 4),
(85, 1, 500, 3.6, 3),
(86, 0, 520, 2.98, 2),
(87, 0, 600, 3.32, 2),
(88, 0, 600, 3.48, 2),
(89, 0, 700, 3.28, 1),
(90, 1, 660, 4, 2),
(91, 0, 700, 3.83, 2),
(92, 1, 720, 3.64, 1),
(93, 0, 800, 3.9, 2),
(94, 0, 580, 2.93, 2),
(95, 1, 660, 3.44, 2),
(96, 0, 660, 3.33, 2),
(97, 0, 640, 3.52, 4),
(98, 0, 480, 3.57, 2),
(99, 0, 700, 2.88, 2),
(100, 0, 400, 3.31, 3),
(101, 0, 340, 3.15, 3),
(102, 0, 580, 3.57, 3),
(103, 0, 380, 3.33, 4),
(104, 0, 540, 3.94, 3),
(105, 1, 660, 3.95, 2),
(106, 1, 740, 2.97, 2),
(107, 1, 700, 3.56, 1),
(108, 0, 480, 3.13, 2),
(109, 0, 400, 2.93, 3),
(110, 0, 480, 3.45, 2),
(111, 0, 680, 3.08, 4),
(112, 0, 420, 3.41, 4),
(113, 0, 360, 3, 3),
(114, 0, 600, 3.22, 1),
(115, 0, 720, 3.84, 3),
(116, 0, 620, 3.99, 3),
(117, 1, 440, 3.45, 2),
(118, 0, 700, 3.72, 2),
(119, 1, 800, 3.7, 1),
(120, 0, 340, 2.92, 3),
(121, 1, 520, 3.74, 2),
(122, 1, 480, 2.67, 2),
(123, 0, 520, 2.85, 3),
(124, 0, 500, 2.98, 3),
(125, 0, 720, 3.88, 3),
(126, 0, 540, 3.38, 4),
(127, 1, 600, 3.54, 1),
(128, 0, 740, 3.74, 4),
(129, 0, 540, 3.19, 2),
(130, 0, 460, 3.15, 4),
(131, 1, 620, 3.17, 2),
(132, 0, 640, 2.79, 2),
(133, 0, 580, 3.4, 2),
(134, 0, 500, 3.08, 3),
(135, 0, 560, 2.95, 2),
(136, 0, 500, 3.57, 3),
(137, 0, 560, 3.33, 4),
(138, 0, 700, 4, 3),
(139, 0, 620, 3.4, 2),
(140, 1, 600, 3.58, 1),
(141, 0, 640, 3.93, 2),
(142, 1, 700, 3.52, 4),
(143, 0, 620, 3.94, 4),
(144, 0, 580, 3.4, 3),
(145, 0, 580, 3.4, 4),
(146, 0, 380, 3.43, 3),
(147, 0, 480, 3.4, 2),
(148, 0, 560, 2.71, 3),
(149, 1, 480, 2.91, 1),
(150, 0, 740, 3.31, 1),
(151, 1, 800, 3.74, 1),
(152, 0, 400, 3.38, 2),
(153, 1, 640, 3.94, 2),
(154, 0, 580, 3.46, 3),
(155, 0, 620, 3.69, 3),
(156, 1, 580, 2.86, 4),
(157, 0, 560, 2.52, 2),
(158, 1, 480, 3.58, 1),
(159, 0, 660, 3.49, 2),
(160, 0, 700, 3.82, 3),
(161, 0, 600, 3.13, 2),
(162, 0, 640, 3.5, 2),
(163, 1, 700, 3.56, 2),
(164, 0, 520, 2.73, 2),
(165, 0, 580, 3.3, 2),
(166, 0, 700, 4, 1),
(167, 0, 440, 3.24, 4),
(168, 0, 720, 3.77, 3),
(169, 0, 500, 4, 3),
(170, 0, 600, 3.62, 3),
(171, 0, 400, 3.51, 3),
(172, 0, 540, 2.81, 3),
(173, 0, 680, 3.48, 3),
(174, 1, 800, 3.43, 2),
(175, 0, 500, 3.53, 4),
(176, 1, 620, 3.37, 2),
(177, 0, 520, 2.62, 2),
(178, 1, 620, 3.23, 3),
(179, 0, 620, 3.33, 3),
(180, 0, 300, 3.01, 3),
(181, 0, 620, 3.78, 3),
(182, 0, 500, 3.88, 4),
(183, 0, 700, 4, 2),
(184, 1, 540, 3.84, 2),
(185, 0, 500, 2.79, 4),
(186, 0, 800, 3.6, 2),
(187, 0, 560, 3.61, 3),
(188, 0, 580, 2.88, 2),
(189, 0, 560, 3.07, 2),
(190, 0, 500, 3.35, 2),
(191, 1, 640, 2.94, 2),
(192, 0, 800, 3.54, 3),
(193, 0, 640, 3.76, 3),
(194, 0, 380, 3.59, 4),
(195, 1, 600, 3.47, 2),
(196, 0, 560, 3.59, 2),
(197, 0, 660, 3.07, 3),
(198, 1, 400, 3.23, 4),
(199, 0, 600, 3.63, 3),
(200, 0, 580, 3.77, 4),
(201, 0, 800, 3.31, 3),
(202, 1, 580, 3.2, 2),
(203, 1, 700, 4, 1),
(204, 0, 420, 3.92, 4),
(205, 1, 600, 3.89, 1),
(206, 1, 780, 3.8, 3),
(207, 0, 740, 3.54, 1),
(208, 1, 640, 3.63, 1),
(209, 0, 540, 3.16, 3),
(210, 0, 580, 3.5, 2),
(211, 0, 740, 3.34, 4),
(212, 0, 580, 3.02, 2),
(213, 0, 460, 2.87, 2),
(214, 0, 640, 3.38, 3),
(215, 1, 600, 3.56, 2),
(216, 1, 660, 2.91, 3),
(217, 0, 340, 2.9, 1),
(218, 1, 460, 3.64, 1),
(219, 0, 460, 2.98, 1),
(220, 1, 560, 3.59, 2),
(221, 0, 540, 3.28, 3),
(222, 0, 680, 3.99, 3),
(223, 1, 480, 3.02, 1),
(224, 0, 800, 3.47, 3),
(225, 0, 800, 2.9, 2),
(226, 1, 720, 3.5, 3),
(227, 0, 620, 3.58, 2),
(228, 0, 540, 3.02, 4),
(229, 0, 480, 3.43, 2),
(230, 1, 720, 3.42, 2),
(231, 0, 580, 3.29, 4),
(232, 0, 600, 3.28, 3),
(233, 0, 380, 3.38, 2),
(234, 0, 420, 2.67, 3),
(235, 1, 800, 3.53, 1),
(236, 0, 620, 3.05, 2),
(237, 1, 660, 3.49, 2),
(238, 0, 480, 4, 2),
(239, 0, 500, 2.86, 4),
(240, 0, 700, 3.45, 3),
(241, 0, 440, 2.76, 2),
(242, 1, 520, 3.81, 1),
(243, 1, 680, 2.96, 3),
(244, 0, 620, 3.22, 2),
(245, 0, 540, 3.04, 1),
(246, 0, 800, 3.91, 3),
(247, 0, 680, 3.34, 2),
(248, 0, 440, 3.17, 2),
(249, 0, 680, 3.64, 3),
(250, 0, 640, 3.73, 3),
(251, 0, 660, 3.31, 4),
(252, 0, 620, 3.21, 4),
(253, 1, 520, 4, 2),
(254, 1, 540, 3.55, 4),
(255, 1, 740, 3.52, 4),
(256, 0, 640, 3.35, 3),
(257, 1, 520, 3.3, 2),
(258, 1, 620, 3.95, 3),
(259, 0, 520, 3.51, 2),
(260, 0, 640, 3.81, 2),
(261, 0, 680, 3.11, 2),
(262, 0, 440, 3.15, 2),
(263, 1, 520, 3.19, 3),
(264, 1, 620, 3.95, 3),
(265, 1, 520, 3.9, 3),
(266, 0, 380, 3.34, 3),
(267, 0, 560, 3.24, 4),
(268, 1, 600, 3.64, 3),
(269, 1, 680, 3.46, 2),
(270, 0, 500, 2.81, 3),
(271, 1, 640, 3.95, 2),
(272, 0, 540, 3.33, 3),
(273, 1, 680, 3.67, 2),
(274, 0, 660, 3.32, 1),
(275, 0, 520, 3.12, 2),
(276, 1, 600, 2.98, 2),
(277, 0, 460, 3.77, 3),
(278, 1, 580, 3.58, 1),
(279, 1, 680, 3, 4),
(280, 1, 660, 3.14, 2),
(281, 0, 660, 3.94, 2),
(282, 0, 360, 3.27, 3),
(283, 0, 660, 3.45, 4),
(284, 0, 520, 3.1, 4),
(285, 1, 440, 3.39, 2),
(286, 0, 600, 3.31, 4),
(287, 1, 800, 3.22, 1),
(288, 1, 660, 3.7, 4),
(289, 0, 800, 3.15, 4),
(290, 0, 420, 2.26, 4),
(291, 1, 620, 3.45, 2),
(292, 0, 800, 2.78, 2),
(293, 0, 680, 3.7, 2),
(294, 0, 800, 3.97, 1),
(295, 0, 480, 2.55, 1),
(296, 0, 520, 3.25, 3),
(297, 0, 560, 3.16, 1),
(298, 0, 460, 3.07, 2),
(299, 0, 540, 3.5, 2),
(300, 0, 720, 3.4, 3),
(301, 0, 640, 3.3, 2),
(302, 1, 660, 3.6, 3),
(303, 1, 400, 3.15, 2),
(304, 1, 680, 3.98, 2),
(305, 0, 220, 2.83, 3),
(306, 0, 580, 3.46, 4),
(307, 1, 540, 3.17, 1),
(308, 0, 580, 3.51, 2),
(309, 0, 540, 3.13, 2),
(310, 0, 440, 2.98, 3),
(311, 0, 560, 4, 3),
(312, 0, 660, 3.67, 2),
(313, 0, 660, 3.77, 3),
(314, 1, 520, 3.65, 4),
(315, 0, 540, 3.46, 4),
(316, 1, 300, 2.84, 2),
(317, 1, 340, 3, 2),
(318, 1, 780, 3.63, 4),
(319, 1, 480, 3.71, 4),
(320, 0, 540, 3.28, 1),
(321, 0, 460, 3.14, 3),
(322, 0, 460, 3.58, 2),
(323, 0, 500, 3.01, 4),
(324, 0, 420, 2.69, 2),
(325, 0, 520, 2.7, 3),
(326, 0, 680, 3.9, 1),
(327, 0, 680, 3.31, 2),
(328, 1, 560, 3.48, 2),
(329, 0, 580, 3.34, 2),
(330, 0, 500, 2.93, 4),
(331, 0, 740, 4, 3),
(332, 0, 660, 3.59, 3),
(333, 0, 420, 2.96, 1),
(334, 0, 560, 3.43, 3),
(335, 1, 460, 3.64, 3),
(336, 1, 620, 3.71, 1),
(337, 0, 520, 3.15, 3),
(338, 0, 620, 3.09, 4),
(339, 0, 540, 3.2, 1),
(340, 1, 660, 3.47, 3),
(341, 0, 500, 3.23, 4),
(342, 1, 560, 2.65, 3),
(343, 0, 500, 3.95, 4),
(344, 0, 580, 3.06, 2),
(345, 0, 520, 3.35, 3),
(346, 0, 500, 3.03, 3),
(347, 0, 600, 3.35, 2),
(348, 0, 580, 3.8, 2),
(349, 0, 400, 3.36, 2),
(350, 0, 620, 2.85, 2),
(351, 1, 780, 4, 2),
(352, 0, 620, 3.43, 3),
(353, 1, 580, 3.12, 3),
(354, 0, 700, 3.52, 2),
(355, 1, 540, 3.78, 2),
(356, 1, 760, 2.81, 1),
(357, 0, 700, 3.27, 2),
(358, 0, 720, 3.31, 1),
(359, 1, 560, 3.69, 3),
(360, 0, 720, 3.94, 3),
(361, 1, 520, 4, 1),
(362, 1, 540, 3.49, 1),
(363, 0, 680, 3.14, 2),
(364, 0, 460, 3.44, 2),
(365, 1, 560, 3.36, 1),
(366, 0, 480, 2.78, 3),
(367, 0, 460, 2.93, 3),
(368, 0, 620, 3.63, 3),
(369, 0, 580, 4, 1),
(370, 0, 800, 3.89, 2),
(371, 1, 540, 3.77, 2),
(372, 1, 680, 3.76, 3),
(373, 1, 680, 2.42, 1),
(374, 1, 620, 3.37, 1),
(375, 0, 560, 3.78, 2),
(376, 0, 560, 3.49, 4),
(377, 0, 620, 3.63, 2),
(378, 1, 800, 4, 2),
(379, 0, 640, 3.12, 3),
(380, 0, 540, 2.7, 2),
(381, 0, 700, 3.65, 2),
(382, 1, 540, 3.49, 2),
(383, 0, 540, 3.51, 2),
(384, 0, 660, 4, 1),
(385, 1, 480, 2.62, 2),
(386, 0, 420, 3.02, 1),
(387, 1, 740, 3.86, 2),
(388, 0, 580, 3.36, 2),
(389, 0, 640, 3.17, 2),
(390, 0, 640, 3.51, 2),
(391, 1, 800, 3.05, 2),
(392, 1, 660, 3.88, 2),
(393, 1, 600, 3.38, 3),
(394, 1, 620, 3.75, 2),
(395, 1, 460, 3.99, 3),
(396, 0, 620, 4, 2),
(397, 0, 560, 3.04, 3),
(398, 0, 460, 2.63, 2),
(399, 0, 700, 3.65, 2),
(400, 0, 600, 3.89, 3)) n (rn, admit, gre, gpa, [rank]);
SELECT *
INTO #mylogit
FROM wct.LOGIT(
'SELECT
admit
,gre
,gpa
,CASE RANK
WHEN 2 THEN 1
ELSE 0
END
,CASE RANK
WHEN 3 THEN 1
ELSE 0
END
,CASE RANK
WHEN 4 THEN 1
ELSE 0
END
FROM
#mydata',
1);
Remember that gre and gpa as treated as continuous data while rank has been treated as discrete data. The possible values for rank are 1, 2, 3, 4.
We calculate the predicted probability of admission at each value of rank by holding gre and gpa at their means.
SELECT ROUND(n.gre, 0) as gre,
ROUND(n.gpa, 2) as gpa,
n.rank,
ROUND(wct.LOGITPROB(b.stat_val, x.x), 3) as y_pred
FROM ( SELECT AVG(cast(gre as float)) as gre,
AVG(cast(gpa as float)) as gpa,
k.SeriesValue as [rank],
CASE k.SeriesValue
WHEN 2 THEN 1
ELSE 0 END as rank2,
CASE k.SeriesValue
WHEN 3 THEN 1
ELSE 0 END as rank3,
CASE k.SeriesValue
WHEN 4 THEN 1
ELSE 0 END as rank4
FROM #mydata
CROSS APPLY wct.SeriesInt(1, 4, NULL, NULL, NULL) k
GROUP BY k.SeriesValue) n
CROSS APPLY ( VALUES (0, 1),
(1, gre),
(2, gpa),
(3, rank2),
(4, rank3),
(5, rank4)) x (idx, x)
JOIN #mylogit b
ON x.idx = b.idx
WHERE b.stat_name = 'b'
GROUP BY n.gre,
n.gpa,
n.rank;
This produces the following result.
| gre | gpa | rank | y_pred |
|---|---|---|---|
| 588 | 3.39 | 1 | 0.517 |
| 588 | 3.39 | 2 | 0.352 |
| 588 | 3.39 | 3 | 0.219 |
| 588 | 3.39 | 4 | 0.185 |
We can see from the above output that the predicted probability of success is 0.517 when the rank is 1 and 0.185 when the rank is 4 holding gre and gpa at their means.
See Also
LINEST - the Ordinary Least Squares (OLS) solution for a series of x-values and y-values
LOGEST - Logarithmic regression
LOGITPRED - Calculate predicted values based on a logit regression