SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-96
Examples of MOVINGSTDDEV
Examples of MOVINGSTDDEV
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 standard deviation of nonnull values of a column in the current window
of three rows:
SELECT MOVINGSTDDEV (I1,3) AS MOVINGSTDDEV3
FROM mining.seqfcn
SEQUENCE BY TS;
MOVINGSTDDEV3
-------------------------
0.00000000000000000E+000
1.55273578080753976E+004
1.48020166531456112E+004
1.51150124820766640E+004
6.03627542446499008E+003
--- 5 row(s) selected.
Note that you can use the CAST function for display purposes. For example:
SELECT CAST(MOVINGSTDDEV (I1,3) AS DEC (18,3))
FROM mining.seqfcn
SEQUENCE BY TS;
(EXPR)
--------------------
.000
15527.357
14802.016
15115.012
6036.275
--- 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'