4 Replies Latest reply: Oct 17, 2011 8:10 AM by Dror Svartzman RSS

    Average of the last X periods

    Dror Svartzman

      Hi,

       

      I'm trying to figure out a way to calculate, in the script, the average time for the last X Iterations. In that case, Iteration = periods.

       

      If X is 4 iterations, then the results should be as follow:

      th0, t1 = 7:34

       

      th0, t2 = 7:43

       

      See script below

      source:
      LOAD * INLINE [
          ThreadID, TaskId, Timestamp
          th0, t1, 17-10-11 7:37
          th0, t1, 18-10-11 7:38
          th0, t1, 19-10-11 7:42
          th0, t1, 20-10-11 7:39
          th0, t1, 21-10-11 7:32
          th0, t1, 22-10-11 7:37
          th0, t1, 23-10-11 7:31
          th0, t2, 17-10-2011 7:40
          th0, t2, 18-10-2011 7:52
          th0, t2, 19-10-2011 7:41
          th0, t2, 20-10-2011 7:39
          th0, t2, 21-10-2011 7:40
      ];
      
      AvgTimeTaskId:
      noconcatenate
      LOAD ThreadID, 
                 TaskId, 
                 Time(Average(frac(Timestamp))) AS Avg(Time)
      RESIDENT source
      [for the last X iterations]
      
        • Average of the last X periods

          Hi,

           

          This your solution:

           

          source:

          LOAD * INLINE [

              ThreadID, TaskId, Timestamp, Time

              th0, t1, 17-10-2011 ,7:37

              th0, t1, 18-10-2011 ,7:38

              th0, t1, 19-10-2011 ,7:42

              th0, t1, 20-10-2011 ,7:39

              th0, t1, 21-10-2011 ,7:32

              th0, t1, 22-10-2011 ,7:37

              th0, t1, 23-10-2011 ,7:31

              th0, t2, 17-10-2011 ,7:40

              th0, t2, 18-10-2011 ,7:52

              th0, t2, 19-10-2011 ,7:41

              th0, t2, 20-10-2011 ,7:39

              th0, t2, 21-10-2011 ,7:40

          ];

           

           

          AvgTimeTaskId:

          load

                    *,

                    floor(AVGTime/60)&':'&round(frac(AVGTime/60)*60)          as Time

                    ;

          load

                    ThreadID,

                    TaskId,

                    avg(if(len(Time)=5,(left(Time,2)*60) + right(Time,2),(left(Time,1)*60) + right(Time,2))) as AVGTime

          resident source

          group by ThreadID,TaskId;

          drop table source;

           

          Succes!

           

          Halmar

            • Average of the last X periods
              Dror Svartzman

              Halmar,

               

              Appericiate your prompt response.

               

              The code calculates the average for the hole subset. In my scenario i require to produce only the last X rows.

               

              In this case:

              for  th0, t2

                 th0, t2, 18-10-2011 ,7:52

                  th0, t2, 19-10-2011 ,7:41

                  th0, t2, 20-10-2011 ,7:39

                  th0, t2, 21-10-2011 ,7:40

              ( 7:43)

               

               

              for th0,t1

              th0, t1, 20-10-2011 ,7:39

                  th0, t1, 21-10-2011 ,7:32

                  th0, t1, 22-10-2011 ,7:37

                  th0, t1, 23-10-2011 ,7:31

              ( 7:34)



                • Average of the last X periods
                  Stefan Wühl

                  Hi,

                   

                  I think you could introduce a rownumber for each partition and then use a where-clause in your final avg-calculation load, like:

                   

                  source:

                  LOAD * INLINE [

                      ThreadID, TaskId, Timestamp

                      th0, t1, 17-10-11 7:37

                      th0, t1, 18-10-11 7:38

                      th0, t1, 19-10-11 7:42

                      th0, t1, 20-10-11 7:39

                      th0, t1, 21-10-11 7:32

                      th0, t1, 22-10-11 7:37

                      th0, t1, 23-10-11 7:31

                      th0, t2, 17-10-2011 7:40

                      th0, t2, 18-10-2011 7:52

                      th0, t2, 19-10-2011 7:41

                      th0, t2, 20-10-2011 7:39

                      th0, t2, 21-10-2011 7:40

                  ];

                   

                  source2:

                  LOAD *,

                  If(Peek(TaskId) = TaskId, Peek(rownumber)+1, 1) as rownumber

                  Resident source order by ThreadID, TaskId, Timestamp desc;

                   

                  drop table source;

                   

                  AvgTimeTaskId:

                  noconcatenate

                  LOAD ThreadID as ThreadID2,

                             TaskId as TaskId2,

                             Time(Avg(frac(Timestamp))) AS AverageTime

                  RESIDENT source2 where rownumber <= 4 group by ThreadID,TaskId;

                   

                  Hope this helps,

                  Stefan