Subject Re: Is it possible to do this with window function?
Author

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...