sql - 用 HAVING cause 和 CASE 对 GROUP BY 求和 - Postgres

Question

我已经设法找到了一个非常手动的解决方法，但我确信有一个更简单的方法可以实现我希望实现的目标。从本质上讲，我试图找出已经达到“积分”(销售额与其乘数)并达到阈值(2000)的客户有多少次赎回作为一个数字示例表:

ID      SALES    Multiplier    
10R46   1140.0   Two
10R46   1123.5   Two
100R91  1115.3   One
10R91   2.0      One
10M95   800.0    One
10M95   600.0    Two
10M95   33.0     Zero

我目前使用的代码是:

SELECT ID,
        SUM(CASE WHEN Multiplier = 'Two' THEN (trunc(sales * 2)) 
        WHEN Multiplier = 'One' THEN (trunc(sales * 1))
        ELSE 0 END) as points
FROM transactions
GROUP BY ID
HAVING SUM(CASE WHEN Multiplier = 'Two' THEN (trunc(sales * 2)) 
        WHEN Multiplier = 'One' THEN (trunc(sales * 1))
        ELSE 0 END) > 2000

我将结果放入临时表并在顶部运行截断求和

SELECT SUM(trunc(points/2000))
FROM temp_table

这给出了兑换次数的理想结果(在本例中为 3)。

所以我想请教各位大神，我该如何简化这个问题，并在没有临时表的情况下进行一个查询？

Answer 1

使用子查询的一种方式。

select sum(trunc(points/2000))
from (
SELECT ID,
        SUM(CASE WHEN Multiplier = 'Two' THEN (trunc(sales * 2)) 
        WHEN Multiplier = 'One' THEN (trunc(sales * 1))
        ELSE 0 END) as points
FROM transactions
GROUP BY ID
HAVING SUM(CASE WHEN Multiplier = 'Two' THEN (trunc(sales * 2)) 
        WHEN Multiplier = 'One' THEN (trunc(sales * 1))
        ELSE 0 END) > 2000
) t

sum 窗口函数的另一个选项。

SELECT DISTINCT
        SUM(SUM(CASE WHEN Multiplier = 'Two' THEN (trunc(sales * 2)) 
                     WHEN Multiplier = 'One' THEN (trunc(sales * 1))
                ELSE 0 END
               )/2000
           ) OVER()
FROM transactions
GROUP BY ID
HAVING SUM(CASE WHEN Multiplier = 'Two' THEN (trunc(sales * 2)) 
        WHEN Multiplier = 'One' THEN (trunc(sales * 1))
        ELSE 0 END) > 2000