How to use SQLite window functions?

SQLite's Window Functions provide powerful data analysis capabilities:

1. Window Function Concept

   • Window functions perform calculations on a set of rows but do not merge multiple rows into one
   • Similar to aggregate functions but retain detailed information of original rows
   • SQLite 3.25.0+ supports window functions

2. Window Function Syntax

   sql
   function_name(expression) OVER (
       [PARTITION BY partition_expression]
       [ORDER BY sort_expression]
       [frame_clause]
   )

3. Common Window Functions

   • Aggregate Functions: SUM(), AVG(), COUNT(), MAX(), MIN()
   • Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK()
   • Offset Functions: LAG(), LEAD()
   • Analytic Functions: FIRST_VALUE(), LAST_VALUE()

4. Ranking Function Examples

   sql
   -- ROW_NUMBER: Continuous ranking
   SELECT 
       name,
       score,
       ROW_NUMBER() OVER (ORDER BY score DESC) as rank_num
   FROM students;
   
   -- RANK: Same score same rank, skip subsequent ranks
   SELECT 
       name,
       score,
       RANK() OVER (ORDER BY score DESC) as rank_num
   FROM students;
   
   -- DENSE_RANK: Same score same rank, do not skip subsequent ranks
   SELECT 
       name,
       score,
       DENSE_RANK() OVER (ORDER BY score DESC) as rank_num
   FROM students;

5. Offset Function Examples

   sql
   -- LAG: Get value from previous row
   SELECT 
       date,
       sales,
       LAG(sales) OVER (ORDER BY date) as prev_sales,
       sales - LAG(sales) OVER (ORDER BY date) as growth
   FROM sales_data;
   
   -- LEAD: Get value from next row
   SELECT 
       date,
       sales,
       LEAD(sales) OVER (ORDER BY date) as next_sales
   FROM sales_data;

6. Partition Examples

   sql
   -- Partition by department, calculate rank within each department
   SELECT 
       department,
       name,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
   FROM employees;
   
   -- Calculate average salary for each department
   SELECT 
       department,
       name,
       salary,
       AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
   FROM employees;

7. Window Frame Examples

   sql
   -- Calculate moving average (3 days)
   SELECT 
       date,
       sales,
       AVG(sales) OVER (
           ORDER BY date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) as moving_avg
   FROM sales_data;
   
   -- Calculate running total
   SELECT 
       date,
       sales,
       SUM(sales) OVER (
           ORDER BY date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) as running_total
   FROM sales_data;

8. Practical Application Scenarios

   sql
   -- Calculate sales ranking
   SELECT 
       product_name,
       sales_amount,
       RANK() OVER (ORDER BY sales_amount DESC) as sales_rank
   FROM sales;
   
   -- Calculate year-over-year growth
   SELECT 
       year,
       month,
       revenue,
       LAG(revenue) OVER (ORDER BY year, month) as prev_revenue,
       (revenue - LAG(revenue) OVER (ORDER BY year, month)) / 
           LAG(revenue) OVER (ORDER BY year, month) * 100 as growth_rate
   FROM monthly_revenue;
   
   -- Find top 3 products in each category
   WITH ranked_products AS (
       SELECT 
           category,
           product_name,
           sales,
           RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_num
       FROM products
   )
   SELECT * FROM ranked_products WHERE rank_num <= 3;

9. Window Frame Types

   • ROWS: Based on physical row count
   • RANGE: Based on value range
   • GROUPS: Based on grouping

10. Performance Optimization

    • Create indexes for ORDER BY and PARTITION BY columns of window functions
    • Avoid using complex expressions in window functions
    • Consider using subqueries or CTE to optimize complex window functions

Window functions are an important tool for SQLite to perform complex data analysis.