我正试图弄清楚如何对查询结果进行分组，最终分组应该发生在时差小于一分钟的地方。
我有一个水表，记录我的用水量，我正在尝试将结果分组，这样图表会更有意义。我对年、月、日和小时的用水量进行分组的sql查询非常完美，但我想深入到最终结果显示分组的位置，例如我给草浇水的位置。
我的表格结构看起来像：
id liter total_liters date time dater
9 3 184 2020/12/06 16:14:58 2020/12/06 16:14
10 1 185 2020/12/06 16:15:04 2020/12/06 16:15
11 3 188 2020/12/06 16:26:49 2020/12/06 16:26
12 2 190 2020/12/06 16:26:55 2020/12/06 16:26
13 2 192 2020/12/06 16:27:01 2020/12/06 16:27
14 1 193 2020/12/06 17:32:16 2020/12/06 17:32
15 1 194 2020/12/06 17:32:22 2020/12/06 17:32
16 1 195 2020/12/06 17:32:28 2020/12/06 17:32
17 1 196 2020/12/06 17:32:35 2020/12/06 17:32
18 1 197 2020/12/06 17:32:41 2020/12/06 17:32
19 1 198 2020/12/06 17:32:47 2020/12/06 17:32
20 1 199 2020/12/06 17:32:53 2020/12/06 17:32
21 1 200 2020/12/06 17:32:59 2020/12/06 17:32
22 1 201 2020/12/06 17:35:05 2020/12/06 17:35
23 1 202 2020/12/06 17:35:17 2020/12/06 17:35
24 1 203 2020/12/06 17:35:23 2020/12/06 17:35
25 1 204 2020/12/06 17:35:29 2020/12/06 17:35
26 1 205 2020/12/06 17:35:41 2020/12/06 17:35
27 1 206 2020/12/06 17:43:05 2020/12/06 17:43
28 3 209 2020/12/06 17:43:11 2020/12/06 17:43
29 2 211 2020/12/06 17:43:17 2020/12/06 17:43
30 2 213 2020/12/06 17:43:23 2020/12/06 17:43
31 2 215 2020/12/06 17:43:29 2020/12/06 17:43
32 3 218 2020/12/06 17:43:36 2020/12/06 17:43
33 2 220 2020/12/06 17:43:42 2020/12/06 17:43
我当前的查询如下：
SELECT DATE_FORMAT(dater,'%H:%i') AS dater,
YEAR(dater),
MONTHNAME(dater),
DAY(dater),
HOUR(dater),
MINUTE(dater),
SUM(liter) as liter
FROM watermeter
WHERE date LIKE '2020-12-08'
GROUP BY YEAR(date), MONTHNAME(date), DAY(dater), HOUR(dater), MINUTE(dater)
ORDER BY id ASC`
结果应该是通过按年份、月份、日期和小时对Liters进行分组，将Liters相加，然后对时差小于60秒的结果进行分组。
我最终可能会将它们分组为年、月、日，然后按时间差小于60秒。
像
2020-12-06 17:35:05 5 Liters
2020-12-06 17:43:05 13 Liters
如果phpmyaddmin-sql转储有助于
-- phpMyAdmin SQL Dump
-- version 4.6.6deb5
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Dec 10, 2020 at 07:27 AM
-- Server version: 10.3.17-MariaDB-0+deb10u1
-- PHP Version: 7.3.11-1~deb10u1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `mysensors`
--
-- --------------------------------------------------------
--
-- Table structure for table `watermeter`
--
CREATE TABLE `watermeter` (
`id` int(10) NOT NULL,
`liter` int(11) NOT NULL,
`total_liters` int(11) NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`dater` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `watermeter`
--
INSERT INTO `watermeter` (`id`, `liter`, `total_liters`, `date`, `time`, `dater`) VALUES
(9, 3, 184, '2020-12-06', '16:14:58', '2020-12-06 16:14:58'),
(10, 1, 185, '2020-12-06', '16:15:04', '2020-12-06 16:15:04'),
(11, 3, 188, '2020-12-06', '16:26:49', '2020-12-06 16:26:49'),
(12, 2, 190, '2020-12-06', '16:26:55', '2020-12-06 16:26:55'),
(13, 2, 192, '2020-12-06', '16:27:01', '2020-12-06 16:27:01'),
(14, 1, 193, '2020-12-06', '17:32:16', '2020-12-06 17:32:16'),
(15, 1, 194, '2020-12-06', '17:32:22', '2020-12-06 17:32:22'),
(16, 1, 195, '2020-12-06', '17:32:28', '2020-12-06 17:32:28'),
(17, 1, 196, '2020-12-06', '17:32:35', '2020-12-06 17:32:35'),
(18, 1, 197, '2020-12-06', '17:32:41', '2020-12-06 17:32:41'),
(19, 1, 198, '2020-12-06', '17:32:47', '2020-12-06 17:32:47'),
(20, 1, 199, '2020-12-06', '17:32:53', '2020-12-06 17:32:53'),
(21, 1, 200, '2020-12-06', '17:32:59', '2020-12-06 17:32:59'),
(22, 1, 201, '2020-12-06', '17:35:05', '2020-12-06 17:35:05'),
(23, 1, 202, '2020-12-06', '17:35:17', '2020-12-06 17:35:17'),
(24, 1, 203, '2020-12-06', '17:35:23', '2020-12-06 17:35:23'),
(25, 1, 204, '2020-12-06', '17:35:29', '2020-12-06 17:35:29'),
(26, 1, 205, '2020-12-06', '17:35:41', '2020-12-06 17:35:41'),
(27, 1, 206, '2020-12-06', '17:43:05', '2020-12-06 17:43:05'),
(28, 3, 209, '2020-12-06', '17:43:11', '2020-12-06 17:43:11'),
(29, 2, 211, '2020-12-06', '17:43:17', '2020-12-06 17:43:17'),
(30, 2, 213, '2020-12-06', '17:43:23', '2020-12-06 17:43:23'),
(31, 2, 215, '2020-12-06', '17:43:29', '2020-12-06 17:43:29'),
(32, 3, 218, '2020-12-06', '17:43:36', '2020-12-06 17:43:36'),
(33, 2, 220, '2020-12-06', '17:43:42', '2020-12-06 17:43:42');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `watermeter`
--
ALTER TABLE `watermeter`
ADD PRIMARY KEY (`id`),
ADD KEY `dater` (`dater`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `watermeter`
--
ALTER TABLE `watermeter`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1061;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
更新1。
我认为进展甚微——总数还不正确。
SELECT '(a.dater, b.dater)', DATE_FORMAT(a.dater,'%H:%i') AS dater,
YEAR(a.dater),
MONTHNAME(a.dater),
DAY(a.dater),
HOUR(a.dater),
MINUTE(a.dater),
a.time,
SUM(a.liter) as liter
FROM watermeter a
INNER JOIN watermeter b
ON b.dater >= a.dater
WHERE b.dater <= DATE_ADD(a.dater, INTERVAL 60 SECOND)
AND a.date LIKE '2020-12-08' GROUP BY YEAR(a.date), MONTHNAME(a.date), DAY(a.dater), HOUR(a.dater), MINUTE(a.dater)
ORDER BY a.id ASC
更新2
所以更新一并没有给我正确的结果。现在尝试了我从中得到的以下内容：MySQL GROUP BY DateTime+/-3秒，但也没有乐趣。
SELECT COUNT(liter),DAY(dater),HOUR(dater),MINUTE(dater)
FROM watermeter
JOIN (SELECT watermeter.id, MAX(S.dater) AS ChainStartTime
FROM watermeter
JOIN (SELECT DISTINCT a.dater
FROM watermeter a
LEFT JOIN watermeter b
ON (b.dater >= a.dater - INTERVAL 60 SECOND
AND b.dater < a.dater)
WHERE b.dater IS NULL
AND a.date LIKE '2020-12-06') S
ON watermeter.dater >= S.dater
GROUP BY watermeter.id) GroupingQuery
ON watermeter.id = GroupingQuery.id
GROUP BY GroupingQuery.ChainStartTime
首先查找与前一行的时间差异：
select
id,
liter,
total_liters,
dater,
lead(dater) over (order by dater) as "lead",
timediff(dater, lead(dater) over (order by dater)) as d1
from watermeter
order by dater;
输出：
+----+-------+--------------+---------------------+---------------------+-----------+
| id | liter | total_liters | dater | lead | d1 |
+----+-------+--------------+---------------------+---------------------+-----------+
| 9 | 3 | 184 | 2020-12-06 16:14:58 | 2020-12-06 16:15:04 | -00:00:06 |
| 10 | 1 | 185 | 2020-12-06 16:15:04 | 2020-12-06 16:26:49 | -00:11:45 |
| 11 | 3 | 188 | 2020-12-06 16:26:49 | 2020-12-06 16:26:55 | -00:00:06 |
| 12 | 2 | 190 | 2020-12-06 16:26:55 | 2020-12-06 16:27:01 | -00:00:06 |
| 13 | 2 | 192 | 2020-12-06 16:27:01 | 2020-12-06 17:32:16 | -01:05:15 |
| 14 | 1 | 193 | 2020-12-06 17:32:16 | 2020-12-06 17:32:22 | -00:00:06 |
| 15 | 1 | 194 | 2020-12-06 17:32:22 | 2020-12-06 17:32:28 | -00:00:06 |
| 16 | 1 | 195 | 2020-12-06 17:32:28 | 2020-12-06 17:32:35 | -00:00:07 |
| 17 | 1 | 196 | 2020-12-06 17:32:35 | 2020-12-06 17:32:41 | -00:00:06 |
等等。。。
然后确定您希望查看的时间，因为它们与前一行的差异大于60秒。(列的id显示在x中(
with cte as (
select id,
dater,
liter,
total_liters,
d1,
abs(time_to_sec(d1)) as g1,
case when abs(time_to_sec(d1))>60 then id else 0 end as x
from (
select
id,
liter,
total_liters,
dater,
lead(dater) over (order by dater) as "lead",
timediff(dater, lead(dater) over (order by dater)) as d1
from watermeter
order by dater
) tmp1
)
select * from cte;
输出：
+----+---------------------+-------+--------------+-----------+------+----+
| id | dater | liter | total_liters | d1 | g1 | x |
+----+---------------------+-------+--------------+-----------+------+----+
| 9 | 2020-12-06 16:14:58 | 3 | 184 | -00:00:06 | 6 | 0 |
| 10 | 2020-12-06 16:15:04 | 1 | 185 | -00:11:45 | 705 | 10 |
| 11 | 2020-12-06 16:26:49 | 3 | 188 | -00:00:06 | 6 | 0 |
| 12 | 2020-12-06 16:26:55 | 2 | 190 | -00:00:06 | 6 | 0 |
| 13 | 2020-12-06 16:27:01 | 2 | 192 | -01:05:15 | 3915 | 13 |
| 14 | 2020-12-06 17:32:16 | 1 | 193 | -00:00:06 | 6 | 0 |
| 15 | 2020-12-06 17:32:22 | 1 | 194 | -00:00:06 | 6 | 0 |
| 16 | 2020-12-06 17:32:28 | 1 | 195 | -00:00:07 | 7 | 0 |
等等。。。
下一步是确定"属于"x:的最大值(id(
with cte as (
select id,
dater,
liter,
total_liters,
d1,
abs(time_to_sec(d1)) as g1,
case when abs(time_to_sec(d1))>60 then id else 0 end as x
from (
select
id,
liter,
total_liters,
dater,
lead(dater) over (order by dater) as "lead",
timediff(dater, lead(dater) over (order by dater)) as d1
from watermeter
order by dater
) tmp1
)
select
id,
dater,
liter,
total_liters
,d1,
g1,
x,
(select min(x)-1 from cte c2 where c2.id>c1.x and c2.x>0) as y
from cte c1
where c1.x<>0
;
输出：
+----+---------------------+-------+--------------+-----------+------+----+------+
| id | dater | liter | total_liters | d1 | g1 | x | y |
+----+---------------------+-------+--------------+-----------+------+----+------+
| 10 | 2020-12-06 16:15:04 | 1 | 185 | -00:11:45 | 705 | 10 | 12 |
| 13 | 2020-12-06 16:27:01 | 2 | 192 | -01:05:15 | 3915 | 13 | 20 |
| 21 | 2020-12-06 17:32:59 | 1 | 200 | -00:02:06 | 126 | 21 | 25 |
| 26 | 2020-12-06 17:35:41 | 1 | 205 | -00:07:24 | 444 | 26 | NULL |
+----+---------------------+-------+--------------+-----------+------+----+------+
请注意，x和y是您所在组的最小和最大id。
最后(这些乱七八糟的东西(：
with cte as (
select id,
dater,
liter,
total_liters,
d1,
abs(time_to_sec(d1)) as g1,
case when abs(time_to_sec(d1))>60 then id else 0 end as x
from (
select
id,
liter,
total_liters,
dater,
lead(dater) over (order by dater) as "lead",
timediff(dater, lead(dater) over (order by dater)) as d1
from watermeter
order by dater
) tmp1
)
select
id,
dater,
(select sum(liter) from watermeter where id between x and y) as rain
from (
select
id,
dater,
liter,
total_liters
,d1,
g1,
x,
(select min(x)-1 from cte c2 where c2.id>c1.x and c2.x>0) as y
from cte c1
where c1.x<>0
) tmp2
;
给出输出：
+------+---------------------+------+
| id | dater | rain |
+------+---------------------+------+
| 10 | 2020-12-06 16:15:04 | 6 |
| 13 | 2020-12-06 16:27:01 | 9 |
| 21 | 2020-12-06 17:32:59 | 5 |
| 26 | 2020-12-06 17:35:41 | NULL |
+------+---------------------+------+
我真的希望这接近预期的产出。。。
在Luuk代码的帮助下，学习了很多关于"case"；以及"；滞后；和铅"；以及使用嵌套选择等。我能够得到一个我想要的工作查询。
SET @wgroup := 0;
with cte as (
select
id,
dater,
liter,
total_liters,
d1,
abs(time_to_sec(d1)) as g1,
case when abs(time_to_sec(d1))>60 then @wgroup := @wgroup+1 else @wgroup end as wgroup
from (
select
id,
liter,
total_liters,
dater,
(case
WHEN lag(dater) over (order by dater) IS NULL
THEN timediff(dater, lead(dater) over (order by dater))
ELSE timediff(dater, lag(dater) over (order by dater))
END) AS d1
from watermeter where date like '2020-12-06'
order by dater
) tmp1
)
(select dater,
wgroup,
SUM(liter)
from cte
GROUP BY wgroup)
;
有了这个，我能够在不丢失一行的情况下对所有时间差小于60秒的值进行求和。
+---------------------+--------+------------+
| dater | wgroup | SUM(liter) |
+---------------------+--------+------------+
| 2020-12-06 16:14:58 | 0 | 4 |
| 2020-12-06 16:26:49 | 1 | 7 |
| 2020-12-06 17:32:16 | 2 | 8 |
| 2020-12-06 17:35:05 | 3 | 5 |
| 2020-12-06 17:43:05 | 4 | 308 |
| 2020-12-06 19:19:03 | 5 | 120 |
| 2020-12-06 19:31:29 | 6 | 4 |
| 2020-12-06 19:34:48 | 7 | 1 |
| 2020-12-06 20:30:08 | 8 | 1 |
| 2020-12-06 21:27:06 | 9 | 23 |
+---------------------+--------+------------+