Probability Distribution calculation in sql

Rosalina5 161 Reputation points
2022-08-29T18:58:57.547+00:00

Hello,

Is there any way to get probability in sql.

Suppose in remarks column we have more loss, and i am trying to show
if we decrease distance or change ChannelSource than there is probability
for particular TrackID to have gain instead of loss.

Pardon me if it is not possible in sql

Data:

DECLARE @MYTABLE TABLE  
(  
ID	VARCHAR (50),  
distance INT,  
ChannelSource VARCHAR (50),	  
REMARKS VARCHAR (50)  
  
)  
  
INSERT @MYTABLE  
  
SELECT 'Z1G510A',719,'ThirdParty','Loss' UNION ALL  
SELECT 'B18EAE2',91,'website',' Gain' UNION ALL  
SELECT 'E116A40',451,'ThirdParty','Loss' UNION ALL  
SELECT '91H8B6C',113,'ThirdParty','Loss' UNION ALL  
SELECT 'S1Z165F',315,'ThirdParty','Loss' UNION ALL  
SELECT 'F1D1308',983,'website','Loss' UNION ALL  
SELECT '21577CA',1052,'website','Loss' UNION ALL  
SELECT 'X1FF3A9',904,'website','Gain' UNION ALL  
SELECT 'S1V7021',621,'website','Gain'   

Thank You so Much

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,682 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 22,781 Reputation points Microsoft Vendor
    2022-08-30T02:01:32.597+00:00

    Hi @Rosalina5
    How about this query:

    DECLARE @MYTABLE TABLE  
     (  
     ID    VARCHAR (50),  
     distance INT,  
     ChannelSource VARCHAR (50),      
     REMARKS VARCHAR (50)      
     )  
     INSERT @MYTABLE      
     SELECT 'Z1G510A',719,'ThirdParty','Loss' UNION ALL  
     SELECT 'B18EAE2',91,'website',' Gain' UNION ALL  
     SELECT 'E116A40',451,'ThirdParty','Loss' UNION ALL  
     SELECT '91H8B6C',113,'ThirdParty','Loss' UNION ALL  
     SELECT 'S1Z165F',315,'ThirdParty','Loss' UNION ALL  
     SELECT 'F1D1308',983,'website','Loss' UNION ALL  
     SELECT '21577CA',1052,'website','Loss' UNION ALL  
     SELECT 'X1FF3A9',904,'website','Gain' UNION ALL  
     SELECT 'S1V7021',621,'website','Gain'   
     SELECT * FROM @MYTABLE  
      
    ;WITH CTE AS   
    (  
     SELECT *,SUM(CASE WHEN REMARKS='Loss' THEN 1 ELSE 0 END)OVER(PARTITION BY 1)*1.0/COUNT(REMARKS)OVER(PARTITION BY 1) AS Probability_of_Loss   
     FROM @MYTABLE  
    )  
    UPDATE CTE  
    SET ChannelSource='website'  
       ,REMARKS = CASE WHEN Probability_of_Loss > 0.5 THEN 'Gain' ELSE 'Loss' END   
    WHERE ID = 'Z1G510A'  
      
    SELECT * FROM @MYTABLE  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

0 additional answers

Sort by: Most helpful