Как проверить значения в mysql для предыдущих строк и использовать их для нахождения суммы
Я должен найти время, когда система, основанная на этой таблице, истекла, имея код «100», поэтому сначала я подумал, что мне нужно найти самую новую строку группы xID, а после этого проверить предыдущие строки, если их код равен 100, если это так, мне нужно продолжить с предыдущей предыдущей строкой, пока она не получит значение 200, после чего она находит время от следующей строки 200 сотен до настоящего момента (значение 100).
ID xID createdDate CODE
1 '1', '2019-07-27 11:52:01', '100'
2 '1', '2019-07-27 11:54:01', '200'
3 '2', '2019-09-03 05:10:02', '200'
4 '2', '2019-09-03 05:12:02', '200'
5 '3', '2019-09-02 05:12:02', '200'
6 '3', '2019-09-02 05:12:02', '100'
7 '3', '2019-09-02 05:12:02', '200'
8 '4', '2019-09-02 05:13:02', '200'
9 '5', '2019-09-03 05:10:03', '200'
10 '6', '2018-12-13 05:03:02', '200'
Таким образом, этот запрос должен для каждой группы xID найти общее время, в течение которого система до сих пор находилась с кодом 100. Надеюсь, я ясно выразился. И вот sql до сих пор.
select id, createdDate, code
from wlogs
where id in (
select max(id)
from wlogs
group by xid
)
order by xid;
Обновлено: ВЕРСИЯ МАЙСКЛ 8.0 РЕЗУЛЬТАТ должен быть примерно таким, где столбец totTimeWithCode100 должен показывать время в секундах или минутах, не имеет значения, для каждого типа xID.
xID totTimeWithCode100
'1', '500'
'2', '2'
'3', '33'
'4', '200'
'5', '40'
'6', '200'
Ответы 2
До версии MySQL 8.0 мы могли использовать определяемые пользователем переменные (неподдерживаемым способом) в тщательно созданном SQL, который использует поведение, которое постоянно наблюдается, но не гарантируется. (Справочное руководство MySQL специально предупреждает об использовании определяемых пользователем переменных.)
Что-то вроде этого:
SELECT s.xid AS `xID`
, IFNULL(SUM(s.secs_in_code100),0) AS `totTimeWithCode100`
FROM (
SELECT IF(@prev_xid = t.xid AND @prev_code = 100, TIMESTAMPDIFF(SECOND,@prev_date,t.createddate),0) AS secs_in_code100
, @prev_xid := t.xid AS xid
, @prev_date := t.createddate AS createddate
, @prev_code := t.code AS code
FROM ( SELECT @prev_xid := ''
, @prev_date := '1970-01-02 03:00'
, @prev_code := ''
) i
CROSS
JOIN wlogs t
ORDER
BY t.xid
, t.createddate
) s
GROUP BY s.xid
ORDER BY s.xid
В MySQL 8.0 мы можем избежать пользовательских переменных, используя аналитические/оконные функции.
В MySQL 8.0 мы могли бы использовать оконную функцию LAG() для доступа к значениям из предыдущей строки вместо сохранения значений из предыдущих строк в пользовательских переменных.
@ spencer7593 OP использует mariadb 10.1, поэтому оконные функции не работают.
@Shadow Да, но я могу перейти на 8.0, если это необходимо, и если он лучше работает с окнами, я не знаю, как это работает в любом случае.
MariaDB-10.2 имеет оконные функции.
@ spencer7593 Можно ли изменить ваш код, который вы написали, на использование его с функцией LAG (), потому что у меня сейчас версия 8.0?
Вы можете получить желаемый результат, найдя все строки с CODE = 100 для данного xID, за которыми сразу (во времени) следует строка с CODE != 100. Это можно сделать, LEFT JOIN сопоставив строки с CODE != 100 с предыдущей строкой если, в которой эта строка имеет CODE = 100:
SELECT w.xID, COALESCE(SUM(TIMESTAMPDIFF(SECOND, w1.createdDate, w2.createdDate)), 0) AS totTimeWithCode100
FROM (SELECT DISTINCT xID FROM wlogs) w
LEFT JOIN (SELECT *
FROM wlogs
WHERE CODE = 100) w1 ON w1.xID = w.xID
LEFT JOIN wlogs w2 ON w2.xID = w1.xID
AND w2.createdDate = (SELECT MIN(createdDate)
FROM wlogs w3
WHERE w3.xID = w1.xID AND
w3.createdDate > w1.createdDate)
GROUP BY w.xID
ORDER BY w.xID;
Будет ли это правильно обрабатывать случай, когда за кодом 100 следует другой код 100? Пример данных OP не включает пример этого, но в спецификации нет ничего, что говорило бы о том, что этого не произойдет. Как следует рассчитывать «общее время в коде 100», если имеется серия из 100 строк кода без промежуточного кода, отличного от 1000?
@ spencer7593 действительно не было бы. Как вы говорите, этого не было в примере, но это могло произойти. Я отредактировал новый запрос, который суммирует все эти времена и новую демонстрацию.
Я анализирую это, но как это можно сделать в MySQL 8.0 с оконным режимом. Я думаю, что я мог бы перейти на него.