时间戳

首页 > 解决方案 > Windows 函数内除以 0 错误

tsql - Windows 函数内除以 0 错误

问题描述

我正在尝试使用 Windows 函数计算已结索赔的百分比除以每月总索赔。但是有些月份的索赔总额为零，这会导致该Divide by zero error encountered消息。

我尝试编写 CASE 语句来处理总索赔为零的情况，将百分比设置为零，但到目前为止我还没有运气。

下面是我所期待的截图：

在此处输入图像描述

下面的 TSQL 是我在处理除以零消息时出错的尝试：我在分隔符的底部添加了一个 NULLIF，并在底部分隔符为零时尝试在 CASE 语句中设置 value = 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;

标签： tsqlsql-server-2016

解决方案

这是一个工作选项。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;

推荐阅读