序号

函数名称

查询语句

openGauss查询结果

Oracle查询结果

1

ASCII

SELECT ASCII('A') FROM DUAL;

openGauss=# SELECT ASCII('A') FROM DUAL; ascii ------- 65 (1 row)

SQL> SELECT ASCII('A') FROM DUAL; ASCII('A') ---------- 65

2

**CHR**

SELECT CHR(65) FROM DUAL;

openGauss=# SELECT CHR(65) FROM DUAL; chr ----- A (1 row)

SQL> SELECT CHR(65) FROM DUAL; C - A

3

**CONCAT**

openGauss=# SELECT CONCAT(CONCAT('Hello', ' '), 'World') FROM DUAL; concat ------------- Hello World (1 row)

SQL> SELECT CONCAT(CONCAT('Hello', ' '), 'World') FROM DUAL; CONCAT(CONC ----------- Hello World

4

**INITCAP**

SELECT INITCAP('hello world') FROM DUAL;

openGauss=# SELECT INITCAP('hello world') FROM DUAL; initcap ------------- Hello World (1 row)

SQL> SELECT INITCAP('hello world') FROM DUAL; INITCAP('HE ----------- Hello World

5

**INSTR**

SELECT INSTR('Hello World', 'o') FROM DUAL;

openGauss=# SELECT INSTR('Hello World', 'o') FROM DUAL; instr ------- 5 (1 row

SQL> SELECT INSTR('Hello World', 'o') FROM DUAL; INSTR('HELLOWORLD','O') ----------------------- 5

6

**LENGTH**

SELECT LENGTH('Hello') FROM DUAL;

openGauss=# SELECT LENGTH('Hello') FROM DUAL; length -------- 5 (1 row)

SQL> SELECT LENGTH('Hello') FROM DUAL; LENGTH('HELLO') --------------- 5

7

**LOWER**

SELECT LOWER('Hello') FROM DUAL;

openGauss=# SELECT LOWER('Hello') FROM DUAL; lower ------- hello (1 row)

SQL> SELECT LOWER('Hello') FROM DUAL; LOWER ----- hello

8

**UPPER**

SELECT UPPER('Hello') FROM DUAL;

openGauss=# SELECT UPPER('Hello') FROM DUAL; upper ------- HELLO (1 row)

SQL> SELECT UPPER('Hello') FROM DUAL; UPPER ----- HELLO

9

**RPAD and LPAD**

SELECT RPAD('Hello', 10, ' ') FROM DUAL;SELECT LPAD('Hello', 10, ' ') FROM DUAL;

openGauss=# SELECT RPAD('Hello', 10, ' ') FROM DUAL; rpad ------------ Hello (1 row) openGauss=# SELECT LPAD('Hello', 10, ' ') FROM DUAL; lpad ------------ Hello (1 row)

SQL> SELECT RPAD('Hello', 10, ' ') FROM DUAL; RPAD('HELL ---------- Hello SQL> SELECT LPAD('Hello', 10, ' ') FROM DUAL; LPAD('HELL ---------- Hello

10

**LTRIM and RTRIM**

SELECT LTRIM(' Hello ') FROM DUAL;SELECT RTRIM(' Hello ') FROM DUAL;

openGauss=# SELECT LTRIM(' Hello ') FROM DUAL; ltrim ---------- Hello (1 row) openGauss=# SELECT RTRIM(' Hello ') FROM DUAL; rtrim ---------- Hello (1 row)

SQL> SELECT LTRIM(' Hello ') FROM DUAL; LTRIM('H -------- Hello SQL> SELECT RTRIM(' Hello ') FROM DUAL; RTRIM('H -------- Hello

11

**SUBSTR**

SELECT SUBSTR('Hello World', 7, 5) FROM DUAL;

openGauss=# SELECT SUBSTR('Hello World', 7, 5) FROM DUAL; substr -------- World (1 row)

SQL> SELECT SUBSTR('Hello World', 7, 5) FROM DUAL; SUBST ----- World

12

**REPLACE**

openGauss=# SELECT REPLACE('Hello World', 'o', 'a') FROM DUAL; replace ------------- Hella Warld (1 row)

SQL> SELECT REPLACE('Hello World', 'o', 'a') FROM DUAL; REPLACE('HE ----------- Hella Warld

13

**SOUNDEX**

SELECT SOUNDEX('Hello') FROM DUAL;

openGauss=# SELECT SOUNDEX('Hello') FROM DUAL; ERROR: function soundex(unknown) does not exist LINE 1: SELECT SOUNDEX('Hello') FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: soundex

SQL> SELECT SOUNDEX('Hello') FROM DUAL; SOUN ---- H400

14

**TRIM**

SELECT TRIM(' Hello ') FROM DUAL;

openGauss=# SELECT TRIM(' Hello ') FROM DUAL; btrim ------- Hello (1 row)

SQL> SELECT TRIM(' Hello ') FROM DUAL; TRIM( ----- Hello

15

**ACOS**

SELECT ACOS(0) FROM DUAL;

openGauss=# SELECT ACOS(0) FROM DUAL; acos ----------------- 1.5707963267949 (1 row)

SQL> SELECT ACOS(0) FROM DUAL; ACOS(0) ---------- 1.57079633

16

**ASIN**

SELECT ASIN(0) FROM DUAL;

openGauss=# SELECT ASIN(0) FROM DUAL; asin ------ 0 (1 row)

SQL> SELECT ASIN(0) FROM DUAL; ASIN(0) ---------- 0

17

**ATAN**

SELECT ATAN(1) FROM DUAL;

openGauss=# SELECT ATAN(1) FROM DUAL; atan ------------------ .785398163397448 (1 row)

SQL> SELECT ATAN(1) FROM DUAL; ATAN(1) ---------- .785398163

18

**CEIL**

SELECT CEIL(3.14) FROM DUAL;

openGauss=# SELECT CEIL(3.14) FROM DUAL; ceil ------ 4 (1 row)

SQL> SELECT CEIL(3.14) FROM DUAL; CEIL(3.14) ---------- 4

19

**COS**

SELECT COS(0) FROM DUAL;

openGauss=# SELECT COS(0) FROM DUAL; cos ----- 1 (1 row)

SQL> SELECT COS(0) FROM DUAL; COS(0) ---------- 1

20

**COSH**

SELECT COSH(0) FROM DUAL;

openGauss=# SELECT COSH(0) FROM DUAL; ERROR: function cosh(integer) does not exist LINE 1: SELECT COSH(0) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: cosh

SQL> SELECT COSH(0) FROM DUAL; COSH(0) ---------- 1

21

**EXP**

SELECT EXP(1) FROM DUAL;

openGauss=# SELECT EXP(1) FROM DUAL; exp ------------------ 2.71828182845905 (1 row)

SQL> SELECT EXP(1) FROM DUAL; EXP(1) ---------- 2.71828183

22

**FLOOR**

SELECT FLOOR(3.14) FROM DUAL;

openGauss=# SELECT FLOOR(3.14) FROM DUAL; floor ------- 3 (1 row)

SQL> SELECT FLOOR(3.14) FROM DUAL; FLOOR(3.14) ----------- 3

23

**LN**

SELECT LN(2) FROM DUAL;

openGauss=# SELECT LN(2) FROM DUAL; ln ------------------ .693147180559945 (1 row)

SQL> SELECT LN(2) FROM DUAL; LN(2) ---------- .693147181

24

**LOG**

SELECT LOG(10, 100) FROM DUAL;

openGauss=# SELECT LOG(10, 100) FROM DUAL; log -------------------- 2.0000000000000000 (1 row)

SQL> SELECT LOG(10, 100) FROM DUAL; LOG(10,100) ----------- 2

25

**MOD**

SELECT MOD(10, 3) FROM DUAL;

openGauss=# SELECT MOD(10, 3) FROM DUAL; mod ----- 1 (1 row)

SQL> SELECT MOD(10, 3) FROM DUAL; MOD(10,3) ---------- 1

26

**POWER**

SELECT POWER(2, 3) FROM DUAL;

openGauss=# SELECT POWER(2, 3) FROM DUAL; power ------- 8 (1 row)

SQL> SELECT POWER(2, 3) FROM DUAL; POWER(2,3) ---------- 8

27

**ROUND and TRUNC**

SELECT ROUND(3.14) FROM DUAL;SELECT TRUNC(3.14) FROM DUAL;

openGauss=# SELECT ROUND(3.14) FROM DUAL; round ------- 3 (1 row) openGauss=# SELECT TRUNC(3.14) FROM DUAL; trunc ------- 3 (1 row)

SQL> SELECT ROUND(3.14) FROM DUAL; ROUND(3.14) ----------- 3 SQL> SELECT TRUNC(3.14) FROM DUAL; TRUNC(3.14) ----------- 3

28

**SIGN**

SELECT SIGN(-10) FROM DUAL;

openGauss=# SELECT SIGN(-10) FROM DUAL; sign ------ -1 (1 row)

SQL> SELECT SIGN(-10) FROM DUAL; SIGN(-10) ---------- -1

29

**SIN**

SELECT SIN(0) FROM DUAL;

openGauss=# SELECT SIN(0) FROM DUAL; sin ----- 0 (1 row)

SQL> SELECT SIN(0) FROM DUAL; SIN(0) ---------- 0

30

**SINH**

SELECT SINH(0) FROM DUAL;

openGauss=# SELECT SINH(0) FROM DUAL; ERROR: function sinh(integer) does not exist LINE 1: SELECT SINH(0) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: sinh openGauss=#

SQL> SELECT SINH(0) FROM DUAL; SINH(0) ---------- 0

31

**SQRT**

SELECT SQRT(16) FROM DUAL;

openGauss=# SELECT SQRT(16) FROM DUAL; sqrt ------ 4 (1 row)

SQL> SELECT SQRT(16) FROM DUAL; SQRT(16) ---------- 4

32

**TAN**

SELECT TAN(0) FROM DUAL;

openGauss=# SELECT TAN(0) FROM DUAL; tan ----- 0 (1 row)

SQL> SELECT TAN(0) FROM DUAL; TAN(0) ---------- 0

33

**TANH**

SELECT TANH(0) FROM DUAL;

openGauss=# SELECT TANH(0) FROM DUAL; ERROR: function tanh(integer) does not exist LINE 1: SELECT TANH(0) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: tanh

SQL> SELECT TANH(0) FROM DUAL; TANH(0) ---------- 0

34

**TRUNC**

SELECT TRUNC(3.14) FROM DUAL;

openGauss=# SELECT TRUNC(3.14) FROM DUAL; trunc ------- 3 (1 row)

SQL> SELECT TRUNC(3.14) FROM DUAL; 3

35

**ADD_MONTHS**

SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;

openGauss=# SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; add_months --------------------- 2023-09-05 21:02:13 (1 row)

SQL> SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; 2023:09:0521:02:12

36

**LAST_DAY**

SELECT LAST_DAY(SYSDATE) FROM DUAL;

openGauss=# SELECT LAST_DAY(SYSDATE) FROM DUAL; last_day --------------------- 2023-08-31 21:03:52 (1 row)

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL; 2023:08:3121:03:51

37

**MONTHS_BETWEEN**

SELECT MONTHS_BETWEEN(TO_DATE('2022-07-01', 'YYYY-MM-DD'), TO_DATE('2022-01-01', 'YYYY-MM-DD')) FROM DUAL;

openGauss=# SELECT MONTHS_BETWEEN(TO_DATE('2022-07-01', 'YYYY-MM-DD'), TO_DATE('2022-01-01', 'YYYY-MM-DD')) FROM DUAL; ERROR: function months_between(timestamp without time zone, timestamp without time zone) does not exist LINE 1: SELECT MONTHS_BETWEEN(TO_DATE('2022-07-01', 'YYYY-MM-DD'), T... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: months_between

SQL> SELECT MONTHS_BETWEEN(TO_DATE('2022-07-01', 'YYYY-MM-DD'), TO_DATE('2022-01-01', 'YYYY-MM-DD')) FROM DUAL; 6

38

**NEW_TIME**

SELECT NEW_TIME(SYSDATE, 'EST', 'PST') FROM DUAL;

openGauss=# SELECT NEW_TIME(SYSDATE, 'EST', 'PST') FROM DUAL; ERROR: function new_time(timestamp without time zone, unknown, unknown) does not exist LINE 1: SELECT NEW_TIME(SYSDATE, 'EST', 'PST') FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: new_time

SQL> SELECT NEW_TIME(SYSDATE, 'EST', 'PST') FROM DUAL; 2023:08:0518:05:28

39

**NEXT_DAY**

SELECT NEXT_DAY(SYSDATE, 'SUNDAY') FROM DUAL;

openGauss=# SELECT NEXT_DAY(SYSDATE, 'SUNDAY') FROM DUAL; next_day --------------------- 2023-08-06 21:06:25 (1 row)

SQL> SELECT NEXT_DAY(SYSDATE, 'SUNDAY') FROM DUAL; 2023:08:0621:06:24

40

**SYSDATE**

SELECT SYSDATE FROM DUAL;

openGauss=# SELECT SYSDATE FROM DUAL; sysdate --------------------- 2023-08-05 21:07:08 (1 row)

SQL> SELECT SYSDATE FROM DUAL; 2023:08:0521:07:08

41

**CHARTOROWID**

SELECT CHARTOROWID('AAAVmKAABAAAXFRAAA') from dual;

openGauss=# SELECT CHARTOROWID('AAAVmKAABAAAXFRAAA') from dual; ERROR: function chartorowid(unknown) does not exist LINE 1: SELECT CHARTOROWID('AAAVmKAABAAAXFRAAA') from dual; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: chartorowid

SQL> SELECT CHARTOROWID('AAAVmKAABAAAXFRAAA') from dual; AAAVmKAABAAAXFRAAA

42

**CONVERT**

SELECT CONVERT('Hello', 'WE8ISO8859P1', 'US7ASCII') FROM DUAL;

openGauss=# SELECT CONVERT('Hello', 'WE8ISO8859P1', 'US7ASCII') FROM DUAL; ERROR: invalid source encoding name "WE8ISO8859P1" CONTEXT: referenced column: convert

SQL> SELECT CONVERT('Hello', 'WE8ISO8859P1', 'US7ASCII') FROM DUAL; Hello

43

**HEXTORAW**

SELECT HEXTORAW('414243') FROM DUAL;

openGauss=# SELECT HEXTORAW('414243') FROM DUAL; hextoraw ---------- 414243 (1 row)

SQL> SELECT HEXTORAW('414243') FROM DUAL; 414243

44

**RAWTOHEX**

SELECT RAWTOHEX('ABC') FROM DUAL;

openGauss=# SELECT RAWTOHEX('ABC') FROM DUAL; rawtohex ---------- 414243 (1 row)

SQL> SELECT RAWTOHEX('ABC') FROM DUAL; 414243

45

**ROWIDTOCHAR**

SELECT ROWIDTOCHAR('AAAVmKAABAAAXFRAAA') FROM DUAL;

openGauss=# SELECT ROWIDTOCHAR('AAAVmKAABAAAXFRAAA') FROM DUAL; ERROR: function rowidtochar(unknown) does not exist LINE 1: SELECT ROWIDTOCHAR('AAAVmKAABAAAXFRAAA') FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: rowidtochar

SQL> SELECT ROWIDTOCHAR('AAAVmKAABAAAXFRAAA') FROM DUAL; AAAVmKAABAAAXFRAAA

46

**TO_CHAR**

SELECT TO_CHAR(12345) AS converted_value FROM DUAL;

openGauss=# SELECT TO_CHAR(12345) AS converted_value FROM DUAL; converted_value ----------------- 12345 (1 row)

SQL> SELECT TO_CHAR(12345) AS converted_value FROM DUAL; 12345

47

**TO_DATE**

SELECT TO_DATE('2022-07-01', 'YYYY-MM-DD') FROM DUAL;

openGauss=# SELECT TO_DATE('2022-07-01', 'YYYY-MM-DD') FROM DUAL; to_date --------------------- 2022-07-01 00:00:00 (1 row)

SQL> SELECT TO_DATE('2022-07-01', 'YYYY-MM-DD') FROM DUAL; 2022:07:0100:00:00

48

**TO_MULTI_BYTE**

SELECT TO_MULTI_BYTE('Hello') FROM DUAL;

openGauss=# SELECT TO_MULTI_BYTE('Hello') FROM DUAL; ERROR: function to_multi_byte(unknown) does not exist LINE 1: SELECT TO_MULTI_BYTE('Hello') FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: to_multi_byte

SQL> SELECT TO_MULTI_BYTE('Hello') FROM DUAL; Ｈｅｌｌｏ

49

**TO_NUMBER**

SELECT TO_NUMBER('123.45', '999.99') FROM DUAL;

openGauss=# SELECT TO_NUMBER('123.45', '999.99') FROM DUAL; to_number ----------- 123.45 (1 row)

SQL> SELECT TO_NUMBER('123.45', '999.99') FROM DUAL; 123.45

50

**BFILENAME**

SELECT BFILENAME('DIRECTORY', 'filename.jpg') AS file_name FROM DUAL;

openGauss=# SELECT BFILENAME('DIRECTORY', 'filename.jpg') AS file_name FROM DUAL; ERROR: function bfilename(unknown, unknown) does not exist LINE 1: SELECT BFILENAME('DIRECTORY', 'filename.jpg') AS file_name F... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: file_name

SQL> SELECT BFILENAME('DIRECTORY', 'filename.jpg') AS file_name FROM DUAL; bfilename('DIRECTORY', 'filename.jpg')

51

**DUMP**

SELECT DUMP('Hello') FROM DUAL;

openGauss=# SELECT DUMP('Hello') FROM DUAL; ERROR: function dump(unknown) does not exist LINE 1: SELECT DUMP('Hello') FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: dump

SQL> SELECT DUMP('Hello') FROM DUAL; Typ=96 Len=5: 72,101,108,108,111

52

**GREATEST**

SELECT GREATEST(2, 5, 3) FROM DUAL;

openGauss=# SELECT GREATEST(2, 5, 3) FROM DUAL; greatest ---------- 5 (1 row)

SQL> SELECT GREATEST(2, 5, 3) FROM DUAL; 5

53

**LEAST**

SELECT LEAST(2, 5, 3) FROM DUAL;

openGauss=# SELECT LEAST(2, 5, 3) FROM DUAL; least ------- 2 (1 row)

SQL> SELECT LEAST(2, 5, 3) FROM DUAL; 2

54

**UID**

SELECT UID FROM DUAL;

openGauss=# SELECT UID FROM DUAL; ERROR: column "uid" does not exist LINE 1: SELECT UID FROM DUAL; ^ CONTEXT: referenced column: uid

SQL> SELECT UID FROM DUAL; 0

55

**USER**

SELECT USER FROM DUAL;

openGauss=# SELECT USER FROM DUAL; current_user -------------- omm (1 row)

SQL> SELECT USER FROM DUAL; SYS

56

**AVG**

SELECT AVG(salary) FROM employees;

openGauss=# SELECT AVG(salary) FROM employees; avg -------------------- 65000.000000000000 (1 row)

SQL> SELECT AVG(salary) FROM employees; 65000

57

**MAX**

SELECT MAX(salary) FROM employees;

openGauss=# SELECT MAX(salary) FROM employees; max ------- 65000 (1 row)

SQL> SELECT MAX(salary) FROM employees; 65000

58

**MIN**

SELECT MIN(salary) FROM employees;

openGauss=# SELECT MIN(salary) FROM employees; min ------- 65000 (1 row)

SQL> SELECT MIN(salary) FROM employees; 65000

59

**STDDEV**

SELECT STDDEV(salary) FROM employees;

openGauss=# SELECT STDDEV(salary) FROM employees; stddev -------- (1 row)

SQL> SELECT STDDEV(salary) FROM employees; 0

60

**VARIANCE**

SELECT VARIANCE(salary) FROM employees;

openGauss=# SELECT VARIANCE(salary) FROM employees; variance ---------- (1 row)

SQL> SELECT VARIANCE(salary) FROM employees; 0

61

**GROUP BY**

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

openGauss=# openGauss=# SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; department_id | avg ---------------+-------------------- 60 | 65000.000000000000 (1 row)

SQL> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; 60 65000

62

**HAVING**

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 5000;

openGauss=# SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 5000; department_id | avg ---------------+-------------------- 60 | 65000.000000000000 (1 row)

SQL> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 5000; 60 65000

63

**ORDER BY**

SELECT * FROM employees ORDER BY salary DESC;

openGauss=# SELECT * FROM employees ORDER BY salary DESC; employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | ma nager_id | department_id -------------+------------+-----------+----------------------+--------------+---------------------+---------+--------+----------------+--- ---------+--------------- 101 | John | Doe | john.doe@example.com | 123-456-7890 | 2023-01-15 00:00:00 | IT_PROG | 65000 | | | 60 (1 row)

SQL> SELECT * FROM employees ORDER BY salary DESC; 101 John Doe john.doe@example.com 123-456-7890 2023:01:1500:00:00 IT_PROG 65000 60

64

**CASE statement in PL/SQL**

DECLARE salary NUMBER := 5000; BEGIN CASE WHEN salary > 10000 THEN DBMS_OUTPUT.PUT_LINE ('High Salary'); WHEN salary > 5000 THEN DBMS_OUTPUT.PUT_LINE ('Medium Salary'); ELSE DBMS_OUTPUT.PUT_LINE ('Low Salary'); END CASE; END; /

openGauss=# DECLARE salary NUMBER := 5000; openGauss-# openGauss-# BEGIN CASE WHEN salary > 10000 THEN DBMS_OUTPUT.PUT_LINE ('High Salary'); openGauss# openGauss# openGauss# THEN openGauss# ELSE openGauss# END CASE; openGauss# / ERROR: schema "dbms_output" does not exist CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 1

SQL> DECLARE 2 salary NUMBER := 5000; 3 BEGIN 4 CASE WHEN salary > 10000 5 6 THEN DBMS_OUTPUT.PUT_LINE ('High Salary'); 7 8 WHEN salary > 5000 9 THEN DBMS_OUTPUT.PUT_LINE ('Medium Salary'); 10 11 ELSE 12 DBMS_OUTPUT.PUT_LINE ('Low Salary'); END CASE; 13 14 END; 15 / PL/SQL procedure successfully completed.

65

**NVL**

SELECT NVL(FIRST_NAME,LAST_NAME) from employees;

openGauss=# SELECT NVL(FIRST_NAME,LAST_NAME) from employees; nvl ------ John (1 row)

SQL> SELECT NVL(FIRST_NAME,LAST_NAME) from employees; John

67

**AVG**

SELECT AVG(salary) FROM employees;

openGauss=# SELECT AVG(salary) FROM employees; avg -------------------- 65000.000000000000 (1 row)

SQL> SELECT AVG(salary) FROM employees; 65000

68

**COUNT**

SELECT COUNT(salary) FROM employees;

openGauss=# SELECT COUNT(salary) FROM employees; count ------- 1 (1 row)

SQL> SELECT COUNT(salary) FROM employees; 1

69

**FIRST_VALUE**

SELECT FIRST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees;

openGauss=# SELECT FIRST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; first_value ------------- 65000 (1 row)

SQL> SELECT FIRST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; 65000

70

**LAG**

SELECT LAG(salary) OVER (ORDER BY DEPARTMENT_ID) FROM employees;

openGauss=# SELECT LAG(salary) OVER (ORDER BY DEPARTMENT_ID) FROM employees; lag ----- (1 row)

SQL> SELECT LAG(salary) OVER (ORDER BY DEPARTMENT_ID) FROM employees; SQL>

71

**LAST_VALUE**

SELECT LAST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees;

openGauss=# SELECT LAST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; last_value ------------ 65000 (1 row)

SQL> SELECT LAST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; 65000

72

**LEAD**

SELECT LEAD(salary) OVER (ORDER BY EMPLOYEE_ID) FROM employees;

openGauss=# SELECT LEAD(salary) OVER (ORDER BY EMPLOYEE_ID) FROM employees; lead ------ (1 row)

SQL> SELECT LEAD(salary) OVER (ORDER BY EMPLOYEE_ID) FROM employees; SQL>

73

**ROW_NUMBER**

SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees;

openGauss=# SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; row_number ------------ 1 (1 row)

SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; 1

74

**STDDEV**

SELECT STDDEV(salary) FROM employees;

openGauss=# SELECT STDDEV(salary) FROM employees; stddev -------- (1 row) openGauss=#

SQL> SELECT STDDEV(salary) FROM employees; 0

75

**STDDEV_POP**

SELECT STDDEV_POP(salary) FROM employees;

openGauss=# SELECT STDDEV_POP(salary) FROM employees; stddev_pop ------------ 0 (1 row) openGauss=#

SQL> SELECT STDDEV_POP(salary) FROM employees; 0

76

**STDDEV_SAMP**

SELECT STDDEV_SAMP(salary) FROM employees;

openGauss=# SELECT STDDEV_SAMP(salary) FROM employees; stddev_samp ------------- (1 row) openGauss=#

SQL> SELECT STDDEV_SAMP(salary) FROM employees; SQL>

77

**SUM (Aggregate Function)**

SELECT SUM(salary) FROM employees;

openGauss=# SELECT SUM(salary) FROM employees; sum ------- 65000 (1 row) openGauss=#

SQL> SELECT SUM(salary) FROM employees; 65000

78

**VAR_POP (Aggregate Function)**

SELECT VAR_POP(salary) FROM employees;

openGauss=# SELECT VAR_POP(salary) FROM employees; var_pop --------- 0 (1 row)

SQL> SELECT VAR_POP(salary) FROM employees; 0 SQL>

79

**VAR_SAMP (Aggregate Function)**

SELECT VAR_SAMP(salary) FROM employees;

openGauss=# SELECT VAR_SAMP(salary) FROM employees; var_samp ---------- (1 row)

SQL> SELECT VAR_SAMP(salary) FROM employees; SQL>

80

**VARIANCE (Aggregate Function)**

SELECT VARIANCE(salary) FROM employees;

openGauss=# SELECT VARIANCE(salary) FROM employees; variance ---------- (1 row) openGauss=#

SQL> SELECT VARIANCE(salary) FROM employees; 0

81

**RANK (Aggregate Function)**

SELECT RANK() OVER (ORDER BY salary) FROM employees;

openGauss=# SELECT RANK() OVER (ORDER BY salary) FROM employees; rank ------ 1 (1 row) openGauss=#

SQL> SELECT RANK() OVER (ORDER BY salary) FROM employees; 1 SQL>

82

**RATIO_TO_REPORT (Aggregate Function)**

SELECT RATIO_TO_REPORT(salary) OVER () FROM employees;

openGauss=# SELECT RATIO_TO_REPORT(salary) OVER () FROM employees; ERROR: function ratio_to_report(numeric) does not exist LINE 1: SELECT RATIO_TO_REPORT(salary) OVER () FROM employees; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: ratio_to_report

SQL> SELECT RATIO_TO_REPORT(salary) OVER () FROM employees; 1 SQL>

83

**ROW_NUMBER (Aggregate Function)**

SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees;

openGauss=# SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; row_number ------------ 1 (1 row) openGauss=#

SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; 1 SQL>

84

**STDDEV (Aggregate Function)**

SELECT STDDEV(salary) FROM employees;

openGauss=# SELECT STDDEV(salary) FROM employees; stddev -------- (1 row)

SQL> SELECT STDDEV(salary) FROM employees; 0

85

**STDDEV_POP (Aggregate Function)**

SELECT STDDEV_POP(salary) FROM employees;

openGauss=# SELECT STDDEV_POP(salary) FROM employees; stddev_pop ------------ 0 (1 row)

SQL> SELECT STDDEV_POP(salary) FROM employees; 0

86

**STDDEV_SAMP (Aggregate Function)**

SELECT STDDEV_SAMP(salary) FROM employees;

openGauss=# SELECT STDDEV_SAMP(salary) FROM employees; stddev_samp ------------- (1 row)

SQL> SELECT STDDEV_SAMP(salary) FROM employees; SQL>

87

**SUM (Aggregate Function)**

SELECT sum(salary) FROM employees;

openGauss=# SELECT sum(salary) FROM employees; sum ------- 65000 (1 row)

SQL> SELECT sum(salary) FROM employees; 65000

88

**VAR_POP (Aggregate Function)**

SELECT VAR_POP(salary) FROM employees;

openGauss=# SELECT VAR_POP(salary) FROM employees; var_pop --------- 0 (1 row) openGauss=#

SQL> SELECT VAR_POP(salary) FROM employees; 0

89

**VAR_SAMP (Aggregate Function)**

SELECT VAR_SAMP(salary) FROM employees;

openGauss=# SELECT VAR_SAMP(salary) FROM employees; var_samp ---------- (1 row) openGauss=#

SQL> SELECT VAR_SAMP(salary) FROM employees; SQL>