在sql中帶大小寫的Div0-有解無憂

我想轉換此行代碼 sql ：

div0(COL1,COL2) * 100 AS NEW_COL,

通過在日期條件下整合一個案例：

div0(CASE WHEN COL1='2022-03-11' THEN (COL1,COL2) ELSE 0 END) AS NEW_COL

提前感謝您的幫助

uj5u.com熱心網友回復：

因為div0是一個函式，你可以嘗試在運算式中使用div0函式。CASE WHEN

(CASE WHEN COL1='2022-03-11' THEN div0(COL1,COL2) ELSE 0 END) AS NEW_COL

uj5u.com熱心網友回復：

如果目標是用作條件聚合，那么：

SELECT SUM(IFF(COLX='2022-03-11', DIV0(COL1, COL2), 0)) 
FROM tab;

uj5u.com熱心網友回復：

因此，IFF 或 CASE 和 DIV0 的邏輯似乎“作業正常”

SELECT 
    column1
    ,sum(column2) as COL1
    ,sum(column3) as COL2
    ,div0(COL1,COL2) * 100 AS NEW_COL
    ,IFF(col1='41',div0(COL1,COL2), 0) AS NEW_COL_IFF
    ,CASE WHEN col1='41' THEN div0(COL1,COL2) ELSE 0 END AS NEW_COL_CASE
FROM VALUES 
    (1, 10, 100),  
    (1, 11, 101),
    (2, 20, null),  
    (2, 21, null),
    (3, 30, 0),  
    (3, 31, 0)
GROUP BY 1
ORDER BY 1;

但評論：

我有一個錯誤，例如：不能嵌套聚合函式

是說你正在做類似的事情：

SELECT 
    column1
    ,sum(column2) as COL1
    ,sum(column3) as COL2
    ,div0(COL1,COL2) * 100 AS NEW_COL
    ,IFF(col1='41',div0(COL1,COL2), 0) AS NEW_COL_IFF
    ,count(new_col_iff) as c_new_col_iff
FROM VALUES 
    (1, 10, 100),  
    (1, 11, 101),
    (2, 20, null),  
    (2, 21, null),
    (3, 30, 0),  
    (3, 31, 0)
GROUP BY 1
ORDER BY 1;

創建：

聚合函式不能嵌套：[SUM(VALUES.COLUMN2)] 嵌套在 [COUNT(NEW_COL_IFF)] 中

because the GROUP BY is used for the SUM's but, what window logic should be used to answer the COUNT. Snowflake is saying "you are doing to much at once"

which ether means simplify, but moving the "extra layer of aggregation to a new select layer, to make the order of operations clear to the compiler:

SELECT a.*
    ,count(new_col_iff) as c_new_col_iff
FROM (
    SELECT 
        column1
        ,sum(column2) as COL1
        ,sum(column3) as COL2
        ,div0(COL1,COL2) * 100 AS NEW_COL
        ,IFF(col1='41',div0(COL1,COL2), 0) AS NEW_COL_IFF
    FROM VALUES 
        (1, 10, 100),  
        (1, 11, 101),
        (2, 20, null),  
        (2, 21, null),
        (3, 30, 0),  
        (3, 31, 0)
    GROUP BY 1
) AS a
GROUP BY 1,2,3,4,5
ORDER BY 1;

COLUMN1	COL1	COL2	NEW_COL	NEW_COL_IFF	C_NEW_COL_IFF
1	21	201	10.4477	0	1
2	41	null	null	null	0
3	61	0	0	0	1

OR be more explicit:

SELECT 
    column1
    ,sum(column2) as COL1
    ,sum(column3) as COL2
    ,div0(COL1,COL2) * 100 AS NEW_COL
    ,IFF(col1='41',div0(COL1,COL2), 0) AS NEW_COL_IFF
    ,count(new_col_iff) over () as c_new_col_iff
FROM VALUES 
    (1, 10, 100),  
    (1, 11, 101),
    (2, 20, null),  
    (2, 21, null),
    (3, 30, 0),  
    (3, 31, 0)
GROUP BY 1
ORDER BY 1;

which has different SUM result:

COLUMN1	COL1	COL2	NEW_COL	NEW_COL_IFF	C_NEW_COL_IFF
1	21	201	10.4477	0	2
2	41	null	null	null	2
3	61	0	0	0	2

but that can be made to be the same:

,count(new_col_iff) over (partition by column1) as c_new_col_iff

COLUMN1	COL1	COL2	NEW_COL	NEW_COL_IFF	C_NEW_COL_IFF
1	21	201	10.4477	0	1
2	41	null	null	null	0
3	61	0	0	0	1

So the point of all this, separate out the math you are doing, and then sort out the minimum steps needed to communicate this to the SQL engine, AND more importantly, the next person reading you SQL.

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/441842.html

標籤：sql 案子雪花云数据平台

上一篇：可以合并UNION查詢中重復的WHERE子句嗎？

下一篇：查詢Oracle資料庫中的重復行