mysql order by sum(列)由另一个具有自联接的列分组

goqiplq2  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(261)

我有表事件:

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)

我怀疑有一个更优雅的解决方案。

chhkpiq4

chhkpiq41#

你能试试下面这个查询吗。

SELECT derv_res.device_channel, SUM(derv_res.diff) AS total_fails 
FROM 
(SELECT     a.device_channel, a.device_name , (b.fails - a.fails) AS diff
FROM EVENTS a
INNER JOIN EVENTS b 
ON b.time > a.time AND 
b.device_name = a.device_name 
GROUP BY 
a.device_channel,
a.device_name) AS derv_res
GROUP BY derv_res.device_channel
ORDER BY total_fails DESC ;
7vux5j2d

7vux5j2d2#

这就是你要的。尝试out:-

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
    ORDER BY fails  desc;

对于频道wise:-

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
    ORDER BY channel ,fails desc;

相关问题