>From Dmitry Lychagin <[email protected]>: 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 <[email protected]> Gerrit-Reviewer: Anon. E. Moose #1000171 Gerrit-Reviewer: Dmitry Lychagin <[email protected]> Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Mike Carey <[email protected]> Gerrit-CC: Simon Dew <[email protected]> Gerrit-Comment-Date: Tue, 06 Apr 2021 17:37:54 +0000 Gerrit-HasComments: Yes Gerrit-Has-Labels: No Gerrit-MessageType: comment
