I've posted my solution but I did not appear so I am reposting it.
I found simple solution.
The test data:
INSERT INTO MYTABLE (ID, DATE_TIME, "TYPE") VALUES ('1', '01.01.2015,
00:00:00.000', 'A');
INSERT INTO MYTABLE (ID, DATE_TIME, "TYPE") VALUES ('5', '01.01.2015,
00:01:00.000', 'A');
INSERT INTO MYTABLE (ID, DATE_TIME, "TYPE") VALUES ('7', '01.01.2015,
00:02:00.000', 'A');
INSERT INTO MYTABLE (ID, DATE_TIME, "TYPE") VALUES ('24', '01.01.2015,
00:10:00.000', 'B');
INSERT INTO MYTABLE (ID, DATE_TIME, "TYPE") VALUES ('36', '01.01.2015,
00:15:00.000', 'B');
INSERT INTO MYTABLE (ID, DATE_TIME, "TYPE") VALUES ('37', '01.01.2015,
00:20:00.000', 'A');
INSERT INTO MYTABLE (ID, DATE_TIME, "TYPE") VALUES ('40', '01.01.2015,
00:25:00.000', 'A');
The query:
SELECT
A."TYPE", MIN(A.DATE_TIME) DATE_MIN, MAX(A.DATE_TIME) DATE_MAX,
(SELECT FIRST 1 D.ID FROM MYTABLE D WHERE D.ID > A.ID AND D."TYPE" <>
A."TYPE" ORDER BY D.ID ASC) FIRST_HIGHER
FROM
MYTABLE A
GROUP BY
FIRST_HIGHER, A."TYPE"
ORDER BY
DATE_MIN
Results:
A 01.01.2015, 00:00:00.000 01.01.2015, 00:02:00.000 24
B 01.01.2015, 00:10:00.000 01.01.2015, 00:15:00.000 37
A 01.01.2015, 00:20:00.000 01.01.2015, 00:25:00.000 [null]
The great thig in this solution is that I could perform SUM, AVG functions on
the parft of the results. For example if I would like to add column SPEED in
the future, I could calculate avarage speed during one "A" cluster. However, I
am afraid that perfromance will be very poor due to subquery and sorting in
it...
