Skip to content

Instantly share code, notes, and snippets.

@savitoh
Created November 15, 2021 01:06
Show Gist options
  • Save savitoh/19295a681ff52aca8bea33f547d551d8 to your computer and use it in GitHub Desktop.
Save savitoh/19295a681ff52aca8bea33f547d551d8 to your computer and use it in GitHub Desktop.
Retrieve consecutives values group by status. Tested in Oracle 11gR2. http://www.sqlfiddle.com/#!4/fc98d/2
-- DDL
CREATE TABLE position (
vehicle int,
start_date date,
ignition int,
speed int
);
-- DML
INSERT
ALL INTO position (vehicle, start_date, ignition, speed)
VALUES
(
1,
TO_DATE('13/11/2021 10:30', 'dd/mm/yyyy hh:mi'),
1,
10
) INTO position (vehicle, start_date, ignition, speed)
VALUES
(
1,
TO_DATE('13/11/2021 10:32', 'dd/mm/yyyy hh:mi'),
1,
8
) INTO position (vehicle, start_date, ignition, speed)
VALUES
(
1,
TO_DATE('13/11/2021 10:38', 'dd/mm/yyyy hh:mi'),
0,
0
) INTO position (vehicle, start_date, ignition, speed)
VALUES
(
1,
TO_DATE('13/11/2021 10:40', 'dd/mm/yyyy hh:mi'),
0,
0
) INTO position (vehicle, start_date, ignition, speed)
VALUES
(
1,
TO_DATE('13/11/2021 10:42', 'dd/mm/yyyy hh:mi'),
1,
15
) INTO position (vehicle, start_date, ignition, speed)
VALUES
(
1,
TO_DATE('13/11/2021 10:31', 'dd/mm/yyyy hh:mi'),
1,
6
) INTO position (vehicle, start_date, ignition, speed)
VALUES
(
1,
TO_DATE('13/11/2021 10:34', 'dd/mm/yyyy hh:mi'),
1,
3
) INTO position (vehicle, start_date, ignition, speed)
VALUES
(
1,
TO_DATE('13/11/2021 10:36', 'dd/mm/yyyy hh:mi'),
1,
3
)
SELECT
*
FROM
dual;
-- Retrieve consecutives values group by status
select
to_char(min(start_date), 'hh:mi') as start_time,
to_char(max(start_date), 'hh:mi') as end_time,
status as status,
count(*) as qtd
from
(
select
position.*,
CASE
WHEN position.ignition = 1 AND position.speed > 3 THEN 'Mov'
WHEN position.ignition = 1 AND position.speed <= 3 THEN 'On/Stop'
ELSE 'Stop'
END as status,
row_number() over (
order by
start_date
) as seqnum,
row_number() over (
partition by (
CASE
WHEN position.ignition = 1 AND position.speed > 3 THEN 'Mov'
WHEN position.ignition = 1 AND position.speed <= 3 THEN 'On/Stop'
ELSE 'Stop'
END
)
order by
start_date
) as seqnum_2
from
position
) base
group by
status,
(seqnum - seqnum_2)
order by
start_time;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment