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

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-90

Considerations for MOVINGCOUNT

No DISTINCT Clause

The MOVINGCOUNT sequence function is defined differently from the COUNT

aggregate function. If you specify DISTINCT for the COUNT aggregate function,

duplicate values are eliminated before COUNT is applied. Note that you cannot specify

DISTINCT for the MOVINGCOUNT sequence function; duplicate values are counted.

Examples of MOVINGCOUNT

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 number of nonnull values of a column in the current window of three

rows:

SELECT MOVINGCOUNT (I1,3) AS MOVINGCOUNT3

FROM mining.seqfcn

SEQUENCE BY TS;

MOVINGCOUNT3

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

--- 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'