Change in asterixdb[master]: [NO ISSUE] Add documentation for bitwise functions
Simon Dew has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/3471 ) Change subject: [NO ISSUE] Add documentation for bitwise functions .. Patch Set 1: Add documentation for bitwise functions -- To view, visit https://asterix-gerrit.ics.uci.edu/3471 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: comment Gerrit-Change-Id: I17a119b12c6e23d5e2d22f2159cd1ce00b9be5cf Gerrit-Change-Number: 3471 Gerrit-PatchSet: 1 Gerrit-Owner: Simon Dew Gerrit-Reviewer: Dmitry Lychagin Gerrit-Reviewer: Hussain Towaileb Gerrit-Reviewer: Jenkins Gerrit-Reviewer: Simon Dew Gerrit-Comment-Date: Fri, 28 Jun 2019 12:57:30 + Gerrit-HasComments: No
Change in asterixdb[master]: [NO ISSUE] Add documentation for bitwise functions
Simon Dew has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/3471 Change subject: [NO ISSUE] Add documentation for bitwise functions .. [NO ISSUE] Add documentation for bitwise functions Add Bitwise functions document Add Bitwise functions to SQL++ ToC Add Bitwise functions to POM Change-Id: I17a119b12c6e23d5e2d22f2159cd1ce00b9be5cf --- M asterixdb/asterix-doc/pom.xml M asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md A asterixdb/asterix-doc/src/main/markdown/builtins/15_bitwise.md 3 files changed, 707 insertions(+), 1 deletion(-) git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/71/3471/1 diff --git a/asterixdb/asterix-doc/pom.xml b/asterixdb/asterix-doc/pom.xml index 83f82ad..9c4c911 100644 --- a/asterixdb/asterix-doc/pom.xml +++ b/asterixdb/asterix-doc/pom.xml @@ -55,7 +55,7 @@ - + diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md index 7064b17..1869169 100644 --- a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md +++ b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md @@ -32,4 +32,5 @@ * [Type Functions](#TypeFunctions) * [Conditional Functions](#ConditionalFunctions) * [Miscellaneous Functions](#MiscFunctions) +* [Bitwise Functions](#BitwiseFunctions) * [Window Functions](#WindowFunctions) diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/15_bitwise.md b/asterixdb/asterix-doc/src/main/markdown/builtins/15_bitwise.md new file mode 100644 index 000..2ca8f83 --- /dev/null +++ b/asterixdb/asterix-doc/src/main/markdown/builtins/15_bitwise.md @@ -0,0 +1,705 @@ + + +## Bitwise Functions ## + +All Bit/Binary functions can only operate on 64-bit signed integers. + +**Note:** All non-integer numbers and other data types result in null. + +**Note:** The query language uses two’s complement representation. + +When looking at the value in binary form, bit 1 is the Least Significant +Bit (LSB) and bit 32 is the Most Significant Bit (MSB). + +(MSB) Bit 32 → ` ` ← Bit 1 (LSB) + +### bitand ### + +* Syntax: + +BITAND(int_value1, int_value2, ... , int_valueN) + +* Returns the result of a bitwise AND operation performed on all input + integer values. + +The bitwise AND operation compares each bit of `int_value1` to the +corresponding bit of every other `int_value`. +If all bits are 1, then the corresponding result bit is set to 1; +otherwise it is set to 0 (zero). + +* Arguments: + +* `int_valueI`: Integers, or any valid expressions which evaluate to + integers, that are used to compare. + +* Return Value: + +* An integer, representing the bitwise AND between all of the input + integers. + +* Limitations: + +* Input values must be integers (such as 1 or 1.0) and cannot contain + decimals (such as 1.2). + +* Example 1: + +Compare 3 (0011 in binary) and 6 (0110 in binary). + +SELECT BITAND(3,6) AS BitAND; + +* The expected result is: + +[ + "BitAND": 2 +] + +This results in 2 (0010 in binary) because only bit 2 is set in both 3 +(00**1**1) and 6 (01**1**0). + +* Example 2: + +Compare 4.5 and 3 (0011 in binary). + +SELECT BITAND(4.5,3) AS BitAND; + +* The expected result is: + +[ + "BitAND": null +] + +The result is null because 4.5 is not an integer. + +* Example 3: + +Compare 4.0 (0100 in binary) and 3 (0011 in binary). + +SELECT BITAND(4.0,3) AS BitAND; + +* The expected result is: + +[ + "BitAND": 0 +] + +This results in 0 (zero) because 4.0 (0100) and 3 (0011) do not share +any bits that are both 1. + +* Example 4: + +Compare 3 (0011 in binary) and 6 (0110 in binary) and 15 ( in binary). + +SELECT BITAND(3,6,15) AS BitAND; + +* The expected result is: + +[ + "BitAND": 2 +] + +This results in 2 (0010 in binary) because only the 2nd bit from the +right is 1 in all three numbers. + +### bitclear ### + +* Syntax: + +BITCLEAR(int_value, positions) + +* Returns the result after clearing the specified bit, or array of bits in + `int_value` using the given `positions`. + +Specifying a negative or zero bit position does not result in any change to +the value. + +**Note:** Specifying a negative or zero bit position makes the function +return a null. + +* Arguments: + +* `int_value`: An integer, or any valid +expression which evaluates +to an integer, that contains the target bit or bits to clear. + +* `positions`: An integer or an array of integers specifying the position or
Change in asterixdb[master]: [NO ISSUE] Reorganize OVER clause documentation
Simon Dew has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/3464 ) Change subject: [NO ISSUE] Reorganize OVER clause documentation .. Patch Set 1: Move OVER clause documentation from the Builtins doc to the SQL++ manual. -- To view, visit https://asterix-gerrit.ics.uci.edu/3464 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: comment Gerrit-Change-Id: I683d024c56fcc55d67e1f87eec5494781bbcdede Gerrit-Change-Number: 3464 Gerrit-PatchSet: 1 Gerrit-Owner: Simon Dew Gerrit-Reviewer: Dmitry Lychagin Gerrit-Reviewer: Jenkins Gerrit-Reviewer: Simon Dew Gerrit-Reviewer: Till Westmann Gerrit-Comment-Date: Wed, 26 Jun 2019 16:44:27 + Gerrit-HasComments: No
Change in asterixdb[master]: [NO ISSUE] Reorganize OVER clause documentation
Simon Dew has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/3464 Change subject: [NO ISSUE] Reorganize OVER clause documentation .. [NO ISSUE] Reorganize OVER clause documentation - Move OVER clause into Query doc - Add link to window function calls from Expressions - Update links to OVER clause - Update ToCs with new location of OVER clause - Standardize IDs - Correct capitalization for admonition headings - Correct indentation in Window Function doc - Separate ToCs for SQL++ and AQL builtins Change-Id: I683d024c56fcc55d67e1f87eec5494781bbcdede --- M asterixdb/asterix-doc/pom.xml M asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md A asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_aql.md A asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_common.md A asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md M asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md D asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/2_expr.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md 11 files changed, 496 insertions(+), 434 deletions(-) git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/64/3464/1 diff --git a/asterixdb/asterix-doc/pom.xml b/asterixdb/asterix-doc/pom.xml index 963daec..83f82ad 100644 --- a/asterixdb/asterix-doc/pom.xml +++ b/asterixdb/asterix-doc/pom.xml @@ -55,10 +55,10 @@ - + - + diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md index 1a94577..c81c656 100644 --- a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md +++ b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md @@ -18,24 +18,3 @@ !--> # Builtin Functions # - -## Table of Contents ## - -* [Numeric Functions](#NumericFunctions) -* [String Functions](#StringFunctions) -* [Binary Functions](#BinaryFunctions) -* [Spatial Functions](#SpatialFunctions) -* [Similarity Functions](#SimilarityFunctions) -* [Tokenizing Functions](#TokenizingFunctions) -* [Temporal Functions](#TemporalFunctions) -* [Object Functions](#ObjectFunctions) -* [Aggregate Functions (Array Functions)](#AggregateFunctions) -* [Comparison Functions](#ComparisonFunctions) -* [Type Functions](#TypeFunctions) -* [Conditional Functions](#ConditionalFunctions) -* [Miscellaneous Functions](#MiscFunctions) -* [Window Functions](#WindowFunctions) -* [OVER Clause (Window Function Calls)](#OverClause) - -The system provides various classes of functions to support operations on numeric, string, spatial, and temporal data. -This document explains how to use these functions. diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_aql.md b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_aql.md new file mode 100644 index 000..f9e81dd --- /dev/null +++ b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_aql.md @@ -0,0 +1,34 @@ + + +## Table of Contents ## + +* [Numeric Functions](#NumericFunctions) +* [String Functions](#StringFunctions) +* [Binary Functions](#BinaryFunctions) +* [Spatial Functions](#SpatialFunctions) +* [Similarity Functions](#SimilarityFunctions) +* [Tokenizing Functions](#TokenizingFunctions) +* [Temporal Functions](#TemporalFunctions) +* [Object Functions](#ObjectFunctions) +* [Aggregate Functions (Array Functions)](#AggregateFunctions) +* [Comparison Functions](#ComparisonFunctions) +* [Type Functions](#TypeFunctions) +* [Conditional Functions](#ConditionalFunctions) +* [Miscellaneous Functions](#MiscFunctions) diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_common.md b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_common.md new file mode 100644 index 000..c1a97cb --- /dev/null +++ b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_common.md @@ -0,0 +1,21 @@ + + +The system provides various classes of functions to support operations on numeric, string, spatial, and temporal data. +This document explains how to use these functions. diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md new file mode 100644 index 000..7064b17 --- /dev/null +++ b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc_sqlpp.md @@ -0,0 +1,35 @@ + + +## Table of Contents ## + +* [Numeric Functions](#NumericFunctions) +* [String Functions](#StringFunctions) +* [Binary Functions](#BinaryFunctions) +* [Spatial Functions](#SpatialFunctions) +*
Change in asterixdb[master]: [NO ISSUE] Correct markup for window function documentation
Simon Dew has uploaded this change for review. (
https://asterix-gerrit.ics.uci.edu/3452
Change subject: [NO ISSUE] Correct markup for window function documentation
..
[NO ISSUE] Correct markup for window function documentation
Correct indentation for code and follow-on paragraphs in bullets.
More descriptive aliases in LEAD and LAG examples.
Change-Id: I34627d2b50b18d4e429e43807161b85eeab9e730
---
M asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
1 file changed, 681 insertions(+), 681 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/52/3452/1
diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
b/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
index e661064..64bcf17 100644
--- a/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
+++ b/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
@@ -39,7 +39,7 @@
* Syntax:
- CUME_DIST() OVER ([window-partition-clause] window-order-clause)
+CUME_DIST() OVER ([window-partition-clause] window-order-clause)
* Returns the percentile rank of the current tuple as part of the cumulative
distribution – that is, the number of tuples ranked lower than or equal to
@@ -63,60 +63,60 @@
* Example:
- For each author, find the cumulative distribution of all messages
- in order of message ID.
+For each author, find the cumulative distribution of all messages
+in order of message ID.
- SELECT m.messageId, m.authorId, CUME_DIST() OVER (
-PARTITION BY m.authorId
-ORDER BY m.messageId
- ) AS `rank`
- FROM GleambookMessages AS m;
+SELECT m.messageId, m.authorId, CUME_DIST() OVER (
+ PARTITION BY m.authorId
+ ORDER BY m.messageId
+) AS `rank`
+FROM GleambookMessages AS m;
* The expected result is:
- [
-{
- "rank": 0.2,
- "messageId": 2,
- "authorId": 1
-},
-{
- "rank": 0.4,
- "messageId": 4,
- "authorId": 1
-},
-{
- "rank": 0.6,
- "messageId": 8,
- "authorId": 1
-},
-{
- "rank": 0.8,
- "messageId": 10,
- "authorId": 1
-},
-{
- "rank": 1,
- "messageId": 11,
- "authorId": 1
-},
-{
- "rank": 0.5,
- "messageId": 3,
- "authorId": 2
-},
-{
- "rank": 1,
- "messageId": 6,
- "authorId": 2
-}
- ]
+[
+ {
+"rank": 0.2,
+"messageId": 2,
+"authorId": 1
+ },
+ {
+"rank": 0.4,
+"messageId": 4,
+"authorId": 1
+ },
+ {
+"rank": 0.6,
+"messageId": 8,
+"authorId": 1
+ },
+ {
+"rank": 0.8,
+"messageId": 10,
+"authorId": 1
+ },
+ {
+"rank": 1,
+"messageId": 11,
+"authorId": 1
+ },
+ {
+"rank": 0.5,
+"messageId": 3,
+"authorId": 2
+ },
+ {
+"rank": 1,
+"messageId": 6,
+"authorId": 2
+ }
+]
### dense_rank ###
* Syntax:
- DENSE_RANK() OVER ([window-partition-clause] window-order-clause)
+DENSE_RANK() OVER ([window-partition-clause] window-order-clause)
* Returns the dense rank of the current tuple – that is, the number of
distinct tuples preceding this tuple in the current window partition, plus
@@ -146,67 +146,67 @@
* Example:
- For each author, find the dense rank of all messages in order of location.
+For each author, find the dense rank of all messages in order of location.
- SELECT m.authorId, m.messageId, m.senderLocation[1] as longitude,
- DENSE_RANK() OVER (
-PARTITION BY m.authorId
-ORDER BY m.senderLocation[1]
- ) AS `rank`
- FROM GleambookMessages AS m;
+SELECT m.authorId, m.messageId, m.senderLocation[1] as longitude,
+DENSE_RANK() OVER (
+ PARTITION BY m.authorId
+ ORDER BY m.senderLocation[1]
+) AS `rank`
+FROM GleambookMessages AS m;
* The expected result is:
- [
-{
- "rank": 1,
- "authorId": 1,
- "messageId": 10,
- "longitude": 70.01
-},
-{
- "rank": 2,
- "authorId": 1,
- "messageId": 11,
- "longitude": 77.49
-},
-{
- "rank": 3,
- "authorId": 1,
- "messageId": 2,
- "longitude": 80.87
-},
-{
- "rank": 3,
- "authorId": 1,
- "messageId": 8,
- "longitude": 80.87
-},
-{
-
Change in asterixdb[master]: [NO ISSUE] Document window functions.
Simon Dew has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/3448 ) Change subject: [NO ISSUE] Document window functions. .. Patch Set 5: (1 comment) https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md File asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md: https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@252 PS1, Line 252: # TIP # > Left this section alone for now. Updated after discussion: the important point is that the boundary expression must use a data type that can be added to the ordering expression. -- To view, visit https://asterix-gerrit.ics.uci.edu/3448 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: comment Gerrit-Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 Gerrit-Change-Number: 3448 Gerrit-PatchSet: 5 Gerrit-Owner: Simon Dew Gerrit-Reviewer: Anon. E. Moose (1000171) Gerrit-Reviewer: Dmitry Lychagin Gerrit-Reviewer: Jenkins Gerrit-Reviewer: Simon Dew Gerrit-Reviewer: Till Westmann Gerrit-Comment-Date: Thu, 20 Jun 2019 09:17:51 + Gerrit-HasComments: Yes
Change in asterixdb[master]: [NO ISSUE] Document window functions.
Hello Anon. E. Moose (1000171), Till Westmann, Jenkins, Dmitry Lychagin, I'd like you to reexamine a change. Please visit https://asterix-gerrit.ics.uci.edu/3448 to look at the new patch set (#4). Change subject: [NO ISSUE] Document window functions. .. [NO ISSUE] Document window functions. Add new window function document. Add new window clause (OVER clause) document. Update Query document: - update list of aggregate functions, - update list of syntactic sugar functions. Update Aggregate SQL Function document: - add link for DISTINCT keyword, - add link for window clause, - add link for SQL standard functions. Add extra blank line if file ends with indented code. Add OVER to reserved keywords. Update Functions ToC. Update Builtins POM. Updates after comments on patch set 1. Data format for RANGE boundary / ordering expression. Fixed typos. Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 --- M asterixdb/asterix-doc/pom.xml M asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md A asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md A asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md M asterixdb/asterix-doc/src/main/markdown/builtins/7_allens.md M asterixdb/asterix-doc/src/main/markdown/builtins/7_temporal.md M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_aql.md M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_1_keywords.md 10 files changed, 1,713 insertions(+), 22 deletions(-) git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/48/3448/4 -- To view, visit https://asterix-gerrit.ics.uci.edu/3448 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: newpatchset Gerrit-Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 Gerrit-Change-Number: 3448 Gerrit-PatchSet: 4 Gerrit-Owner: Simon Dew Gerrit-Reviewer: Anon. E. Moose (1000171) Gerrit-Reviewer: Dmitry Lychagin Gerrit-Reviewer: Jenkins Gerrit-Reviewer: Simon Dew Gerrit-Reviewer: Till Westmann
Change in asterixdb[master]: [NO ISSUE] Document window functions.
Hello Anon. E. Moose (1000171), Till Westmann, Jenkins, Dmitry Lychagin, I'd like you to reexamine a change. Please visit https://asterix-gerrit.ics.uci.edu/3448 to look at the new patch set (#3). Change subject: [NO ISSUE] Document window functions. .. [NO ISSUE] Document window functions. Add new window function document. Add new window clause (OVER clause) document. Update Query document: - update list of aggregate functions, - update list of syntactic sugar functions. Update Aggregate SQL Function document: - add link for DISTINCT keyword, - add link for window clause, - add link for SQL standard functions. Add extra blank line if file ends with indented code. Add OVER to reserved keywords. Update Functions ToC. Update Builtins POM. Updates after comments on patch set 1. Data format for RANGE boundary / ordering expression. Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 --- M asterixdb/asterix-doc/pom.xml M asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md A asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md A asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md M asterixdb/asterix-doc/src/main/markdown/builtins/7_allens.md M asterixdb/asterix-doc/src/main/markdown/builtins/7_temporal.md M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_aql.md M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_1_keywords.md 10 files changed, 1,713 insertions(+), 22 deletions(-) git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/48/3448/3 -- To view, visit https://asterix-gerrit.ics.uci.edu/3448 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: newpatchset Gerrit-Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 Gerrit-Change-Number: 3448 Gerrit-PatchSet: 3 Gerrit-Owner: Simon Dew Gerrit-Reviewer: Anon. E. Moose (1000171) Gerrit-Reviewer: Dmitry Lychagin Gerrit-Reviewer: Jenkins Gerrit-Reviewer: Simon Dew Gerrit-Reviewer: Till Westmann
Change in asterixdb[master]: [NO ISSUE] Document window functions.
Hello Anon. E. Moose (1000171), Till Westmann, Jenkins, Dmitry Lychagin, I'd like you to reexamine a change. Please visit https://asterix-gerrit.ics.uci.edu/3448 to look at the new patch set (#2). Change subject: [NO ISSUE] Document window functions. .. [NO ISSUE] Document window functions. Add new window function document. Add new window clause (OVER clause) document. Update Query document: - update list of aggregate functions, - update list of syntactic sugar functions. Update Aggregate SQL Function document: - add link for DISTINCT keyword, - add link for window clause, - add link for SQL standard functions. Add extra blank line if file ends with indented code. Add OVER to reserved keywords. Update Functions ToC. Update Builtins POM. Updates after comments on patch set 1. Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 --- M asterixdb/asterix-doc/pom.xml M asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md A asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md A asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md M asterixdb/asterix-doc/src/main/markdown/builtins/7_allens.md M asterixdb/asterix-doc/src/main/markdown/builtins/7_temporal.md M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_aql.md M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md M asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_1_keywords.md 10 files changed, 1,715 insertions(+), 22 deletions(-) git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/48/3448/2 -- To view, visit https://asterix-gerrit.ics.uci.edu/3448 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: newpatchset Gerrit-Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 Gerrit-Change-Number: 3448 Gerrit-PatchSet: 2 Gerrit-Owner: Simon Dew Gerrit-Reviewer: Anon. E. Moose (1000171) Gerrit-Reviewer: Dmitry Lychagin Gerrit-Reviewer: Jenkins Gerrit-Reviewer: Simon Dew Gerrit-Reviewer: Till Westmann
Change in asterixdb[master]: [NO ISSUE] Document window functions.
Simon Dew has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/3448 ) Change subject: [NO ISSUE] Document window functions. .. Patch Set 1: (1 comment) https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md File asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md: https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md@38 PS1, Line 38: * [Window Clause (OVER Clause)](#WindowClause) > Changed to "OVER clause (Window Definition)" Updated to "OVER Clause (Window Function Calls)" -- To view, visit https://asterix-gerrit.ics.uci.edu/3448 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: comment Gerrit-Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 Gerrit-Change-Number: 3448 Gerrit-PatchSet: 1 Gerrit-Owner: Simon Dew Gerrit-Reviewer: Anon. E. Moose (1000171) Gerrit-Reviewer: Dmitry Lychagin Gerrit-Reviewer: Jenkins Gerrit-Reviewer: Simon Dew Gerrit-Reviewer: Till Westmann Gerrit-Comment-Date: Wed, 19 Jun 2019 11:01:47 + Gerrit-HasComments: Yes
Change in asterixdb[master]: [NO ISSUE] Document window functions.
Simon Dew has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/3448 ) Change subject: [NO ISSUE] Document window functions. .. Patch Set 1: (17 comments) https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md File asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md: https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md@38 PS1, Line 38: * [Window Clause (OVER Clause)](#WindowClause) > OVER clause is strictly speaking not a window clause. Changed to "OVER clause (Window Definition)" https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md File asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md: https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@260 PS1, Line 260: tied objects in the window frame, the function returns the lowest value > "lowest"? what if ORDER BY had DESC modifier? Changed to "first". Added a note that the order of tuples depends on the window order clause. https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@491 PS1, Line 491: tied objects in the window frame, the function returns the highest > "highest"? What if ORDER BY had DESC modifier? Changed to "last". Added a note that the order of tuples depends on the window order clause. https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@691 PS1, Line 691: * [Nth Val From](#nthval-from): (Optional) Determines where the function > trailing whitespace Done https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@743 PS1, Line 743: * If the window frame is defined by `RANGE` or `GROUPS`, and there are > lowest/highest will also depend on the ORDER BY modifier (DESC/ASC) Changed to "first" and "last". Added a note that the order of tuples depends on the window order clause. https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md File asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md: https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@20 PS1, Line 20: ## Window Clause (OVER Clause) ## > May be instead of "window clause" we should call it "window function call e I've called it "OVER Clause (Window Function Calls)". https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@23 PS1, Line 23: the order of objects within the window, and the size of the window frame. > May be say "the order of tuples within those partitions". Also, I'd may be Done https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@24 PS1, Line 24: The `OVER` keyword introduces the window clause. > " ... introduces the window function call" ? Removed this, as I think it's not necessary if referring to the "OVER clause". https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@94 PS1, Line 94: The AS keyword enables you to specify an alias for the window clause. > ".. an alias for the window frame contents". It introduces a variable which Done https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@97 PS1, Line 97: window clause using this alias, for example: > may be "refers to this alias"? The alias is just a variable that's introduc Done https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@99 PS1, Line 99: FROM source > I'd give an alias for "source" too: "FROM source AS src", then change "FROM Done https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@125 PS1, Line 125: ### Window Clause ### > Again, we probably should not call it "Window Clause" to avoid confusion wi I've gone for "Window Definition", following PostgreSQL https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@137 PS1, Line 137: The **window partition clause** groups the query results into partitions using > "groups the query results" might be confusing. Unlike GROUP BY window funct Done https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@150 PS1, Line 150: The **window order clause** determines how objects are ordered within each > I think we call them "tuples" in this documentation, not "objects". Done throughout https://asterix-gerrit.ics.uci.edu/#/c/3448/1/asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md@238 PS1, Line 238: The ordering term expression
Change in asterixdb[master]: [NO ISSUE] Document window functions.
Simon Dew has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/3448 ) Change subject: [NO ISSUE] Document window functions. .. Patch Set 1: Documentation for window functions and aggregate functions used as window functions. -- To view, visit https://asterix-gerrit.ics.uci.edu/3448 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: comment Gerrit-Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1 Gerrit-Change-Number: 3448 Gerrit-PatchSet: 1 Gerrit-Owner: Simon Dew Gerrit-Reviewer: Anon. E. Moose (1000171) Gerrit-Reviewer: Dmitry Lychagin Gerrit-Reviewer: Jenkins Gerrit-Reviewer: Simon Dew Gerrit-Reviewer: Till Westmann Gerrit-Comment-Date: Mon, 17 Jun 2019 10:09:06 + Gerrit-HasComments: No
Change in asterixdb[master]: [NO ISSUE] Document window functions.
Simon Dew has uploaded this change for review. (
https://asterix-gerrit.ics.uci.edu/3448
Change subject: [NO ISSUE] Document window functions.
..
[NO ISSUE] Document window functions.
Add new window function document.
Add new window clause (OVER clause) document.
Update Query document:
- update list of aggregate functions,
- update list of syntactic sugar functions.
Update Aggregate SQL Function document:
- add link for DISTINCT keyword,
- add link for window clause,
- add link for SQL standard functions.
Add extra blank line if file ends with indented code.
Add OVER to reserved keywords.
Update Functions ToC.
Update Builtins POM.
Change-Id: I52d6e97a27c2fa51208810c6ac3d98cb21a0e2b1
---
M asterixdb/asterix-doc/pom.xml
M asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md
A asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
A asterixdb/asterix-doc/src/main/markdown/builtins/15_over.md
M asterixdb/asterix-doc/src/main/markdown/builtins/7_allens.md
M asterixdb/asterix-doc/src/main/markdown/builtins/7_temporal.md
M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_aql.md
M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_1_keywords.md
10 files changed, 1,707 insertions(+), 22 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/48/3448/1
diff --git a/asterixdb/asterix-doc/pom.xml b/asterixdb/asterix-doc/pom.xml
index b6f77a5..963daec 100644
--- a/asterixdb/asterix-doc/pom.xml
+++ b/asterixdb/asterix-doc/pom.xml
@@ -55,7 +55,7 @@
-
+
diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md
b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md
index e0d23d4..7bb23bb 100644
--- a/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md
+++ b/asterixdb/asterix-doc/src/main/markdown/builtins/0_toc.md
@@ -34,6 +34,8 @@
* [Type Functions](#TypeFunctions)
* [Conditional Functions](#ConditionalFunctions)
* [Miscellaneous Functions](#MiscFunctions)
+* [Window Functions](#WindowFunctions)
+* [Window Clause (OVER Clause)](#WindowClause)
The system provides various classes of functions to support operations on
numeric, string, spatial, and temporal data.
This document explains how to use these functions.
diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
b/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
new file mode 100644
index 000..08bcef6
--- /dev/null
+++ b/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
@@ -0,0 +1,1302 @@
+
+
+## Window Functions ##
+
+Window functions are used to compute an aggregate or cumulative value, based on
+a group of objects.
+For each input object, a movable window of objects is defined.
+The window determines the objects to be used by the window function.
+
+All window functions must be used with a window clause,
+which is introduced by the `OVER` keyword.
+Refer to [Window Clause](#WindowClause) for details.
+
+Window functions cannot appear in the FROM clause clause or LIMIT clause.
+
+The examples in this section use the `GleambookMessages` dataset,
+described in the section on [SELECT Statements](manual.html#SELECT_statements).
+
+### cume_dist ###
+
+* Syntax:
+
+ CUME_DIST() OVER ([window-partition-clause] window-order-clause)
+
+* Returns the percentile rank of the current object as part of the cumulative
+ distribution – that is, the number of objects ranked lower than or equal to
+ the current object, including the current object, divided by the total number
+ of objects in the window partition.
+
+* Arguments:
+
+* None.
+
+* Clauses:
+
+* (Optional) [Window Partition Clause](#window-partition-clause).
+
+* (Required) [Window Order Clause](#window-order-clause).
+
+* Return Value:
+
+* A number greater than 0 and less than or equal to 1.
+ The higher the value, the higher the ranking.
+
+* Example:
+
+ For each author, find the cumulative distribution of all messages
+ in order of message ID.
+
+ SELECT m.messageId, m.authorId, CUME_DIST() OVER (
+PARTITION BY m.authorId
+ORDER BY m.messageId
+ ) AS `rank`
+ FROM GleambookMessages AS m;
+
+* The expected result is:
+
+ [
+{
+ "rank": 0.2,
+ "messageId": 2,
+ "authorId": 1
+},
+{
+ "rank": 0.4,
+ "messageId": 4,
+ "authorId": 1
+},
+{
+ "rank": 0.6,
+ "messageId": 8,
+ "authorId": 1
+},
+{
+ "rank": 0.8,
+ "messageId": 10,
+ "authorId": 1
+
Change in asterixdb[master]: [NO ISSUE] Fix Markdown errors in Data Model documentation
Simon Dew has uploaded a new change for review.
https://asterix-gerrit.ics.uci.edu/3028
Change subject: [NO ISSUE] Fix Markdown errors in Data Model documentation
..
[NO ISSUE] Fix Markdown errors in Data Model documentation
Change-Id: Iec17bc31981b83e996ec6628392eb045f703bb36
---
M asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_composite.md
M asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_header.md
M asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_incomplete.md
M
asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_primitive_common.md
M asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_primitive_delta.md
5 files changed, 7 insertions(+), 7 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/28/3028/1
diff --git
a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_composite.md
b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_composite.md
index 7ed4e34..92b0374 100644
--- a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_composite.md
+++ b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_composite.md
@@ -20,7 +20,7 @@
## Derived Types ##
-### Object###
+### Object ###
An `object` contains a set of fields, where each field is described by its name
and type. An object type may be defined as either open or closed. Open objects
(instances of open object types) are permitted to contain fields that are not
part of the type definition, while closed objects do not permit their instances
to carry extra fields. An example type definition for an object is:
create type SoldierType as open {
@@ -38,7 +38,7 @@
The first instance has all of the type's prescribed content. The second
instance is missing the name field, which is fine because it is optional (due
to the ?). The third instance has an extra field; that is fine because the type
definition specifies that it is open (which is also true by default, if open is
not specified). To more tightly control object content, specifying closed
instead of open in the type definition for SoldierType would have made the
third example instance an invalid instance of the type.
-### Array###
+### Array ###
An `array` is a container that holds a fixed number of values. Array
constructors are denoted by brackets: "[...]".
An example would be
@@ -47,7 +47,7 @@
["alice", 123, "bob", null]
-### Multiset###
+### Multiset ###
A `multiset` is a generalization of the concept of a set that, unlike a set,
allows multiple instances of the multiset's elements.
Multiset constructors are denoted by two opening curly braces followed by
data and two closing curly braces, like "{{...}}".
diff --git
a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_header.md
b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_header.md
index 74bab7f..cc66a3f 100644
--- a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_header.md
+++ b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_header.md
@@ -39,7 +39,7 @@
* [Duration/Year_month_duration/Day_time_duration](#PrimitiveTypesDuration)
* [Interval](#PrimitiveTypesInterval)
* [UUID](#PrimitiveTypesUUID)
-* [Incomplete Information Types] (#IncompleteInformationTypes)
+* [Incomplete Information Types](#IncompleteInformationTypes)
* [Null](#IncompleteInformationTypesNull)
* [Missing](#IncompleteInformationTypesMissing)
* [Derived Types](#DerivedTypes)
diff --git
a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_incomplete.md
b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_incomplete.md
index da88ea0..c65ed85 100644
--- a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_incomplete.md
+++ b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_incomplete.md
@@ -17,7 +17,7 @@
! under the License.
!-->
-## Incomplete Information Types##
+## Incomplete Information Types ##
### Null ###
`null` is a special value that is often used to represent an unknown value.
diff --git
a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_primitive_common.md
b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_primitive_common.md
index 8f9b28e..4c0b2e0 100644
---
a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_primitive_common.md
+++
b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_primitive_common.md
@@ -17,7 +17,7 @@
! under the License.
!-->
-## Primitive Types##
+## Primitive Types ##
### Boolean ###
`boolean` data type can have one of the two values: _*true*_ or _*false*_.
diff --git
a/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_primitive_delta.md
b/asterixdb/asterix-doc/src/main/markdown/datamodel/datamodel_primitive_delta.md
index 51aaed3..dc35381 100644
---
Change in asterixdb[master]: [NO ISSUE] Less "branded" description of the query language,...
Simon Dew has uploaded a new change for review. https://asterix-gerrit.ics.uci.edu/3027 Change subject: [NO ISSUE] Less "branded" description of the query language, continued .. [NO ISSUE] Less "branded" description of the query language, continued Remove "branded" description of query language from Aggregate Functions Change-Id: I35330ab3e86775d2b786a50d657f14b0edc28e13 --- M asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md 1 file changed, 4 insertions(+), 5 deletions(-) git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/27/3027/1 diff --git a/asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md b/asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md index 423edf1..3fdb4dd 100644 --- a/asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md +++ b/asterixdb/asterix-doc/src/main/markdown/builtins/9_aggregate_sql.md @@ -19,11 +19,10 @@ ## Aggregate Functions (Array Functions) ## -This section contains detailed descriptions of each SQL++ aggregate function (i.e., array function). -Note that as described in the SQL++ query reference documentation, standard -SQL aggregate functions (e.g., `MIN`, `MAX`, `SUM`, `COUNT`, and `AVG`) -are not real functions in SQL++ but just syntactic sugars over corresponding -SQL++ builtin aggregate functions (e.g., `ARRAY_MIN`, `ARRAY_MAX`, +This section contains detailed descriptions of each aggregate function (i.e., array function) in the query language. +Note that standard SQL aggregate functions (e.g., `MIN`, `MAX`, `SUM`, `COUNT`, and `AVG`) +are not real functions in the query language, but just syntactic sugars over corresponding +builtin aggregate functions (e.g., `ARRAY_MIN`, `ARRAY_MAX`, `ARRAY_SUM`, `ARRAY_COUNT`, and `ARRAY_AVG`). ### array_count ### -- To view, visit https://asterix-gerrit.ics.uci.edu/3027 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I35330ab3e86775d2b786a50d657f14b0edc28e13 Gerrit-PatchSet: 1 Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-Owner: Simon Dew
