Support for Advanced Analysis Functions

Availability

This feature is available since openGauss 1.1.0.

Introduction

None

Benefits

Window functions are provided for advanced data analysis and processing. The window function groups the data in a table in advance. Each row belongs to a specific group. Then, a series of association analysis calculations are performed on the group. In this way, some attributes of each tuple in the set and association information with other tuples can be mined.

Description

The following uses an example to describe the window analysis function: Compare the salary of each person in a department with the average salary of the department.

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; 
depname | empno | salary | avg
-----------+-------+--------+----------------------- 
develop | 11 | 5200 | 5020.0000000000000000 
develop | 7 | 4200 | 5020.0000000000000000 
develop | 9 | 4500 | 5020.0000000000000000 
develop | 8 | 6000 | 5020.0000000000000000 
develop | 10 | 5200 | 5020.0000000000000000 
personnel | 5 | 3500 | 3700.0000000000000000 
personnel | 2 | 3900 | 3700.0000000000000000 
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667 
sales | 4 | 4800 | 4866.6666666666666667 
(10 rows)

The analysis function avg(salary) OVER (PARTITION BY depname) easily calculates each employee's salary and the average salary of the department.

Currently, the system supports the following analysis functions: row_number(), rank(), dense_rank(), percent_rank(), cume_dist(), ntile(), lag(), lead(), first_value(), last_value(), and nth_value(). For details about functions and statements, see Window Functions.

Enhancements

None

Constraints

None

Dependencies

None