CAST内部的postgreSQL语法错误，带有case语句

Question

我想从我的数据库中列出阳性和阴性bank_transfers。为此，我使用以下查询：

SELECT
bank_transfers.date AS day,
CAST(SUM(CASE WHEN bank_transfers.amount_cents < 0 THEN bank_transfers.amount_cents END)) AS sum_expenses,
CAST(SUM(CASE WHEN bank_transfers.amount_cents > 0 THEN bank_transfers.amount_cents END)) AS sum_revenue
FROM bank_transfers
JOIN customers ON customers.id = bank_transfers.customer_id
WHERE customers.id = 1
AND bank_transfers.date < '2020-11-26'
AND bank_transfers.date >= '2020-08-26'
GROUP BY DAY

但它给了我一个错误syntax error at or near ")"。我想它来自CAST，那么我应该把括号放在哪里？

Answer 1

您甚至不需要这些强制转换。您不会强制转换为任何类型(除非sum_expenses和sum_revenue是您的自定义类型(。

编辑：如果要强制转换为整数：

SELECT
bank_transfers.date AS day,
SUM(CASE WHEN bank_transfers.amount_cents < 0 THEN bank_transfers.amount_cents END)::integer AS sum_expenses,
SUM(CASE WHEN bank_transfers.amount_cents > 0 THEN bank_transfers.amount_cents END)::integer AS sum_revenue
FROM bank_transfers
JOIN customers ON customers.id = bank_transfers.customer_id
WHERE customers.id = 1
AND bank_transfers.date < '2020-11-26'
AND bank_transfers.date >= '2020-08-26'
GROUP BY DAY

或者使用cast(... as integer)。

Answer 2

此查询甚至不需要JOIN，因为客户id在bank_transfers表中。我还强烈推荐FILTER用于条件聚合：

SELECT bt.date AS day,
SUM(bt.amount_cents) FILTER (WHERE bt.amount_cents < 0) as sum_expenses,
SUM(bt.amount_cents) FILTER (WHERE bt.amount_cents > 0) as sum_revenue
FROM bank_transfers bt
WHERE bt.customer_id = 1 AND
bt.date >= '2020-08-26' AND
bt.date < '2020-11-26'
GROUP BY DAY;