Subject | Re: Is it possible to do this with window function? |
---|---|
Author | |
Post date | 2015-05-03T11:36:33Z |
I think I've found ellegant and simple solution. However, I am afraid it might be very slow for large amount of data :(
Test data with holes in sequence:
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
And the result:
TYPE DATE_MIN DATE_MAX FIRST_HIGHER
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]
But as I've said I am afraid that the subquery will kill the performance here.
Test data with holes in sequence:
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
And the result:
TYPE DATE_MIN DATE_MAX FIRST_HIGHER
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]
But as I've said I am afraid that the subquery will kill the performance here.