时间戳

首页 > 解决方案 > 比较分组查询中上一行的日期

mysql - 比较分组查询中上一行的日期

问题描述

我计算了气温低于 7.22 的小时数。我想在 SQL 端做这个计算。

我有下表

CREATE TABLE datas (
  id INT,
  air_temperature DOUBLE(8,2),
  created_at TIMESTAMP
);

INSERT INTO datas (id, air_temperature, created_at) VALUES (1, 6.50, '2020-12-01 23:45:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (2, 6.50, '2020-12-02 03:45:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (3, 6.92, '2020-12-02 04:00:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (4, 6.99, '2020-12-02 04:15:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (5, 7.45, '2020-12-02 04:30:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (6, 7.34, '2020-12-02 04:45:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (7, 7.10, '2020-12-02 05:00:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (8, 7.00, '2020-12-02 05:20:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (9, 7.12, '2020-12-02 07:15:02');

数据的日期间隔可以是任何值。

我想得到什么

date            ch
2020-12-01      1
2020-12-02      3.083
2020-12-03      {calculatedValue}
2020-12-04      {calculatedValue}

规则

2020-12-02 的值如何是 3.083？

rowId 2 = take ch as 60 minutes (there's no previous row to compare, so take as 60 minutes. related to rule-1).
rowId 3 = take ch as 15 minutes. (the difference between the date of previous data and the date of current data)
rowId 4 = take ch as 15 minutes (same as rowId 3)
rowId 5 = take ch as 0 (it's not below 7.22)
rowId 6 = take ch as 0 (it's not below 7.22)
rowId 7 = take ch as 15 minutes (same as rowId 3)
rowId 8 = take ch as 20 minutes (same as rowId 3)
rowId 9 = take ch as 60 minutes (previous date is higher than 60 minutes. so take as 60 minutes. related to rule-2)

total minutes of day / 60 = 185 / 60 = 3.083 hours

如果我得到前一个值的日期，我可以比较和计算，但我不知道如何在使用 group by 时获得前一行。

我想过这样的事情，但我不确定如何应用上述规则。

SELECT
    DATE_FORMAT(created_at, '%d-%m-%Y') as `date`,
    SUM((CASE WHEN air_temperature < 7.22 THEN (apply second rule here) ELSE 0 END)) as ch
FROM datas
GROUP BY `date`

编辑：dbfiddle

标签： mysqlsql

解决方案

你可以用LEAD()得到下一个created_at。然后使用算术将差异限制为 60：

SELECT DATE_FORMAT(created_at, '%d-%m-%Y') as `date`,
       SUM( COALESCE(LEAST(TIMESTAMPDIFF(minute, created_at, next_created_at), 60)), 60) / 60 as hours
FROM (SELECT d.*,
             LEAD(created_at) OVER (ORDER BY created_at) as next_created_at
      FROM datas d
     ) d
WHERE air_temperature < 7.22
GROUP BY `date`

推荐阅读