mysql - 对从值 x 开始一直到遇到值 y 的行求和然后重复

Question

我试图将行分组在一起，从值大于 0 的点开始，然后继续直到值回到 0，然后在整个表中重复。

这是针对 MySQL 8.0 的...我不知道从哪里开始。

示例表

| tag          | value | timestamp                |
|--------------|-------|--------------------------|
| sts_downtime | 0     | 2019-01-03 09:31:40.8240 |
| sts_downtime | 1     | 2019-01-03 09:50:23.0310 |
| sts_downtime | 3     | 2019-01-03 09:53:07.7750 |
| sts_downtime | 4     | 2019-01-03 09:53:40.6060 |
| sts_downtime | 0     | 2019-01-04 08:48:27.1020 |
| sts_downtime | 0     | 2019-01-04 13:30:26.5180 |
| sts_downtime | 10    | 2019-01-04 14:19:56.3740 |
| sts_downtime | 10    | 2019-01-07 08:49:03.8480 |
| sts_downtime | 10    | 2019-01-07 09:34:25.0850 |
| sts_downtime | 0     | 2019-01-07 09:34:53.9940 |
| sts_downtime | 0     | 2019-01-07 12:59:21.3210 |

我想得到什么

| Sum of Value | Start                    | End                      |
|--------------|--------------------------|--------------------------|
| 8            | 2019-01-03 09:50:23.0310 | 2019-01-03 09:53:40.6060 |
| 30           | 2019-01-04 14:19:56.3740 | 2019-01-07 09:34:25.0850 |

Answer 1

有几个 CTE:

with 
ctemin as (
  select t.timestamp from tablename t
    where value <> 0 and 
    (select value from tablename where timestamp = 
       (select max(timestamp) from tablename where timestamp < t.timestamp)
    ) = 0
),  
ctemax as (
  select t.timestamp from tablename t
    where value <> 0 and 
    (select value from tablename where timestamp = 
       (select min(timestamp) from tablename where timestamp > t.timestamp)
    ) = 0
),
cte as (
  select 
    t.timestamp Start,
    (select min(timestamp) from ctemax 
     where timestamp >= t.timestamp) End
  from ctemin t
)  

select 
  sum(value) `Sum of Value`,
  c.Start, c.End
from cte c inner join tablename t
on t.timestamp between c.Start and c.End
group by c.Start, c.End

参见 demo .
结果:

| Sum of Value | Start                      | End                        |
| ------------ | -------------------------- | -------------------------- |
| 8            | 2019-01-03 09:50:23.031000 | 2019-01-03 09:53:40.606000 |
| 30           | 2019-01-04 14:19:56.374000 | 2019-01-07 09:34:25.085000 |