avg/Avg

avg/Avg — Static and dynamic aggregate functions for average (mean)

SYNOPSIS

Avg(expr)

avg(expr)

DESCRIPTION

Computes a value for each record returned by the query predicate or from the base table and returns the average (mean) of the values.

  • For static aggregation, aggregates refresh as data in the active (source) table is updated.

  • For dynamic aggregation, aggregates refresh as rows are returned by the query predicate.

Expression expr must be a StreamBase expression of numeric type. The returned average is a value of that type.

The function name is case-sensitive. The Title-cased syntax is for static (table-based) aggregation. The lower-cased syntax is for dynamic (query-based) aggregation. See Data Aggregation for more information on aggregation in LiveView.

EXAMPLES

This example shows you how to use dynamic aggregation to find the average price of a group of items by issuing a query against a running LiveView server.

Load and run the Hello LiveView sample as delivered with LiveView. Follow these steps:

  1. Start StreamBase Studio in the SB Authoring perspective.

  2. Load the Hello LiveView sample.

    1. Select File → Load StreamBase Sample from Studio's top-level menu.

    2. In the Load StreamBase Projects dialog, open the StreamBase LiveView category.

    3. Select the sample whose description is Shows a simple Hello World application and press OK.

    The Hello LiveView sample loads into Studio with the project name sample_lv-helloliveview.

  3. In the Package Explorer view, select the name of the project, right-click, and from the context menu, select Run As → StreamBase LiveView Project. The Console view shows several messages as the LiveView Server compiles the project and starts.

  4. When you see message All tables have been loaded in the Console view, start LiveView Desktop:

    • On Windows, run Start → All Programs → StreamBase LiveView n.m → LiveView Desktop.

    • On Linux, run the following command: 

      /opt/streambase/liveview/desktop/liveview &

    This opens the LiveView Desktop start dialog.

  5. In the LiveView Desktop start dialog, select the radio button for Download and click Select.

  6. Select the workspace named Hello LiveView and version Latest (1.0).

  7. Click OK. This returns you to the LiveView Desktop start dialog. The Download field now contains Hello LiveView (Latest).

  8. Click OK.

Now you can create a query that finds the average of the lastSoldPrice field from the ItemsSales table, grouping by category and color. Follow these steps:

  1. Select the ItemsSales table from the Tables pane of the LiveView Tables view.

  2. In the Query on ItemsSales pane, enter the following:

    1. In the Select window, enter: 

      category AS myCategory, Item as MyItem, avg(lastSoldPrice) AS avgPrice

    2. In the Query window, enter the following: 

      group by Item, category

    3. Click Open Query.

The query results open, by default, in a grid view. The avgPrice field is the average of the the lastSoldPrice field in the Items table. LiveView Server recalculates the average whenever the rows returned by the query predicate change.

This example shows an lvconf file that uses static aggregation configures the average price and total quantity of a group of items. This code is contained in the lvconf file for the ItemsSales table from the same Hello LiveView sample as used in the previous sample. 

<data-sources>
  <data-source>
    <aggregation table-ref="ItemsSales">
      <field-map>
          <field ref="category">category</field>
           <!-- Note that the expressions for these fields are not StreamBase
                Expression Language expressions. Refer to the LiveView
                documentation on aggregation functions to know which are currently
                available for use. -->
          <field ref="quantityAvg">Avg(quantityRemaining)</field>
          <field ref="priceAvg">Avg(lastSoldPrice)</field>
          <field ref="totalSales">Count()</field>
          <field ref="totalInventory">Avg(lastSoldPrice*quantityRemaining)</field>
      </field-map>
   </aggregation>
 </data-source>
</data-sources>