Windowing functions session for Slovak SQL Pass & BI
Download as PPTX, PDF0 likes38 views
I wil show how to understand OVER() and PARTITION BY in TSQL, examples and benefits of windowing functions ¨C managing heavy aggregations, de-duplicating data, running totals, paging and data islands. See what is behind ¡°set theory¡± of windowing functions.
![Windowing Functions ¨C what to take home
? OVER() looks tricky but is worth of additional effort
? WFs are standard across DB providers
? WF are usually faster than other approaches
? Use POC index (Partitioning, Ordering, Covering)
? SUM( Qty ) OVER ( PARTITION BY [ShipperId] ORDER BY OrderYearMonth )
? Partitioning: [ShipperId]
? Ordering: [OrderYearMonth]
? Covering: [Qty]
? CREATE INDEX Idx_ShipperId_OrderYearMonth_i_Qty ON Table ( ShipperId
/* P */, OrderYearMonth /* O */) INCLUDE(Qty /* C */);
12/06/2017 ºÝºÝߣ 9/11](https://image.slidesharecdn.com/enwindowfunctionsugbratislava06122017-180520201758/85/Windowing-functions-session-for-Slovak-SQL-Pass-BI-9-320.jpg)
More Related Content
What's hot (12)
Similar to Windowing functions session for Slovak SQL Pass & BI (20)
Recently uploaded (20)
Windowing functions session for Slovak SQL Pass & BI
- 1. Windowing Functions in SQL Server Andrej Zafka MS SQL Developer 12/06/2017
- 2. What are good for? ? Windowing functions are functions applied to sets of rows defined by a clause called OVER(). ? Used mainly for analytical purposes allowing to calculate: 1) Aggregates with details 2) De-duplicating data 3) Paging 4) Running Totals 5) Gaps and islands in data ? The idea behind this concept is to allow you to apply various calculations to a set, or window, of rows and return a single value. 12/06/2017 ºÝºÝߣ 2/11
- 3. History and background of Windowing Functions ? First time available from SQL Server 2005, still improving in SQL Server 2017 ? Definition: ? Window is the set of rows, or relation, given as input to the logical query processing phase where the window function appears. ? OVER() function defines for each row in result set of the query separate, independent window ? This is true for all rows in the result set of the query. In other words, with respect to each row, the OVER clause defines a window independent of the other rows. ? Strong math background: By a ¡°set¡± we mean any collection M into a whole of definite, distinct objects m (which are called the ¡°elements¡± of M) of our perception or of our thought. ¡ªJoseph W. Dauben, Georg Cantor (Princeton University Press, 1990) 12/06/2017 ºÝºÝߣ 3/11
- 4. 1) Aggregates with details: ? SUM, AVG, MIN, MAX, COUNT functions return aggregates with GROUP BY clause ? But we are losing detail ¨C how to calculate % from total by category, or difference between total and current row ? Subqueries ¨C heavy for IO and CPU ? Using CTE (Common Table Expressions) less painful, still ugly ? Windowing functions ¨C OVER() ¨C easy to read, easy to IO and CPU ? SQL Server¡¯s optimizer was coded with the logic to look for multiple functions with the same window specification. If any are found, SQL Server will use the same visit (whichever kind of scan was chosen) to the data for those. 12/06/2017 ºÝºÝߣ 4/11
- 5. 2) De-duplicating data: ? Finding duplicates in table ? Common ETL issue ? Use ROW_NUMBER() per attribute, like OrderId, ProductId ? ¡°per attribute¡± means PARTITION BY ? Every other row within partition will have number +1 ? Outside select * with condition Row = 1 will take ordered unique rows 12/06/2017 ºÝºÝߣ 5/11
- 6. 3) Paging: ? Returns specific page with dynamic size of page ? Inputs are ¡°Page number¡± and ¡°Page size¡± ? Result are tens of rows from all records ? Common task to return just previous / current / next page ? We don¡¯t want to load all records, send them to application and then manipulate / filter / sort 12/06/2017 ºÝºÝߣ 6/11
- 7. 4) Running Totals: ? Keep accumulating the values in one attribute (the aggregated element) based on ordering defined by another attribute or attributes ? There are many examples in life for calculating running totals like: ? Calculating bank account balances ? Tracking product stock levels in a warehouse ? Tracking cumulative sales values ? Prior to SQL Server 2012, the set-based solutions used to calculate running totals were extremely expensive ( Subtree cost fall from 43,5 to 0,09 with windowing functions) 12/06/2017 ºÝºÝߣ 7/11
- 8. 5) Gaps and Islands in data: ? The gaps problem involves identifying the ranges of missing values in the sequence: ? Input (2),(3),(7),(8),(9),(11),(15),(16),(17),(28) ? Result are ranges: 4-6, 10, 12-14, 18-27 ? Using LAG and LEAD functions ? The islands problem involves identifying ranges of existing values in the numeric sequence. ? Result are ranges: 2-3, 7-9, 11, 15-17, 28 12/06/2017 ºÝºÝߣ 8/11
- 9. Windowing Functions ¨C what to take home ? OVER() looks tricky but is worth of additional effort ? WFs are standard across DB providers ? WF are usually faster than other approaches ? Use POC index (Partitioning, Ordering, Covering) ? SUM( Qty ) OVER ( PARTITION BY [ShipperId] ORDER BY OrderYearMonth ) ? Partitioning: [ShipperId] ? Ordering: [OrderYearMonth] ? Covering: [Qty] ? CREATE INDEX Idx_ShipperId_OrderYearMonth_i_Qty ON Table ( ShipperId /* P */, OrderYearMonth /* O */) INCLUDE(Qty /* C */); 12/06/2017 ºÝºÝߣ 9/11
- 10. Sources: https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql- server/ http://www.sqlpassion.at/archive/2015/01/22/sql-server-windowing-functions- rows-vs-range/ Microsoft? SQL Server ? 2012 High-Performance T-SQL Using Window Functions Itzik Ben-Gan 12/06/2017 ºÝºÝߣ 10/11
- 11. Thanks for your attention! By ¡°Slovak SQL Server & BI User Group¡± slovak@sqlpass.org 12/06/2017 ºÝºÝߣ 11/11 Please let me feedback on SurveyMonkey: