Joe Yoder wrote:
Below is demo code that averages values into time periods from multiple
tables and combines them into a single table. The first approach uses
three SQL Selects and takes about 3/4 second while the second one does the
same job with a single select but takes about 32 seconds. Is there a
better way to do it in one? Thanks in Advance - Joe
SELECT Period(Joe.Stamp) as Period, Joe.Stamp, AVG(Joe.value) as Joe,
AVG(Moe.value) as Moe;
FROM joe, Moe;
WHERE Period(Joe.Stamp) = Period(Moe.Stamp);
GROUP BY 1;
INTO CURSOR Out2
FUNCTION PERIOD
PARAMETERS m.ts
RETURN INT((m.Ts - m.TSref) / 60)
The trouble with this select is that you may have one or more in each
period in either moe or joe or both, which will not
give you the correct results. If you have 3 records in Moe and 2 in Joe
each moe record will be joined with the joe record to produce 6 rows
instead of 5. You could also have 3 in moe and none in joe and this
inner join will not show any rows in the output.
I thought you wanted to use one hour as the period for grouping?
Peter
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
