comphead commented on code in PR #66:
URL: https://github.com/apache/datafusion-site/pull/66#discussion_r2044952546


##########
content/blog/2025-04-17-user-defined-window-functions.md:
##########
@@ -0,0 +1,427 @@
+---
+layout: post
+title: User defined Window Functions in DataFusion 
+date: 2025-04-17
+author: Aditya Singh Rathore
+categories: [tutorial]
+---
+
+<!--
+{% comment %}
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements.  See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to you under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License.  You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+{% endcomment %}
+-->
+
+
+Window functions are a powerful feature in SQL, allowing for complex 
analytical computations over a subset of data. However, efficiently 
implementing them, especially sliding windows, can be quite challenging. With 
[Apache DataFusion]'s user-defined window functions, developers can easily take 
advantage of all the effort put into DataFusion's implementation.
+
+In this post, we'll explore:
+
+- What window functions are and why they matter
+
+- Understanding sliding windows
+
+- The challenges of computing window aggregates efficiently
+
+- How to implement user-defined window functions in DataFusion
+
+
+[Apache DataFusion]: https://datafusion.apache.org/
+
+## Understanding Window Functions in SQL 
+
+
+Imagine you're analyzing sales data and want insights without losing the finer 
details. This is where **[window functions]** come into play. Unlike **GROUP 
BY**, which condenses data, window functions let you retain each row while 
performing calculations over a defined **range** —like having a moving lens 
over your dataset.
+
+[window functions]: https://en.wikipedia.org/wiki/Window_function_(SQL)
+
+
+Picture a business tracking daily sales. They need a running total to 
understand cumulative revenue trends without collapsing individual 
transactions. SQL makes this easy:
+```sql
+SELECT id, value, SUM(value) OVER (ORDER BY id) AS running_total
+FROM sales;
+```
+
+```text
+example:
++------------+--------+-------------------------------+
+|   Date     | Sales  | Rows Considered               |
++------------+--------+-------------------------------+
+| Jan 01     | 100    | [100]                         |
+| Jan 02     | 120    | [100, 120]                    |
+| Jan 03     | 130    | [100, 120, 130]               |
+| Jan 04     | 150    | [100, 120, 130, 150]          |
+| Jan 05     | 160    | [100, 120, 130, 150, 160]     |
+| Jan 06     | 180    | [100, 120, 130, 150, 160, 180]|
+| Jan 07     | 170    | [100, ..., 170] (7 days)      |
+| Jan 08     | 175    | [120, ..., 175]               |
++------------+--------+-------------------------------+
+```
+**Figure 1**: A row-by-row representation of how a 7-day moving average 
includes the previous 6 days and the current one.
+
+
+This helps in analytical queries where we need cumulative sums, moving 
averages, or ranking without losing individual records.
+
+
+## User Defined Window Functions
+DataFusion's [Built-in window functions] such as `first_value`, `rank` and 
`row_number` serve many common use cases, but sometimes custom logic is 
needed—for example:
+
+- Calculating moving averages with complex conditions (e.g. exponential 
averages, integrals, etc)
+
+- Implementing a custom ranking strategy
+
+- Tracking non-standard cumulative logic
+
+Thus, **User-Defined Window Functions (UDWFs)** allow developers to define 
their own behavior while allowing DataFusion to handle the calculations of the  
windows and grouping specified in the `OVER` clause
+
+Writing a user defined window function is slightly more complex than an 
aggregate function due
+to the variety of ways that window functions are called. I recommend reviewing 
the
+[online 
documentation](https://datafusion.apache.org/library-user-guide/adding-udfs.html#registering-a-window-udf)
+for a description of which functions need to be implemented. 
+
+[Built-in window functions]: 
https://datafusion.apache.org/user-guide/sql/window_functions.html
+
+## Understanding Sliding Window 
+
+Sliding windows define a **moving range** of data over which aggregations are 
computed. Unlike simple cumulative functions, these windows are dynamically 
updated as new data arrives.
+
+For instance, if we want a 7-day moving average of sales:
+
+```sql
+SELECT date, sales, 
+       AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT 
ROW) AS moving_avg
+FROM sales;
+```
+Here, each row’s result is computed based on the last 7 days, making it 
computationally intensive as data grows.
+
+## Why Computing Sliding Windows Is Hard
+
+Imagine you’re at a café, and the barista is preparing coffee orders. If they 
made each cup from scratch without using pre-prepared ingredients, the process 
would be painfully slow. This is exactly the problem with naïve sliding window 
computations.

Review Comment:
   reg to this paragraph computing sliding window is hard because it is slow? 



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org

Reply via email to