<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Rutuja Bauskar on Medium]]></title>
        <description><![CDATA[Stories by Rutuja Bauskar on Medium]]></description>
        <link>https://medium.com/@rutujabauskar28?source=rss-9c2b805b1650------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*TQ9w_JLJmf2ej9rgAYN7rQ.jpeg</url>
            <title>Stories by Rutuja Bauskar on Medium</title>
            <link>https://medium.com/@rutujabauskar28?source=rss-9c2b805b1650------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 12:40:18 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rutujabauskar28/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[c]]></title>
            <link>https://medium.com/@rutujabauskar28/c-bac0986cb170?source=rss-9c2b805b1650------2</link>
            <guid isPermaLink="false">https://medium.com/p/bac0986cb170</guid>
            <dc:creator><![CDATA[Rutuja Bauskar]]></dc:creator>
            <pubDate>Sat, 24 Jun 2023 12:13:38 GMT</pubDate>
            <atom:updated>2023-06-26T08:54:37.858Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Level Up Your Data Transformations: Exploring Window Functions in Apache Spark</strong></h3><p>In this blog I would like to explain about window functions and its advantages, also different types of window functions along with examples.Before going deep into understanding about what window functions are and how they can be used, let’s first try to understand what was the need for using Window functions.</p><p>So basically in Spark , there are two types of functions already being used known as the aggregate functions and the built in functions.</p><p>Aggregate functions are used to combine a group of rows and give a single result for the whole group. Examples of such functions are sum , max ,etc</p><p>Built-in functions such as split , round , etc are used when we have to apply a particular function on every input row and get the result for each row.</p><p>But there can be one more scenario wherein you want to get a result for every input row for a particular group of rows.Here , window functions come to our rescue. By using window functions we can simplify the code and also optimise the performance.</p><p>So , you must be wondering what window functions are and how it can help us to achieve a simplified and easy code.</p><p><strong><em>“Window functions are used to calculate a return value for each record present in a group of rows called as a frame or window”</em></strong></p><p>Window functions are also used to find out the ranking , cumulative distance , moving average , first value , current row , etc.</p><p><strong>How can window functions be helpful?</strong></p><p>Window functions help to maintain separate identity for each row. When compared with group by we just get the single result for a group of rows.</p><p>It helps us to avoid self joins when we want any other data along with the return value of the applied function which helps in the performance also.</p><p>Let’s see an example for this.</p><p>First we will have to create a dataframe. Below is the code to create a dataframe which contains employee records. Employee has three attributes: name , salary and department.We will be using this dataframe for all the examples below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3OzcvE6XJZKgyh52" /></figure><p>By using the show() method we can see the records present in the database. By default it will return 20 records , but if you wish to see more records you can specify that as the first parameter in the method. So below are the ten records which we created.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/686/0*OQwkCROSztaZbutA" /></figure><p>Firstly I have applied groupBy function on the department column and max function on the salary column to get the maximum salary.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*2S7g50luZNbGNPsn" /></figure><p>Output : Here you get only two columns: the department and the maximum salary for each department.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/452/0*DYnQpFGWWVSSGJAC" /></figure><p>But what if you want other columns as well….?? Then you will have to perform a self join as shown in below screenshot.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*wK0yw-NlkdwoGG9-" /></figure><p>Output : Here you get to know for each row what is the maximum salary</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/880/0*X3ore1o86H6E6Q4B" /></figure><p>This can be simply achieved with the help of window functions.Because window functions do not give an aggregated result for a particular group of rows and it helps us to return the whole dataframe with all the columns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*A5tHCZUv9HHNxDGk" /></figure><p>Output: Here we get the maximum salary for each current row in a particular department.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/1*ZQaORnlcOSrKbpJq_tkhgA.png" /></figure><p>So window functions helps you to avoid using joins which can provide better performance.</p><p><strong>Syntax of window functions:</strong></p><p>You can use these functions by using over clause on the supported function in spark sql or by over method on supported function of dataframe API</p><p>Spark SQL example — sum(salary) over (…);</p><p>DataFrame API example — rank().over(…)</p><p>There are three types of window functions ⇒</p><ol><li><strong>Aggregate functions</strong>:- sum, max, min, avg , count</li></ol><p>2. <strong>Ranking functions</strong>:- rank , dense_rank, ntile, row_number, percent_rank</p><p>3. <strong>Analytical functions</strong>:- lead, lag , first_value, last_value, cume_dist</p><p>Let’s understand each of them by using Dataframe API methods.</p><p>For this , we will need to create a <strong>window specification</strong>.</p><p><strong>Window specification</strong> is used to know what all rows would be included in a window or frame. A frame or a window is a group of records which we would be considering to apply the window functions on.Window specification can be made with the help of Window object and for this we will have to import Window from org.apache.spark.sql.expressions library</p><p>A window specification is consists of three parts :</p><ol><li><strong>Partitioning</strong> — Here we use the PARTITION BY keyword to divide the whole input data into different sets based on a key.</li><li><strong>Ordering</strong> — For ordering we use the ORDER BY keywords to sort the data in an ordered way. By default the order is ascending , but if we want descending we can specify it.</li><li><strong>Frame</strong> — A frame refers to the group of rows which can be set to have an upper bound or lower bound from the current row. For example , if we set the frame as, the current row to the four rows following the current row means that the frame will include the current row and the next four rows after the current row.There are two types of frames which are the row frames and the range frames.</li></ol><p>So now we have some idea about window specification, let’s move on to the examples</p><p><strong><em>Aggregate Function</em></strong></p><p>These functions are used to<strong> </strong>get the sum max min count for a group of rows</p><p><strong>1.</strong> <strong>max() &amp; min() function</strong></p><p>Now suppose you want to know which employees have the highest and lowest salary in each department.To calculate this we can use the max() and min() aggregate functions here.First we will have to create a window spec which will partition the data by department.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*k00wkkWrFrxgq5jb" /></figure><p>As you can see above we are using the max() and min() aggregate window function over the window spec.These functions can be imported from the spark sql functions library. Basically the whole dataset would be divided into partitions/frames as per the department and then on each row of the partition/frame we would be applying the window function.</p><p>In below output you can see the min and max salary for each row in the department</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*AVLZCCJ7QVKey5hf" /></figure><p><strong>2. avg() &amp; sum()</strong></p><p>Like max min we also have average and sum functions as shown in the example below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_0gqpGTzr4K5SVnu" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4RmNq6GOTwe0bcpM" /><figcaption>DataFrame with average and sum of the salary</figcaption></figure><p><strong>Ranking Functions</strong></p><p>Let’s try to understand now about the ranking functions</p><p><strong>1. rank()</strong></p><p>Now suppose you want to rank the employees in each department based on their salaries or you want to know who is the highest and second highest paid employee. rank() function can be used to achieve this.First we will create a window spec by using partitionBy on department and orderBy on salary in descending order so that rank can be given from the maximum salary.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*cQRSAOxPPQHuwErX" /></figure><p>Below you can see the result which now contains a new column called rank. This function ranks each record of the partition and it will skip the next rank if there is any duplicate record.For eg in below screenshot you can see for the Sales department, there are two employees who have 2000 as salary, so both of them are having the same rank as 2. But for the next employee David the rank is 4 because there was a duplicate and the rank 3 was skipped.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/760/0*d3Vggx7qURqXGzWb" /></figure><p><strong>2. dense_rank()</strong></p><p>This function works similar to rank with one difference which is, dense_rank does not skip any ranks even if there are duplicates.Dense_rank should be used majorly when you have to calculate top most things based on the ranks as it does not skip any rank.</p><p>To calculate the dense rank we have to use dense_rank() function over the window specification</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fo-gOkvrnAQfcl-M" /></figure><p>As you can see in the screenshot below, employees have been ranked in order of their salaries in each particular department.But when there is a duplicate the rank is not skipped. In the example for the duplicate records in the Sales department the rank is given as 2 and for the next record the rank is 3.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/0*r7wHKtqG4NCNjJgJ" /></figure><p><strong>3. row_number()</strong></p><p>Row_number function is used to assign a number to the row within the frame. In case of row_number when there are duplicates it is not determined which value will get assigned to the row.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*K0w-57Ai3v3vq5dw" /></figure><p>A unique row number is assigned to each row within the window in the below result</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/0*estyoZznrmSmfONd" /></figure><p><strong>4. percent_rank()</strong></p><p>This function is used to get the relative percentile rank within the frame.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*qPjNIk7WIuXdmXuj" /></figure><p>As you can see in the below result , percent_rank returns percentile rank within the window frame.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1020/0*ja0TyEUXY4NhqpO5" /></figure><p><strong>5. ntile()</strong></p><p>This function is used to divide the rows within partitions into buckets ranging from 1 to ’n’ both inclusive whereas n is the specified parameter</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*vG3my6-qRjZcUsba" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/788/0*S2hhwoHJLW4Oi151" /><figcaption>Dataframe showing specified buckets for each employee</figcaption></figure><p><strong>Analytical Functions</strong></p><p>These functions are most commonly used to do comparison of the current row with the previous or following records, to get the cumulative distribution</p><p>Let’s see examples of some functions</p><p><strong>1. lead()</strong></p><p>This function is used to get the following records from the current record as per the specified offset. We can also set a default value for rows which do not have any leading records.I have set the offset as 2 and default value as 0 in the below example</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Xk2YYxVjGEXeYh7N" /></figure><p>As you can see in the below result the lead function returns the second row after the current row for every record as the offset is 2. Also the default value is 0 , so for those records who don’t have following rows gets the default value. If we do not specify any default value then by default null gets assigned. There is one more property called as “ignoreNulls” to ignore null values in the data frame.This is a boolean value which we can set to true or false</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/776/0*aeo-G2tqUxEX0POc" /></figure><p><strong>2. lag()</strong></p><p>This function is used to get the previous records from the current record as per the specified offset. We can also set a default value for rows which do not have any previous records.I have set the offset as 1 and default value as 0 in the below example</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KDI18NnWm5i7pKMB" /></figure><p>As you can see in the below result the lag function returns the previous row before the current row for every record as the offset is 1. Also the default value is 0 , so for those records who don’t have preceding rows gets the default value. If we do not specify any default value then by default null gets assigned. There is one more property called as “ignoreNulls” to ignore null values in the data frame.This is a boolean value which we can set to true or false</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/778/0*pMDyLQATVHShbolb" /></figure><p><strong>3. cume_dist()</strong></p><p>This function is used to return the cumulative distribution of the values present in the frame.We can apply the cume_dist() function on the window specification created earlier</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*LjNp91yO0EHaE_7U" /></figure><p>Cumulative distribution is calculated for each row in a frame as shown below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/856/0*y2I9RKHBr-vH2ae8" /></figure><p>I hope you have got some understanding about the different window functions used in the spark.</p><p>Now let’s take a look at defining custom boundaries for the window .</p><p><strong>Defining boundaries for a window</strong></p><p>By default , boundaries are defined by the partition and the ordering we specify in the window specification. But suppose you want to get the maximum salary of an employee within a specific range or by comparing the previous and following records. For this we can set some specific boundary values for the window by using two functions such as the rowsBetween and rangeBetween.</p><p><strong>rowsBetwee</strong>n is based on the physical offset of the rows and <strong>rangeBetween</strong> refers to the logical range for the given column in the dataframe. These functions take two parameters start and end which is use to specify the start position and end posit</p><p>There can be three different types of boundaries we can use:</p><p><strong>Window.currentRow</strong> — refers to the current row</p><p><strong>Window.UnboundedPreceding</strong> — This can be used to have a unbounded start for the frame</p><p><strong>Window.UnboundedFollowing</strong> — This can be used to have a unbounded end for the frame</p><p>Let’s check an example to see the usage of rowsBetween and rangeBetween while getting the max salary</p><p><strong>-&gt; rangeBetween(start, end)</strong></p><p>So we have to use the rangeBetween method when defining the window specification. Here I have given the start as the current row and the end as the last row of the partition..</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dXt65vw5GhRuGSgj" /></figure><p>As you can see in the below result for the first row in the Sales department, the range was from the current row which was 1000 and the end was the unbounded following which was the last row of the partition i.e 5000. So when we tried to calculate the max from 1000 to 5000 the maximum salary was 5000</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/762/0*Ytp7Xua37txKSwqe" /></figure><p><strong>-&gt; rowsBetween(start, end)</strong></p><p>We can also set some offset positions such as I have set in the example below. I have set -1 as the start so it will refer to the preceding record before the current row and 1 as the end so it will refer to the next following record from the current row position</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*kbsCfD9wQFv3_WUA" /></figure><p>You can see in the output for the admin department, to calculate the max it will take the current row, one record before the current row and one record after the current row. Out of these three records it will check which is the maximum one and return that salary as the maximum salary. So incase of employee Alexander, it will check between 1000 and 2500 as there is no preceding record to the current row. 2500 is the maximum value out of the two so it’s assigned as the max salary for that employee record. Similarly for employee james, current value is 2500 , preceding is 1000 and following is 2800.Out of these three 2800 get assigned as the maximum salary as it is the highest value.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/776/0*sc4sk6zw4kFrdDGL" /></figure><p><strong>Summary:</strong></p><p>In this blog , we tried to cover what window functions are, it’s syntax and how they can be helpful to us. Also we got to know about window specification and different types of window functions , its usages and some examples.</p><p>Hope you enjoyed learning about window functions in spark…!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bac0986cb170" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>