I have a time series data and the interval between 10s(Let’s call this table as log_raw). For a capacity reason, I delete every data that is older than 7 days. So when the user requires data older than 7 days, I cannot provide.
But, I have summarized table which has an interval an hour(Let’s call this table log_by_hour). This table has data older than 7 days, so I came up with an idea that when the user requires older than 7-day data, provide that data with log_by_hour.
So, Is there any way I can switch the table according to the time range user requested?
I would try something like two SELECTs and add the results together - one of
them will return data, the other will not; the sum should give you whatever
data you have.
So, for example:
select r+v from (select value as r from log_raw),(select value as h from
log_by_hour) where…
Obviously the exact syntax will depend on which back-end data store you’re
using and the details of its query language.
I used UNION ALL and substr to change the table according to the time range
SELECT a.time, a.diffValue diff, "RPUSH" metric
FROM (
SELECT l.instance_id, l.created AS "time",
l.value as diff,
if( BINARY @lastInstanceId = BINARY l.instance_id, l.value - @lastValue, 0 ) as diffValue,
@lastInstanceId := l.instance_id lcmdId,
@lastValue := l.value lvalue
FROM log_raw l, (select @lastInstanceId:='',@lastValue:=0 ) lv
WHERE l.cmd_id = 7
AND l.instance_id =$instance_id
AND $__timeFilter(l.created)
AND substr('$__interval',-1)='s'
UNION ALL
SELECT l.instance_id, l.time AS "time",
l.avg as diff,
if( BINARY @lastInstanceId = BINARY l.instance_id, l.avg - @lastValue, 0 ) as diffValue,
@lastInstanceId := l.instance_id lcmdId,
@lastValue := l.avg lvalue
FROM log_by_minute l, (select @lastInstanceId:='',@lastValue:=0 ) lv
WHERE l.cmd_id = 7
AND l.instance_id =$instance_id
AND $__timeFilter(l.time)
AND substr('$__interval',-1)='m'
UNION ALL
SELECT l.instance_id, l.time AS "time",
l.avg as diff,
if( BINARY @lastInstanceId = BINARY l.instance_id, l.avg - @lastValue, 0 ) as diffValue,
@lastInstanceId := l.instance_id lcmdId,
@lastValue := l.avg lvalue
FROM log_by_hour l, (select @lastInstanceId:='',@lastValue:=0 ) lv
WHERE l.cmd_id = 7
AND l.instance_id =$instance_id
AND $__timeFilter(l.time)
AND substr('$__interval',-1) in ('h','d')
) a
ORDER BY a.time