Отловить и вывести изменения по определенному полю

Question

Имеем такую таблицу

CREATE TABLE result (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  branch_id int(11) DEFAULT NULL,
  ip varchar(15) DEFAULT NULL,
  ping int(11) NOT NULL DEFAULT '0',
  interface varchar(10) DEFAULT NULL,
  mac varchar(20) DEFAULT NULL,
  abbrev_mac varchar(10) DEFAULT NULL,
  vender varchar(255) DEFAULT NULL,
  ext_data int(11) NOT NULL DEFAULT '0',
  year year(4) NOT NULL DEFAULT '0000',
  month int(11) NOT NULL DEFAULT '0',
  week int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id`),
  UNIQUE KEY `id (id`),
  KEY `index_ip (ip)
) ENGINE=InnoDB AUTO_INCREMENT=255028 DEFAULT CHARSET=utf8;

Я не волшебник, а только учусь, поэтому с SQL у меня туговато бывает.

Есть запрос
SELECT * from  ping_and_arp.result
WHERE interface != "I" and week between 1 and 15
group by ip, mac

При выводе встречаются записи такого вида. Сменился mac

id	branch_id	ip	ping	interface	mac	abbrev_mac	vender	ext_data	year	month	week
41441	2	10.101.0.30	1	D	00:20:D2:50:92:16	00:20:D2	RAD DATA COMMUNICATIONS LTD.	1	2021	4	15
6244	2	10.101.0.30	1	D	00:20:D2:50:9D:D4	00:20:D2	RAD DATA COMMUNICATIONS LTD.	1	2021	4	14

Если сделать так

SELECT * from  ping_and_arp.result
WHERE interface != "I" and week between 1 and 15 and ip = "10.101.0.30"
order by week

id	branch_id	ip	ping	interface	mac	abbrev_mac	vender	ext_data	year	month	week
23124	2	10.101.0.30	1	D	00:20:D2:50:9D:D4	00:20:D2	RAD DATA COMMUNICATIONS LTD.	1	2021	3	12
49856	2	10.101.0.30	1	D	00:20:D2:50:9D:D4	00:20:D2	RAD DATA COMMUNICATIONS LTD.	1	2021	4	13
6244	2	10.101.0.30	1	D	00:20:D2:50:9D:D4	00:20:D2	RAD DATA COMMUNICATIONS LTD.	1	2021	4	14
41441	2	10.101.0.30	1	D	00:20:D2:50:92:16	00:20:D2	RAD DATA COMMUNICATIONS LTD.	1	2021	4	15

То видно, что mac на 15 неделе другой

Как вывести все записи, у которых сменился mac? Если бы скажем еще были записи, ну до 18 недели, а смена была на 17. То мне нужны с 14, 15, 18 неделя, как раз последние где встрчается разный mac

Answer 1

Ну типа так:

WITH cte AS ( SELECT *,
                     LAG(mac) OVER (PARTITION BY ip ORDER BY week) previous_mac,
                     LEAD(mac) OVER (PARTITION BY ip ORDER BY week) next_mac
              FROM result )
SELECT *
FROM cte
WHERE previous_mac IS NOT NULL
  AND next_mac IS NOT NULL
  AND (    (mac != previous_mac)
        OR (mac != next_mac) )
ORDER BY ip, week

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ee438a8410d19f6fd4ee2cfa9aeb73cb

БЛОГ НА HUSL

Отловить и вывести изменения по определенному полю

Ответы (1 шт):