Aggregate Functions

• any_value(expression)

  Description: Any expression in all input lines (the first expression by default)

  Argument types: any set, numeric, string, or date/time type

  Return type: same as the argument data type

  Example:

  MogDB=# create table test_any_value(a int, b int);
  CREATE TABLE
  MogDB=# insert into test_any_value values(1,1),(2,1),(3,2),(4,2);
  INSERT 0 4
  MogDB=# select any_value(a), b from test_any_value group by b;
  any_value | b
  -----------+---
          1 | 1
          3 | 2
  (2 rows)

• default(column_name)

  Description: Gets the default value output for a table field.

  Return type: text

  Example:

  MogDB=# create database test dbcompatibility 'B';
  CREATE DATABASE
  MogDB=# \c test
  Non-SSL connection (SSL connection is recommended when requiring high-security)
  You are now connected to database "test" as user "test".
  test=# CREATE TABLE TEST(id int default 100, stime timestamp default now());
  CREATE TABLE
  test=# insert into test values(1, now());
  INSERT 0 1
  test=# select default(id) from test;
  mode_b_default
  ----------------
              100
  (1 row)
  
  test=# select default(stime) from test;
  mode_b_default
  ----------------
  
  (1 row)
  
  test=# insert into test values(default(id) + 10);
  INSERT 0 1
  test=# update test set id = default(id) - 10;
  UPDATE 2
  test=# delete from test where id = default(id) - 10;
  DELETE 2

• When the default value in a field is a function, the default function returns null.

• The default function is only used in DML statements.