Shulou(Shulou.com)06/01 Report--

The purpose of the window function is to declaratively convert business reporting requirements to SQL thereby significantly improving query performance and developer / business analyst efficiency. I see real-world reports and dashboards ranging from hours to minutes, minutes to seconds after using the window function. The query size has been reduced from 40 pages to several pages. As early as the 1990s, Redbrick databases really understood business use cases and created a new functional layer for business reporting, including ranking, running totals, calculating commissions and inventory based on subgroups, locations, and so on. All of these take advantage of this feature in every BI layer (such as Tableau,Looker,Cognos) in the SQL standard.

Brief introduction of window function

Imagine that you have six golfers in two rounds. Now you need to create a ranking and rank it. Use SQL to rank them

Player first round Round2 Marco 7573 John 7268 often 6776 Night 7471Sitaram6872 Bingjie 7167

Insert data into Couchbase.

INSERT INTO Golf VALUES ("KP1", {"player": "Marco", "round1": 75, "round2": 73}), VALUES ("KP2", {"player": "Johan", "round1": 72, "round2": 68}), VALUES ("KP3", {"player": "Chang", "round1": 67, "round2": 76}), VALUES ("KP4", {"player": "Isha", "round1": 74, "round2": 71}), VALUES ("KP5") {"player": "Sitaram", "round1": 68, "round2": 72}), VALUES ("KP6", {"player": "Bing Jie", "ROUND1": 71, "round2": 67})

No window function (current state-Couchbase 6. 0)

To write a query without using window functions, you need a subquery to calculate the level of each player. The subquery must scan all data, resulting in the worst algorithm complexity of O (N ^ 2), which greatly increases the execution time and throughput.

Use G1 as (select players, 1st round, 2nd round from the golf course) SELECT g3. Player AS player, (G3. Round 1 + G3. Round 2) AS T, ((G3. Round 1 + g3. Round 2)-144) AS ToPar (select the original 1 + COUNT (*) from G1 as G2, where (G2. Round 1 + 2. Round 2)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.