我的数据结构如下:
Timestamp | Hour | Count
--------------------------
20190801 01 | 1 | 10
20190801 02 | 2 | 20
20190801 03 | 3 | 10
20190801 04 | 4 | 5
20190801 05 | 5 | 15
20190801 06 | 6 | 10
20190802 01 | 1 | 5
20190802 02 | 2 | 20
20190802 03 | 3 | 5
20190802 04 | 4 | 15
20190802 05 | 5 | 20
20190802 06 | 6 | 5
20190803 01 | 1 | 30
我正在尝试创建一个 SQL 查询来计算正在运行的 SUM,但会在小时为 1 时重置。结果应如下所示:
Hour | Count | SUM
------------------
1 | 10 | 10
2 | 20 | 30
3 | 10 | 40
4 | 5 | 45
5 | 15 | 60
6 | 10 | 70
1 | 5 | 5 /* RESET */
2 | 20 | 25
3 | 5 | 30
4 | 15 | 45
5 | 20 | 65
6 | 5 | 70
1 | 30 | 30 /* RESET */
您可以使用sum()
解析函数:
select t."Hour","Count",
sum("Count") over (partition by substr("Timestamp",1,8) order by "Timestamp") as "sum"
from t
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)