使用SQL查询计算从一条记录到下一条记录的变化百分比 [英] Calculating percent change from one record to next using a SQL query

问题描述

我正在使用此查询来计算从当前学期到上学期的总注册人数变化百分比.我需要对此查询进行哪些更改以避免空值? 我尝试使用非常慢的子查询.

I am using this query to calculate percent change of Enrollment total from current semester to the last. What changes do I need to make to this query to avoid nulls ? I have tried using a sub query which is painfully slow.

SELECT E2.Term AS Prev_Term, 
       E2.[Enrollment Total] AS Prev_Enrl_Tot, 
       E1.Term,
       E1.Location, 
       E1.milestone_description, 
       E1.polling_date,
       Round(((E1.[Enrollment Total] - [Prev_Enrl_Tot]) 
                                   / [Prev_Enrl_Tot]) * 100,2) AS Percent_Change
FROM EnrollmentsByLocation E1 
LEFT OUTER JOIN EnrollmentsByLocation E2
ON E1.Location = E2.Location 
AND E1.milestone_description = E2.milestone_description 
AND E1.Term - E2.Term = 1;

推荐答案

SQL有什么风味?避免空值是什么意思?您是否在尝试避免被零除错误或不显示空值?使用ISNULL，但将NULLIF包裹在除数周围，以避免除以零误差.这两者都可以处理. 还是您要消除他们返回的机会?如果是这样，请查看您的LEFT联接或WHERE子句. 您需要将别名放置在[Prev_Enrl_Tot]上.

What flavor SQL? What do you mean by avoid nulls? Are you trying to avoid divide by zero error, or not display nulls? Use ISNULL, but wrap NULLIF around the divisor to avoid divide by zero error. This handles both. Or are you trying to eliminate them from returning? If so, look at your LEFT join or a WHERE clause. You'll need to place your aliases on [Prev_Enrl_Tot].

 ISNULL(Round(((E1.[Enrollment Total] - [Prev_Enrl_Tot]) / NULLIF([Prev_Enrl_Tot],0)) * 100,2),0) AS Percent_Change

这篇关于使用SQL查询计算从一条记录到下一条记录的变化百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！