3

I'm using MariaDB 10.6 and have a table:

CREATE TABLE velocities (
  `state` int(8) NOT NULL,
  `timestamp` datetime NOT NULL,
  `velocity` decimal(5,2) NOT NULL,
  `name` varchar(15) NOT NULL
) DEFAULT CHARSET=utf8;

Each row describes the velocity for an object at a specific timestamp. Together multiple rows describe a period. The state column may be -1, -2, -3 or any number above 0. If state is -1 it means it's a start of a period, if it's -2 it means we are in the middle of a period and if it's -3 it means it's the last timestamp for a period. If state is a number above 0 it means the period has been assigned an id. The table has multiple periods in it and they may be overlapping between different names but not overlapping with the same name.

Now I want to make a query that returns one row per period with start_time, end_time, max_velocity_time, velocity_at_start, velocity_at_end, max_velocity.

Example data

INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:01", 2, "FOO");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:02", 3, "FOO");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:03", 2, "FOO");    
    
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-1, "2021-01-01 00:00:00", 3, "BAZ");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:01", 4, "BAZ");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:02", 5, "BAZ");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:03", 6, "BAZ");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-3, "2021-01-01 00:00:04", 2, "BAZ");

INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-1, "2021-01-01 00:00:02", 4, "BAR");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:03", 7, "BAR");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:04", 8, "BAR");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-2, "2021-01-01 00:00:05", 10, "BAR");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (-3, "2021-01-01 00:00:06", 2, "BAR");
     
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (42, "2021-01-01 00:00:07", 5, "BAR");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (42, "2021-01-01 00:00:08", 7, "BAR");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (42, "2021-01-01 00:00:09", 10, "BAR");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (42, "2021-01-01 00:00:10", 17, "BAR");
INSERT INTO velocities (state, timestamp, velocity, name) VALUES
    (42, "2021-01-01 00:00:11", 2, "BAR");

Given this data I would like the result to look like:

booked_id name start_time end_time max_velocity_time start_time_velocity end_time_velocity max_velocity
null BAZ 2021-01-01 00:00:00 2021-01-01 00:00:04 2021-01-01 00:00:03 3.00 2.00 6.00
null FOO 2021-01-01 00:00:01 2021-01-01 00:00:03 2021-01-01 00:00:02 2.00 2.00 3.00
null BAR 2021-01-01 00:00:02 2021-01-01 00:00:06 2021-01-01 00:00:05 4.00 2.00 10.00
null BAR 2021-01-01 00:00:07 2021-01-01 00:00:11 2021-01-01 00:00:10 5.00 2.00 17.00

First attempt

My first attempt took inspiration from https://stackoverflow.com/questions/1136597/group-by-for-continuous-rows-in-sql and I got it to work in MySQL 8.0 but it doesn't work in MariaDB 10.6 where I need it. The query I got working was:

WITH cte AS (
  SELECT
    @r := @r
      + (
        CASE 
          WHEN @state > 0 THEN @state != v.state
          WHEN @state - v.state < 0 THEN 1
          ELSE 0
        END
      ) AS id,
    @state := state AS _,
    v.name,
    v.timestamp,
    v.velocity,
    (CASE WHEN v.state > 0 THEN v.state ELSE NULL END) AS booked_id
  FROM (SELECT @r := 0, @state := 0) vars, velocities v
  ORDER BY v.name, v.timestamp
),
inner_max_velocity_cte AS (SELECT id, MAX(velocity) AS velocity FROM cte GROUP BY id),
max_velocity_cte AS (
  SELECT cte.id, cte.timestamp, cte.velocity
  FROM cte
  INNER JOIN inner_max_velocity_cte x ON cte.id = x.id AND cte.velocity = x.velocity
),
inner_end_time_cte AS (SELECT id, MAX(timestamp) AS timestamp FROM cte GROUP BY id),
end_time_cte AS (
  SELECT cte.id, cte.timestamp, cte.velocity
  FROM cte
  INNER JOIN inner_end_time_cte x ON cte.id = x.id AND cte.timestamp = x.timestamp
)
SELECT
  cte.booked_id,
  cte.name,
  cte.timestamp AS start_time,
  end_time_cte.timestamp AS end_time,
  max_velocity_cte.timestamp AS max_velocity_time,
  cte.velocity AS start_time_velocity,
  end_time_cte.velocity AS end_time_velocity,
  max_velocity_cte.velocity AS max_velocity
FROM cte
  LEFT OUTER JOIN max_velocity_cte ON max_velocity_cte.id = cte.id
  LEFT OUTER JOIN end_time_cte ON end_time_cte.id = cte.id
GROUP BY cte.id
ORDER BY start_time

Someone suggested I could do this with window functions instead but I'm not sure how to get that to work?

2
  • FOO doesn't have a -1 row, how does that work? Oct 1 at 6:39
  • @Charlieface since a common constraint to the query will be to get all periods between a start and an end time. In that case the -1 and/or the -3 will be outside the scope.
    – samuel02
    Oct 1 at 7:39
3

This is a classic gaps-and-islands problem.

There are many solutions. A standard method is to define starting or ending points for each section, then use a windowed conditional COUNT to number the islands. Then you simply group by this grouping number.

There are added complications here

  • We need to check if the previous row is negative but this is not
  • You have an island which does not start with -1, therefore we also need to check for a null previous value
  • We need to use more window functions to get the final values per group. I've avoided multiple row-numbers there in order to avoid extra sorts, so we use LEAD and LAG for that
WITH PrevValues AS (
    SELECT *,
      LAG(state) OVER (PARTITION BY name ORDER BY timestamp) AS prevValue
    FROM velocities
),
Groupings AS (
    SELECT *,
      COUNT(CASE WHEN state = -1 OR prevValue IS NULL OR (prevValue < 0 AND state >= 0) THEN 1 END)
        OVER (PARTITION BY name ORDER BY timestamp ROWS UNBOUNDED PRECEDING) AS GroupId
    FROM PrevValues
),
PerGroup AS (
    SELECT *,
      IFNULL(LAG(GroupId) OVER (PARTITION BY name ORDER BY timestamp), -1) AS prevGroup,
      IFNULL(LEAD(GroupId) OVER (PARTITION BY name ORDER BY timestamp), -1) AS nextGroup,
      ROW_NUMBER() OVER (PARTITION BY name, GroupId ORDER BY velocity DESC) AS rnVelocity
    FROM Groupings
)
SELECT
  (CASE WHEN state > 0 THEN state END) AS booked_id,
  name,
  MIN(timestamp) AS start_time,
  MAX(timestamp) AS end_time,
  MIN(CASE WHEN rnVelocity = 1 THEN timestamp END) AS max_velocity_time,
  MIN(CASE WHEN prevGroup <> GroupId THEN velocity END) AS start_time_velocity,
  MIN(CASE WHEN nextGroup <> GroupId THEN velocity END) AS end_time_velocity,
  MAX(velocity) AS max_velocity
FROM PerGroup
GROUP BY
  name,
  GroupId;

db<>fiddle

2
  • Thanks a lot! This works perfectly. Follow-up question: in practice the velocities table is quite big and there will be a where clause in the first CTE on f_time. What index should I add to make it fast? I assumed an index on timestamp, name but it doesn't seem to use it?
    – samuel02
    Oct 1 at 9:42
  • 1
    You probably want name, timestamp (because you first partition by name then sort by timestamp) and it still might not use it because of the lack of INCLUDE column support. If the where is an equality predicate then you want that column first in the index before any others Oct 1 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.