Clickhouse中按条件计算指定用户时段交易金额总和的SQL问题

我有一张存储用户交易记录的Clickhouse表，需要计算指定用户在特定时间戳区间内，各交易分类对应的金额总和，规则为：Type = 'debit'时金额加总，Type = 'credit'时金额从总和中扣除。

表结构 #

┌─name────────────┬─type───
│ User            │ String │
│ Amount          │ String │
│ Type            │ String │
│ Category        │ String │
│ Timestamp       │ Number │
└─────────────────┴────────

示例数据与预期结果 #

示例数据 #

ROW 1
User: 1234
Amount: 10
Type: 'debit'
Category: 'family'

ROW 2
User: 1234
Amount: 3
Type: 'credit'
Category: 'family'

ROW 3
User: 1234
Amount: 3
Type: 'debit'
Category: 'work'

预期结果 #

Category: 'family'
Amount: 7

Category: 'work'
Amount: 3

当前尝试的问题 #

我已经写出了时间区间过滤的基础查询：

select User, Amount, Timestamp from transactions t
where t.User = '1234' AND t.Timestamp >= 1663166651 AND t.Timestamp <= 1663266651

但尝试用startsWith区分交易类型求和时出现错误：

select User, sum(if(startsWith(Type, 'debit'), -Amount, Amount)) as Total, Timestamp from transactions t

错误信息：

DB::Exception: Illegal type String of argument of function negate: While processing if(startsWith(Type 'debit')

解决方案 #

问题原因 #

错误核心是**Amount字段为String类型，无法直接进行数值运算**。Clickhouse不支持对字符串执行加减、取反操作，必须先转换为数值类型（如Decimal或Float64）。

正确SQL实现 #

结合需求完成以下步骤：

1. 将Amount转换为数值类型（推荐用Decimal避免精度丢失）
2. 根据Type判断金额正负：debit取正，credit取负
3. 按Category分组求和
4. 添加用户和时间区间过滤条件

最终SQL：

SELECT
    Category,
    SUM(
        CASE
            WHEN Type = 'debit' THEN toDecimal64(Amount, 2)
            WHEN Type = 'credit' THEN -toDecimal64(Amount, 2)
            ELSE 0
        END
    ) AS TotalAmount
FROM transactions
WHERE
    User = '1234'
    AND Timestamp >= 1663166651
    AND Timestamp <= 1663266651
GROUP BY Category

说明 #

• toDecimal64(Amount, 2)将字符串金额转换为保留两位小数的Decimal类型，适配金额计算场景
• 用CASE WHEN替代if（两者功能等价，但CASE逻辑更清晰），根据交易类型设置金额正负
• 按Category分组，对转换后的金额求和
• 保留用户和时间区间过滤条件，确保只计算目标范围内的交易

内容的提问来源于stack exchange，提问作者Chan Jing Hong