MySQL-获取一系列行中连续事件的最小/最大

Question

我有一个看起来像这样的表: http://sqlfiddle.com/#!9/152d2/1/0

CREATE TABLE Table1 (
  id int,
  value decimal(10,5),
  dt datetime, 
  threshold_id int
);

当前查询:

SELECT sensors_id, DATE_FORMAT(datetime, '%Y-%m-%d'), MIN(value), MAX(value) 
FROM Readings 
WHERE datetime < "2015-11-18 00:00:00" 
AND datetime > "2015-10-18 00:00:00" 
AND sensors_id = 9 
GROUP BY DATE_FORMAT(datetime, '%Y-%m-%d') 
ORDER BY datetime DESC

我想要做的是返回每个组中的最小/最大值，其中threshold_id不为空。因此，该示例应返回类似以下内容的内容:

min_value | max_value | start_date          | end_date  
9         | 10.5      | 2015-07-29 10:52:31 | 2015-07-29 10:57:31
8.5       | 9.5       | 2015-07-29 11:03:31 | 2015-07-29 11:05:31

我不知道如何进行这种分组。我需要返回阈值 ID 不为空的每组连续行的最小值/最大值。

Answer 1

使用用户变量将现有值与先前值进行比较，并增加可用于分组依据的列，在我的计算机上进行了测试。

SELECT MIN(value),MAX(value),MIN(dt),MAX(dt)
FROM (
  SELECT id,value,dt,
    CASE WHEN  COALESCE(threshold_id,'')=@last_ci  THEN @n ELSE @n:=@n+1 END AS g,
    @last_ci := COALESCE(threshold_id,'') As th
  FROM
    Table1, (SELECT @n:=0) r 
  ORDER BY
    id 
) s
WHERE th!=''
GROUP BY
  g

对于 mysql 8，这可以重写如下。使用 CTE 来获取不同的序列和 GROUP By 它们之间的差异。

WITH cte as (
SELECT *,
  ROW_NUMBER() OVER (ORDER BY id)as rn,
  ROW_NUMBER() OVER (PARTITION BY threshold_id ORDER BY id)as rnn
  FROM Table1
  ORDER BY id
)
SELECT MIN(value),MAX(value),MIN(dt),MAX(dt) FROM cte WHERE threshold_id IS NOT NULL GROUP BY rn-rnn

MYSQL8

FIDDLE