CODEWITHSUNDEEP
mysqlsqlgroup-bywindow-functions
Mauzzz0
Mauzzz0

Reputation: 105

How to fill missing values in aggregate-by-time function

I have function (from this question) which groups values by every 5 minutes and calculate min/avg/max:

SELECT (FLOOR(clock / 300) * 300) as period_start,
       MIN(value), AVG(value), MAX(value)
FROM data 
WHERE clock BETWEEN 1200000000 AND 1200001200
GROUP BY FLOOR(clock / 300);

However, due to missing values, some five-minute periods are skipped, making the timeline inconsistent. How to make it so that in the absence of data for a certain period, the value of max / avg / min becomes 0, instead of being skipped?

For example:

If I have timestamp - value

I want to get this: (select min/avg/max, time between 1200000000 and 1200001200)
Instead of this: (time between 1200000000 and 1200001200)

My Answer:

Generate first table with required time range, and then left join this generated table on query with common group by operator. Such like this:

select * from

(select UNIX_TIMESTAMP(gen_date) as unix_date from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31') date_range_table

left join (
    SELECT (FLOOR(clock / 300) * 300) as period_start,
       MIN(value), AVG(value), MAX(value)
    FROM table
    WHERE clock BETWEEN 1483218000 AND 1514667600
    GROUP BY FLOOR(clock / 300)) data_table

on date_range_table.unix_date = data_table.period_start;

Upvotes: 0

Views: 296

Answers (4)

Mauzzz0
Mauzzz0

Reputation: 105

Alternative answer is generate first table with required time range, and then left join this generated table on query with common group by operator.

Upvotes: -1

Kemal Kaplan
Kemal Kaplan

Reputation: 1024

You can try this one;

with seq as (
select
    (step-1)* 300 + (select (FLOOR(min(clock) / 300) * 300) from data) as step
    from
        (select row_number() over() as step from data) tmp
    where
        tmp.step-1 < (select(max(clock)-min(clock))/ 300 from data))
SELECT seq.step as period_start, MIN(value), AVG(value), MAX(value)
FROM seq left join data on (seq.step=(FLOOR(clock / 300) * 300))
WHERE clock BETWEEN 1622667600 AND 1625259600
GROUP BY period_start

Upvotes: 0

Akina
Akina

Reputation: 42611

Use recursive CTE (available in MariaDB starting from 10.2.2) and generate base calendar table:

WITH RECURSIVE
cte AS ( SELECT @timestart timestart, @timestart + 300 timeend
         UNION ALL
         SELECT timestart + 300, timeend + 300 FROM cte WHERE timeend < @timeend)
SELECT cte.timestart, 
       COALESCE(MIN(value), 0) min_value, 
       COALESCE(AVG(value), 0) avg_value, 
       COALESCE(MAX(value), 0) max_value
FROM cte
LEFT JOIN example ON example.clock >= cte.timestart
                 AND example.clock < cte.timeend
GROUP BY cte.timestart;

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

Upvotes: 3

Shivam Dobariya
Shivam Dobariya

Reputation: 1

I am not very sure but here's a link which can solve your problem https://www.sqlservercurry.com/2009/06/find-missing-identity-numbers-in-sql.html

Upvotes: 0

Related Questions