Joe Yoder wrote on 2014-06-09: > 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 > > m.TSref = DATETIME() > CREATE CURSOR joe (Stamp T, Value n(4)) > FOR nn = 1 TO 100000 > INSERT INTO joe VALUES (m.TSref + nn, 1000*RAND()) > NEXT > > CREATE CURSOR Moe (Stamp T, Value n(4)) > FOR nn = 1 TO 100000 > INSERT INTO moe VALUES (m.TSref + nn, 1000*RAND()) > NEXT > m.strt = SECONDS() > SELECT Period(Joe.Stamp) as Period, Joe.Stamp, AVG(Joe.value) as Joe; > FROM joe; > GROUP BY 1; > INTO CURSOR Tmp1 > SELECT Period(Moe.Stamp) as Period, Moe.Stamp, AVG(Moe.value) as Moe; > FROM Moe; > GROUP BY 1; > INTO CURSOR Tmp2 > SELECT Tmp1.Period, Tmp1.stamp, Joe, Moe; > FROM Tmp1, Tmp2; > WHERE Tmp1.Period = Tmp2.period; > into cursor Out1 > WAIT WINDOW SECONDS() - m.Strt > > m.strt = SECONDS() > 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 > WAIT WINDOW SECONDS() - m.Strt > > FUNCTION PERIOD > PARAMETERS m.ts > RETURN INT((m.Ts - m.TSref) / 60) >
Joe, Does this work for you? SELECT Joe1.Period, Joe1.Joe, ; moe1.Moe, moe1.period as moeperiod FROM ; (select Period(Joe.Stamp) as Period, ; AVG(Joe.Value) as Joe FROM Joe GROUP BY 1) as Joe1 ; LEFT OUTER JOIN ; (select Period(Moe.Stamp) as Period, ; AVG(Moe.Value) as Moe FROM Moe GROUP BY 1) as Moe1 ; ON joe1.Period = moe1.Period I took out the Stamp field, since it errors in VFP 9 that I'm using. "SQL: GROUP BY clause is missing or invalid." (1807) Tracy Pearson PowerChurch Software _______________________________________________ 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.
