我有表事件:
create table events (
time TIMESTAMP NOT NULL,
passes INT UNSIGNED NOT NULL,
fails INT UNSIGNED NOT NULL,
device_channel VARCHAR(5) NOT NULL,
device_name VARCHAR(5) NOT NULL,
events_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
包含以下数据:
INSERT INTO events VALUES
('2018-08-08 10:30:00',3000,15,'ch1','dev1',NULL),
('2018-08-08 10:30:00',3000,12,'ch1','dev2',NULL),
('2018-08-08 10:30:00',3000,9,'ch1','dev3',NULL),
('2018-08-08 10:30:00',3000,11,'ch2','dev4',NULL),
('2018-08-08 10:30:00',3000,10,'ch2','dev5',NULL),
('2018-08-08 10:30:00',3000,8,'ch2','dev6',NULL),
('2018-08-08 10:45:00',4000,18,'ch1','dev1',NULL),
('2018-08-08 10:45:00',4000,16,'ch1','dev2',NULL),
('2018-08-08 10:45:00',4000,10,'ch1','dev3',NULL),
('2018-08-08 10:45:00',4000,11,'ch2','dev4',NULL),
('2018-08-08 10:45:00',4000,20,'ch2','dev5',NULL),
('2018-08-08 10:45:00',4000,10,'ch2','dev6',NULL);
我需要显示时间间隔内passes和fails列的差异,并创建了以下查询:
SELECT a.time AS Start_time, b.time AS End_time ,a.device_channel AS Channel,a.device_name AS Device,b.passes-a.passes AS Passes, b.fails-a.fails AS Fails
FROM events a
JOIN events b
ON b.time > a.time AND b.device_name = a.device_name;
此查询生成以下结果:
+---------------------+---------------------+---------+--------+--------+-------+
| Start_time | End_time | Channel | Device | Passes | Fails |
+---------------------+---------------------+---------+--------+--------+-------+
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev1 | 1000 | 3 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev2 | 1000 | 4 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev3 | 1000 | 1 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev4 | 1000 | 0 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev5 | 1000 | 10 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev6 | 1000 | 2 |
+---------------------+---------------------+---------+--------+--------+-------+
6 rows in set (0.00 sec)
但是,我需要在sum(fails)上对表进行排序,并将其分组到device\u通道上,以便在查询之后该表的结果如下:
+---------------------+---------------------+---------+--------+--------+-------+
| Start_time | End_time | Channel | Device | Passes | Fails |
+---------------------+---------------------+---------+--------+--------+-------+
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev4 | 1000 | 0 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev5 | 1000 | 10 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev6 | 1000 | 2 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev1 | 1000 | 3 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev2 | 1000 | 4 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev3 | 1000 | 1 |
+---------------------+---------------------+---------+--------+--------+-------+
6 rows in set (0.00 sec)
我尝试将以下order by子句附加到查询中,但似乎不起作用:
ORDER BY SUM(b.fails-a.fails) OVER (PARTITION BY channel.channel_num) DESC;
非常感谢您的帮助。
根据@vinay chhabra的输入,我创建了一个查询,它提供了所需的结果:
SELECT t1.*
FROM
(
SELECT a.time AS Start_time, b.time AS End_time ,a.device_channel AS Channel,a.device_name AS Device, b.fails-a.fails AS Fails
FROM events a
JOIN events b
ON b.time > a.time AND b.device_name = a.device_name
) AS t1
JOIN
(
SELECT a.device_channel AS Channel,a.device_name AS Device, b.fails-a.fails AS Fails, SUM(b.fails-a.fails) AS Sumfails
FROM events a
JOIN events b
ON b.time > a.time AND b.device_name = a.device_name
Group by Channel
) t2
ON t1.Channel = t2.Channel
ORDER by Sumfails DESC, Fails DESC;
+---------------------+---------------------+---------+--------+-------+
| Start_time | End_time | Channel | Device | Fails |
+---------------------+---------------------+---------+--------+-------+
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev5 | 10 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev6 | 2 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch2 | dev4 | 0 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev2 | 4 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev1 | 3 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 | ch1 | dev3 | 1 |
+---------------------+---------------------+---------+--------+-------+
6 rows in set (0.00 sec)
我怀疑有一个更优雅的解决方案。
2条答案
按热度按时间chhkpiq41#
你能试试下面这个查询吗。
7vux5j2d2#
这就是你要的。尝试out:-
对于频道wise:-