Interpolate Multiseries Data In SQL

user_15213512 0
Viewed 74

I have a system that stores the data only when they are changed. So, the dataset looks like below.

data_type_id data_value inserted_at
2 240 2022-01-19 17:20:52
1 30 2022-01-19 17:20:47
2 239 2022-01-19 17:20:42
1 29 2022-01-19 17:20:42

My data frequency is every 5 seconds. So, whether there's any timestamp or not I need to get the result by assuming in this 5th-second data value the same as the previous value.

As I am storing the data that are only changed, indeed the dataset should be like below.

data_type_id data_value inserted_at
2 240 2022-01-19 17:20:52
1 30 2022-01-19 17:20:52
2 239 2022-01-19 17:20:47
1 30 2022-01-19 17:20:47
2 239 2022-01-19 17:20:42
1 29 2022-01-19 17:20:42

I don't want to insert into my table, I just want to retrieve the data like this on the SELECT statement.

Is there any way I can create this query?

PS. I have many data_types hence when the OP makes a query, it usually gets around a million rows.

EDIT: Information about server Server version: 10.3.27-MariaDB-0+deb10u1 Debian 10

The User is going to determine the SELECT DateTime. So, there's no certain between time.

As @Akina mentioned, sometimes there're some gaps between the inserted_at. The difference might be ~4seconds or ~6seconds instead of a certain 5seconds. Since it's not going to happen so frequently, It is okay to generate by ignoring this fact.

missing-data interpolation mysql sql
2 Answers

With the help of a query that gets you all the combinations of data_type_id and the 5-second moments you need, you can achieve the result you need using a subquery that gets you the closest data_value:

with recursive u as
(select '2022-01-19 17:20:42' as d
union all
select DATE_ADD(d, interval 5 second) from u
where d < '2022-01-19 17:20:52'),
v as
(select * from u cross join (select distinct data_type_id from table_name) t)
select v.data_type_id, 
(select data_value from table_name where inserted_at <= d and data_type_id = v.data_type_id
order by inserted_at desc limit 1) as data_value, 
d as inserted_at
from v

Fiddle

You can replace the recursive CTE with any query that gets you all the 5-second moments you need.

user_176450990 
WITH RECURSIVE
cte1 AS ( SELECT @start_datetime dt
          UNION ALL
          SELECT dt + INTERVAL 5 SECOND FROM cte1 WHERE dt < @end_datetime),
cte2 AS ( SELECT *,
                 ROW_NUMBER() OVER (PARTITION BY test.data_type_id, cte1.dt
                                    ORDER BY test.inserted_at DESC) rn
          FROM cte1
          LEFT JOIN test ON FIND_IN_SET(test.data_type_id, @data_type_ids) 
                        AND cte1.dt >= test.inserted_at )
SELECT * 
FROM cte2
WHERE rn = 1

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=380ad334de0c980a0ddf1b49bb6fa38e

user_101387340
Related