MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `data` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `ID1` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `ID2` INT(11) NULL DEFAULT NULL,
    `ACT_TIME` TIMESTAMP NULL DEFAULT NULL,
    `X` FLOAT NULL DEFAULT NULL,
    `Y` FLOAT NULL DEFAULT NULL,
    `Z` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`ID`) USING BTREE,
    INDEX `ID1` (`ID1`) USING BTREE,
    INDEX `ACT_TIME` (`ACT_TIME`) USING BTREE
);
insert into data (ID, ID1, ID2, X, Y, Z, ACT_TIME) values  
(3895733, 'A',8, -76, 124, 43.41, "2021-09-29 23:59:00"),
(3895732, 'A', 8, -14, 144, 46.27, "2021-09-29 23:59:00"),
(3895731, 'A', 8, 40, 136, 44.72, "2021-09-29 23:59:00"),
(3895730, 'A', 8, 132, 60, 40.64, "2021-09-29 23:59:00"),
(3895729, 'A', 8, 36,140,42.47, "2021-09-29 23:59:00"),
(3895728, 'A', 8, -52, 132, 43.88, "2021-09-29 23:59:00"),
(3895727, 'A', 8, -90, -112, 47.35, "2021-09-29 23:59:00"),
(3895725, 'A', 8, 66, -128, 51.33, "2021-09-29 23:59:00"),
(3988629, 'A', 8, -96, -108, 42.25, "2021-09-30 11:20"),
(3988626, 'A', 8, -52, 132, 42.38, "2021-09-30 11:20"),
(3988625, 'A', 8, -74, 124, 42.07, "2021-09-30 11:20"),
(3988624, 'A', 8, 66, -128, 53.49, "2021-09-30 11:20"),
(3988623, 'A', 8, -90, -112, 47.55, "2021-09-30 11:20"),
(3988622, 'A', 8, -12, 144, 44.6, "2021-09-30 11:20")
-- The CTE1 is just to create columns that dictate the bound of what is considered the same entry
-- Also I do a dense rank by ACT_TIME, and a PARITION BY on ID1, ID2
-- so all ID1/ID2 combos are ranked by when they ran
WITH 
cte1 AS (SELECT *, (X-2) AS X_START, (X+2) AS X_END, (Y-2) AS Y_START, (Y+2) AS Y_END, (Z*1.2) AS Z_MAX,
    DENSE_RANK() OVER (PARTITION BY ID1, ID2 ORDER BY ACT_TIME) AS DENSE_RANK
    FROM data
    ORDER BY data.ACT_TIME)

,cte2 AS (
-- Create new set of column as comparisons
SELECT 
ID AS ID_COMP,
ID1 AS ID1_COMP,
ID2 AS ID2_COMP,
X_START AS X_START_COMP,
X_END AS X_END_COMP,
Y_START AS Y_START_COMP,
Y_END AS Y_END_COMP,
Z AS Z_MAX_COMP,
DENSE_RANK AS DENSE_RANK_COMP
FROM cte1)
, cte3 AS (
-- join cte1 on cte2 only when X and Y value from cte1 is between the limits of cte2 AND 
-- Z max value from cte 2 is larger than Z value from cte1 AND ID1/ID2 match
-- The result will have an ID of a row that should be removed since their x and y was in between the limits 
-- Then remove any rows where rank from cte2 is higher than cte1
--  Remove any rows that were joined onto it self 
SELECT 
cte1.* ,
cte2.*
FROM cte1
JOIN cte2 ON (( cte2.X_END_COMP >= cte1.X  AND  cte1.X >= cte2.X_START_COMP) AND (cte2.Y_END_COMP >= cte1.Y AND cte1.Y>= cte2.Y_START_COMP) 
                    AND (cte1.Z < cte2.Z_MAX_COMP) AND (cte2.ID2_COMP = cte1.ID2) AND (cte2.ID1_COMP = cte1.ID1))
WHERE cte1.ID <> cte2.ID_COMP AND cte2.DENSE_RANK_COMP <= cte1.DENSE_RANK)
-- Any IDs that shows up in cte3 remove from the final result
SELECT data.*
FROM data
WHERE ID NOT IN (SELECT DISTINCT ID FROM cte3)
ORDER BY data.ACT_TIME


ID
ID1
ID2
ACT_TIME
X
Y
Z


3895725
A
8
2021-09-29 23:59:00
66
-128
51.33

3895733
A
8
2021-09-29 23:59:00
-76
124
43.41

3895732
A
8
2021-09-29 23:59:00
-14
144
46.27

3895731
A
8
2021-09-29 23:59:00
40
136
44.72

3895730
A
8
2021-09-29 23:59:00
132
60
40.64

3895729
A
8
2021-09-29 23:59:00
36
140
42.47

3895728
A
8
2021-09-29 23:59:00
-52
132
43.88

3895727
A
8
2021-09-29 23:59:00
-90
-112
47.35

3988623
A
8
2021-09-30 11:20:00
-90
-112
47.55

3988624
A
8
2021-09-30 11:20:00
66
-128
53.49

3988629
A
8
2021-09-30 11:20:00
-96
-108
42.25


WITH 
cte1 AS (SELECT *, (X-2) AS X_START, (X+2) AS X_END, (Y-2) AS Y_START, (Y+2) AS Y_END, (Z*1.2) AS Z_MAX,
    DENSE_RANK() OVER (PARTITION BY ID1, ID2 ORDER BY ACT_TIME) AS `DENSE_RANK`
    FROM data
), cte3 AS (
SELECT 
c.ID
FROM cte1
JOIN cte1 c ON (cte1.ID <> c.ID and cte1.X_END >= c.X and c.X >= cte1.X_START and cte1.Y_END >= c.Y and c.Y >= cte1.Y_START and c.Z < cte1.Z*1.2 AND c.`DENSE_RANK` > cte1.`DENSE_RANK`)
)
SELECT data.*
FROM data
WHERE NOT EXISTS (SELECT 1 FROM cte3 where cte3.ID=data.ID)
ORDER BY data.ACT_TIME


ID
ID1
ID2
ACT_TIME
X
Y
Z


3895725
A
8
2021-09-29 23:59:00
66
-128
51.33

3895727
A
8
2021-09-29 23:59:00
-90
-112
47.35

3895728
A
8
2021-09-29 23:59:00
-52
132
43.88

3895729
A
8
2021-09-29 23:59:00
36
140
42.47

3895730
A
8
2021-09-29 23:59:00
132
60
40.64

3895731
A
8
2021-09-29 23:59:00
40
136
44.72

3895732
A
8
2021-09-29 23:59:00
-14
144
46.27

3895733
A
8
2021-09-29 23:59:00
-76
124
43.41

3988629
A
8
2021-09-30 11:20:00
-96
-108
42.25

ID	ID1	ID2	ACT_TIME	X	Y	Z
3895725	A	8	2021-09-29 23:59:00	66	-128	51.33
3895733	A	8	2021-09-29 23:59:00	-76	124	43.41
3895732	A	8	2021-09-29 23:59:00	-14	144	46.27
3895731	A	8	2021-09-29 23:59:00	40	136	44.72
3895730	A	8	2021-09-29 23:59:00	132	60	40.64
3895729	A	8	2021-09-29 23:59:00	36	140	42.47
3895728	A	8	2021-09-29 23:59:00	-52	132	43.88
3895727	A	8	2021-09-29 23:59:00	-90	-112	47.35
3988623	A	8	2021-09-30 11:20:00	-90	-112	47.55
3988624	A	8	2021-09-30 11:20:00	66	-128	53.49
3988629	A	8	2021-09-30 11:20:00	-96	-108	42.25