Doris uses window functions for analysis

What is a Window Function?

Window Function is a method used in SQL to calculate statistical indicators such as average, sum, and variance based on values within a data range.

Why use Window Functions?

1. Calculate values within a data range: Data can be divided into certain ranges, and calculations can be performed on the data within those ranges, such as calculating the average or total within a specific area.

2. High flexibility: Different window functions can be used to implement different types of statistical operations, such as calculating the difference between each data point and its surrounding values, or calculating the average of each group of data.

3. Solve problems: It can solve problems that cannot be directly addressed in traditional methods, such as calculating the sum or average within a specific area.

Basic Concepts of Using Window Functions

1. window_frame: Defines the scope of the window, which can be specific values or determined automatically.

2. row_partitioning: Classifies data by dividing it into different parts based on certain conditions.

3. column_partitioning: Similarly classifies data.

Common Window Functions in Doris

1. ROW_NUMBER()

Converts all rows into unique integer numbers, typically used to mark the order of data.

SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM table_name;

2. RANK()

Assigns a rank to each number, distributing the ranking based on the size of the value. The first few numbers with the same value will have the same rank.

SELECT *, RANK() OVER (ORDER BY id) AS rank
FROM table_name;

3. DENSE_RANK()

Assigns a rank to each number, distributing the ranking based on the size of the value. The first few numbers with the same value will have the same rank.

SELECT *, DENSE_RANK() OVER (ORDER BY id) AS rank
FROM table_name;

4. NTILE()

Divides data into one or more segments, with each segment containing a certain proportion of the total data.

SELECT *, NTILE(5) OVER (ORDER BY salary) AS ntile
FROM employees;

5. SUM(), AVG(), MAX(), MIN()

These functions can be used within a window to calculate values within a specified range.

SELECT department, AVG(salary),
       SUM(salary),
       MIN(salary),
       MAX(salary)
FROM employees
GROUP BY department;

Examples

CREATE TABLE employees (
    id INT,
    name VARCHAR(255),
    salary DECIMAL(10,2)
);

INSERT INTO employees (id, name, salary) VALUES 
(1,'John',10000.00), 
(2,'Mary',20000.00), 
(3,'David',30000.00), 
(4,'Jane',40000.00), 
(5,'Bob',50000.00),
(6,'Alice',60000.00);

SELECT department, AVG(salary) OVER (PARTITION BY department ORDER BY salary) AS avg_salary,
       SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;

Practice Exercises

CREATE TABLE students (
    id INT,
    name VARCHAR(255),
    grade DECIMAL(2,1)
);

INSERT INTO students (id, name, grade) VALUES 
(101,'Li',88.5), 
(102,'Huang',90.0), 
(103,'Wang','75.6'),
(104,'Zhang','98.9');

SELECT *, AVG(DISTANCE()) OVER (PARTITION BY class ORDER BY score DESC) AS avg_distance
FROM students;