在Oracle中满足条件时重置总和 [英] Reset 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，但在小时数为3时将重置.结果应如下所示:

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

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    

推荐答案

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

You could create subgroup using conditional sum:

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

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