Window Function Queries

The window function performs a calculation on the related row set in the data set and returns a result set.

Compared with the calculation performed by the aggregate function, the window function does not aggregate multiple rows into a single output row.

-- Create a table and insert data into the table.
gaussdb=#CREATE TABLE fruit_sale (
  "statistical_date" date,
  "product" varchar(255),
  "year" varchar(5),
  "sales_quantity" numeric(8),
  "amount" numeric(8)
);
                  
gaussdb=#INSERT INTO fruit_sale VALUES ('2024-01-01', 'Watermelon', '2024', 1721, 253541);
gaussdb=#INSERT INTO fruit_sale VALUES ('2024-01-01', 'Apple', '2024', 5559, 269419);
gaussdb=#INSERT INTO fruit_sale VALUES ('2024-02-01', 'Watermelon', '2024', 4711, 129644);

-- Collect statistics on the sales volume of all fruits.
gaussdb=#SELECT *,SUM(sales_quantity) OVER (PARTITION by null) total_qty FROM fruit_sale;
 statistical_date | product | year | sales_quantity | amount | total_qty 
------------------+---------+------+----------------+--------+-----------
 2024-01-01       | Watermelon    | 2024 |           1721 | 253541 |     11991
 2024-01-01       | Apple    | 2024 |           5559 | 269419 |     11991
 2024-02-01       | Watermelon    | 2024 |           4711 | 129644 |     11991
(3 rows)

-- Drop.
gaussdb=#DROP TABLE fruit_sale;