在Oracle中满足条件时重置总和

一尘不染

在Oracle中满足条件时重置总和

sql

我的数据结构如下：

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，但在小时数为3时将重置。结果应如下所示：

Hour | Count | SUM
------------------
   1 |    10 |  10
   2 |    20 |  30
   3 |    10 |  10    /* RESET */
   4 |     5 |  15
   5 |    15 |  30
   6 |    10 |  40
   1 |     5 |  45    
   2 |    20 |  65
   3 |     5 |   5    /* RESET */
   4 |    15 |  20
   5 |    20 |  40
   6 |     5 |  45
   1 |    30 |  75

阅读 166

2021-03-08

共1个答案

一尘不染

您可以使用条件总和创建子组：

WITH cte AS (
   SELECT t.*,SUM(CASE WHEN hour=3 THEN 1 ELSE 0 END) OVER(ORDER BY timestamp) grp
   FROM t
)
SELECT cte.*, SUM(Count) OVER(PARTITION BY grp ORDER BY timestamp) AS total
FROM cte

2021-03-08