Doris Data Aggregation (GROUP BY, SUM, COUNT, HAVING, etc.)

what is data aggregation?

Data Aggregation refers to summarizing, calculating, and analyzing a set of data to produce richer and more meaningful data.

Doris Data Aggregation

Doris is a distributed relational database based on columnar storage and partitioned indexing. It supports various data aggregation algorithms, such as GROUP BY, SUM, COUNT, etc., for processing large datasets.

GROUP BY

GROUP BY is one of the most common methods for data aggregation, allowing us to categorize and summarize data. In Doris, the syntax for GROUP BY is:

SELECT column1, column2
FROM table_name
GROUP BY column1;

SUM

SUM is a function used to calculate the total of data. The syntax used in Doris is:

SELECT SUM(column_name)
FROM table_name;

For example:

SELECT SUM(salary) FROM employees;

COUNT

COUNT is a function used to count the number of data items. The syntax used in Doris is:

SELECT COUNT(*)
FROM table_name;

For example:

SELECT COUNT(*) FROM orders;

HAVING

HAVING is a keyword used for filtering conditions after data aggregation. The syntax used in Doris is:

SELECT column1, column2
FROM table_name
GROUP BY column1
HAVING condition;

For example:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Example

The following is an example demonstrating the use of data aggregation:

-- Create a sample table
CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(255),
  total_price DECIMAL(10,2)
);

-- Insert some data
INSERT INTO orders (order_id, customer_name, total_price) VALUES
  (1, 'John', 100.00),
  (2, 'Jane', 200.00),
  (3, 'Bob', 300.00);

-- Data aggregation
SELECT department, SUM(total_price) AS total_sales
FROM orders
GROUP BY department
HAVING SUM(total_price) > 2500;

In this example, we first create a table named orders containing order_id, customer_name, and total_price. Then we use the SUM function to aggregate by department and filter out departments where the total sales exceed 2500.