BNF grammar for OLAP functions

The following Backus-Naur Form grammar outlines the specific syntactic support for the various ANSI SQL analytic functions, many of which are implemented in Sybase IQ.

Grammar rule 1

<SELECT LIST EXPRESSION> ::=
  <EXPRESSION>
  | <GROUP BY EXPRESSION>
  | <AGGREGATE FUNCTION>
  | <GROUPING FUNCTION>
  | <TABLE COLUMN>
  | <WINDOWED TABLE FUNCTION>

Grammar rule 2

<QUERY SPECIFICATION> ::=
  <FROM CLAUSE>
  [ <WHERE CLAUSE> ]
  [ <GROUP BY CLAUSE> ]
  [ <HAVING CLAUSE> ]
  [ <WINDOW CLAUSE> ]
[ <ORDER BY CLAUSE> ]

Grammar rule 3

<ORDER BY CLAUSE> ::= <ORDER SPECIFICATION>

Grammar rule 4

<GROUPING FUNCTION> ::=
  GROUPING <LEFT PAREN> <GROUP BY EXPRESSION> 
  <RIGHT PAREN>

Grammar rule 5

<WINDOWED TABLE FUNCTION> ::=
  <WINDOWED TABLE FUNCTION TYPE> OVER <WINDOW NAME OR
  SPECIFICATION>

Grammar rule 6

<WINDOWED TABLE FUNCTION TYPE> ::=
  <RANK FUNCTION TYPE> <LEFT PAREN> <RIGHT PAREN>
  | ROW_NUMBER <LEFT PAREN> <RIGHT PAREN>
  | <WINDOW AGGREGATE FUNCTION>

Grammar rule 7

<RANK FUNCTION TYPE> ::=
  RANK | DENSE RANK | PERCENT RANK | CUME_DIST

Grammar rule 8

<WINDOW AGGREGATE FUNCTION> ::=
  <SIMPLE WINDOW AGGREGATE FUNCTION>
  | <STATISTICAL AGGREGATE FUNCTION>

Grammar rule 9

<AGGREGATE FUNCTION> ::=
  <DISTINCT AGGREGATE FUNCTION>
  | <SIMPLE AGGREGATE FUNCTION>
  | <STATISTICAL AGGREGATE FUNCTION>

Grammar rule 10

<DISTINCT AGGREGATE FUNCTION> ::=
  <BASIC AGGREGATE FUNCTION TYPE> <LEFT PAREN>
  <DISTINCT> <EXPRESSION> <RIGHT PAREN>
  | LIST <LEFT PAREN> DISTINCT <EXPRESSION> 
  [ <COMMA> <DELIMITER> ]
  [ <ORDER SPECIFICATION> ] <RIGHT PAREN>

Grammar rule 11

<BASIC AGGREGATE FUNCTION TYPE> ::=
  SUM | MAX | MIN | AVG | COUNT

Grammar rule 12

<SIMPLE AGGREGATE FUNCTION> ::=
  <SIMPLE AGGREGATE FUNCTION TYPE> <LEFT PAREN>
  <EXPRESSION> <RIGHT PAREN>
  | LIST <LEFT PAREN> <EXPRESSION> [ <COMMA>
  <DELIMITER> ]
  [ <ORDER SPECIFICATION> ] <RIGHT PAREN>

Grammar rule 13

<SIMPLE AGGREGATE FUNCTION TYPE> ::= <SIMPLE WINDOW AGGREGATE FUNCTION TYPE>

Grammar rule 14

<SIMPLE WINDOW AGGREGATE FUNCTION> ::=
  <SIMPLE WINDOW AGGREGATE FUNCTION TYPE> <LEFT PAREN>
  <EXPRESSION> <RIGHT PAREN>
| GROUPING FUNCTION

Grammar rule 15

<SIMPLE WINDOW AGGREGATE FUNCTION TYPE> ::=
  <BASIC AGGREGATE FUNCTION TYPE>
  | STDDEV | STDDEV_POP | STDDEV_SAMP
  | VARIANCE | VARIANCE_POP | VARIANCE_SAMP

Grammar rule 16

<STATISTICAL AGGREGATE FUNCTION> ::=
  <STATISTICAL AGGREGATE FUNCTION TYPE> <LEFT PAREN>
  <DEPENDENT EXPRESSION> <COMMA> <INDEPENDENT
  EXPRESSION> <RIGHT PAREN>

Grammar rule 17

<STATISTICAL AGGREGATE FUNCTION TYPE> ::=
  CORR | COVAR_POP | COVAR_SAMP | REGR_R2 |
  REGR_INTERCEPT | REGR_COUNT | REGR_SLOPE | 
  REGR_SXX | REGR_SXY | REGR_SYY | REGR_AVGY |
  REGR_AVGX

Grammar rule 18

<WINDOW NAME OR SPECIFICATION> ::=
  <WINDOW NAME> | <IN-LINE WINDOW SPECIFICATION>

Grammar rule 19

<WINDOW NAME> ::= <IDENTIFIER>

Grammar rule 20

<IN-LINE WINDOW SPECIFICATION> ::= <WINDOW SPECIFICATION>

Grammar rule 21

<WINDOW CLAUSE> ::= <WINDOW WINDOW DEFINITION LIST>

Grammar rule 22

<WINDOW DEFINITION LIST> ::=
  <WINDOW DEFINITION> [ { <COMMA> <WINDOW DEFINITION>
  } . . . ]

Grammar rule 23

<WINDOW DEFINITION> ::=
  <NEW WINDOW NAME> AS <WINDOW SPECIFICATION>

Grammar rule 24

<NEW WINDOW NAME> ::= <WINDOW NAME>

Grammar rule 25

<WINDOW SPECIFICATION> ::=
  <LEFT PAREN> <WINDOW SPECIFICATION> <DETAILS> <RIGHT
  PAREN>

Grammar rule 26

<WINDOW SPECIFICATION DETAILS> ::=
  [ <EXISTING WINDOW NAME> ]
  [ <WINDOW PARTITION CLAUSE> ]
  [ <WINDOW ORDER CLAUSE> ]
  [ <WINDOW FRAME CLAUSE> ]

Grammar rule 27

<EXISTING WINDOW NAME> ::= <WINDOW NAME>

Grammar rule 28

<WINDOW PARTITION CLAUSE> ::=
  PARTITION BY <WINDOW PARTITION EXPRESSION LIST>

Grammar rule 29

<WINDOW PARTITION EXPRESSION LIST> ::=
  <WINDOW PARTITION EXPRESSION>
  [ { <COMMA> <WINDOW PARTITION EXPRESSION> } . . . ]

Grammar rule 30

<WINDOW PARTITION EXPRESSION> ::= <EXPRESSION>

Grammar rule 31

<WINDOW ORDER CLAUSE> ::= <ORDER SPECIFICATION>

Grammar rule 32

<WINDOW FRAME CLAUSE> ::=
  <WINDOW FRAME UNIT>
  <WINDOW FRAME EXTENT>

Grammar rule 33

<WINDOW FRAME UNIT> ::= ROWS | RANGE

Grammar rule 34

<WINDOW FRAME EXTENT> ::= <WINDOW FRAME START> | <WINDOW FRAME BETWEEN>

Grammar rule 35

<WINDOW FRAME START> ::=
  UNBOUNDED PRECEDING
  | <WINDOW FRAME PRECEDING>
  | CURRENT ROW

Grammar rule 36

<WINDOW FRAME PRECEDING> ::= <UNSIGNED VALUE SPECIFICATION> PRECEDING

Grammar rule 37

<WINDOW FRAME BETWEEN> ::=
  BETWEEN <WINDOW FRAME BOUND 1> AND <WINDOW FRAME
  BOUND 2>

Grammar rule 38

<WINDOW FRAME BOUND 1> ::= <WINDOW FRAME BOUND>

Grammar rule 39

<WINDOW FRAME BOUND 2> ::= <WINDOW FRAME BOUND>

Grammar rule 40

<WINDOW FRAME BOUND> ::=
  <WINDOW FRAME START>
  | UNBOUNDED FOLLOWING
  | <WINDOW FRAME FOLLOWING>

Grammar rule 41

<WINDOW FRAME FOLLOWING> ::= <UNSIGNED VALUE SPECIFICATION> FOLLOWING

Grammar rule 42

<GROUP BY EXPRESSION> ::= <EXPRESSION>

Grammar rule 43

<SIMPLE GROUP BY TERM> ::=
  <GROUP BY EXPRESSION>
  | <LEFT PAREN> <GROUP BY EXPRESSION> <RIGHT PAREN>
  | <LEFT PAREN> <RIGHT PAREN>

Grammar rule 44

<SIMPLE GROUP BY TERM LIST> ::=
  <SIMPLE GROUP BY TERM> [ { <COMMA> <SIMPLE GROUP BY
  TERM> } . . . ]

Grammar rule 45

<COMPOSITE GROUP BY TERM> ::=
  <LEFT PAREN> <SIMPLE GROUP BY TERM>
  [ { <COMMA> <SIMPLE GROUP BY TERM> } . . . ]
  <RIGHT PAREN>

Grammar rule 46

<ROLLUP TERM> ::= ROLLUP <COMPOSITE GROUP BY TERM>

Grammar rule 47

<CUBE TERM> ::= CUBE <COMPOSITE GROUP BY TERM>

Grammar rule 48

<GROUP BY TERM> ::=
  <SIMPLE GROUP BY TERM>
  | <COMPOSITE GROUP BY TERM>
  | <ROLLUP TERM>
  | <CUBE TERM>

Grammar rule 49

<GROUP BY TERM LIST> ::=
  <GROUP BY TERM> [ { <COMMA> <GROUP BY TERM> } . . . ]

Grammar rule 50

<GROUP BY CLAUSE> ::= GROUP BY <GROUPING SPECIFICATION>

Grammar rule 51

<GROUPING SPECIFICATION> ::=
  <GROUP BY TERM LIST>
  | <SIMPLE GROUP BY TERM LIST> WITH ROLLUP
  | <SIMPLE GROUP BY TERM LIST> WITH CUBE
  | <GROUPING SETS SPECIFICATION>

Grammar rule 52

<GROUPING SETS SPECIFICATION> ::=
  GROUPING SETS <LEFT PAREN> <GROUP BY TERM LIST>
  <RIGHT PAREN>

Grammar rule 53

<ORDER SPECIFICATION> ::= ORDER BY <SORT SPECIFICATION LIST>