我有一张表,内容示例如下: | token | from_address | to_address | time | value | | ---- | ---- | ---- | ---- | ---- | | A | a_xxx | b_xxx | 2022-01-01 01:00:00 | 1000 | | A | a_xxx | b_xxx | 2022-01-01 02:00:00 | 3000 | | A | b_xxx | c_xxx | 2022-01-01 02:00:00 | 2000 | | A | a_xxx | b_xxx | 2022-01-03 03:00:00 | 3000 | | B | b_xxx | c_xxx | 2022-01-02 02:00:00 | 2000 |
请问该如何将表数据通过group by(token,from_address,toDate(time)),然后sum(value),这里的sum是从有from_address有token开始,累加到当天的数据,并填充日期数据变成如下:
| token | from_address | date | value | | ---- | ---- | ---- | ---- | | A | a_xxx | 2022-01-01 | 4000 | | A | a_xxx | 2022-01-02 | 4000 | | A | a_xxx | 2022-01-03 | 7000 | | A | b_xxx | 2022-01-01 | 2000 | | A | b_xxx | 2022-01-02 | 2000 | | A | b_xxx | 2022-01-03 | 2000 | | B | b_xxx | 2022-01-01 | 0 | | B | b_xxx | 2022-01-02 | 2000 | | B | b_xxx | 2022-01-03 | 2000 |
在pgsql中,我原来的做法是先执行下面语句,放入一张表中。
SELECT token,
SUM(value) AS inflow,
from_address AS address,
toDate(time) AS date
FROM
table
GROUP BY
token,from_address,date
然后再通过窗口函数 lastValue over 来整合成到一张表中来实现功能。
问题一: 不知道clickhouse有没有什么更好的办法能够一条语句完成呢?
问题二: value值可能80位,我看了下有unit256,不过好像也存不下,这种数据怎么处理比较好呢?