mysql - mysql ORDER BY SUM 作为解析函数的问题

Question

我有以下数据库表：

create table channel (
    channel_name VARCHAR(15) NOT NULL,
channel_num TINYINT NOT NULL,
channel_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

create table device(
device_name VARCHAR(5) NOT NULL,
channel_num TINYINT NOT NULL,
device_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

create table events (
time TIMESTAMP NOT NULL,
passes INT UNSIGNED NOT NULL,
fails INT UNSIGNED NOT NULL,
device_name VARCHAR(5) NOT NULL,
events_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO channel VALUES
('ch1',1,NULL),
('ch2',2,NULL); 

INSERT INTO device VALUES 
('dev1',1,NULL),
('dev2',1,NULL),
('dev3',1,NULL),
('dev4',2,NULL),
('dev5',2,NULL),
('dev6',2,NULL);

INSERT INTO events VALUES
('2018-08-08 10:30:00',3000,15,'dev1',NULL),
('2018-08-08 10:30:00',3000,12,'dev2',NULL),
('2018-08-08 10:30:00',3000,9,'dev3',NULL),
('2018-08-08 10:30:00',3000,11,'dev4',NULL),
('2018-08-08 10:30:00',3000,10,'dev5',NULL),
('2018-08-08 10:30:00',3000,8,'dev6',NULL),

('2018-08-08 10:45:00',4000,18,'dev1',NULL),
('2018-08-08 10:45:00',4000,16,'dev2',NULL),
('2018-08-08 10:45:00',4000,10,'dev3',NULL),
('2018-08-08 10:45:00',4000,11,'dev4',NULL),
('2018-08-08 10:45:00',4000,12,'dev5',NULL),
('2018-08-08 10:45:00',4000,8,'dev6',NULL);

我创建了以下查询，以便我可以订购按 Channel 分组的 SUM of Fails 的视图，但它不能按我需要的方式工作。这是我创建的查询：

SELECT a.time AS Start_time, b.time AS End_time ,channel.channel_num AS Channel,device.device_name AS Device,b.passes-a.passes Passes, b.fails-a.fails Fails
  FROM events a
  JOIN events b 
    ON b.time > a.time AND b.device_name = a.device_name
  JOIN device
    ON a.device_name = device.device_name
  JOIN channel
    ON device.channel_num = channel.channel_num
  ORDER BY SUM(b.fails-a.fails) OVER (PARTITION BY channel.channel_num) DESC;

这是查询的输出：

+---------------------+---------------------+---------+--------+--------+-------+
| Start_time          | End_time            | Channel | Device | Passes | Fails |
+---------------------+---------------------+---------+--------+--------+-------+
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       2 | dev6   |   1000 |     0 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       1 | dev1   |   1000 |     3 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       1 | dev2   |   1000 |     4 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       1 | dev3   |   1000 |     1 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       2 | dev4   |   1000 |     0 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       2 | dev5   |   1000 |     2 |
+---------------------+---------------------+---------+--------+--------+-------+
6 rows in set (0.00 sec)

这就是我希望输出的样子：

+---------------------+---------------------+---------+--------+--------+-------+
| Start_time          | End_time            | Channel | Device | Passes | Fails |
+---------------------+---------------------+---------+--------+--------+-------+
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       1 | dev1   |   1000 |     3 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       1 | dev2   |   1000 |     4 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       1 | dev3   |   1000 |     1 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       2 | dev4   |   1000 |     0 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       2 | dev5   |   1000 |     2 |
| 2018-08-08 10:30:00 | 2018-08-08 10:45:00 |       2 | dev6   |   1000 |     0 |
+---------------------+---------------------+---------+--------+--------+-------+
6 rows in set (0.00 sec)

非常感谢任何帮助！

标签： mysql