tsql - Windows 函数内部除以 0 错误

Question

我正在尝试使用 Windows 函数计算已结 claim 的百分比除以每月 claim 总数。但有些月份的 claim 总数将为零，这会导致出现 除以零错误 消息。

我曾尝试编写一个 CASE 语句来处理 claim 总数为零的情况，以便将百分比设置为零，但到目前为止我还没有成功。

下面是我期待的截图:

下面的 TSQL 是我在错误处理除以零消息时的尝试:我在分隔符的底部添加了一个 NULLIF，并尝试在底部分隔符为零时使用 CASE 语句设置值 = 0 - 两者都会导致相同的错误。关于如何解决此错误的任何建议？

CREATE TABLE #ClaimCounts
(
Year INT,
ClaimStatus VARCHAR (50),
LossMonth DATE,
ClaimMonth DATE,
ClaimCount INT
);

INSERT INTO #ClaimCounts
(
Year,
ClaimStatus,
LossMonth,
ClaimMonth,
ClaimCount
)
VALUES
(2008, 'Closed', '20080630', '20080131', 0),
(2008, 'Total', '20080630', '20080131', 0),
(2008, 'Closed', '20080630', '20080229', 0),
(2008, 'Total', '20080630', '20080229', 0),
(2008, 'Closed', '20080630', '20080331', 0),
(2008, 'Total', '20080630', '20080331', 0),
(2008, 'Closed', '20080630', '20080430', 0),
(2008, 'Total', '20080630', '20080430', 0),
(2008, 'Closed', '20080630', '20080531', 0),
(2008, 'Total', '20080630', '20080531', 0),
(2008, 'Closed', '20080630', '20080630', 0),
(2008, 'Total', '20080630', '20080630', 6),
(2008, 'Closed', '20080630', '20090731', 2),
(2008, 'Total', '20080630', '20090731', 5),
(2008, 'Closed', '20080630', '20080831', 1),
(2008, 'Total', '20080630', '20080831', 1),
(2008, 'Closed', '200806308', '20080930', 3),
(2008, 'Total', '20080630', '20080930', 3),
(2008, 'Closed', '20080630', '20081031', 2),
(2008, 'Total', '20080630', '20081031', 3),
(2008, 'Closed', '200806308', '20081130', 0),
(2008, 'Total', '20080630', '20081130', 0);

SELECT Year,
   ClaimStatus,
   LossMonth,
   ClaimMonth,
   ClaimCount,
   SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) / 
   SUM ( CASE WHEN ClaimStatus = 'Total' THEN ClaimCount * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) AS PercentageClosedClaims1 ,

   SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) / 
   SUM ( CASE WHEN ClaimStatus = 'Total' THEN NULLIF(ClaimCount, 0) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) AS PercentageClosedClaims2 ,

   CASE WHEN ClaimStatus = 'Total' AND ClaimCount = 0 THEN 0
       ELSE SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) / 
            SUM ( CASE WHEN ClaimStatus = 'Total' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth)
   END AS PercentageClosedClaims3
FROM #ClaimCounts;

DROP TABLE IF EXISTS #ClaimCounts;

Answer 1

这是一个可行的选项。 IsNull() 是可选的。我个人不介意 NULL 值。

SELECT Year
      ,ClaimStatus
      ,LossMonth
      ,ClaimMonth
      ,ClaimCount
      ,PercentageClosedClaims1 = IsNull(SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) 
                                        / nullif(SUM ( CASE WHEN ClaimStatus = 'Total' THEN ClaimCount * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth),0) 
                                        ,0)
      ,PercentageClosedClaims2 = IsNull(SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) 
                                        / nullif(SUM ( CASE WHEN ClaimStatus = 'Total' THEN NULLIF(ClaimCount, 0) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth),0) 
                                        ,0)
      ,PercentageClosedClaims3 = IsNull(SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) 
                                        / nullif(SUM ( CASE WHEN ClaimStatus = 'Total' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth),0)
                                        ,0)

FROM #ClaimCounts;