PostgreSQL 系统函数

Halo 数据库在 PostgreSQL 兼容模式下支持完整的聚合函数和窗口函数。

聚合函数

支持 SUM、AVG、COUNT、MAX、MIN 等标准聚合函数。

postgres=# CREATE TABLE halo_test (id INT, product TEXT, amount NUMERIC);
CREATE TABLE

postgres=# INSERT INTO halo_test VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 250);
INSERT 0 4

postgres=# SELECT product, SUM(amount) AS total FROM halo_test GROUP BY product;
 product | total 
---------+-------
 B       |   450
 A       |   250
(2 行记录)

postgres=# DROP TABLE halo_test;
DROP TABLE

窗口函数

支持 RANK、DENSE_RANK、ROW_NUMBER、LEAD、LAG 等窗口函数。

postgres=# CREATE TABLE halo_test (id INT, product TEXT, amount NUMERIC);
CREATE TABLE

postgres=# INSERT INTO halo_test VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 250);
INSERT 0 4

postgres=# SELECT id, product, amount,
    RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank
FROM halo_test;
 id | product | amount | rank 
----+---------+--------+------
  3 | A       |    150 |    1
  1 | A       |    100 |    2
  4 | B       |    250 |    1
  2 | B       |    200 |    2
(4 行记录)

postgres=# DROP TABLE halo_test;
DROP TABLE