SQL Server线性回归查询避免除零错误的实现方案

嘿，这个问题我之前处理过，咱们先理清楚根源，再给你几个实用的解决办法！

为啥会触发除零错误？ #

虽然你在HAVING里加了COUNT(symbol) > 1来过滤单条记录的分组，但SQL的执行顺序是先计算SELECT里的所有表达式，再应用HAVING过滤。当某个分组只有1条记录时：

• xbar就是这条记录的x值（因为avg()对单个值求平均就是它本身）
• 所以(x - xbar)等于0，sum((x-xbar)*(x-xbar))的结果自然是0
• 这时候执行除法就会直接触发除零错误，哪怕这个分组最后会被HAVING过滤掉

靠谱的解决办法 #

方法1：用NULLIF规避除零 #

NULLIF(a, b)函数会在a等于b时返回NULL，否则返回a。把分母用这个函数包裹，除法结果就会变成NULL而不是报错，之后HAVING里的条件会自动忽略NULL的情况。

修改后的完整查询：

IF OBJECT_ID('tempdb..#temp4') IS NOT NULL DROP TABLE #temp4
GO
create table #temp4 (
 id int,
 value decimal(18,8),
 symbol nvarchar(50),
 [created] datetime
)
insert into #temp4 (id, value, symbol, [created]) values
(1,0.1,'abc','2018-01-19 20:34:24'),
(1,0.2,'abc','2018-01-19 21:00:45'),
(1,0.3,'abc','2018-01-19 21:54:08'),
(2,50,'123','2018-01-19 21:00:45'),
(2,60,'123','2018-01-19 21:54:08'),
(3,40,'CJ','2018-01-19 21:36:20')

select id, symbol, 
       1.0*sum((x-xbar)*(y-ybar))/NULLIF(sum((x-xbar)*(x-xbar)), 0) as Beta
from (
 select id, symbol,
 avg(value) over(partition by id) as ybar,
 value as y,
 avg(datediff(second,'2018-01-01 20:34',[created])) over(partition by id) as xbar,
 datediff(second,'2018-01-01 20:34',[created]) as x
 from #temp4
) as Calcs
group by id, symbol
having COUNT(symbol) > 1 AND 1.0*sum((x-xbar)*(y-ybar))/NULLIF(sum((x-xbar)*(x-xbar)), 0) > 0

方法2：先过滤有效分组再计算 #

先提前筛选出记录数≥2的分组，再基于这些分组做后续计算，从根源上避免处理单条记录的分组。

用CTE实现的版本：

IF OBJECT_ID('tempdb..#temp4') IS NOT NULL DROP TABLE #temp4
GO
create table #temp4 (
 id int,
 value decimal(18,8),
 symbol nvarchar(50),
 [created] datetime
)
insert into #temp4 (id, value, symbol, [created]) values
(1,0.1,'abc','2018-01-19 20:34:24'),
(1,0.2,'abc','2018-01-19 21:00:45'),
(1,0.3,'abc','2018-01-19 21:54:08'),
(2,50,'123','2018-01-19 21:00:45'),
(2,60,'123','2018-01-19 21:54:08'),
(3,40,'CJ','2018-01-19 21:36:20')

WITH ValidGroups AS (
    SELECT id, symbol
    FROM #temp4
    GROUP BY id, symbol
    HAVING COUNT(*) > 1
)
SELECT vg.id, vg.symbol, 
       1.0*sum((x-xbar)*(y-ybar))/sum((x-xbar)*(x-xbar)) as Beta
FROM (
    SELECT t.id, t.symbol,
           avg(t.value) over(partition by t.id) as ybar,
           t.value as y,
           avg(datediff(second,'2018-01-01 20:34',t.[created])) over(partition by t.id) as xbar,
           datediff(second,'2018-01-01 20:34',t.[created]) as x
    FROM #temp4 t
    INNER JOIN ValidGroups vg ON t.id = vg.id AND t.symbol = vg.symbol
) as Calcs
GROUP BY id, symbol
HAVING 1.0*sum((x-xbar)*(y-ybar))/sum((x-xbar)*(x-xbar))>0

方法3：用CASE WHEN做条件判断 #

如果需要对单条记录的分组返回特定值（比如0或者NULL），可以用CASE WHEN手动判断分母是否为0：

select id, symbol, 
       CASE 
           WHEN sum((x-xbar)*(x-xbar)) = 0 THEN NULL -- 或者你想要的默认值，比如0
           ELSE 1.0*sum((x-xbar)*(y-ybar))/sum((x-xbar)*(x-xbar)) 
       END as Beta
from (
 select id, symbol,
 avg(value) over(partition by id) as ybar,
 value as y,
 avg(datediff(second,'2018-01-01 20:34',[created])) over(partition by id) as xbar,
 datediff(second,'2018-01-01 20:34',[created]) as x
 from #temp4
) as Calcs
group by id, symbol
having COUNT(symbol) > 1 AND Beta > 0

这三种方法都能有效避免除零错误，你可以根据自己的需求选择最适合的~

内容的提问来源于stack exchange，提问作者Rob