从值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

您可以访问MySQL 8中的窗口函数，请使用它们。

WITH cte1 AS (
    SELECT *, CASE
        WHEN LAG(value, 1, 0) OVER (ORDER BY timestamp) = 0 AND value > 0 THEN 1
        WHEN LAG(value, 1, 0) OVER (ORDER BY timestamp) > 0 AND value = 0 THEN 1 END AS chg
    FROM t
), cte2 AS (
    SELECT *, SUM(chg) OVER (ORDER BY timestamp) AS grp
    FROM cte1
)
SELECT SUM(value) AS `Sum of value`, MIN(timestamp) AS `Start`, MAX(timestamp) AS `End`
FROM cte2
GROUP BY grp
HAVING SUM(value) > 0

Demo on db<>fiddle

Answer 2

有几个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

见演示。

结果：

| 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 |