Postgres 9.6

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE test (
    mobile_id int,
    state text,
    time timestamp without time zone,
    region text
);

INSERT INTO test (mobile_id, state, time, region ) VALUES
(1, 'active', TIMESTAMP '2018-08-09 15:00:00', 'EU'),  
(1, 'active', TIMESTAMP '2018-08-09 16:00:00', 'EU'),
(1, 'no_signal', TIMESTAMP '2018-08-09 17:00:00', 'EU'),
(1, 'no_signal', TIMESTAMP '2018-08-09 18:00:00', 'EU'),
(1, 'no_signal', TIMESTAMP '2018-08-09 19:00:00', 'EU'),
(1, 'active', TIMESTAMP '2018-08-09 20:00:00', 'EU'),
(1, 'inactive', TIMESTAMP '2018-08-09 21:00:00', 'EU'),
(1, 'active', TIMESTAMP '2018-08-09 22:00:00', 'EU'),
(1, 'active', TIMESTAMP '2018-08-09 23:00:00', 'EU'),
(2, 'active', TIMESTAMP '2018-08-10 00:00:00', 'EU'),
(2, 'no_signal', TIMESTAMP '2018-08-10 01:00:00', 'EU'),
(2, 'active', TIMESTAMP '2018-08-10 02:00:00', 'EU'),
(2, 'no_signal', TIMESTAMP '2018-08-10 03:00:00', 'EU'),
(2, 'no_signal', TIMESTAMP '2018-08-10 04:00:00', 'EU'),
(2, 'no_signal', TIMESTAMP '2018-08-10 05:00:00', 'EU'),
(2, 'no_signal', TIMESTAMP '2018-08-10 06:00:00', 'EU'),
(3, 'active', TIMESTAMP '2018-08-10 07:00:00', 'SA'),
(3, 'active', TIMESTAMP '2018-08-10 08:00:00', 'SA'),
(3, 'no_signal', TIMESTAMP '2018-08-10 09:00:00', 'SA'),
(3, 'no_signal', TIMESTAMP '2018-08-10 10:00:00', 'SA'),
(3, 'inactive', TIMESTAMP '2018-08-10 11:00:00', 'SA'),
(3, 'inactive', TIMESTAMP '2018-08-10 12:00:00', 'SA'),
(3, 'no_signal', TIMESTAMP '2018-08-10 13:00:00', 'SA');

SELECT * FROM test;


mobile_id
state
time
region


1
active
2018-08-09 15:00:00
EU

1
active
2018-08-09 16:00:00
EU

1
no_signal
2018-08-09 17:00:00
EU

1
no_signal
2018-08-09 18:00:00
EU

1
no_signal
2018-08-09 19:00:00
EU

1
active
2018-08-09 20:00:00
EU

1
inactive
2018-08-09 21:00:00
EU

1
active
2018-08-09 22:00:00
EU

1
active
2018-08-09 23:00:00
EU

2
active
2018-08-10 00:00:00
EU

2
no_signal
2018-08-10 01:00:00
EU

2
active
2018-08-10 02:00:00
EU

2
no_signal
2018-08-10 03:00:00
EU

2
no_signal
2018-08-10 04:00:00
EU

2
no_signal
2018-08-10 05:00:00
EU

2
no_signal
2018-08-10 06:00:00
EU

3
active
2018-08-10 07:00:00
SA

3
active
2018-08-10 08:00:00
SA

3
no_signal
2018-08-10 09:00:00
SA

3
no_signal
2018-08-10 10:00:00
SA

3
inactive
2018-08-10 11:00:00
SA

3
inactive
2018-08-10 12:00:00
SA

3
no_signal
2018-08-10 13:00:00
SA



SELECT 23


-- The subquery

SELECT 
    *, 
    SUM(is_diff) OVER (ORDER BY time) 
FROM (
    SELECT 
        *,
        CASE WHEN state = lag(state) over (order by time) THEN 0 ELSE 1 END as is_diff
    FROM test 
)s


mobile_id
state
time
region
is_diff
sum


1
active
2018-08-09 15:00:00
EU
1
1

1
active
2018-08-09 16:00:00
EU
0
1

1
no_signal
2018-08-09 17:00:00
EU
1
2

1
no_signal
2018-08-09 18:00:00
EU
0
2

1
no_signal
2018-08-09 19:00:00
EU
0
2

1
active
2018-08-09 20:00:00
EU
1
3

1
inactive
2018-08-09 21:00:00
EU
1
4

1
active
2018-08-09 22:00:00
EU
1
5

1
active
2018-08-09 23:00:00
EU
0
5

2
active
2018-08-10 00:00:00
EU
0
5

2
no_signal
2018-08-10 01:00:00
EU
1
6

2
active
2018-08-10 02:00:00
EU
1
7

2
no_signal
2018-08-10 03:00:00
EU
1
8

2
no_signal
2018-08-10 04:00:00
EU
0
8

2
no_signal
2018-08-10 05:00:00
EU
0
8

2
no_signal
2018-08-10 06:00:00
EU
0
8

3
active
2018-08-10 07:00:00
SA
1
9

3
active
2018-08-10 08:00:00
SA
0
9

3
no_signal
2018-08-10 09:00:00
SA
1
10

3
no_signal
2018-08-10 10:00:00
SA
0
10

3
inactive
2018-08-10 11:00:00
SA
1
11

3
inactive
2018-08-10 12:00:00
SA
0
11

3
no_signal
2018-08-10 13:00:00
SA
1
12



SELECT 23


-- The main query

SELECT DISTINCT
    mobile_id,
    first_value(time) over (partition by ranked order by ranked, time) as start_time,
    first_value(time) over (partition by ranked order by ranked, time desc) as end_time,
    region
FROM (
    SELECT 
        *, 
        SUM(is_diff) OVER (ORDER BY time) as ranked
    FROM (
        SELECT 
            *,
            CASE WHEN state = lag(state) over (order by time) THEN 0 ELSE 1 END as is_diff
        FROM test 
    )s
)s
WHERE state = 'no_signal'


mobile_id
start_time
end_time
region


1
2018-08-09 17:00:00
2018-08-09 19:00:00
EU

2
2018-08-10 01:00:00
2018-08-10 01:00:00
EU

2
2018-08-10 03:00:00
2018-08-10 06:00:00
EU

3
2018-08-10 09:00:00
2018-08-10 10:00:00
SA

3
2018-08-10 13:00:00
2018-08-10 13:00:00
SA



SELECT 5


-- all together with the diff calculation

SELECT 
    *, 
    EXTRACT(epoch from (end_time - start_time)) / 60 as diff
FROM (

    SELECT DISTINCT
        mobile_id,
        first_value(time) over (partition by ranked order by ranked, time) as start_time,
        first_value(time) over (partition by ranked order by ranked, time desc) as end_time,
        region
    FROM (
        SELECT 
            *, 
            SUM(is_diff) OVER (ORDER BY time) as ranked
        FROM (
            SELECT 
                *,
                CASE WHEN state = lag(state) over (order by time) THEN 0 ELSE 1 END as is_diff
            FROM test 
        )s
    )s
    WHERE state = 'no_signal'
)s


mobile_id
start_time
end_time
region
diff


1
2018-08-09 17:00:00
2018-08-09 19:00:00
EU
120

2
2018-08-10 01:00:00
2018-08-10 01:00:00
EU
0

2
2018-08-10 03:00:00
2018-08-10 06:00:00
EU
180

3
2018-08-10 09:00:00
2018-08-10 10:00:00
SA
60

3
2018-08-10 13:00:00
2018-08-10 13:00:00
SA
0



SELECT 5

mobile_id	state	time	region
1	active	2018-08-09 15:00:00	EU
1	active	2018-08-09 16:00:00	EU
1	no_signal	2018-08-09 17:00:00	EU
1	no_signal	2018-08-09 18:00:00	EU
1	no_signal	2018-08-09 19:00:00	EU
1	active	2018-08-09 20:00:00	EU
1	inactive	2018-08-09 21:00:00	EU
1	active	2018-08-09 22:00:00	EU
1	active	2018-08-09 23:00:00	EU
2	active	2018-08-10 00:00:00	EU
2	no_signal	2018-08-10 01:00:00	EU
2	active	2018-08-10 02:00:00	EU
2	no_signal	2018-08-10 03:00:00	EU
2	no_signal	2018-08-10 04:00:00	EU
2	no_signal	2018-08-10 05:00:00	EU
2	no_signal	2018-08-10 06:00:00	EU
3	active	2018-08-10 07:00:00	SA
3	active	2018-08-10 08:00:00	SA
3	no_signal	2018-08-10 09:00:00	SA
3	no_signal	2018-08-10 10:00:00	SA
3	inactive	2018-08-10 11:00:00	SA
3	inactive	2018-08-10 12:00:00	SA
3	no_signal	2018-08-10 13:00:00	SA