在一种情况下，当创建一个新列作为其他列的聚合时，会出现多个条件

Question

我有一个运行良好的查询：

SELECT col1,
col2,
CASE WHEN SUM(B.col3) > 0 THEN 
CASE WHEN COUNT(DISTINCT B.col4) > 1 THEN 
SUM(B.col5*1) / (SUM(B.col6*1) / (SUM(B.col7)/ COUNT(distinct B.col4))) 
ELSE SUM(B.col5*1) / (SUM(B.col6*1) / SUM(B.col7)) 
END 
END AS NewCol
FROM table A
INNER JOIN table_ B on A.col1 = B.col1_ AND  A.col2 = B.col2_
WHERE B.DATE = '2022-01-31' AND filter = 1
GROUP BY 1, 2

但我想在when的情况下移动日期条件，而不是在where子句中，因为在我的完整查询中，我计算其他协议，而不是基于这个日期

为了更容易，我想在SQL中使用CASE WHEN，比如这个

CASE WHEN DATE = '2022-01-31' AND SUM(col3) > 0 AND COUNT(DISTINCT col4) > 1 
THEN  SUM(col3)/COUNT(DISTINCT col4)
ELSE SUM(col3) END 
AS NewCol

但它不起作用，并返回以下消息：

编辑：错误的消息是SQL编译错误：不能嵌套聚合函数：[SUM(IFF((DATE=(CAST('2022-01-31'AS TIMESTAMP_NTZ(9(((AND(col2>(CAST((0 AS FLOAT(((和(COUNT(DISTINCT col3((>1(，1，0(]

当我写作时：

CASE WHEN (DATE = '2022-01-31' AND SUM(col3) > 0 AND COUNT(distinct col4) > 1) 
THEN  SUM(col3)/COUNT(distinct col4))
ELSE SUM(col3) END 
AS NewCol

我收到以下错误信息：

SQL编译错误：select子句中位置18"DATE"处的错误行3既不是聚合，也不在group-by子句中。

如何对此进行编码？

提前感谢的帮助

Answer 1

当DATE过滤器在WHERE子句中时，您只会得到符合标准的行

因此，要将其从WHERE中移出，您需要对所有SUM进行相同的筛选。

第一点是你内心的CASE语句：

CASE
WHEN COUNT(DISTINCT B.col4) > 1 THEN 
SUM(B.col5*1) / (SUM(B.col6*1) / (SUM(B.col7) / COUNT(distinct B.col4))) 
ELSE SUM(B.col5*1) / (SUM(B.col6*1) / SUM(B.col7)) 
END

可以用代替

IFF(SUM(B.col3) > 0, SUM(B.col5*1)/(SUM(B.col6*1) / (SUM(B.col7*1) / greatest(COUNT(DISTINCT B.col4), 1))), null) AS NewCol_3

但这些都需要通过日期过滤器来保护。

因此，我们创建了具有两组结果的数据，其中一组结果将在过滤器存在时被过滤掉，另一组则不会，因此我们可以检查数学是否有效：

with table_a(col1, col2, filter) as (
SELECT * FROM VALUES 
(1,10, 1),
(2,11, 1)
), table_b(col1_, col2_, date, col3, col4, col5, col6, col7) as (
SELECT * FROM VALUES 
(1, 10, '2022-01-31', 1, 99001, 100, 6, 3),
(1, 10, '2022-01-31', 1, 99002, 100, 6, 3),
(1, 10, '2022-01-30', 1, 99001, 100, 6, 3),
(2, 11, '2022-01-31', 1, 99001, 100, 6, 3),
(2, 11, '2022-01-31', 1, 99002, 100, 6, 3)
)

首先让我们展示两种形式的计算是相同的：

SELECT 
col1,
col2,

CASE 
WHEN SUM(B.col3) > 0 THEN 
CASE 
WHEN COUNT(DISTINCT B.col4) > 1 THEN 
SUM(B.col5*1) / (SUM(B.col6*1) / (SUM(B.col7) / COUNT(distinct B.col4))) 
ELSE SUM(B.col5*1) / (SUM(B.col6*1) / SUM(B.col7)) 
END 
END AS NewCol
-- ABOVE ALL ORIGNAL CODE

,SUM(B.col3) as s_col3
,COUNT(DISTINCT B.col4) as c_col4
,SUM(B.col5*1) as math_5
,SUM(B.col6*1) as math_6
,SUM(B.col7*1) as math_7
,IFF(s_col3 > 0, math_5/(math_6 / (math_7 / greatest(c_col4, 1))), null) AS NewCol_2
-- ABOVE origin decomposed into part and rewritten

FROM table_a AS A
JOIN table_b AS B 
ON A.col1 = B.col1_ AND  A.col2 = B.col2_
WHERE filter = 1 
AND B.DATE = '2022-01-31'
GROUP BY 1, 2

s_COL3C_COL4数学512><10>><10>

COL1	COL2	NEWCOL
1	10	50	2	200	6
2	11	50	2	200	12		6