SQLite中CASE WHEN子句计算现金流异常问题排查

表结构与数据 #

SQLite中有一张transactions表，结构及数据如下：

需求 #

按日期计算现金流，包含in_flow和out_flow：

• in_flow：当日transaction_type为'sell'的所有交易价值之和
• out_flow：当日transaction_type为'buy'的所有交易价值之和

交易价值计算规则：

• buy类型：volume * price + commission
• sell类型：volume * price - commission

错误的查询语句 #

编写的查询语句如下：

SELECT t.date,
    CASE 
        When t.transaction_type = 'sell' THEN 
            sum(t.volume * t.price) - sum(t.commission)
    END as in_flow,
    Case
        When t.transaction_type = 'buy' Then 
            sum(t.volume * t.price) + sum(t.commission)
    End as out_flow
From 
    transactions as t
WHERE
    date = 1697846400
Group By t.date;

查询结果问题 #

结果中仅out_flow计算正确，in_flow为NULL：

尝试过把sell的判断改为LOWER(TRIM(t.transaction_type))='sell'，调整in_flow和out_flow的顺序，都没解决问题。但单独查询sell类型交易时结果正常：

Select date, sum(volume * price) - sum(commission) as in_flow
From transactions
Where transaction_type = 'sell' And date = 1697846400
Group By date;

结果：

请问我哪里出错了？

问题原因及修正 #

问题出在聚合函数和CASE语句的顺序搞反了。

原查询中，先判断transaction_type再做聚合，但按date分组后，transaction_type不是分组字段，SQLite会随机选取分组内某一行的transaction_type值触发CASE判断，导致只会计算其中一种类型的流量，另一个字段返回NULL。

正确的做法是把CASE语句嵌套在SUM函数内部，先对每行按交易类型计算对应价值，再聚合求和。

修正后的查询语句 #

SELECT 
    t.date,
    SUM(CASE WHEN t.transaction_type = 'sell' THEN (t.volume * t.price - t.commission) ELSE 0 END) AS in_flow,
    SUM(CASE WHEN t.transaction_type = 'buy' THEN (t.volume * t.price + t.commission) ELSE 0 END) AS out_flow
FROM 
    transactions AS t
WHERE
    date = 1697846400
GROUP BY 
    t.date;

执行该语句后，会正确得到in_flow=7197500、out_flow=13210541的结果。

内容的提问来源于stack exchange，提问作者Tân Lê