This example shows the use of the EXPAND ON clause with grouping on a span of entries from the select list.
First create the stock table.
CREATE SET TABLE stock ( stock_id INTEGER, stock_quantity INTEGER, begin_end_date PERIOD(DATE)) PRIMARY INDEX (stockid);
The stock table contains the following rows.
stock_id | stock_quantity | begin_end_date |
---|---|---|
100 | 200 | 2005-10-10,2005-11-15 |
101 | 20 | 2005-06-01,2005-08-31 |
This example shows how you can compute a weighted average for the stock_quantity column on a monthly basis.
Assume that udf_agspan is an aggregate UDF that adds the stock quantity for given month of a year and then divides the sum by the number of days in that month. This provides a different result when compared to the AVG function when the row is not spanning the whole month.
SELECT udf_agspan(stock_quantity, EXTRACT(YEAR FROM BEGIN(expdcol)), EXTRACT(MONTH FROM BEGIN(expdcol))) (FORMAT ‘-----9.999’) AS wavg, EXTRACT(YEAR FROM BEGIN(expdcol) AS yr, EXTRACT(MONTH FROM BEGIN(expdcol) AS mn, stock_id FROM (SELECT stock.*, expdcol FROM stock EXPAND ON begin_end_date AS expdcol BY INTERVAL '1'DAY) AS dt GROUP BY 2,3,4;
This statement returns the following rows.
wavg | yr | mn | stock_id |
141.935 | 2005 | 10 | 100 |
93.333 | 2005 | 11 | 100 |
20.000 | 2005 | 06 | 101 |
20.000 | 2005 | 07 | 101 |
19.355 | 2005 | 08 | 101 |