- Section 1: Basics
- 2: User-friendly language
- 3: Documents, not rows
- 4: Pattern matching with LIKE
- 5: Matching elements in nested arrays with ANY
- 6: Combining multiple conditions with AND
- 7: Querying primary keys
- 8: Quick review
- 9: Pagination with LIMIT and OFFSET
- 10: Filtering grouped data with HAVING
- 11. Review
- 12. Section 2: Joins
- 13. Joins
- 14. Joins
- 15. Exercise
- 16. Exercise
- 17. NEST
- 18. Chaining JOINs
- 19. Example
- 20. Array Comprehensions
- 21. Section 3: DML Statements
- 22. Nest
- 23. Nest
- 24. UNNEST
- 25. Filtering on nested data
- 26. Subquery
- 27. Subquery
- 28. Window Functions
- 29. Window Functions
- 30. UPDATE
- 31. Case Study I. E-Commerce
- 32. Shopper - Browsing products from page to page
- 33. Shopper - Listing product categories
- 34. Shopper - Browsing and searching for a product
- 35. Shopper - Listing products in a category
- 36. Shopper - Finding the most popular products in a category
- 37. Shopper - Browsing products and sorting results
- 38. Shopper - Shopping at a one-day sale
- 39. Shopper - Listing the top 10 best selling products
- 40. Shopper - Listing the highest rated products
- 41. Merchant - Preparing a purchase order
- 42. Merchant - Finding the most valued shoppers
- 43. Merchant - Reporting customers by region
- 44. Merchant - Reporting the active monthly customers
- 45. Merchant - Identifying non-performing products
- 46. Merchant - Generating the month-over-month sales report
- 47. Merchant - Big ticket orders
- 48. Case Study II . Social Game
- 49. Assembling and loading user profiles
- 50. Listing messages sent by a user
- 51. Generating scoreboards
Summary:Window functions are used to compute cumulative, moving, and reporting aggregations.
Window functions are used to compute an aggregate or cumulative value, based on a group of objects. The objects are not grouped into a single output object — each object remains separate in the query output.
All window functions must have a window definition. This divides the query result set into one or more partitions, and specifies the order of objects in those partitions. Within each partition, a movable window frame is defined for every input object. The window frame determines the objects to be used by the window function.
The OVER clause specifies the window definition. Some window functions take additional window options, which are specified by further clauses before the OVER clause.
N1QL has a dedicated set of window functions. Here is the whole list of supported functions.
Link
Here is an example of using RANK()
It returns the rank of the current object – that is, the number of distinct objects preceding this object in the current window partition, plus one.
The objects are ordered by the window order clause. If any objects are tied, they will have the same rank.
When any objects have the same rank, the rank of the next object will include all preceding objects, so there may be a gap in the sequence of returned values. For example, if there are three objects ranked 2, the next rank is 5.
Window functions are used to compute an aggregate or cumulative value, based on a group of objects. The objects are not grouped into a single output object — each object remains separate in the query output.
All window functions must have a window definition. This divides the query result set into one or more partitions, and specifies the order of objects in those partitions. Within each partition, a movable window frame is defined for every input object. The window frame determines the objects to be used by the window function.
The OVER clause specifies the window definition. Some window functions take additional window options, which are specified by further clauses before the OVER clause.
N1QL has a dedicated set of window functions. Here is the whole list of supported functions.
Link
Here is an example of using RANK()
It returns the rank of the current object – that is, the number of distinct objects preceding this object in the current window partition, plus one.
The objects are ordered by the window order clause. If any objects are tied, they will have the same rank.
When any objects have the same rank, the rank of the next object will include all preceding objects, so there may be a gap in the sequence of returned values. For example, if there are three objects ranked 2, the next rank is 5.
To run this example, click the button in the top right corner of the code editor.