Select query from multiple tables

Hi

I have done easy reporting query which gets hourly values from partition table and it is working nicely.
Only “problem” is that if I want to get values between times which are stored in different partition table my query does not work.
Is there any easy way to get values from multiple table, or how this should handled?

Here is my query:
Now I have dynamic properties called {Root Container.Table.table} which is making table name from choosed start time. This works nice if start time and end time is between one partition but if end time is stored different table result is wrong.

SELECT
date_format(FROM_UNIXTIME(t_stamp/1000),’%d.%m.%y %H:00’) as ‘Time’,
avg(floatvalue) as ‘Average’,
sum(floatvalue) as ‘Sum’,
min(floatvalue) as ‘Min’,
max(floatvalue) as ‘Max’ ,
count(floatvalue) as ‘Count’
FROM {Root Container.Table.table}
where dataintegrity=192
and tagid = ‘{Root Container.Dropdown.selectedValue}’
and (t_stamp/1000) > UNIX_TIMESTAMP(’{Root Container.Date Range.startDate}’)
and (t_stamp/1000) < UNIX_TIMESTAMP(’{Root Container.Date Range.endDate}’)
GROUP BY YEAR(FROM_UNIXTIME(t_stamp/1000)),MONTH(FROM_UNIXTIME(t_stamp/1000)),DATE(FROM_UNIXTIME(t_stamp/1000)),HOUR(FROM_UNIXTIME(t_stamp/1000))

Br
Tommi Vahtera
THT Control Oy

Well, querying across multiple partitions is hard to do manually. What you need to do is create a query that combines multiple partitions into one and then applies the grouping and aggregate functions afterwords. It would look something like:SELECT t_stamp, avg(floatvalue), ... FROM ((SELECT t_stamp, floatvalue FROM parition1 WHERE dataintegrity = 192 AND ...) UNION (SELECT t_stamp, floatvalue FROM parition2 WHERE dataintegrity = 192 AND ...) ) result GROUP BY ...It is a complicated query to write.

The other option is to use thesystem.tag.queryTagHistoryfunction to return the values and in scripting create a new dataset out of it applying the grouping and aggregate functions manually. Hope this helps.

If by “partition” you really mean a SQLTags History table, I don’t think that your query is going to work like you want.

The main problem is that SQLTags History only stores value on-change, so I don’t think that using the “avg” aggregate query is going to give back what you want. The aggregate doesn’t have any knowledge of time. If the value is 10 at 12pm, and 20 at 12:59pm, and you query from 12-1pm, your average will be 15. But, the reality is much different: the average should really be more like 10, because that’s what it was for the majority of the time.

In the past, customers would use SQLBridge groups to log the data they wanted at regular intervals. With regular data, you can use these aggregate functions. That’s what I would recommend you use in this case.

We have a variety of outstanding feature requests related to this that we intend to get to soon. Above all, the ability to get average, sum, etc. from the sqltags history query interface. Also, the ability get get SQLTags History to store data at regular intervals. In the meantime though, I would recommend using the SQLBridge- history is a big part of what it’s for.

Regards,

Hi

You are right about calculations and we did understood that.
This “report” is used more or less as quick report, like report look from trend and there this works nicely.
In this case we anyway log values at least 1 minute resolution so average calculation is also quite good.

I made it without using multiple tables so customer will choose one month table as source before making more filters.
It works great and make what it needs.

In real reports we use Transaction Groups and “result” tables where is hourly based data.

Thanks
Tommi Vahtera
THT Control Oy