Analytics Functions

Analytic functions in SQL are powerful tools designed to perform complex calculations across sets of rows that are related to the current row. These functions are integral to advanced data analysis and reporting tasks, enabling users to derive meaningful insights from large datasets. Unlike traditional aggregate functions, which reduce data to a single summary value, analytic functions allow each row to retain its individuality while still participating in broader calculations.

Key Analytic Functions

Analytic functions like LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() offer advanced capabilities for accessing and comparing data points across rows. These functions enable users to perform detailed comparisons, identify trends, and extract specific data points within a window of rows, thereby facilitating comprehensive data analysis and decision-making processes.

LAG() and LEAD()

• Functionality: Access data from preceding or following rows.
• Syntax:

LAG(column_name, offset, default) OVER (PARTITION BY column_name ORDER BY column_name)
LEAD(column_name, offset, default) OVER (PARTITION BY column_name ORDER BY column_name)

FIRST_VALUE() and LAST_VALUE()

• Functionality: Retrieve the first or last value in a window.
• Syntax:

FIRST_VALUE(column_name) OVER (PARTITION BY column_name ORDER BY column_name)
LAST_VALUE(column_name) OVER (PARTITION BY column_name ORDER BY column_name)

NTH_VALUE()

• Functionality: Retrieve the nth value in a window.
• Syntax:

NTH_VALUE(column_name, n) OVER (PARTITION BY column_name ORDER BY column_name)

Practical Examples

LAG() Example

SELECT employee_id, 
       salary, 
       LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
FROM employees;

LEAD() Example

SELECT employee_id, 
       salary, 
       LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary
FROM employees;

FIRST_VALUE() Example

SELECT employee_id, 
       salary, 
       FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS min_salary
FROM employees;

LAST_VALUE() Example

SELECT employee_id, 
       salary, 
       LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS max_salary
FROM employees;

NTH_VALUE() Example

SELECT employee_id, 
       salary, 
       NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY salary) AS second_salary
FROM employees;

Use Cases

1. Trend Analysis:
   Compare current data with previous or next values.
2. Boundary Analysis:
   Identify minimum or maximum values in partitions.
3. Nth Position Analysis:
   Extract specific positional values.

Benefits

• Enhanced Data Comparison:
  Easily compare rows within partitions.
• Detailed Insights:
  Provides access to specific data points within a window.
• Improved Analysis:
  Facilitates comprehensive trend and pattern analysis.