如何计算无零列的AVG ?使用WITH两次?

Question

我得到了这个查询:

with T1 as (
SELECT [CaseNum]
,CreationDate
,[IsPanic]
,ISNULL(LAG(CreationDate,1)OVER(PARTITION BY [CaseNum] ORDER BY CreationDate),CreationDate) PreviousDate
FROM [EmergencyRoomMetrics].[dbo].[ERSystem_RequestsResults_LAB]
where TestDescr like '%Potassium%'
and CreationDate >= dateadd(day, -365, getdate())
)

SELECT CaseNum,
CreationDate,
IsPanic,
PreviousDate,
DATEDIFF(minute,PreviousDate,CreationDate) as LapsedSeconds

From T1

它工作得很好，给了我我想看到的表，但是我怎么能计算没有零值的LapsedSeconds列的AVG ?

的LapsedSeconds看起来像这样:

LapsedSeconds
----------------------------
0
0
0
34
34
0
89
67

我试过:

with T1 as (
SELECT [CaseNum]
,CreationDate
,[IsPanic]
,ISNULL(LAG(CreationDate,1)OVER(PARTITION BY [CaseNum] ORDER BY CreationDate),CreationDate) PreviousDate
FROM [EmergencyRoomMetrics].[dbo].[ERSystem_RequestsResults_LAB]
where TestDescr like '%Potassium%'
and CreationDate >= dateadd(day, -365, getdate())
),
T2 as(
SELECT CaseNum,
CreationDate,
IsPanic,
PreviousDate,
DATEDIFF(minute,PreviousDate,CreationDate) as LapsedSeconds

From T1
)
SELECT AVG(NULLIF(LapsedSeconds,0) as AVGE
FROM T2

不起作用。它说:'T2'附近语法不正确。(在结尾)

那么我如何计算没有零的LapsedSeconds列的AVG呢?

Answer 1

感谢评论，我看到我在末尾缺少一个括号，所以完整的答案是:

with T1 as (
SELECT [CaseNum]
,CreationDate
,[IsPanic]
,ISNULL(LAG(CreationDate,1)OVER(PARTITION BY [CaseNum] ORDER BY 
CreationDate),CreationDate) PreviousDate
FROM [EmergencyRoomMetrics].[dbo].[ERSystem_RequestsResults_LAB]
where TestDescr like '%Potassium%'
and CreationDate >= dateadd(day, -365, getdate())
),
T2 as(
SELECT CaseNum,
CreationDate,
IsPanic,
PreviousDate,
DATEDIFF(minute,PreviousDate,CreationDate) as LapsedSeconds
From T1
)
SELECT AVG(NULLIF(LapsedSeconds,0)) as AVGE
FROM T2
AVGE
-------
109

感谢所有帮助过我的人!