Порядок mysql по сумме (столбцу), сгруппирован по другому столбцу с самосоединением
у меня есть события таблицы:
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);
Мне нужно показать разницу в столбцах "прошел" и "не прошел" за интервал времени, и я создал следующий запрос:
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_channel, чтобы после запроса таблица выглядела следующим образом:
+---------------------+---------------------+---------+--------+--------+-------+
| 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)
Однако я подозреваю, что есть более элегантное решение.
@Eric: вышеупомянутый запрос запускается для меня в MySQL v5.6.23 и v8.0.12 и дает требуемый результат выше. ch1 содержит dev1, dev2 и dev3, тогда как ch2 содержит dev4, dev5 и dev6.
Выучите хорошую привычку использовать правильный GROUP BY. Вероятно, MySQL - единственная dbms, которая допускает испорченный GROUP BY. Ваш запрос даже не будет работать в других СУБД.
@Eric, не могли бы вы предоставить правильный запрос, который предоставит требуемый результат, пожалуйста .. будьте заинтересованы узнать, какой здесь правильный подход.
Ответы 2
Вы просите об этом. Проверять:-
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;
и для канала: -
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;
нет Я хочу заказать по SUM (не удалось) сгруппировать по device_channel. Итак, как показано выше, СУММ (сбой) = 12 по каналу 2 и СУММ (сбой) = 8 по каналу 1. Поэтому, если мы заказываем на основе SUM (неудач), сгруппированных по device_channel DESC, мы должны получить 3 строки с ch2, за которыми следуют 3 строки ch1, как показано выше.
этот запрос возвращается, как вы упомянули в своем вопросе. Я не могу понять, что означает «SUM (fails) = 12 over ch2 and SUM (fails) = 8 over ch1») означает, можете ли вы предоставить желаемый результат
в вопросе выше вторая таблица - это результат, который я хочу ... это показывает, что ошибки упорядочены по 0,10,2,3,4,1, где 0,10,2 связаны с ch2 и 3,4,1 с ch1
Это результат, который вы получаете с этим запросом
Можете ли вы попробовать этот запрос ниже.
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 ;
это просто отображает 2 строки .. Мне нужно увидеть все 6 строк, как показано выше. большое спасибо за ваш вклад
Да, есть два уникальных канала устройства. так что это даст две записи. Если мы хотим получить 6 строк, достаточно внутреннего запроса. но для внутреннего запроса нужно добавить заказ по diff desc.
Используйте подходящий
GROUP BY. Ваш запрос даже не запустится, потому что в нем нет смысла. Когда вы группируете поChannel, какое значениеDeviceизвлекается?