{"id":3065,"date":"2019-07-21T19:52:18","date_gmt":"2019-07-21T07:52:18","guid":{"rendered":"https:\/\/www.helenanderson.co.nz\/?p=3065"},"modified":"2020-05-25T20:18:41","modified_gmt":"2020-05-25T08:18:41","slug":"sql-window-functions-part-2","status":"publish","type":"post","link":"https:\/\/helenanderson.co.nz\/sql-window-functions-part-2\/","title":{"rendered":"Why you need SQL Window Functions (part 2)"},"content":{"rendered":"\n

Welcome back to the world of Window Functions<\/a>.<\/p>\n\n\n\n

Window functions are closely related to\u00a0Aggregate Functions<\/a>. But rather than collapsing all the rows we want into one row, we keep them and add a new column with a running total, rank or moving average. This becomes our \u2018window frame.\u2019<\/p>\n\n\n\n

It becomes clearer with examples and pictures, so if you\u2019re interested in using this powerful tool, keep reading.<\/p>\n\n\n\n

\n\n\n\n

Introduction<\/a>
Setting the Scene<\/a>
Bucket rows into groups<\/a>
Access a total from an earlier row<\/a>
Use Window Functions to perform calculations<\/a><\/p><\/blockquote>\n\n\n\n

\n\n\n\n

Introduction<\/h2>\n\n\n\n

Window functions come in three main types. These are:<\/p>\n\n\n\n

Aggregate Window Functions<\/strong>
These use aggregate functions<\/a> like SUM, COUNT, MAX, MIN over a set of rows and return a single result from the query.<\/p>\n\n\n\n

Ranking Window Functions<\/strong>
These assign a \u2018rank\u2019 to a set of rows and using RANK, DENSE_RANK, ROW_NUMBER, NTILE<\/p>\n\n\n\n

Value Window Functions<\/strong>
These use LAG, LEAD, FIRST_VALUE, LAST_VALUE to access a previous row without having to do a self join<\/a>.<\/p>\n\n\n\n

\n\n\n\n

Setting the Scene<\/h2>\n\n\n\n

This post is picking up where we left off in\u00a0Part One<\/a>, where we used window functions to answer questions from a Sales Manager<\/a> who is setting targets for the year.<\/p>\n\n\n\n

In the last post we covered aggregate window functions and how to use RANK(). In this post, we\u2019ll be exploring how to use NTILE()<\/strong>, then exploring how we can use value window functions to answer questions using sales data.<\/p>\n\n\n\n

\n\n\n\n
The raw Sales Order table, not much use to your Sales Manager<\/figcaption><\/figure>\n\n\n\n
\n\n\n\n

Bucket rows into groups<\/h2>\n\n\n\n

When we left her in the last post<\/a> we had provided the Sales Manager two tables. One showing daily sales with a running total, and all sales ranked by their dollar value.<\/p>\n\n\n\n

This time the Sales Manager would like to give a bonus to the Territory who has bought in the top 25% of customers over the last year.<\/p>\n\n\n\n

To do this we can use NTILE()<\/strong> to bucket the dollar values into four groups that represent four quartiles.<\/p>\n\n\n\n

\n\n\n\n
select\n  territoryid,\n  customerid,\n  sum(subtotal) as subtotal,\n  ntile(4) over(order by sum(subtotal)) as bucket\nfrom \n  sales.salesorderheader \nwhere sales_date between \n'2018-01-01' and '2019-01-01'\ngroup by \n  territoryid, \n  customerid<\/code><\/pre>\n\n\n\n
\n\n\n\n
NTILE()<\/strong> can be used to bucket groups of rows and can be adjusted by changing the number in the brackets.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n
\n\n\n\n
Access a total from an earlier row<\/h2>\n\n\n\n
The Sales Manager needs one last thing before the targets for next year can be locked in. A table that shows the \u2018big customers\u2019 identified earlier and the dates they are making orders.<\/p>\n\n\n\n
What the Sales Manager needs for this last piece of work is an idea of how many days there are between orders for these big customers. If she can anticipate when they are going to buy next we can get in touch with them earlier.<\/p>\n\n\n\n
\n\n\n\n
select\n  customerid, \n  subtotal, \n  sale_date,\n  lag(sale_date) over(order by sale_date) as last_order,\nfrom sales.salesorderheader \nwhere \n  customerid = 11078\n  and sale_date between \n'2018-01-01' and '2019-01-01'<\/code><\/pre>\n\n\n\n
\n\n\n\n
By using LAG()<\/strong> we are able to reach up and return the result from the previous row and add it to a new column. LEAD()<\/strong> works in the same way but in reverse.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n
\n\n\n\n
Use Window Functions to perform calculations<\/h2>\n\n\n\n
To make this useful for the Sales Manager we can use CAST and a new column to calculate the difference between the dates.<\/p>\n\n\n\n
\n\n\n\n
select\n  customerid, \n  subtotal, \n  sale_date,\n  lag(sale_date) over(order by sale_date) as last_order,\n  cast(sale_date - (lag(sale_date) \nover(order by sale_date)) as int) as days_between\nfrom sales.salesorderheader \nwhere \n  customerid = 11078\n  and sale_date between '2018-01-01' and '2019-01-01'<\/code><\/pre>\n\n\n\n
\n\n\n\n
<\/figure>\n\n\n\n
\n\n\n\n
I hope you\u2019ve enjoyed these two posts on SQL Window Functions. How do you use these in your work? Do you find them a useful and powerful tool?<\/p>\n\n\n\n
\n\n\n\n
Photo by Nubia Navarro (nubikini)<\/a><\/strong> from Pexels<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server Window Functions are incredibly powerful and allow us to access a total from another row and bucket data to show top performers.<\/p>\n","protected":false},"author":1,"featured_media":3066,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":""},"categories":[214],"tags":[198,199],"yoast_head":"\nWhy you need SQL Window Functions (part 2) | Helen Anderson<\/title>\n<meta name=\"description\" content=\"SQL Server Window Functions are incredibly powerful and allow us to access a total from another row and bucket data to show top performers.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Why you need SQL Window Functions (part 2) | Helen Anderson\" \/>\n<meta property=\"og:description\" content=\"SQL Server Window Functions are incredibly powerful and allow us to access a total from another row and bucket data to show top performers.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Helen Anderson\" \/>\n<meta property=\"article:published_time\" content=\"2019-07-21T07:52:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-05-25T08:18:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2020\/05\/sql-window-functions-2.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"853\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@helenanders26\" \/>\n<meta name=\"twitter:site\" content=\"@helenanders26\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\">\n\t<meta name=\"twitter:data1\" content=\"3 minutes\">\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/13.237.200.153\/#website\",\"url\":\"https:\/\/13.237.200.153\/\",\"name\":\"Helen Anderson\",\"description\":\"Data Analyst | Technical Writer\",\"publisher\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/13.237.200.153\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-NZ\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/#primaryimage\",\"inLanguage\":\"en-NZ\",\"url\":\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2020\/05\/sql-window-functions-2.jpg\",\"width\":1280,\"height\":853,\"caption\":\"sql window functions\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/#webpage\",\"url\":\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/\",\"name\":\"Why you need SQL Window Functions (part 2) | Helen Anderson\",\"isPartOf\":{\"@id\":\"https:\/\/13.237.200.153\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/#primaryimage\"},\"datePublished\":\"2019-07-21T07:52:18+00:00\",\"dateModified\":\"2020-05-25T08:18:41+00:00\",\"description\":\"SQL Server Window Functions are incredibly powerful and allow us to access a total from another row and bucket data to show top performers.\",\"inLanguage\":\"en-NZ\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/\"]}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/#webpage\"},\"author\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"headline\":\"Why you need SQL Window Functions (part 2)\",\"datePublished\":\"2019-07-21T07:52:18+00:00\",\"dateModified\":\"2020-05-25T08:18:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/#webpage\"},\"publisher\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"image\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-window-functions-part-2\/#primaryimage\"},\"keywords\":\"analysis,sql\",\"articleSection\":\"Data Analysis\",\"inLanguage\":\"en-NZ\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\",\"name\":\"Helen Anderson\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/13.237.200.153\/#personlogo\",\"inLanguage\":\"en-NZ\",\"url\":\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2019\/11\/helen-anderson-profile-selects-FA-1000.jpg\",\"width\":1000,\"height\":1000,\"caption\":\"Helen Anderson\"},\"logo\":{\"@id\":\"https:\/\/13.237.200.153\/#personlogo\"},\"description\":\"Hi, I'm Helen. I'm a data analyst, technical writer, and AWS Data Hero. I interpret the story behind the numbers, build data applications, and grow analyst and developer communities - currently at Kiwibank.\",\"sameAs\":[\"http:\/\/www.helenanderson.co.nz\/\",\"https:\/\/www.instagram.com\/helenanders26\/\",\"https:\/\/www.linkedin.com\/in\/helenanders26\/\",\"https:\/\/twitter.com\/helenanders26\",\"https:\/\/www.youtube.com\/channel\/UCttVhJizwkhgmMlDBMUE0wQ\"]}]}<\/script>\n","_links":{"self":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/3065"}],"collection":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/comments?post=3065"}],"version-history":[{"count":6,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/3065\/revisions"}],"predecessor-version":[{"id":3298,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/3065\/revisions\/3298"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/media\/3066"}],"wp:attachment":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/media?parent=3065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/categories?post=3065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/tags?post=3065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}