Thanks Peter and Tracy, My goal has always been to be able to adjust the length of the summary period easily. My initial period was an hour as that can be unambiguously labeled on a report with two characters. When I drop it to five minutes the jagged details are much more prominent. My live (non test) situation could have missing data. With my current code, if there is no data for an entire period in any one column that period simply is not included in the output. This functionality is okay for my current purposes but it could be enhanced in the future. (I am assuming that the outer join has this same limitation.) I ran repeated speed test and was surprised to discover that Tracy's out join and my triple select run neck and neck and that the speed between runs varies. I would have expected the execution speed to be consistent when using a SSD and that the single statement would always win. It appears that the triple select does have a slight edge. In my situation there is a variable number of data streams to be included in the output table. I build the statements in a memory variable then execute it via macro substitution so that process is simpler using separate statements. At this point I simply output an XLS file and open it in Open Office where I display it as a chart. When I can afford to spend more time I hope to find a way to do the charts in VFP as well.
Thanks again, Joe On Mon, Jun 9, 2014 at 1:48 PM, Tracy Pearson <[email protected]> wrote: > 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 > > [excessive quoting removed by server] _______________________________________________ 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/cabqednxivhegdrxauayfjptwz_fql_x4x+mrhjox+3nrvzj...@mail.gmail.com ** 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.
