我有一张表，内容示例如下: | 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，不过好像也存不下，这种数据怎么处理比较好呢？