You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu> on 2021/04/01 16:42:07 UTC
Change in asterixdb[cheshire-cat]: [ASTERIXDB-2840][DOC] Window function examples
From Simon Dew <Si...@couchbase.com>:
Simon Dew has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824 )
Change subject: [ASTERIXDB-2840][DOC] Window function examples
......................................................................
[ASTERIXDB-2840][DOC] Window function examples
- Update Window function examples to use Commerce dataset
- Update expected results
Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
---
M asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
1 file changed, 570 insertions(+), 424 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/24/10824/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 67ead24..7fe7de9 100644
--- a/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
+++ b/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
@@ -64,52 +64,62 @@
* Example:
- For each author, find the cumulative distribution of all messages
- in order of message ID.
+ For each customer, find the cumulative distribution of all orders
+ by order number.
- SELECT m.messageId, m.authorId, CUME_DIST() OVER (
- PARTITION BY m.authorId
- ORDER BY m.messageId
- ) AS `rank`
- FROM GleambookMessages AS m;
+ FROM orders AS o
+ SELECT o.custid, o.orderno, CUME_DIST() OVER (
+ PARTITION BY o.custid
+ ORDER BY o.orderno
+ ) AS `rank`;
* 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.25,
+ "orderno": 1002,
+ "custid": "C13"
},
{
"rank": 0.5,
- "messageId": 3,
- "authorId": 2
+ "orderno": 1007,
+ "custid": "C13"
+ },
+ {
+ "rank": 0.75,
+ "orderno": 1008,
+ "custid": "C13"
},
{
"rank": 1,
- "messageId": 6,
- "authorId": 2
+ "orderno": 1009,
+ "custid": "C13"
+ },
+ {
+ "rank": 1,
+ "orderno": 1003,
+ "custid": "C31"
+ },
+ {
+ "rank": 1,
+ "orderno": 1004,
+ "custid": "C35"
+ },
+ {
+ "rank": 1,
+ "orderno": 1005,
+ "custid": "C37"
+ },
+ {
+ "rank": 0.5,
+ "orderno": 1001,
+ "custid": "C41"
+ },
+ {
+ "rank": 1,
+ "orderno": 1006,
+ "custid": "C41"
}
]
@@ -131,7 +141,7 @@
For this function, when any tuples have the same rank, the rank of the next
tuple will be consecutive, so there will not be a gap in the sequence of
returned values.
- For example, if there are three tuples ranked 2, the next dense rank is 3.
+ For example, if there are five tuples ranked 3, the next dense rank is 4.
* Arguments:
@@ -149,59 +159,61 @@
* Example:
- For each author, find the dense rank of all messages in order of location.
+ Find the dense rank of all orders by number of items.
- SELECT m.authorId, m.messageId, m.senderLocation[1] as longitude,
+ FROM orders AS o
+ SELECT o.orderno, LEN(o.items) AS items,
DENSE_RANK() OVER (
- PARTITION BY m.authorId
- ORDER BY m.senderLocation[1]
- ) AS `rank`
- FROM GleambookMessages AS m;
+ ORDER BY LEN(o.items)
+ ) AS `rank`;
* The expected result is:
[
{
+ "items": 0,
"rank": 1,
- "authorId": 1,
- "messageId": 10,
- "longitude": 70.01
+ "orderno": 1009
},
{
+ "items": 1,
"rank": 2,
- "authorId": 1,
- "messageId": 11,
- "longitude": 77.49
+ "orderno": 1008
},
{
+ "items": 2,
"rank": 3,
- "authorId": 1,
- "messageId": 2,
- "longitude": 80.87
+ "orderno": 1004
},
{
+ "items": 2,
"rank": 3,
- "authorId": 1,
- "messageId": 8,
- "longitude": 80.87
+ "orderno": 1003
},
{
+ "items": 2,
+ "rank": 3,
+ "orderno": 1001
+ },
+ {
+ "items": 2,
+ "rank": 3,
+ "orderno": 1002
+ },
+ {
+ "items": 2,
+ "rank": 3,
+ "orderno": 1007
+ },
+ {
+ "items": 3,
"rank": 4,
- "authorId": 1,
- "messageId": 4,
- "longitude": 97.04
+ "orderno": 1006
},
{
- "rank": 1,
- "authorId": 2,
- "messageId": 6,
- "longitude": 75.56
- },
- {
- "rank": 2,
- "authorId": 2,
- "messageId": 3,
- "longitude": 81.01
+ "items": 4,
+ "rank": 5,
+ "orderno": 1005
}
]
@@ -268,61 +280,75 @@
* Example:
- For each author, show the length of each message, including the
- length of the shortest message from that author.
+ For each customer, show the value of each order, including the
+ value of the smallest order from that customer.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- FIRST_VALUE(LENGTH(m.message)) OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
- ) AS shortest_message
- FROM GleambookMessages AS m;
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ FIRST_VALUE(s.revenue) OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
+ ) AS smallest_order;
* The expected result is:
[
{
- "message_length": 31,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 8
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "smallest_order": null
},
{
- "message_length": 39,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 11
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "smallest_order": null
},
{
- "message_length": 44,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 4
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "smallest_order": null
},
{
- "message_length": 45,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 2
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "smallest_order": null
},
{
- "message_length": 51,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 10
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "smallest_order": 477.94999999999993
},
{
- "message_length": 35,
- "shortest_message": 35,
- "authorId": 2,
- "messageId": 3
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "smallest_order": 199.94
},
{
- "message_length": 44,
- "shortest_message": 35,
- "authorId": 2,
- "messageId": 6
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "smallest_order": 4639.92
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "smallest_order": 157.73
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "smallest_order": 157.73
}
]
@@ -380,61 +406,75 @@
* Example:
- For each author, show the length of each message, including the
- length of the next-shortest message.
+ For each customer, show the value of each order, including the
+ value of the next-smallest order.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- LAG(LENGTH(m.message), 1, "No shorter message") OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
- ) AS next_shortest_message
- FROM GleambookMessages AS m;
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ LAG(s.revenue, 1, "No smaller order") OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
+ ) AS next_smallest_order;
* The expected result is:
[
{
- "message_length": 31,
- "authorId": 1,
- "messageId": 8,
- "next_shortest_message": "No shorter message"
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "next_smallest_order": "No smaller order"
},
{
- "message_length": 39,
- "authorId": 1,
- "messageId": 11,
- "next_shortest_message": 31
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "next_smallest_order": null
},
{
- "message_length": 44,
- "authorId": 1,
- "messageId": 4,
- "next_shortest_message": 39
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "next_smallest_order": 130.45
},
{
- "message_length": 45,
- "authorId": 1,
- "messageId": 2,
- "next_shortest_message": 44
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "next_smallest_order": 1999.8
},
{
- "message_length": 51,
- "authorId": 1,
- "messageId": 10,
- "next_shortest_message": 45
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "next_smallest_order": "No smaller order"
},
{
- "message_length": 35,
- "authorId": 2,
- "messageId": 3,
- "next_shortest_message": "No shorter message"
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "next_smallest_order": "No smaller order"
},
{
- "message_length": 44,
- "authorId": 2,
- "messageId": 6,
- "next_shortest_message": 35
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "next_smallest_order": "No smaller order"
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "next_smallest_order": "No smaller order"
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "next_smallest_order": 157.73
}
]
@@ -503,62 +543,76 @@
* Example:
- For each author, show the length of each message, including the
- length of the longest message from that author.
+ For each customer, show the value of each order, including the
+ value of the largest order from that customer.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- LAST_VALUE(LENGTH(m.message)) OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ LAST_VALUE(s.revenue) OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- ➊
- ) AS longest_message
- FROM GleambookMessages AS m;
+ ) AS largest_order;
* The expected result is:
[
{
- "message_length": 31,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 8
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "largest_order": 10906.55
},
{
- "message_length": 39,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 11
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "largest_order": 10906.55
},
{
- "message_length": 44,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 4
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "largest_order": 10906.55
},
{
- "message_length": 45,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 2
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "largest_order": 10906.55
},
{
- "message_length": 51,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 10
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "largest_order": 477.94999999999993
},
{
- "message_length": 35,
- "longest_message": 44,
- "authorId": 2,
- "messageId": 3
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "largest_order": 199.94
},
{
- "message_length": 44,
- "longest_message": 44,
- "authorId": 2,
- "messageId": 6
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "largest_order": 4639.92
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "largest_order": 18847.58
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "largest_order": 18847.58
}
]
@@ -566,8 +620,8 @@
end of the window partition.
Without this clause, the end point of the window frame would always be the
current tuple.
- This would mean that the longest message would always be the same as the
- current message.
+ This would mean that the largest order would always be the same as the
+ current order.
### lead ###
@@ -623,61 +677,75 @@
* Example:
- For each author, show the length of each message, including the
- length of the next-longest message.
+ For each customer, show the value of each order, including the
+ value of the next-largest order.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- LEAD(LENGTH(m.message), 1, "No longer message") OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
- ) AS next_longest_message
- FROM GleambookMessages AS m;
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ LEAD(s.revenue, 1, "No larger order") OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
+ ) AS next_largest_order;
* The expected result is:
[
{
- "message_length": 31,
- "authorId": 1,
- "messageId": 8,
- "next_longest_message": 39
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "next_largest_order": 130.45
},
{
- "message_length": 39,
- "authorId": 1,
- "messageId": 11,
- "next_longest_message": 44
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "next_largest_order": 1999.8
},
{
- "message_length": 44,
- "authorId": 1,
- "messageId": 4,
- "next_longest_message": 45
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "next_largest_order": 10906.55
},
{
- "message_length": 45,
- "authorId": 1,
- "messageId": 2,
- "next_longest_message": 51
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "next_largest_order": "No larger order"
},
{
- "message_length": 51,
- "authorId": 1,
- "messageId": 10,
- "next_longest_message": "No longer message"
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "next_largest_order": "No larger order"
},
{
- "message_length": 35,
- "authorId": 2,
- "messageId": 3,
- "next_longest_message": 44
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "next_largest_order": "No larger order"
},
{
- "message_length": 44,
- "authorId": 2,
- "messageId": 6,
- "next_longest_message": "No longer message"
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "next_largest_order": "No larger order"
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "next_largest_order": 18847.58
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "next_largest_order": "No larger order"
}
]
@@ -756,62 +824,76 @@
* Example 1:
- For each author, show the length of each message, including the
- length of the second shortest message from that author.
+ For each customer, show the value of each order, including the
+ value of the second smallest order from that customer.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- NTH_VALUE(LENGTH(m.message), 2) FROM FIRST OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ NTH_VALUE(s.revenue, 2) FROM FIRST OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- ➊
- ) AS shortest_message_but_1
- FROM GleambookMessages AS m;
+ ) AS smallest_order_but_1;
* The expected result is:
[
{
- "message_length": 31,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 8
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "smallest_order_but_1": 130.45
},
{
- "message_length": 39,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 11 // ➋
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45, // ➋
+ "smallest_order_but_1": 130.45
},
{
- "message_length": 44,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 4
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "smallest_order_but_1": 130.45
},
{
- "message_length": 45,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 2
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "smallest_order_but_1": 130.45
},
{
- "message_length": 51,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 10
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "smallest_order_but_1": null
},
{
- "message_length": 35,
- "shortest_message_but_1": 44,
- "authorId": 2,
- "messageId": 3
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "smallest_order_but_1": null
},
{
- "message_length": 44,
- "shortest_message_but_1": 44,
- "authorId": 2,
- "messageId": 6 // ➋
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "smallest_order_but_1": null
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "smallest_order_but_1": 18847.58
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58, // ➋
+ "smallest_order_but_1": 18847.58
}
]
@@ -819,69 +901,83 @@
end of the window partition.
Without this clause, the end point of the window frame would always be the
current tuple.
- This would mean that for the shortest message, the function
- would be unable to find the route with the second shortest message.
+ This would mean that for the smallest order, the function
+ would be unable to find the route with the second smallest order.
- ➁ The second shortest message from this author.
+ ➁ The second smallest order from this customer.
* Example 2:
- For each author, show the length of each message, including the
- length of the second longest message from that author.
+ For each customer, show the value of each order, including the
+ value of the second largest order from that customer.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- NTH_VALUE(LENGTH(m.message), 2) FROM LAST OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ NTH_VALUE(s.revenue, 2) FROM LAST OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- ➊
- ) AS longest_message_but_1
- FROM GleambookMessages AS m;
+ ) AS largest_order_but_1;
* The expected result is:
[
{
- "message_length": 31,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 8
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "largest_order_but_1": 1999.8
},
{
- "message_length": 39,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 11
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8, // ➋
+ "largest_order_but_1": 1999.8
},
{
- "message_length": 44,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 4
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "largest_order_but_1": 1999.8
},
{
- "message_length": 45,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 2 // ➋
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "largest_order_but_1": 1999.8
},
{
- "message_length": 51,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 10
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "largest_order_but_1": null
},
{
- "message_length": 35,
- "longest_message_but_1": 35,
- "authorId": 2,
- "messageId": 3 // ➋
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "largest_order_but_1": null
},
{
- "message_length": 44,
- "longest_message_but_1": 35,
- "authorId": 2,
- "messageId": 6
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "largest_order_but_1": null
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "largest_order_but_1": 157.73
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73, // ➋
+ "largest_order_but_1": 157.73
}
]
@@ -889,10 +985,10 @@
end of the window partition.
Without this clause, the end point of the window frame would always be the
current tuple.
- This would mean the function would be unable to find the second longest
- message for shorter messages.
+ This would mean the function would be unable to find the second largest
+ order for smaller orders.
- ➁ The second longest message from this author.
+ ➁ The second largest order from this customer.
### ntile ###
@@ -932,51 +1028,64 @@
* Example:
- Allocate each message to one of three tiles by length and message ID.
+ Allocate each order to one of three tiles by value.
- SELECT m.messageId, LENGTH(m.message) AS `length`,
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.orderno, s.revenue,
NTILE(3) OVER (
- ORDER BY LENGTH(m.message), m.messageId
- ) AS `ntile`
- FROM GleambookMessages AS m;
+ ORDER BY s.revenue
+ ) AS `ntile`;
* The expected result is:
[
{
- "length": 31,
"ntile": 1,
- "messageId": 8
+ "orderno": 1009,
+ "revenue": null
},
{
- "length": 35,
"ntile": 1,
- "messageId": 3
+ "orderno": 1007,
+ "revenue": 130.45
},
{
- "length": 39,
"ntile": 1,
- "messageId": 11
+ "orderno": 1001,
+ "revenue": 157.73
},
{
- "length": 44,
"ntile": 2,
- "messageId": 4
+ "orderno": 1004,
+ "revenue": 199.94
},
{
- "length": 44,
"ntile": 2,
- "messageId": 6
+ "orderno": 1003,
+ "revenue": 477.94999999999993
},
{
- "length": 45,
- "ntile": 3,
- "messageId": 2
+ "ntile": 2,
+ "orderno": 1008,
+ "revenue": 1999.8
},
{
- "length": 51,
"ntile": 3,
- "messageId": 10
+ "orderno": 1005,
+ "revenue": 4639.92
+ },
+ {
+ "ntile": 3,
+ "orderno": 1002,
+ "revenue": 10906.55
+ },
+ {
+ "ntile": 3,
+ "orderno": 1006,
+ "revenue": 18847.58
}
]
@@ -1011,52 +1120,61 @@
* Example:
- For each author, find the percentile rank of all messages in order
- of message ID.
+ For each customer, find the percentile rank of all orders by order number.
- SELECT m.messageId, m.authorId, PERCENT_RANK() OVER (
- PARTITION BY m.authorId
- ORDER BY m.messageId
- ) AS `rank`
- FROM GleambookMessages AS m;
+ FROM orders AS o
+ SELECT o.custid, o.orderno, PERCENT_RANK() OVER (
+ PARTITION BY o.custid
+ ORDER BY o.orderno
+ ) AS `rank`;
* The expected result is:
[
{
"rank": 0,
- "messageId": 2,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1002
},
{
- "rank": 0.25,
- "messageId": 4,
- "authorId": 1
+ "rank": 0.3333333333333333,
+ "custid": "C13",
+ "orderno": 1007
},
{
- "rank": 0.5,
- "messageId": 8,
- "authorId": 1
- },
- {
- "rank": 0.75,
- "messageId": 10,
- "authorId": 1
+ "rank": 0.6666666666666666,
+ "custid": "C13",
+ "orderno": 1008
},
{
"rank": 1,
- "messageId": 11,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1009
},
{
"rank": 0,
- "messageId": 3,
- "authorId": 2
+ "custid": "C31",
+ "orderno": 1003
+ },
+ {
+ "rank": 0,
+ "custid": "C35",
+ "orderno": 1004
+ },
+ {
+ "rank": 0,
+ "custid": "C37",
+ "orderno": 1005
+ },
+ {
+ "rank": 0,
+ "custid": "C41",
+ "orderno": 1001
},
{
"rank": 1,
- "messageId": 6,
- "authorId": 2
+ "custid": "C41",
+ "orderno": 1006
}
]
@@ -1077,7 +1195,7 @@
When any tuples have the same rank, the rank of the next tuple will include
all preceding tuples, so there may be a gap in the sequence of returned
values.
- For example, if there are three tuples ranked 2, the next rank is 5.
+ For example, if there are five tuples ranked 3, the next rank is 8.
To avoid gaps in the returned values, use the DENSE_RANK() function instead.
@@ -1097,59 +1215,61 @@
* Example:
- For each author, find the rank of all messages in order of location.
+ Find the rank of all orders by number of items.
- SELECT m.authorId, m.messageId, m.senderLocation[1] as longitude,
+ FROM orders AS o
+ SELECT o.orderno, LEN(o.items) AS items,
RANK() OVER (
- PARTITION BY m.authorId
- ORDER BY m.senderLocation[1]
- ) AS `rank`
- FROM GleambookMessages AS m;
+ ORDER BY LEN(o.items)
+ ) AS `rank`;
* The expected result is:
[
{
+ "items": 0,
"rank": 1,
- "authorId": 1,
- "messageId": 10,
- "longitude": 70.01
+ "orderno": 1009
},
{
+ "items": 1,
"rank": 2,
- "authorId": 1,
- "messageId": 11,
- "longitude": 77.49
+ "orderno": 1008
},
{
+ "items": 2,
"rank": 3,
- "authorId": 1,
- "messageId": 2,
- "longitude": 80.87
+ "orderno": 1004
},
{
+ "items": 2,
"rank": 3,
- "authorId": 1,
- "messageId": 8,
- "longitude": 80.87
+ "orderno": 1003
},
{
- "rank": 5,
- "authorId": 1,
- "messageId": 4,
- "longitude": 97.04
+ "items": 2,
+ "rank": 3,
+ "orderno": 1001
},
{
- "rank": 1,
- "authorId": 2,
- "messageId": 6,
- "longitude": 75.56
+ "items": 2,
+ "rank": 3,
+ "orderno": 1002
},
{
- "rank": 2,
- "authorId": 2,
- "messageId": 3,
- "longitude": 81.01
+ "items": 2,
+ "rank": 3,
+ "orderno": 1007
+ },
+ {
+ "items": 3,
+ "rank": 8,
+ "orderno": 1006
+ },
+ {
+ "items": 4,
+ "rank": 9,
+ "orderno": 1005
}
]
@@ -1187,52 +1307,65 @@
* Example:
- For each author, calculate the length of each message as a
- fraction of the total length of all messages.
+ For each customer, calculate the value of each order as a
+ fraction of the total value of all orders.
- SELECT m.messageId, m.authorId,
- RATIO_TO_REPORT(LENGTH(m.message)) OVER (
- PARTITION BY m.authorId
- ) AS length_ratio
- FROM GleambookMessages AS m;
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno,
+ RATIO_TO_REPORT(s.revenue) OVER (
+ PARTITION BY o.custid
+ ) AS fractional_ratio;
* The expected result is:
[
{
- "length_ratio": 0.21428571428571427,
- "messageId": 2,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1008,
+ "fractional_ratio": 0.15339653902798234
},
{
- "length_ratio": 0.20952380952380953,
- "messageId": 4,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1009,
+ "fractional_ratio": null
},
{
- "length_ratio": 0.14761904761904762,
- "messageId": 8,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1002,
+ "fractional_ratio": 0.8365971710849288
},
{
- "length_ratio": 0.24285714285714285,
- "messageId": 10,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1007,
+ "fractional_ratio": 0.010006289887088855
},
{
- "length_ratio": 0.18571428571428572,
- "messageId": 11,
- "authorId": 1
+ "custid": "C31",
+ "orderno": 1003,
+ "fractional_ratio": 1
},
{
- "length_ratio": 0.4430379746835443,
- "messageId": 3,
- "authorId": 2
+ "custid": "C35",
+ "orderno": 1004,
+ "fractional_ratio": 1
},
{
- "length_ratio": 0.5569620253164557,
- "messageId": 6,
- "authorId": 2
+ "custid": "C37",
+ "orderno": 1005,
+ "fractional_ratio": 1
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "fractional_ratio": 0.008299259522733382
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "fractional_ratio": 0.9917007404772666
}
]
@@ -1265,52 +1398,65 @@
* Example:
- For each author, number all messages in order of length.
+ For each customer, number all orders by value.
- SELECT m.messageId, m.authorId,
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno,
ROW_NUMBER() OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
- ) AS `row`
- FROM GleambookMessages AS m;
+ PARTITION BY o.custid
+ ORDER BY s.revenue
+ ) AS `row`;
* The expected result is:
[
{
"row": 1,
- "messageId": 8,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1009
},
{
"row": 2,
- "messageId": 11,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1007
},
{
"row": 3,
- "messageId": 4,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1008
},
{
"row": 4,
- "messageId": 2,
- "authorId": 1
- },
- {
- "row": 5,
- "messageId": 10,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1002
},
{
"row": 1,
- "messageId": 3,
- "authorId": 2
+ "custid": "C31",
+ "orderno": 1003
+ },
+ {
+ "row": 1,
+ "custid": "C35",
+ "orderno": 1004
+ },
+ {
+ "row": 1,
+ "custid": "C37",
+ "orderno": 1005
+ },
+ {
+ "row": 1,
+ "custid": "C41",
+ "orderno": 1001
},
{
"row": 2,
- "messageId": 6,
- "authorId": 2
+ "custid": "C41",
+ "orderno": 1006
}
]
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: cheshire-cat
Gerrit-Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
Gerrit-Change-Number: 10824
Gerrit-PatchSet: 1
Gerrit-Owner: Simon Dew <Si...@couchbase.com>
Gerrit-MessageType: newchange
Change in asterixdb[cheshire-cat]: [ASTERIXDB-2840][DOC] Window function examples
Posted by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu>.
From Dmitry Lychagin <dm...@couchbase.com>:
Dmitry Lychagin has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824 )
Change subject: [ASTERIXDB-2840][DOC] Window function examples
......................................................................
Patch Set 2: Code-Review+2
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: cheshire-cat
Gerrit-Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
Gerrit-Change-Number: 10824
Gerrit-PatchSet: 2
Gerrit-Owner: Simon Dew <Si...@couchbase.com>
Gerrit-Reviewer: Anon. E. Moose #1000171
Gerrit-Reviewer: Dmitry Lychagin <dm...@couchbase.com>
Gerrit-Reviewer: Jenkins <je...@fulliautomatix.ics.uci.edu>
Gerrit-Reviewer: Mike Carey <dt...@gmail.com>
Gerrit-CC: Simon Dew <si...@couchbase.com>
Gerrit-Comment-Date: Wed, 14 Apr 2021 16:01:51 +0000
Gerrit-HasComments: No
Gerrit-Has-Labels: Yes
Gerrit-MessageType: comment
Change in asterixdb[cheshire-cat]: [ASTERIXDB-2840][DOC] Window function examples
Posted by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu>.
From Simon Dew <Si...@couchbase.com>:
Simon Dew has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824 )
Change subject: [ASTERIXDB-2840][DOC] Window function examples
......................................................................
[ASTERIXDB-2840][DOC] Window function examples
- Update Window function examples to use Commerce dataset
- Update expected results
Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
---
M asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
1 file changed, 570 insertions(+), 424 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/24/10824/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 67ead24..7fe7de9 100644
--- a/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
+++ b/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
@@ -64,52 +64,62 @@
* Example:
- For each author, find the cumulative distribution of all messages
- in order of message ID.
+ For each customer, find the cumulative distribution of all orders
+ by order number.
- SELECT m.messageId, m.authorId, CUME_DIST() OVER (
- PARTITION BY m.authorId
- ORDER BY m.messageId
- ) AS `rank`
- FROM GleambookMessages AS m;
+ FROM orders AS o
+ SELECT o.custid, o.orderno, CUME_DIST() OVER (
+ PARTITION BY o.custid
+ ORDER BY o.orderno
+ ) AS `rank`;
* 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.25,
+ "orderno": 1002,
+ "custid": "C13"
},
{
"rank": 0.5,
- "messageId": 3,
- "authorId": 2
+ "orderno": 1007,
+ "custid": "C13"
+ },
+ {
+ "rank": 0.75,
+ "orderno": 1008,
+ "custid": "C13"
},
{
"rank": 1,
- "messageId": 6,
- "authorId": 2
+ "orderno": 1009,
+ "custid": "C13"
+ },
+ {
+ "rank": 1,
+ "orderno": 1003,
+ "custid": "C31"
+ },
+ {
+ "rank": 1,
+ "orderno": 1004,
+ "custid": "C35"
+ },
+ {
+ "rank": 1,
+ "orderno": 1005,
+ "custid": "C37"
+ },
+ {
+ "rank": 0.5,
+ "orderno": 1001,
+ "custid": "C41"
+ },
+ {
+ "rank": 1,
+ "orderno": 1006,
+ "custid": "C41"
}
]
@@ -131,7 +141,7 @@
For this function, when any tuples have the same rank, the rank of the next
tuple will be consecutive, so there will not be a gap in the sequence of
returned values.
- For example, if there are three tuples ranked 2, the next dense rank is 3.
+ For example, if there are five tuples ranked 3, the next dense rank is 4.
* Arguments:
@@ -149,59 +159,61 @@
* Example:
- For each author, find the dense rank of all messages in order of location.
+ Find the dense rank of all orders by number of items.
- SELECT m.authorId, m.messageId, m.senderLocation[1] as longitude,
+ FROM orders AS o
+ SELECT o.orderno, LEN(o.items) AS items,
DENSE_RANK() OVER (
- PARTITION BY m.authorId
- ORDER BY m.senderLocation[1]
- ) AS `rank`
- FROM GleambookMessages AS m;
+ ORDER BY LEN(o.items)
+ ) AS `rank`;
* The expected result is:
[
{
+ "items": 0,
"rank": 1,
- "authorId": 1,
- "messageId": 10,
- "longitude": 70.01
+ "orderno": 1009
},
{
+ "items": 1,
"rank": 2,
- "authorId": 1,
- "messageId": 11,
- "longitude": 77.49
+ "orderno": 1008
},
{
+ "items": 2,
"rank": 3,
- "authorId": 1,
- "messageId": 2,
- "longitude": 80.87
+ "orderno": 1004
},
{
+ "items": 2,
"rank": 3,
- "authorId": 1,
- "messageId": 8,
- "longitude": 80.87
+ "orderno": 1003
},
{
+ "items": 2,
+ "rank": 3,
+ "orderno": 1001
+ },
+ {
+ "items": 2,
+ "rank": 3,
+ "orderno": 1002
+ },
+ {
+ "items": 2,
+ "rank": 3,
+ "orderno": 1007
+ },
+ {
+ "items": 3,
"rank": 4,
- "authorId": 1,
- "messageId": 4,
- "longitude": 97.04
+ "orderno": 1006
},
{
- "rank": 1,
- "authorId": 2,
- "messageId": 6,
- "longitude": 75.56
- },
- {
- "rank": 2,
- "authorId": 2,
- "messageId": 3,
- "longitude": 81.01
+ "items": 4,
+ "rank": 5,
+ "orderno": 1005
}
]
@@ -268,61 +280,75 @@
* Example:
- For each author, show the length of each message, including the
- length of the shortest message from that author.
+ For each customer, show the value of each order, including the
+ value of the smallest order from that customer.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- FIRST_VALUE(LENGTH(m.message)) OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
- ) AS shortest_message
- FROM GleambookMessages AS m;
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ FIRST_VALUE(s.revenue) OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
+ ) AS smallest_order;
* The expected result is:
[
{
- "message_length": 31,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 8
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "smallest_order": null
},
{
- "message_length": 39,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 11
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "smallest_order": null
},
{
- "message_length": 44,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 4
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "smallest_order": null
},
{
- "message_length": 45,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 2
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "smallest_order": null
},
{
- "message_length": 51,
- "shortest_message": 31,
- "authorId": 1,
- "messageId": 10
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "smallest_order": 477.94999999999993
},
{
- "message_length": 35,
- "shortest_message": 35,
- "authorId": 2,
- "messageId": 3
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "smallest_order": 199.94
},
{
- "message_length": 44,
- "shortest_message": 35,
- "authorId": 2,
- "messageId": 6
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "smallest_order": 4639.92
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "smallest_order": 157.73
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "smallest_order": 157.73
}
]
@@ -380,61 +406,75 @@
* Example:
- For each author, show the length of each message, including the
- length of the next-shortest message.
+ For each customer, show the value of each order, including the
+ value of the next-smallest order.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- LAG(LENGTH(m.message), 1, "No shorter message") OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
- ) AS next_shortest_message
- FROM GleambookMessages AS m;
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ LAG(s.revenue, 1, "No smaller order") OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
+ ) AS next_smallest_order;
* The expected result is:
[
{
- "message_length": 31,
- "authorId": 1,
- "messageId": 8,
- "next_shortest_message": "No shorter message"
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "next_smallest_order": "No smaller order"
},
{
- "message_length": 39,
- "authorId": 1,
- "messageId": 11,
- "next_shortest_message": 31
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "next_smallest_order": null
},
{
- "message_length": 44,
- "authorId": 1,
- "messageId": 4,
- "next_shortest_message": 39
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "next_smallest_order": 130.45
},
{
- "message_length": 45,
- "authorId": 1,
- "messageId": 2,
- "next_shortest_message": 44
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "next_smallest_order": 1999.8
},
{
- "message_length": 51,
- "authorId": 1,
- "messageId": 10,
- "next_shortest_message": 45
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "next_smallest_order": "No smaller order"
},
{
- "message_length": 35,
- "authorId": 2,
- "messageId": 3,
- "next_shortest_message": "No shorter message"
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "next_smallest_order": "No smaller order"
},
{
- "message_length": 44,
- "authorId": 2,
- "messageId": 6,
- "next_shortest_message": 35
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "next_smallest_order": "No smaller order"
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "next_smallest_order": "No smaller order"
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "next_smallest_order": 157.73
}
]
@@ -503,62 +543,76 @@
* Example:
- For each author, show the length of each message, including the
- length of the longest message from that author.
+ For each customer, show the value of each order, including the
+ value of the largest order from that customer.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- LAST_VALUE(LENGTH(m.message)) OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ LAST_VALUE(s.revenue) OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- ➊
- ) AS longest_message
- FROM GleambookMessages AS m;
+ ) AS largest_order;
* The expected result is:
[
{
- "message_length": 31,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 8
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "largest_order": 10906.55
},
{
- "message_length": 39,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 11
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "largest_order": 10906.55
},
{
- "message_length": 44,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 4
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "largest_order": 10906.55
},
{
- "message_length": 45,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 2
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "largest_order": 10906.55
},
{
- "message_length": 51,
- "longest_message": 51,
- "authorId": 1,
- "messageId": 10
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "largest_order": 477.94999999999993
},
{
- "message_length": 35,
- "longest_message": 44,
- "authorId": 2,
- "messageId": 3
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "largest_order": 199.94
},
{
- "message_length": 44,
- "longest_message": 44,
- "authorId": 2,
- "messageId": 6
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "largest_order": 4639.92
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "largest_order": 18847.58
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "largest_order": 18847.58
}
]
@@ -566,8 +620,8 @@
end of the window partition.
Without this clause, the end point of the window frame would always be the
current tuple.
- This would mean that the longest message would always be the same as the
- current message.
+ This would mean that the largest order would always be the same as the
+ current order.
### lead ###
@@ -623,61 +677,75 @@
* Example:
- For each author, show the length of each message, including the
- length of the next-longest message.
+ For each customer, show the value of each order, including the
+ value of the next-largest order.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- LEAD(LENGTH(m.message), 1, "No longer message") OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
- ) AS next_longest_message
- FROM GleambookMessages AS m;
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ LEAD(s.revenue, 1, "No larger order") OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
+ ) AS next_largest_order;
* The expected result is:
[
{
- "message_length": 31,
- "authorId": 1,
- "messageId": 8,
- "next_longest_message": 39
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "next_largest_order": 130.45
},
{
- "message_length": 39,
- "authorId": 1,
- "messageId": 11,
- "next_longest_message": 44
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "next_largest_order": 1999.8
},
{
- "message_length": 44,
- "authorId": 1,
- "messageId": 4,
- "next_longest_message": 45
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "next_largest_order": 10906.55
},
{
- "message_length": 45,
- "authorId": 1,
- "messageId": 2,
- "next_longest_message": 51
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "next_largest_order": "No larger order"
},
{
- "message_length": 51,
- "authorId": 1,
- "messageId": 10,
- "next_longest_message": "No longer message"
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "next_largest_order": "No larger order"
},
{
- "message_length": 35,
- "authorId": 2,
- "messageId": 3,
- "next_longest_message": 44
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "next_largest_order": "No larger order"
},
{
- "message_length": 44,
- "authorId": 2,
- "messageId": 6,
- "next_longest_message": "No longer message"
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "next_largest_order": "No larger order"
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "next_largest_order": 18847.58
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "next_largest_order": "No larger order"
}
]
@@ -756,62 +824,76 @@
* Example 1:
- For each author, show the length of each message, including the
- length of the second shortest message from that author.
+ For each customer, show the value of each order, including the
+ value of the second smallest order from that customer.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- NTH_VALUE(LENGTH(m.message), 2) FROM FIRST OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ NTH_VALUE(s.revenue, 2) FROM FIRST OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- ➊
- ) AS shortest_message_but_1
- FROM GleambookMessages AS m;
+ ) AS smallest_order_but_1;
* The expected result is:
[
{
- "message_length": 31,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 8
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "smallest_order_but_1": 130.45
},
{
- "message_length": 39,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 11 // ➋
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45, // ➋
+ "smallest_order_but_1": 130.45
},
{
- "message_length": 44,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 4
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8,
+ "smallest_order_but_1": 130.45
},
{
- "message_length": 45,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 2
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "smallest_order_but_1": 130.45
},
{
- "message_length": 51,
- "shortest_message_but_1": 39,
- "authorId": 1,
- "messageId": 10
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "smallest_order_but_1": null
},
{
- "message_length": 35,
- "shortest_message_but_1": 44,
- "authorId": 2,
- "messageId": 3
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "smallest_order_but_1": null
},
{
- "message_length": 44,
- "shortest_message_but_1": 44,
- "authorId": 2,
- "messageId": 6 // ➋
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "smallest_order_but_1": null
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73,
+ "smallest_order_but_1": 18847.58
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58, // ➋
+ "smallest_order_but_1": 18847.58
}
]
@@ -819,69 +901,83 @@
end of the window partition.
Without this clause, the end point of the window frame would always be the
current tuple.
- This would mean that for the shortest message, the function
- would be unable to find the route with the second shortest message.
+ This would mean that for the smallest order, the function
+ would be unable to find the route with the second smallest order.
- ➁ The second shortest message from this author.
+ ➁ The second smallest order from this customer.
* Example 2:
- For each author, show the length of each message, including the
- length of the second longest message from that author.
+ For each customer, show the value of each order, including the
+ value of the second largest order from that customer.
- SELECT m.authorId, m.messageId,
- LENGTH(m.message) AS message_length,
- NTH_VALUE(LENGTH(m.message), 2) FROM LAST OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno, s.revenue,
+ NTH_VALUE(s.revenue, 2) FROM LAST OVER (
+ PARTITION BY o.custid
+ ORDER BY s.revenue
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- ➊
- ) AS longest_message_but_1
- FROM GleambookMessages AS m;
+ ) AS largest_order_but_1;
* The expected result is:
[
{
- "message_length": 31,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 8
+ "custid": "C13",
+ "orderno": 1002,
+ "revenue": 10906.55,
+ "largest_order_but_1": 1999.8
},
{
- "message_length": 39,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 11
+ "custid": "C13",
+ "orderno": 1008,
+ "revenue": 1999.8, // ➋
+ "largest_order_but_1": 1999.8
},
{
- "message_length": 44,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 4
+ "custid": "C13",
+ "orderno": 1007,
+ "revenue": 130.45,
+ "largest_order_but_1": 1999.8
},
{
- "message_length": 45,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 2 // ➋
+ "custid": "C13",
+ "orderno": 1009,
+ "revenue": null,
+ "largest_order_but_1": 1999.8
},
{
- "message_length": 51,
- "longest_message_but_1": 45,
- "authorId": 1,
- "messageId": 10
+ "custid": "C31",
+ "orderno": 1003,
+ "revenue": 477.94999999999993,
+ "largest_order_but_1": null
},
{
- "message_length": 35,
- "longest_message_but_1": 35,
- "authorId": 2,
- "messageId": 3 // ➋
+ "custid": "C35",
+ "orderno": 1004,
+ "revenue": 199.94,
+ "largest_order_but_1": null
},
{
- "message_length": 44,
- "longest_message_but_1": 35,
- "authorId": 2,
- "messageId": 6
+ "custid": "C37",
+ "orderno": 1005,
+ "revenue": 4639.92,
+ "largest_order_but_1": null
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "revenue": 18847.58,
+ "largest_order_but_1": 157.73
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "revenue": 157.73, // ➋
+ "largest_order_but_1": 157.73
}
]
@@ -889,10 +985,10 @@
end of the window partition.
Without this clause, the end point of the window frame would always be the
current tuple.
- This would mean the function would be unable to find the second longest
- message for shorter messages.
+ This would mean the function would be unable to find the second largest
+ order for smaller orders.
- ➁ The second longest message from this author.
+ ➁ The second largest order from this customer.
### ntile ###
@@ -932,51 +1028,64 @@
* Example:
- Allocate each message to one of three tiles by length and message ID.
+ Allocate each order to one of three tiles by value.
- SELECT m.messageId, LENGTH(m.message) AS `length`,
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.orderno, s.revenue,
NTILE(3) OVER (
- ORDER BY LENGTH(m.message), m.messageId
- ) AS `ntile`
- FROM GleambookMessages AS m;
+ ORDER BY s.revenue
+ ) AS `ntile`;
* The expected result is:
[
{
- "length": 31,
"ntile": 1,
- "messageId": 8
+ "orderno": 1009,
+ "revenue": null
},
{
- "length": 35,
"ntile": 1,
- "messageId": 3
+ "orderno": 1007,
+ "revenue": 130.45
},
{
- "length": 39,
"ntile": 1,
- "messageId": 11
+ "orderno": 1001,
+ "revenue": 157.73
},
{
- "length": 44,
"ntile": 2,
- "messageId": 4
+ "orderno": 1004,
+ "revenue": 199.94
},
{
- "length": 44,
"ntile": 2,
- "messageId": 6
+ "orderno": 1003,
+ "revenue": 477.94999999999993
},
{
- "length": 45,
- "ntile": 3,
- "messageId": 2
+ "ntile": 2,
+ "orderno": 1008,
+ "revenue": 1999.8
},
{
- "length": 51,
"ntile": 3,
- "messageId": 10
+ "orderno": 1005,
+ "revenue": 4639.92
+ },
+ {
+ "ntile": 3,
+ "orderno": 1002,
+ "revenue": 10906.55
+ },
+ {
+ "ntile": 3,
+ "orderno": 1006,
+ "revenue": 18847.58
}
]
@@ -1011,52 +1120,61 @@
* Example:
- For each author, find the percentile rank of all messages in order
- of message ID.
+ For each customer, find the percentile rank of all orders by order number.
- SELECT m.messageId, m.authorId, PERCENT_RANK() OVER (
- PARTITION BY m.authorId
- ORDER BY m.messageId
- ) AS `rank`
- FROM GleambookMessages AS m;
+ FROM orders AS o
+ SELECT o.custid, o.orderno, PERCENT_RANK() OVER (
+ PARTITION BY o.custid
+ ORDER BY o.orderno
+ ) AS `rank`;
* The expected result is:
[
{
"rank": 0,
- "messageId": 2,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1002
},
{
- "rank": 0.25,
- "messageId": 4,
- "authorId": 1
+ "rank": 0.3333333333333333,
+ "custid": "C13",
+ "orderno": 1007
},
{
- "rank": 0.5,
- "messageId": 8,
- "authorId": 1
- },
- {
- "rank": 0.75,
- "messageId": 10,
- "authorId": 1
+ "rank": 0.6666666666666666,
+ "custid": "C13",
+ "orderno": 1008
},
{
"rank": 1,
- "messageId": 11,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1009
},
{
"rank": 0,
- "messageId": 3,
- "authorId": 2
+ "custid": "C31",
+ "orderno": 1003
+ },
+ {
+ "rank": 0,
+ "custid": "C35",
+ "orderno": 1004
+ },
+ {
+ "rank": 0,
+ "custid": "C37",
+ "orderno": 1005
+ },
+ {
+ "rank": 0,
+ "custid": "C41",
+ "orderno": 1001
},
{
"rank": 1,
- "messageId": 6,
- "authorId": 2
+ "custid": "C41",
+ "orderno": 1006
}
]
@@ -1077,7 +1195,7 @@
When any tuples have the same rank, the rank of the next tuple will include
all preceding tuples, so there may be a gap in the sequence of returned
values.
- For example, if there are three tuples ranked 2, the next rank is 5.
+ For example, if there are five tuples ranked 3, the next rank is 8.
To avoid gaps in the returned values, use the DENSE_RANK() function instead.
@@ -1097,59 +1215,61 @@
* Example:
- For each author, find the rank of all messages in order of location.
+ Find the rank of all orders by number of items.
- SELECT m.authorId, m.messageId, m.senderLocation[1] as longitude,
+ FROM orders AS o
+ SELECT o.orderno, LEN(o.items) AS items,
RANK() OVER (
- PARTITION BY m.authorId
- ORDER BY m.senderLocation[1]
- ) AS `rank`
- FROM GleambookMessages AS m;
+ ORDER BY LEN(o.items)
+ ) AS `rank`;
* The expected result is:
[
{
+ "items": 0,
"rank": 1,
- "authorId": 1,
- "messageId": 10,
- "longitude": 70.01
+ "orderno": 1009
},
{
+ "items": 1,
"rank": 2,
- "authorId": 1,
- "messageId": 11,
- "longitude": 77.49
+ "orderno": 1008
},
{
+ "items": 2,
"rank": 3,
- "authorId": 1,
- "messageId": 2,
- "longitude": 80.87
+ "orderno": 1004
},
{
+ "items": 2,
"rank": 3,
- "authorId": 1,
- "messageId": 8,
- "longitude": 80.87
+ "orderno": 1003
},
{
- "rank": 5,
- "authorId": 1,
- "messageId": 4,
- "longitude": 97.04
+ "items": 2,
+ "rank": 3,
+ "orderno": 1001
},
{
- "rank": 1,
- "authorId": 2,
- "messageId": 6,
- "longitude": 75.56
+ "items": 2,
+ "rank": 3,
+ "orderno": 1002
},
{
- "rank": 2,
- "authorId": 2,
- "messageId": 3,
- "longitude": 81.01
+ "items": 2,
+ "rank": 3,
+ "orderno": 1007
+ },
+ {
+ "items": 3,
+ "rank": 8,
+ "orderno": 1006
+ },
+ {
+ "items": 4,
+ "rank": 9,
+ "orderno": 1005
}
]
@@ -1187,52 +1307,65 @@
* Example:
- For each author, calculate the length of each message as a
- fraction of the total length of all messages.
+ For each customer, calculate the value of each order as a
+ fraction of the total value of all orders.
- SELECT m.messageId, m.authorId,
- RATIO_TO_REPORT(LENGTH(m.message)) OVER (
- PARTITION BY m.authorId
- ) AS length_ratio
- FROM GleambookMessages AS m;
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno,
+ RATIO_TO_REPORT(s.revenue) OVER (
+ PARTITION BY o.custid
+ ) AS fractional_ratio;
* The expected result is:
[
{
- "length_ratio": 0.21428571428571427,
- "messageId": 2,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1008,
+ "fractional_ratio": 0.15339653902798234
},
{
- "length_ratio": 0.20952380952380953,
- "messageId": 4,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1009,
+ "fractional_ratio": null
},
{
- "length_ratio": 0.14761904761904762,
- "messageId": 8,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1002,
+ "fractional_ratio": 0.8365971710849288
},
{
- "length_ratio": 0.24285714285714285,
- "messageId": 10,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1007,
+ "fractional_ratio": 0.010006289887088855
},
{
- "length_ratio": 0.18571428571428572,
- "messageId": 11,
- "authorId": 1
+ "custid": "C31",
+ "orderno": 1003,
+ "fractional_ratio": 1
},
{
- "length_ratio": 0.4430379746835443,
- "messageId": 3,
- "authorId": 2
+ "custid": "C35",
+ "orderno": 1004,
+ "fractional_ratio": 1
},
{
- "length_ratio": 0.5569620253164557,
- "messageId": 6,
- "authorId": 2
+ "custid": "C37",
+ "orderno": 1005,
+ "fractional_ratio": 1
+ },
+ {
+ "custid": "C41",
+ "orderno": 1001,
+ "fractional_ratio": 0.008299259522733382
+ },
+ {
+ "custid": "C41",
+ "orderno": 1006,
+ "fractional_ratio": 0.9917007404772666
}
]
@@ -1265,52 +1398,65 @@
* Example:
- For each author, number all messages in order of length.
+ For each customer, number all orders by value.
- SELECT m.messageId, m.authorId,
+ FROM orders AS o, (
+ FROM o.items AS i
+ SELECT SUM(i.qty * i.price) AS revenue
+ ) AS s
+ SELECT o.custid, o.orderno,
ROW_NUMBER() OVER (
- PARTITION BY m.authorId
- ORDER BY LENGTH(m.message)
- ) AS `row`
- FROM GleambookMessages AS m;
+ PARTITION BY o.custid
+ ORDER BY s.revenue
+ ) AS `row`;
* The expected result is:
[
{
"row": 1,
- "messageId": 8,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1009
},
{
"row": 2,
- "messageId": 11,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1007
},
{
"row": 3,
- "messageId": 4,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1008
},
{
"row": 4,
- "messageId": 2,
- "authorId": 1
- },
- {
- "row": 5,
- "messageId": 10,
- "authorId": 1
+ "custid": "C13",
+ "orderno": 1002
},
{
"row": 1,
- "messageId": 3,
- "authorId": 2
+ "custid": "C31",
+ "orderno": 1003
+ },
+ {
+ "row": 1,
+ "custid": "C35",
+ "orderno": 1004
+ },
+ {
+ "row": 1,
+ "custid": "C37",
+ "orderno": 1005
+ },
+ {
+ "row": 1,
+ "custid": "C41",
+ "orderno": 1001
},
{
"row": 2,
- "messageId": 6,
- "authorId": 2
+ "custid": "C41",
+ "orderno": 1006
}
]
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: cheshire-cat
Gerrit-Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
Gerrit-Change-Number: 10824
Gerrit-PatchSet: 1
Gerrit-Owner: Simon Dew <Si...@couchbase.com>
Gerrit-MessageType: newchange
Change in asterixdb[cheshire-cat]: [ASTERIXDB-2840][DOC] Window function examples
Posted by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu>.
From Simon Dew <Si...@couchbase.com>:
Hello Anon. E. Moose #1000171, Mike Carey, Jenkins, Dmitry Lychagin,
I'd like you to reexamine a change. Please visit
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824
to look at the new patch set (#2).
Change subject: [ASTERIXDB-2840][DOC] Window function examples
......................................................................
[ASTERIXDB-2840][DOC] Window function examples
- Update Window function examples to use Commerce dataset
- Update expected results
Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
---
M asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md
1 file changed, 579 insertions(+), 422 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/24/10824/2
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: cheshire-cat
Gerrit-Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
Gerrit-Change-Number: 10824
Gerrit-PatchSet: 2
Gerrit-Owner: Simon Dew <Si...@couchbase.com>
Gerrit-Reviewer: Anon. E. Moose #1000171
Gerrit-Reviewer: Dmitry Lychagin <dm...@couchbase.com>
Gerrit-Reviewer: Jenkins <je...@fulliautomatix.ics.uci.edu>
Gerrit-Reviewer: Mike Carey <dt...@gmail.com>
Gerrit-CC: Simon Dew <si...@couchbase.com>
Gerrit-MessageType: newpatchset
Change in asterixdb[cheshire-cat]: [ASTERIXDB-2840][DOC] Window function examples
Posted by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu>.
Anon. E. Moose #1000171 has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824 )
Change subject: [ASTERIXDB-2840][DOC] Window function examples
......................................................................
Patch Set 2: Contrib-2
Analytics Compatibility Tests Failed
https://cbjenkins.page.link/LRoAcjtbtennUdBa7 : UNSTABLE
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: cheshire-cat
Gerrit-Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
Gerrit-Change-Number: 10824
Gerrit-PatchSet: 2
Gerrit-Owner: Simon Dew <Si...@couchbase.com>
Gerrit-Reviewer: Anon. E. Moose #1000171
Gerrit-Reviewer: Dmitry Lychagin <dm...@couchbase.com>
Gerrit-Reviewer: Jenkins <je...@fulliautomatix.ics.uci.edu>
Gerrit-Reviewer: Mike Carey <dt...@gmail.com>
Gerrit-CC: Simon Dew <si...@couchbase.com>
Gerrit-Comment-Date: Sat, 10 Apr 2021 01:29:20 +0000
Gerrit-HasComments: No
Gerrit-Has-Labels: Yes
Gerrit-MessageType: comment
Change in asterixdb[cheshire-cat]: [ASTERIXDB-2840][DOC] Window function examples
Posted by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu>.
From Simon Dew <si...@couchbase.com>:
Simon Dew has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824 )
Change subject: [ASTERIXDB-2840][DOC] Window function examples
......................................................................
Patch Set 1:
The window functions documentation was using the TinySocial example data, which is no longer described anywhere.
This change updates the window functions documentation to use the Commerce example data.
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: cheshire-cat
Gerrit-Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
Gerrit-Change-Number: 10824
Gerrit-PatchSet: 1
Gerrit-Owner: Simon Dew <Si...@couchbase.com>
Gerrit-Reviewer: Dmitry Lychagin <dm...@couchbase.com>
Gerrit-Reviewer: Michael Carey <dt...@gmail.com>
Gerrit-CC: Anon. E. Moose #1000171
Gerrit-CC: Jenkins <je...@fulliautomatix.ics.uci.edu>
Gerrit-CC: Simon Dew <si...@couchbase.com>
Gerrit-Comment-Date: Thu, 01 Apr 2021 16:54:55 +0000
Gerrit-HasComments: No
Gerrit-Has-Labels: No
Gerrit-MessageType: comment
Change in asterixdb[cheshire-cat]: [ASTERIXDB-2840][DOC] Window function examples
Posted by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu>.
From Dmitry Lychagin <dm...@couchbase.com>:
Dmitry Lychagin has posted comments on this change. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824 )
Change subject: [ASTERIXDB-2840][DOC] Window function examples
......................................................................
Patch Set 1:
(14 comments)
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/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/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@70
PS1, Line 70: FROM orders AS o
can we change the order of clause from FROM ... SELECT ... to SELECT ... FROM ... ? (here and in all other new queries)
SELECT ... FROM ... is easier to read for SQL users.
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@74
PS1, Line 74: ) AS `rank`;
1. should this output field be called "rank"?
2. may be let's add ORDER BY clause to the query itself (ORDER BY o.custid, o.orderno), because the result order is not guaranteed without explicit ORDER BY in the query. (for this and following new queries)
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@168
PS1, Line 168: ) AS `rank`;
Add ORDER BY `rank`, o.orderno to the query?
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@283
PS1, Line 283: For each customer, show the value of each order, including the
minor. We're not iterating over customer dataset, so saying "for each customer" seems a bit confusing. May be we should just say "for each order"?
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@287
PS1, Line 287: FROM o.items AS i
I'd write this FROM part a bit differently:
FROM orders AS o
LET revenue = (SELECT VALUE SUM(qty*price) FROM o.items)[0]
This makes it easier to understand that we're computing one revenue per order.
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@302
PS1, Line 302: "revenue": null,
how come the revenue is null here? This order (1009) should have some items, right? This revenue=null probably leads to smallest_order=null for each subsequent result for this customer (custid=13)
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@326
PS1, Line 326: "revenue": 477.94999999999993,
Can we add ROUND(.., 2) around revenue value?
...
LET revenue = ROUND( (SELECT VALUE SUM(qty*price) FROM o.items)[0], 2)
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@412
PS1, Line 412: FROM orders AS o, (
I'd also rewrite this as
FROM orders AS o
LET revenue = (SELECT VALUE SUM(qty*price) FROM o.items)[0]
...
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@452
PS1, Line 452: "revenue": 477.94999999999993,
Add ROUND(,2)?
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@549
PS1, Line 549: FROM orders AS o, (
Same comment as above.
FROM orders AS o
LET revenue = (SELECT VALUE SUM(qty*price) FROM o.items)[0]
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@683
PS1, Line 683: FROM orders AS o, (
Same comment as above. Rewrite as FROM/LET
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@723
PS1, Line 723: "revenue": 477.94999999999993,
Add ROUND(,2)?
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@830
PS1, Line 830: FROM orders AS o, (
Same comment as above. Rewrite as FROM/LET
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824/1/asterixdb/asterix-doc/src/main/markdown/builtins/14_window.md@871
PS1, Line 871: "revenue": 477.94999999999993,
Add ROUND(,2)?
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10824
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: cheshire-cat
Gerrit-Change-Id: I43c9a26e8cc667b45dd6dc6120951c21cf9107e8
Gerrit-Change-Number: 10824
Gerrit-PatchSet: 1
Gerrit-Owner: Simon Dew <Si...@couchbase.com>
Gerrit-Reviewer: Anon. E. Moose #1000171
Gerrit-Reviewer: Dmitry Lychagin <dm...@couchbase.com>
Gerrit-Reviewer: Jenkins <je...@fulliautomatix.ics.uci.edu>
Gerrit-Reviewer: Mike Carey <dt...@gmail.com>
Gerrit-CC: Simon Dew <si...@couchbase.com>
Gerrit-Comment-Date: Tue, 06 Apr 2021 17:37:54 +0000
Gerrit-HasComments: Yes
Gerrit-Has-Labels: No
Gerrit-MessageType: comment