在Oracle中满足条件时重置运行总和 [英] Reset running sum when condition is met in Oracle

问题描述

我的数据结构如下:

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时将重置.结果应如下所示:

I'm trying to make an SQL query that will calculate a running SUM but resets when the hour is 1. The result should look like this:

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

演示

这篇关于在Oracle中满足条件时重置运行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！