SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—544517-008

9-92

Examples of MOVINGAVG

Suppose that SEQFCN has been created as:

CREATE TABLE $db.mining.seqfcn

(I1 INTEGER,TS TIMESTAMP);

Within MXCI, the ANSI alias name has been mapped as:

CREATE SQLMP ALIAS db.mining.seqfcn $db.mining.seqfcn;

The table SEQFCN has columns I1 and TS with data that is sequenced by column TS:

•

Return the average of nonnull values of a column in the current window of three

rows:

SELECT MOVINGAVG (I1,3) AS MOVINGAVG3 FROM mining.seqfcn SEQUENCE BY TS;

MOVINGAVG3

--------------------

6215

17194

16385

8281

--- 5 row(s) selected

I1 TS

6215 TIMESTAMP '1950-03-05 08:32:09'

28174 TIMESTAMP '1951-02-15 14:35:49'

null TIMESTAMP '1955-05-18 08:40:10'

4597 TIMESTAMP '1960-09-19 14:40:39'

11966 TIMESTAMP '1964-05-01 16:41:02'

Note.

1. The size the history buffer must not equal to the total result of the query. The required size

of the history buffer might be the largest window size for computing a MOVINGXXX

function, an OFFSET function or a ROWS SICE function.

2. Examining a large history buffer for a false condition wil be an expensive operation. Such

operation cannot be parallelized. Ex: ROWS SINCE function.