{"id":7534,"date":"2019-08-12T22:56:36","date_gmt":"2019-08-13T05:56:36","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=7534"},"modified":"2025-06-13T19:27:40","modified_gmt":"2025-06-14T02:27:40","slug":"window-functions-in-couchbase-analytics","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/window-functions-in-couchbase-analytics\/","title":{"rendered":"Window functions in Couchbase Analytics"},"content":{"rendered":"

Co-author:\u00a0 Till Westmann,\u00a0 Senior Director, Engineering<\/strong><\/p>\n

\n

The <\/span>bits<\/span><\/a> for Couchbase 6.5, the upcoming release of Couchbase Server, are now available. The Analytics service, which supports efficient parallel query processing, has added support for support for window functions defined in the sql:2003 standard<\/a>.<\/span><\/p>\n

For many of us (yours truly included) window functions is this magical feature that we rarely use. But window functions make SQL queries more concise thereby improving readability which makes it easy to maintain queries for real world applications. The queries expressed by window functions would otherwise involve expensive self-joins and subqueries which are hard to optimize resulting in slow running queries. <\/span>I\u2019ll illustrate the point by calculating a running total which is the most practical example for using window functions.There are multiple ways of calculating a running total in SQL. In this blog post, I will calculate the running total in two ways – the old school way using an INNER JOIN and the newly available Window Functions in Couchbase 6.5.<\/span><\/p>\n

What is a Running Total?<\/b><\/h3>\n

A<\/span> Running Total<\/span><\/a> is a total that is continually adjusted to take account of items as they are added. Another term for it is partial sum. In the example below, the running total for a transaction date would be the InvoiceID\u2019s running total plus the current TransactionAmount.<\/span><\/p>\n

\"\"<\/span><\/p>\n

 <\/p>\n

While I have the RunningTotal calculated above, please note the first three fields are the source data and the RunningTotal needs to be calculated using a SQL query.<\/span><\/p>\n

Pre-requisites<\/b><\/h3>\n

Download<\/span> Couchbase Server 6.5<\/span><\/a> and setup a Couchbase bucket for the sample data to run the queries in this blog post.\u00a0\u00a0<\/span><\/p>\n

Create a Couchbase bucket called \u201cinvoices\u201d with the data below.<\/span><\/p>\n

[\r\n  {\r\n    \"invoices\": {\r\n      \"InvoiceID\": 1,\r\n      \"TransactionDate\": \"2019-08-11\",\r\n      \"TransactionAmount\": 100\r\n    }\r\n  },\r\n  {\r\n    \"invoices\": {\r\n      \"InvoiceID\": 2,\r\n      \"TransactionDate\": \"2019-08-11\",\r\n      \"TransactionAmount\": 200\r\n    }\r\n  },\r\n ...\r\n ...\r\n  {\r\n    \"invoices\": {\r\n      \"InvoiceID\": 3,\r\n      \"TransactionDate\": \"2019-08-11\",\r\n      \"TransactionAmount\": 800\r\n    }\r\n  }\r\n]\r\n<\/pre>\n
On the Analytics workbench, run the following statement to create the dataset<\/span><\/p>\n
CREATE DATASET invoices on invoices;\r\nCONNECT LINK Local;<\/pre>\n
Inner Join query for Running total on a given day<\/b><\/h3>\n
For each InvoiceID, we need to retrieve the transaction amount and all transaction amounts before it. This is accomplished using an Inner Join with a condition to get each invoice and those prior to it.<\/span><\/p>\n
SELECT   inv1.InvoiceID , inv1.TransactionDate, inv1.TransactionAmount, SUM(inv2.TransactionAmount) AS RunningTotal\r\nFROM     invoices inv1 JOIN invoices inv2 ON inv1.InvoiceID >= inv2.InvoiceID\r\nWHERE    inv1.TransactionDate = \"2019-08-11\"\r\nGROUP BY inv1.InvoiceID,  inv1.TransactionDate, inv1.TransactionAmount\r\nORDER BY inv1.InvoiceID<\/pre>\n
JOINS and NoSQL haven\u2019t always played well together. In fact, the conventional wisdom is that data in the NoSQL world is denormalized to avoid JOINs. But Couchbase Analytics was created for data exploration and ad-hoc analysis that includes complex joins and aggregations. The Analytics query engine can process JOINs that are not supported by other NoSQL vendors –\u00a0<\/span><\/p>\n