英文:
Scanning sum() of Decimal type with Go, sqlx, ClickHouse
问题
我在ClickHouse表中有一个类型为Decimal(38, 3)的"Duration"字段。
从我的Golang服务中，我发送了一个查询以获取它的SUM()，但是我无法将结果扫描回来。我尝试使用int、uint、float、sql.NullFloat64、结构体类型、逐行扫描、整个结构体、数组、带有数组的结构体，使用sqlx.Query、sqlx.Select等等，但都没有成功。
rows, _ := s.db.Query("SELECT sum(Duration) AS duration FROM mytable")
for rows.Next() {
var count sql.NullFloat64
log.Println(rows.Scan(&count))
log.Printf("count: %v\n", count)
}
我总是得到这种类型的错误：
sql: 在列索引0上扫描错误，名称"duration"：将driver.Value类型[]uint8 ("\x04!\xdf\xdc\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00")转换为float64：无效的语法
在检查一些行信息时，我得到了以下结果：
log.Printf("db类型名称：%v\n", t.DatabaseTypeName())
log.Printf("db扫描类型：%v\n", t.ScanType())
>>>
db类型名称：Decimal(38, 3)
db扫描类型：[]uint8
对于其他没有聚合函数的Decimal类型的SELECT语句，我使用了float32/64，但是这个查询就是不起作用。
在控制台中直接运行查询，我得到了一个预期的单个值：3702.5
有什么想法吗？
--- 更新1 ---
我设法将其扫描到一个字节数组中，但我不知道如何将其转换为客户端显示的数字：3708199.5
count := []byte{}
rows.Scan(&count)
log.Printf("count: %v\n", count)
>>>
count: [76 162 6 221 0 0 0 0 0 0 0 0 0 0 0 0]
英文:
I have a "Duration" field of type Decimal(38, 3) in a ClickHouse table.
From my Golang service I'm sending a query to get the SUM() of it but I just can't scan the result back. I tried using int, uint, float, sql.NullFloat64, a struct type, scanning row by row, the whole struct, arrays, structs with arrays, using sqlx.Query, sqlx.Select and nothing worked.
rows, _ := s.db.Query("SELECT sum(Duration) AS duration FROM mytable")
for rows.Next() {
var count sql.NullFloat64
log.Println(rows.Scan(&count))
log.Printf("count: %v\n", count)
}
I always get back this kind of errors:
sql: Scan error on column index 0, name "duration": converting driver.Value type []uint8 ("\x04!\xdf\xdc\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00") to a float64: invalid syntax
While checking some of the rows info I get the following:
log.Printf("db type name: %v\n", t.DatabaseTypeName())
log.Printf("db scan type: %v\n", t.ScanType())
>>>
db type name: Decimal(38, 3)
db scan type: []uint8
For other SELECT statements of Decimal types without aggregation functions I used float32/64 but this one just refuses to work.
Running the query directly in the console I get a single value as expected: 3702.5
Any ideas?
--- UPDATE 1 ---
I manage to scan it into a byte array but I don't know how to transform it into the number shown in the client: 3708199.5
count := []byte{}
rows.Scan(&count)
log.Printf("count: %v\n", count)
>>>
count: [76 162 6 221 0 0 0 0 0 0 0 0 0 0 0 0]
答案1
得分: 1
从v1迁移到v2版本的库后，sum()函数现在返回一个字符串，但由于目标是一个浮点数，所以我将值转换为已知类型以修复它。
rows, _ := s.db.Query("SELECT toDecimal64(sum(Duration), 2) AS duration FROM mytable")
迁移后的代码如上所示。
英文:
After migration from v1 to v2 of the library, the sum() returns now an string, but it still fails because the destination is a float, so I cast the value to a known type to fix it.
rows, _ := s.db.Query("SELECT toDecimal64(sum(Duration), 2) AS duration FROM mytable")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论