calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [1/4] incubator-calcite git commit: Document WITH, LATERAL, GROUPING SETS, CUBE, ROLLUP; add descriptions for all built-in functions and operators.
Date Sun, 23 Nov 2014 10:44:20 GMT
Repository: incubator-calcite
Updated Branches:
  refs/heads/master a5584ea79 -> c6d66d792


Document WITH, LATERAL, GROUPING SETS, CUBE, ROLLUP; add descriptions for all built-in functions
and operators.

Add a test for composite windowed count.


Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/ac85bebb
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/ac85bebb
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/ac85bebb

Branch: refs/heads/master
Commit: ac85bebb5d82c13f519e118f8e223bdefa5315d2
Parents: a5584ea
Author: Julian Hyde <julianhyde@gmail.com>
Authored: Sun Nov 23 00:07:29 2014 -0800
Committer: Julian Hyde <julianhyde@gmail.com>
Committed: Sun Nov 23 00:10:42 2014 -0800

----------------------------------------------------------------------
 core/src/test/resources/sql/winagg.oq |  19 ++
 doc/REFERENCE.md                      | 405 ++++++++++++++++-------------
 2 files changed, 242 insertions(+), 182 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/ac85bebb/core/src/test/resources/sql/winagg.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/winagg.oq b/core/src/test/resources/sql/winagg.oq
index 4a4b23a..16f2b08 100644
--- a/core/src/test/resources/sql/winagg.oq
+++ b/core/src/test/resources/sql/winagg.oq
@@ -204,4 +204,23 @@ window w1 as (),
 
 !ok
 
+# Composite COUNT.
+select deptno, gender, count(gender, deptno) over w1 as a
+from emp
+window w1 as ();
+ DEPTNO | GENDER | A
+--------+--------+---
+     10 | F      | 8
+     10 | M      | 8
+     20 | M      | 8
+     30 | F      | 8
+     30 | F      | 8
+     50 | F      | 8
+     50 | M      | 8
+     60 | F      | 8
+        | F      | 8
+(9 rows)
+
+!ok
+
 # End winagg.oq

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/ac85bebb/doc/REFERENCE.md
----------------------------------------------------------------------
diff --git a/doc/REFERENCE.md b/doc/REFERENCE.md
index d6579a4..d21ab24 100644
--- a/doc/REFERENCE.md
+++ b/doc/REFERENCE.md
@@ -4,28 +4,34 @@
 
 ```SQL
 query:
-  {
-      select
-  |   query UNION [ ALL ] query
-  |   query EXCEPT query
-  |   query INTERSECT query
-  }
-  [ ORDER BY orderItem [, orderItem ]* ]
-  [ LIMIT { count | ALL } ]
-  [ OFFSET start { ROW | ROWS } ]
-  [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ]
+      [ WITH withItem [ , withItem ]* query ]
+  |   {
+          select
+      |   query UNION [ ALL ] query
+      |   query EXCEPT query
+      |   query INTERSECT query
+      }
+      [ ORDER BY orderItem [, orderItem ]* ]
+      [ LIMIT { count | ALL } ]
+      [ OFFSET start { ROW | ROWS } ]
+      [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ]
+
+withItem:
+      name
+      [ '(' column [, column ]* ')' ]
+      AS '(' query ')'
 
 orderItem:
-  expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
+      expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
 
 select:
-  SELECT [ ALL | DISTINCT ]
-      { * | projectItem [, projectItem ]* }
-  FROM tableExpression
-  [ WHERE booleanExpression ]
-  [ GROUP BY { () | expression [, expression]* } ]
-  [ HAVING booleanExpression ]
-  [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
+      SELECT [ ALL | DISTINCT ]
+          { * | projectItem [, projectItem ]* }
+      FROM tableExpression
+      [ WHERE booleanExpression ]
+      [ GROUP BY { groupItem [, groupItem ]* } ]
+      [ HAVING booleanExpression ]
+      [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
 
 projectItem:
       expression [ [ AS ] columnAlias ]
@@ -37,37 +43,55 @@ tableExpression:
 
 joinCondition:
       ON booleanExpression
-  |   USING ( column [, column ]* )
+  |   USING '(' column [, column ]* ')'
 
 tableReference:
-  tablePrimary [ [ AS ] alias [ ( columnAlias [, columnAlias ]* ) ] ]
+      [ LATERAL ]
+      tablePrimary
+      [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
 
 tablePrimary:
       [ TABLE ] [ [ catalogName . ] schemaName . ] tableName
-  |   ( query )
+  |   '(' query ')'
   |   VALUES expression [, expression ]*
-  |   UNNEST ( expression )
-  |   ( TABLE expression )
+  |   UNNEST '(' expression ')'
+  |   '(' TABLE expression ')'
+
+groupItem:
+      expression
+  |   '(' ')'
+  |   '(' expression [, expression ]* ')'
+  |   CUBE '(' expression [, expression ]* ')'
+  |   ROLLUP '(' expression [, expression ]* ')'
+  |   GROUPING SETS '(' groupItem [, groupItem ]* ')'
 
 windowRef:
       windowName
   |   windowSpec
 
 windowSpec:
-  [ windowName ]
-  (
+      [ windowName ]
+      '('
       [ ORDER BY orderItem [, orderItem ]* ]
       [ PARTITION BY expression [, expression ]* ]
-      {
+      [
           RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING }
-      |
-          ROWS numericExpression { PRECEDING | FOLLOWING }
-      }
-  )
+      |   ROWS numericExpression { PRECEDING | FOLLOWING }
+      ]
+      ')'
 ```
 
 In *orderItem*, if *expression* is a positive integer *n*, it denotes
-the *n*th item in the SELECT clause.
+the <em>n</em>th item in the SELECT clause.
+
+An aggregate query is a query that contains a GROUP BY or a HAVING
+clause, or aggregate functions in the SELECT clause. In the SELECT,
+HAVING and ORDER BY clauses of an aggregate query, all expressions
+must be constant within the current group (that is, grouping constants
+as defined by the GROUP BY clause, or constants), or aggregate
+functions, or a combination of constants and aggregate
+functions. Aggregate and grouping functions may only appear in an
+aggregate query, and only in a SELECT, HAVING or ORDER BY clause.
 
 A scalar sub-query is a sub-query used as an expression. It can occur
 in most places where an expression can occur (such as the SELECT
@@ -107,21 +131,21 @@ name will have been converted to upper case also.
 
 | Data type   | Description               | Range and examples   |
 | ----------- | ------------------------- | ---------------------|
-| BOOLEAN   | Logical values            | Values: TRUE, FALSE, UNKNOWN
-| TINYINT   | 1 byte signed integer     | Range is -255 to 256
-| SMALLINT  | 2 byte signed integer     | Range is -32768 to 32767
-| INTEGER, INT | 4 byte signed integer  | Range is -2147483648 to 2147483647
-| BIGINT    | 8 byte signed integer     | Range is -9223372036854775808 to 9223372036854775807
-| DECIMAL(p, s) | Fixed point           | Example: 123.45 is a DECIMAL(5, 2) value.
-| NUMERIC   | Fixed point               |
-| REAL, FLOAT | 4 byte floating point   | 6 decimal digits precision
-| DOUBLE    | 8 byte floating point     | 15 decimal digits precision
+| BOOLEAN     | Logical values            | Values: TRUE, FALSE, UNKNOWN
+| TINYINT     | 1 byte signed integer     | Range is -255 to 256
+| SMALLINT    | 2 byte signed integer     | Range is -32768 to 32767
+| INTEGER, INT | 4 byte signed integer    | Range is -2147483648 to 2147483647
+| BIGINT      | 8 byte signed integer     | Range is -9223372036854775808 to 9223372036854775807
+| DECIMAL(p, s) | Fixed point             | Example: 123.45 is a DECIMAL(5, 2) value.
+| NUMERIC     | Fixed point               |
+| REAL, FLOAT | 4 byte floating point     | 6 decimal digits precision
+| DOUBLE      | 8 byte floating point     | 15 decimal digits precision
 | CHAR(n), CHARACTER(n) | Fixed-width character string | 'Hello', '' (empty string), _latin1'Hello',
n'Hello', _UTF16'Hello', 'Hello' 'there' (literal split into multiple parts)
 | VARCHAR(n), CHARACTER VARYING(n) | Variable-length character string | As CHAR(n)
-| BINARY(n) | Fixed-width binary string | x'45F0AB', x'' (empty binary string), x'AB' 'CD'
(multi-part binary string literal)
+| BINARY(n)   | Fixed-width binary string | x'45F0AB', x'' (empty binary string), x'AB' 'CD'
(multi-part binary string literal)
 | VARBINARY(n), BINARY VARYING(n) | Variable-length binary string | As BINARY(n)
-| DATE      | Date                      | Example: DATE '1969-07-20'
-| TIME      | Time of day               | Example: TIME '20:17:40'
+| DATE        | Date                      | Example: DATE '1969-07-20'
+| TIME        | Time of day               | Example: TIME '20:17:40'
 | TIMESTAMP [ WITHOUT TIME ZONE ] | Date and time | Example: TIMESTAMP '1969-07-20 20:17:40'
 | TIMESTAMP WITH TIME ZONE | Date and time with time zone | Example: TIMESTAMP '1969-07-20
20:17:40 America/Los Angeles'
 | INTERVAL timeUnit [ TO timeUnit ] | Date time interval | Examples: INTERVAL '1:5' YEAR
TO MONTH, INTERVAL '45' DAY
@@ -153,82 +177,79 @@ Note:
 
 ### Comparison operators
 
-| Operator syntax | Description
-| --------------- | -----------
-| value = value
-| value <> value
-| value > value
-| value >= value
-| value < value
-| value <= value
-| value IS NULL
-| value IS NOT NULL
-| value IS DISTINCT FROM value
-| value IS NOT DISTINCT FROM value
-| value BETWEEN value AND value
-| value NOT BETWEEN value AND value
-| string LIKE string
-| string NOT LIKE string
-| string SIMILAR TO string
-| string NOT SIMILAR TO string
-| string SIMILAR TO string ESCAPE string
-| string NOT SIMILAR TO string ESCAPE string
-| value IN (value [, value]* )
-| value NOT IN (value [, value]* )
-| value IN (sub-query)
-| value NOT IN (sub-query)
-| EXISTS (sub-query)
+| Operator syntax                                   | Description
+| ------------------------------------------------- | -----------
+| value1 = value2                                   | Equals
+| value1 <> value2                                  | Not equal
+| value1 > value2                                   | Greater than
+| value1 >= value2                                  | Greater than or equal
+| value1 < value2                                   | Less than
+| value1 <= value2                                  | Less than or equal
+| value IS NULL                                     | Whether *value* is null
+| value IS NOT NULL                                 | Whether *value* is not null
+| value1 IS DISTINCT FROM value2                    | Whether two values are not equal, treating
null values as the same
+| value1 IS NOT DISTINCT FROM value2                | Whether two values are equal, treating
null values as the same
+| value1 BETWEEN value2 AND value3                  | Whether *value1* is greater than or
equal to *value2* and less than or equal to *value3*
+| value1 NOT BETWEEN value2 AND value3              | Whether *value1* is less than *value2*
or greater than *value3*
+| string1 LIKE string2 [ ESCAPE string3 ]           | Whether *string1* matches pattern *string2*
+| string1 NOT LIKE string2 [ ESCAPE string3 ]       | Whether *string1* does not match pattern
*string2*
+| string1 SIMILAR TO string2 [ ESCAPE string3 ]     | Whether *string1* matches regular expression
*string2*
+| string1 NOT SIMILAR TO string2 [ ESCAPE string3 ] | Whether *string1* does not match regular
expression *string2*
+| value IN (value [, value]* )                      | Whether *value* is equal to a value
in a list
+| value NOT IN (value [, value]* )                  | Whether *value* is not equal to every
value in a list
+| value IN (sub-query)                              | Whether *value* is equal to a row returned
by *sub-query*
+| value NOT IN (sub-query)                          | Whether *value* is not equal to every
row returned by *sub-query*
+| EXISTS (sub-query)                                | Whether *sub-query* returns at least
one row
 
 ### Logical operators
 
-| Operator syntax | Description
-| --------------- | -----------
-| boolean OR boolean
-| boolean AND boolean
-| NOT boolean
-| boolean IS FALSE
-| boolean IS NOT FALSE
-| boolean IS TRUE
-| boolean IS NOT TRUE
-| boolean IS UNKNOWN
-| boolean IS NOT UNKNOWN
+| Operator syntax        | Description
+| ---------------------- | -----------
+| boolean1 OR boolean2   | Whether *boolean1* is TRUE or *boolean2* is TRUE
+| boolean1 AND boolean2  | Whether *boolean1* and *boolean2* are both TRUE
+| NOT boolean            | Whether *boolean* is not TRUE; returns UNKNOWN if *boolean* is
UNKNOWN
+| boolean IS FALSE       | Whether *boolean* is FALSE; returns FALSE if *boolean* is UNKNOWN
+| boolean IS NOT FALSE   | Whether *boolean* is not FALSE; returns TRUE if *boolean* is UNKNOWN
+| boolean IS TRUE        | Whether *boolean* is TRUE; returns FALSE if *boolean* is UNKNOWN
+| boolean IS NOT TRUE    | Whether *boolean* is not TRUE; returns TRUE if *boolean* is UNKNOWN
+| boolean IS UNKNOWN     | Whether *boolean* is UNKNOWN
+| boolean IS NOT UNKNOWN | Whether *boolean* is not UNKNOWN
 
 ### Arithmetic operators and functions
 
-| Operator syntax | Description
-| --------------- | -----------
-| + numeric
-| - numeric
-| numeric + numeric
-| numeric - numeric
-| numeric * numeric
-| numeric / numeric
-| POWER(numeric, numeric)
-| ABS(numeric)
-| MOD(numeric, numeric)
-| SQRT(numeric)
-| LN(numeric)
-| LOG10(numeric)
-| EXP(numeric)
-| CEIL(numeric)
-| FLOOR(numeric)
+| Operator syntax           | Description
+| ------------------------- | -----------
+| + numeric                 | Returns *numeric*
+| - numeric                 | Returns negative *numeric*
+| numeric1 + numeric2       | Returns *numeric1* plus *numeric2*
+| numeric1 - numeric2       | Returns *numeric1* minus *numeric2*
+| numeric1 * numeric2       | Returns *numeric1* multiplied by *numeric2*
+| numeric1 / numeric2       | Returns *numeric1* divided by *numeric2*
+| POWER(numeric1, numeric2) | Returns *numeric1* raised to the power of *numeric2*
+| ABS(numeric)              | Returns the absolute value of *numeric*
+| MOD(numeric, numeric)     | Returns the remainder (modulus) of *numeric1* divided by *numeric2*.
The result is negative only if *numeric1* is negative
+| SQRT(numeric)             | Returns the square root of *numeric*
+| LN(numeric)               | Returns the natural logarithm (base *e*) of *numeric*
+| LOG10(numeric)            | Returns the base 10 logarithm of *numeric*
+| EXP(numeric)              | Returns *e* raised to the power of *numeric*
+| CEIL(numeric)             | Rounds *numeric* up, and returns the smallest number that is
greater than or equal to *numeric*
+| FLOOR(numeric)            | Rounds *numeric* down, and returns the largest number that
is less than or equal to *numeric*
 
 ### Character string operators and functions
 
-| Operator syntax | Description
-| --------------- | -----------
+| Operator syntax            | Description
+| -------------------------- | -----------
 | string &#124;&#124; string | Concatenates two character strings.
-| CHAR_LENGTH(string)        | Returns the number of characters in a character string.
-| CHARACTER_LENGTH(string)   | As CHAR_LENGTH(string)
-| UPPER(string)              | Returns a character string converted to upper-case.
-| LOWER(string)              | Returns a character string converted to lower-case.
-| POSITION(string IN string)
-| TRIM( { BOTH ;&#124; LEADING ;&#124; TRAILING } string FROM string)
-| OVERLAY(string PLACING string FROM string)
-| OVERLAY(string PLACING string FROM integer)
+| CHAR_LENGTH(string)        | Returns the number of characters in a character string
+| CHARACTER_LENGTH(string)   | As CHAR_LENGTH(*string*)
+| UPPER(string)              | Returns a character string converted to upper case
+| LOWER(string)              | Returns a character string converted to lower case
+| POSITION(string1 IN string2) | Returns the position of the first occurrence of *string1*
in *string2*
+| TRIM( { BOTH ;&#124; LEADING ;&#124; TRAILING } string1 FROM string2) | Removes
the longest string containing only the characters in *string1* from the start/end/both ends
of *string1*
+| OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ]) | Replaces a substring of
*string1* with *string2*
 | SUBSTRING(string FROM integer)  | Returns a substring of a character string starting at
a given point.
 | SUBSTRING(string FROM integer FOR integer) | Returns a substring of a character string
starting at a given point with a given length.
-| INITCAP(string)
+| INITCAP(string)            | Returns *string* with the first letter of each word converter
to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated
by non-alphanumeric characters.
 
 Not implemented:
 * SUBSTRING(string FROM regexp FOR regexp)
@@ -238,22 +259,23 @@ Not implemented:
 | Operator syntax | Description
 | --------------- | -----------
 | binary &#124;&#124; binary | Concatenates two binary strings.
-| POSITION(binary IN binary)
-| SUBSTRING(binary FROM integer FOR integer) | Returns a substring of a binary string starting
at a given point with a given length.
+| POSITION(binary1 IN binary2) | Returns the position of the first occurrence of *binary1*
in *binary2*
+| OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ]) | Replaces a substring of
*binary1* with *binary2*
+| SUBSTRING(binary FROM integer) | Returns a substring of *binary* starting at a given point
+| SUBSTRING(binary FROM integer FOR integer) | Returns a substring of *binary* starting at
a given point with a given length
 
 ### Date/time functions
 
-| Operator syntax | Description
-| --------------- | -----------
-| LOCALTIME
-| LOCALTIME(n)
-| LOCALDATE
-| LOCALTIMESTAMP
-| LOCALTIMESTAMP(n)
-| CURRENT_TIME
-| CURRENT_DATE
-| CURRENT_TIMESTAMP
-| EXTRACT(timeUnit FROM datetime)
+| Operator syntax           | Description
+| ------------------------- | -----------
+| LOCALTIME                 | Returns the current date and time in the session time zone
in a value of datatype TIME
+| LOCALTIME(precision)      | Returns the current date and time in the session time zone
in a value of datatype TIME, with *precision* digits of precision
+| LOCALTIMESTAMP            | Returns the current date and time in the session time zone
in a value of datatype TIMESTAMP
+| LOCALTIMESTAMP(precision) | Returns the current date and time in the session time zone
in a value of datatype TIMESTAMP, with *precision* digits of precision
+| CURRENT_TIME              | Returns the current time in the session time zone, in a value
of datatype TIMESTAMP WITH TIME ZONE
+| CURRENT_DATE              | Returns the current date in the session time zone, in a value
of datatype DATE
+| CURRENT_TIMESTAMP         | Returns the current date and time in the session time zone,
in a value of datatype TIMESTAMP WITH TIME ZONE
+| EXTRACT(timeUnit FROM datetime) | Extracts and returns the value of a specified datetime
field from a datetime value expression
 
 Not implemented:
 * EXTRACT(timeUnit FROM interval)
@@ -273,12 +295,12 @@ Not implemented:
 
 | Operator syntax | Description
 | --------------- | -----------
-| USER
-| CURRENT_USER
-| SESSION_USER
-| SYSTEM_USER
-| CURRENT_PATH
-| CURRENT_ROLE
+| USER            | Equivalent to CURRENT_USER
+| CURRENT_USER    | User name of current execution context
+| SESSION_USER    | Session user name
+| SYSTEM_USER     | Returns the name of the current data store user as identified by the
operating system
+| CURRENT_PATH    | Returns a character string representing the current lookup scope for
references to user-defined routines and types
+| CURRENT_ROLE    | Returns the current active role
 
 ### Conditional functions and operators
 
@@ -319,52 +341,51 @@ See also: UNNEST relational operator converts a collection to a relation.
 
 #### Numeric
 
-| Operator syntax | Description
-| --------------- | -----------
-| {fn LOG10(numeric)}
-| {fn POWER(numeric, numeric)}
+| Operator syntax                | Description
+| ------------------------------ | -----------
+| {fn LOG10(numeric)}            | Returns the base-10 logarithm of *numeric*
+| {fn POWER(numeric1, numeric2)} | Returns *numeric1* raised to the power of *numeric2*
 
 Not implemented:
-* {fn ABS(numeric)}
-* {fn ACOS(numeric)}
-* {fn ASIN(numeric)}
-* {fn ATAN(numeric)}
+* {fn ABS(numeric)} - Returns the absolute value of *numeric*
+* {fn ACOS(numeric)} - Returns the arc cosine of *numeric*
+* {fn ASIN(numeric)} - Returns the arc sine of *numeric*
+* {fn ATAN(numeric)} - Returns the arc tangent of *numeric*
 * {fn ATAN2(numeric, numeric)}
-* {fn CEILING(numeric)}
-* {fn COS(numeric)}
+* {fn CEILING(numeric)} - Rounds *numeric* up, and returns the smallest number that is greater
than or equal to *numeric*
+* {fn COS(numeric)} - Returns the cosine of *numeric*
 * {fn COT(numeric)}
-* {fn DEGREES(numeric)}
-* {fn EXP(numeric)}
-* {fn FLOOR(numeric)}
-* {fn LOG(numeric)}
-* {fn LOG10(numeric)}
-* {fn MOD(numeric, numeric)}
-* {fn PI()}
-* {fn RADIANS(numeric)}
+* {fn DEGREES(numeric)} - Converts *numeric* from radians to degrees
+* {fn EXP(numeric)} - Returns *e* raised to the power of *numeric*
+* {fn FLOOR(numeric)} - Rounds *numeric* down, and returns the largest number that is less
than or equal to *numeric*
+* {fn LOG(numeric)} - Returns the natural logarithm (base *e*) of *numeric*
+* {fn MOD(numeric1, numeric2)} - Returns the remainder (modulus) of *numeric1* divided by
*numeric2*. The result is negative only if *numeric1* is negative
+* {fn PI()} - Returns a value that is closer than any other value to *pi*
+* {fn RADIANS(numeric)} - Converts *numeric* from degrees to radians
 * {fn RAND(numeric)}
 * {fn ROUND(numeric, numeric)}
 * {fn SIGN(numeric)}
-* {fn SIN(numeric)}
-* {fn SQRT(numeric)}
-* {fn TAN(numeric)}
+* {fn SIN(numeric)} - Returns the sine of *numeric*
+* {fn SQRT(numeric)} - Returns the square root of *numeric*
+* {fn TAN(numeric)} - Returns the tangent of *numeric*
 * {fn TRUNCATE(numeric, numeric)}
 
 #### String
 
 | Operator syntax | Description
 | --------------- | -----------
-| {fn LOCATE(string, string)}
-| {fn INSERT(string, integer, integer, string)}
-| {fn LCASE(string)}
+| {fn LOCATE(string1, string2)} | Returns the position in *string2* of the first occurrence
of *string1*. Searches from the beginning of the second CharacterExpression, unless the startIndex
parameter is specified.
+| {fn INSERT(string1, start, length, string2)} | Inserts *string2* into a slot in *string1*
+| {fn LCASE(string)}            | Returns a string in which all alphabetic characters in
*string* have been converted to lower case
 
 Not implemented:
-* {fn ASCII(string)}
+* {fn ASCII(string)} - Convert a single-character string to the corresponding ASCII code,
an integer between 0 and 255
 * {fn CHAR(string)}
-* {fn CONCAT(string, string)}
+* {fn CONCAT(character, character)} - Returns the concatenation of character strings
 * {fn DIFFERENCE(string, string)}
 * {fn LEFT(string, integer)}
 * {fn LENGTH(string)}
-* {fn LOCATE(string, string, integer)}
+* {fn LOCATE(string1, string2 [, integer])} - Returns the position in *string2* of the first
occurrence of *string1*. Searches from the beginning of *string2*, unless *integer* is specified.
 * {fn LTRIM(string)}
 * {fn REPEAT(string, integer)}
 * {fn REPLACE(string, string, string)}
@@ -373,7 +394,7 @@ Not implemented:
 * {fn SOUNDEX(string)}
 * {fn SPACE(integer)}
 * {fn SUBSTRING(string, integer, integer)}
-* {fn UCASE(string)}
+* {fn UCASE(string)} - Returns a string in which all alphabetic characters in *string* have
been converted to upper case
 
 #### Date/time
 
@@ -406,37 +427,50 @@ Not implemented:
 
 ### Aggregate functions
 
-| Operator syntax | Description
-| --------------- | -----------
-| COUNT( [ DISTINCT ] value)
-| COUNT(*)
-| AVG( [ DISTINCT ] numeric)
-| SUM( [ DISTINCT ] numeric)
-| MAX( [ DISTINCT ] value)
-| MIN( [ DISTINCT ] value)
-| STDDEV_POP( [ DISTINCT ] value)
-| STDDEV_SAMP( [ DISTINCT ] value)
-| VAR( [ DISTINCT ] value)
-| VAR_POP( [ DISTINCT ] value)
+| Operator syntax                    | Description
+| ---------------------------------- | -----------
+| COUNT( [ DISTINCT ] value [, value]* ) | Returns the number of input rows for which *value*
is not null (wholly not null if *value* is composite)
+| COUNT(*)                           | Returns the number of input rows
+| AVG( [ DISTINCT ] numeric)         | Returns the average (arithmetic mean) of *numeric*
across all input values
+| SUM( [ DISTINCT ] numeric)         | Returns the sum of *numeric* across all input values
+| MAX( [ DISTINCT ] value)           | Returns the maximum value of *value* across all input
values
+| MIN( [ DISTINCT ] value)           | Returns the minimum value of *value* across all input
values
+| STDDEV_POP( [ DISTINCT ] numeric)  | Returns the population standard deviation of *numeric*
across all input values
+| STDDEV_SAMP( [ DISTINCT ] numeric) | Returns the sample standard deviation of *numeric*
across all input values
+| VAR_POP( [ DISTINCT ] value)       | Returns the population variance (square of the population
standard deviation) of *numeric* across all input values
+| VAR_SAMP( [ DISTINCT ] numeric)    | Returns the sample variance (square of the sample
standard deviation) of *numeric* across all input values
+| COVAR_POP(numeric1, numeric2)      | Returns the population covariance of the pair (*numeric1*,
*numeric2*) across all input values
+| COVAR_SAMP(numeric1, numeric2)     | Returns the sample covariance of the pair (*numeric1*,
*numeric2*) across all input values
+| REGR_SXX(numeric1, numeric2)       | Returns the sum of squares of the dependent expression
in a linear regression model
+| REGR_SYY(numeric1, numeric2)       | Returns the sum of squares of the independent expression
in a linear regression model
+
+Not implemented:
+* REGR_AVGX(numeric1, numeric2)
+* REGR_AVGY(numeric1, numeric2)
+* REGR_COUNT(numeric1, numeric2)
+* REGR_INTERCEPT(numeric1, numeric2)
+* REGR_R2(numeric1, numeric2)
+* REGR_SLOPE(numeric1, numeric2)
+* REGR_SXY(numeric1, numeric2)
 
 ### Window functions
 
-| Operator syntax | Description
-| --------------- | -----------
-| COUNT(value) OVER window
-| COUNT(*) OVER window
-| AVG(numeric) OVER window
-| SUM(numeric) OVER window
-| MAX(value) OVER window
-| MIN(value) OVER window
-| RANK() OVER window
-| DENSE_RANK() OVER window
-| ROW_NUMBER() OVER window
-| FIRST_VALUE(value) OVER window
-| LAST_VALUE(value) OVER window
-| LEAD(value, offset, default) OVER window
-| LAG(value, offset, default) OVER window
-| NTILE(value) OVER window
+| Operator syntax                           | Description
+| ----------------------------------------- | -----------
+| COUNT(value [, value ]* ) OVER window     | Returns the number of rows in *window* for
which *value* is not null (wholly not null if *value* is composite)
+| COUNT(*) OVER window                      | Returns the number of rows in *window*
+| AVG(numeric) OVER window                  | Returns the average (arithmetic mean) of *numeric*
across all values in *window*
+| SUM(numeric) OVER window                  | Returns the sum of *numeric* across all values
in *window*
+| MAX(value) OVER window                    | Returns the maximum value of *value* across
all values in *window*
+| MIN(value) OVER window                    | Returns the minimum value of *value* across
all values in *window*
+| RANK() OVER window                        | Returns the rank of the current row with gaps;
same as ROW_NUMBER of its first peer
+| DENSE_RANK() OVER window                  | Returns the rank of the current row without
gaps; this function counts peer groups
+| ROW_NUMBER() OVER window                  | Returns the number of the current row within
its partition, counting from 1
+| FIRST_VALUE(value) OVER window            | Returns *value* evaluated at the row that is
the first row of the window frame
+| LAST_VALUE(value) OVER window             | Returns *value* evaluated at the row that is
the last row of the window frame
+| LEAD(value, offset, default) OVER window  | Returns *value* evaluated at the row that is
*offset* rows after the current row within the partition; if there is no such row, instead
returns *default*. Both *offset* and *default* are evaluated with respect to the current row.
If omitted, *offset* defaults to 1 and *default* to NULL
+| LAG(value, offset, default) OVER window   | Returns *value* evaluated at the row that is
*offset* rows before the current row within the partition; if there is no such row, instead
returns *default*. Both *offset* and *default* are evaluated with respect to the current row.
If omitted, *offset* defaults to 1 and *default* to NULL
+| NTILE(value) OVER window                  | Returns an integer ranging from 1 to *value*,
dividing the partition as equally as possible
 
 Not implemented:
 * COUNT(DISTINCT value) OVER window
@@ -444,3 +478,10 @@ Not implemented:
 * LAST_VALUE(value) IGNORE NULLS OVER window
 * PERCENT_RANK(value) OVER window
 * CUME_DIST(value) OVER window
+* NTH_VALUE(value, nth) OVER window
+
+### Grouping functions
+
+| Operator syntax      | Description
+| -------------------- | -----------
+| GROUPING(expression) | Returns 1 if expression is rolled up in the current row's grouping
set, 0 otherwise


Mime
View raw message