I need to calculate the average values for a tag over minutes interval (between 1 and 2441) over a date range.

For instance, I may be asked to calculate the 1 minute average value for tag name "Cusman21" over the past 3 days.

I'm wondering what is the best way to accomplish this - do I query the piarchive..piavg table? Or should I just

craft a SQL query against the piarchive.picomp table?

I have searched through the forums and not found any posts that might help. I have seen a few references to queries

against the piavg table, but nobody here has ever queried the piavg table before and I haven't found documents

describing best practices querying data from piavg.

any help would be greatly appreciated.

Hi Derek,

Using the AVG operator as defined by the ANSI 92 standard returns the average of the values in a group, excluding Null values. Using such an operator with compressed values from the archives will get the mean of all the data returned which can be very different from the time-weighted average. The PIARCHIVE..PIAVG table lets you choose how you compound the average by indicating the calculation basis such as: event-weighted, time-weighted, time-weighted in continuous, time-weighted in discrete, event-weighted excluding the most recent event, event-weighted excluding the earliest recent event and vent-weighted excluding both ends. If there is no compression applied to the tag, the mean (calculated by the AVG operator) and the time-weighted average will give the same result as all events in the group have the same weight.

The PIARCHIVE..PIAVG table allows the definition of an interval from which you want to calculate the average, and more you can get an asymmetrical interval if you join this table with another one such as the PIARCHIVE..PICOMP2. Using the PIARCHIVE..PIAVG table involves a simpler logic to create the calculation interval than using the AVG operator with the GROUP BY one needed to generate the group on which performing the aggregation.

Also another advantage of using the PIARCHIVE..PIAVG over the PIARCHIVE..PICOMP2 (or PIARCHIVE..PICOMP) table is on where the calculation takes place. The first one performs the calculation completely on the PI Server and only returns the result, the second returns all the events necessary to compound the average on the client where the PI OLEDB provider is installed. This can seriously hit the performance of calculating the average if you do this over millions of events over several tags.

Note: Using the PIARCHIVE..PICOMP2 over the PIARCHIVE..PICOMP table is preferred as this table is fully updatable and is tuned for better performance.

Performing a query to the PIARCHIVE..PIAVG table would look like the following:

Performing a query to the PIARCHIVE..PICOMP table and compounding the average would look like the following, you must assume there are events every minute in archive:

If you do not have events every minute you could perform the same by utilizing the PIARCHIVE..PIINTERP2 table to get interpolated values every minute and compound the average thereafter:

To summarize, the advantages of using PIARCHIVE..PIAVG over the PIARCHIVE..PICOMP2 or the PIARCHIVE..PIINTERP2 table are:- Capable of choosing the calculation basis;- Calculation takes place on the PI Server;- Define sub-intervals of calculation;- Define asymmetrical sub-intervals of calculation.If you want to learn more, I invite you to take a look to the PI OLEDB Data Provider for the PI System user guide or the PI Application Development Course workbook which is available in the Training Center building of OSIsoft vCampus.