Query block example: Average employee sales

This example of a query uses OLAP window aggregation functions.

In this example, Informix® Warehouse Accelerator processes the join between the employee and sales tables, and then returns the result set to the Informix database server. The Informix database server then processes the OLAP functions, RANK and AVG.

SELECT e.emp_name,
           RANK() OVER (PARTITION BY region
                                 ORDER BY total_sales desc),
           AVG(sales) OVER (PARTITION BY region, year)
FROM employee e, sales s
WHERE e.emp_id = s.emp_id;