华为云用户手册

  • create_audit_csv_foregion_table() 描述:创建读审计日志的分区外表。用户调用该函数生成审计日志外表pgxc_audit_logs,通过读取pgxc_audit_logs可以读取放在obs上的审计日志信息。该函数仅8.2.1.300及以上集群支持。 入参: obs_server:text类型,obs server名称 file_path:text类型,obs文件路径 返回值类型:record 示例: --创建obs server CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( address 'obs.example.xxx:xxx', encrypt 'true', access_key 'xxxxxxxxx', secret_access_key 'xxxxxxxxxxxxxx', type 'obs' ); --调用该函数生成审计日志外表 SELECT * FROM pg_catalog.create_audit_csv_foregion_table('obs_server','/obs-audit/test/'); create_audit_csv_foregion_table --------------------------------- t (1 row) --读取放在obs上的审计日志信息 SELECT * FROM pgxc_audit_logs where year=2023 and month=7 and date=24; year | month | date | begintime | endtime | operation_type | audit_type | result | username | database | client_conninfo | object_name | object_details |command_text| detail_info | transaction_xid | query_id | node_name | session_id | local_port | remote_port | result_rows | error_code ------+-------+------+----------------------------+----------------------------+----------------+-------------------+--------+----------+----------+-----------------+--------------------------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------+--------------------+-----------+------------------------------------+------------+-------------+-------------+------------ 2023 | 7 | 24 | 2023-07-24 09:56:40.727+08 | 2023-07-24 09:56:42.215+08 | dml | dml_action_select | ok | dbadmin | gaussdb | gsql@[local] | public.pgxc_audit_logs | | select * f rom pgxc_audit_logs where year=2023 and date=24 and month=7;
  • pg_query_audit_details() 描述:查看审计日志并将审计日志中的object_name和object_details字段由json格式解析出来。该函数仅8.2.1.100及以上集群版本支持。 返回值类型:record 函数返回字段如下: 表4 pg_query_audit()函数返回字段 名称 类型 描述 begintime timestamp with time zone 操作的执行开始时间。 endtime timestamp with time zone 操作的执行结束时间。 operation_type text 操作类型,具体类型见表2。 audit_type text 审计类型,具体类型见表3。 result text 操作结果。 username text 执行操作的用户名。 database text 数据库名称。 client_conninfo text 客户端连接信息,即gsql,jdbc或odbc。 transaction_xid text 事务ID。 query_id text 查询ID。 node_name text 节点名称。 session_id text 会话ID。 local_port text 本地端口。 remote_port text 远端端口。 object_name text 表名、函数名、视图名。 column_name text 列名。 type_of_use text 对象的使用类型: 1:仅涉及(在实际使用中,此标志位暂不存在) 2:执行过程中访问(语句中出现的列以及在解析过程中访问到的列) 4:条件中发现(条件类型语句中,不包括条件表达式及函数) 8:inner join中发现 16:outer join中发现 32:聚合节点中发现(包括distinct、group by、聚集函数) 64:full join中发现 该列数值为叠加显示的数值。 use_type text type_of_use解析出的具体类型: 1:Reference only 2:Access 4:Conditional 8:Inner join 16:Outer join 32:Sum 64:Full join command_text text 操作的执行命令。 示例: 查询审计语句中所有对象的列及其在语句中使用的类型: 1 2 SET audit_object_details = on; SELECT object_name,object_details,result_rows,error_code,command_text FROM pg_query_audit('2023-05-12-03 8:00:00','2023-05-12 22:55:00') where command_text like '%student%'; 查询结果如下: 1 2 3 4 5 6 object_name | object_details | result_rows | error_code | command_text ------------------------------------------------------------------+------------------------------------------------------------------------------------------+-------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------- student | | 0 | | CREATE TABLE student(stuNo int, stuName TEXT); studentscore | | 0 | | CREATE TABLE studentscore(stuNo int, stuscore int); ["public.student_view01","public.studentscore","public.student"] | | 0 | | CREATE OR REPLACE VIEW student_view01 AS SELECT * FROM student t1 where t1.stuNo in (select stuNo from studentscore t2 where t1.stuNo = t2.stuNo); ["public.student_view01","public.student","public.studentscore"] | {"public.student":[{"stuno":"6"},{"stuname":"2"}],"public.studentscore":[{"stuno":"6"}]} | 0 | | SELECT * FROM student_view01 查询结果object_details中显示部分语句执行过程中,涉及到的列及其使用类型,使用json格式进行记录。 使用pg_query_audit_details函数对object_name和object_details列进行解析: 1 SELECT database,object_name,column_name,type_of_use,use_type FROM pg_query_audit_details('2021-02-03 8:00:00','2024-02-03 22:55:00','current') where command_text like '%student%'; 查询结果如下: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 database | object_name | column_name | type_of_use | use_type ----------+-----------------------+-------------+-------------+-------------------- gaussdb | student | | 2 | Access gaussdb | | | 2 | Access gaussdb | studentscore | | 2 | Access gaussdb | | | 2 | Access gaussdb | public.student_view01 | | 2 | Access gaussdb | public.studentscore | | 2 | Access gaussdb | public.student | | 2 | Access gaussdb | | | 2 | Access gaussdb | public.student | stuno | 6 | Access,Conditional gaussdb | public.student | stuname | 2 | Access gaussdb | public.studentscore | stuno | 6 | Access,Conditional gaussdb | public.student_view01 | | 2 | Access gaussdb | public.student | | 2 | Access gaussdb | public.studentscore | | 2 | Access gaussdb | | | 2 | Access (15 rows)
  • login_audit_messages_pid(flag boolean) 描述:查看登录用户的登录信息。与login_audit_messages的区别在于结果基于当前backendid向前查找。所以不会因为同一用户的后续登录,而影响本次登录的查询结果。也就是查询不到该用户后续登录的信息。 返回值类型:元组 示例: 查看上一次登录认证通过的日期、时间和IP等信息: 1 2 3 4 5 SELECT * FROM login_audit_messages_pid(true); username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+---------------+--------+--------------------+----------------------------------------- dbadmin | postgres | 2017-06-02 15:28:34+08 | login_success | ok | gsql@[local] | 140311900702464 | 1663745305.140192538154752.coordinator1 (1 row) 查看上一次登录认证失败的日期、时间和IP等信息: 1 2 3 4 SELECT * FROM login_audit_messages_pid(false) ORDER BY logintime desc limit 1; username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows) 查看自从最后一次认证通过以来失败的尝试次数、日期和时间: 1 2 3 4 SELECT * FROM login_audit_messages_pid(false); username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows)
  • pg_query_audit() 描述:查看当前CN节点审计日志。 返回值类型:record 函数返回字段如下: 表1 pg_query_audit()函数返回字段 名称 类型 描述 begintime timestamp with time zone 操作的执行开始时间。 endtime timestamp with time zone 操作的执行结束时间。 operation_type text 操作类型,具体类型见表2。 audit_type text 审计类型,具体类型见表3。 result text 操作结果。 username text 执行操作的用户名。 database text 数据库名称。 client_conninfo text 客户端连接信息,即gsql,jdbc或odbc。 object_name text 操作对象名称。 object_details text 记录语句中涉及的列及其使用类型。 command_text text 操作的执行命令。 detail_info text 执行操作详细信息。 transaction_xid text 事务ID。 query_id text 查询ID。 node_name text 节点名称。 session_id text 会话ID。 local_port text 本地端口。 remote_port text 远端端口。 result_rows text 语句执行返回的记录数。 error_code text 语句执行错误时的错误码。 表2 operation_type操作类型项 操作类型 描述 audit_switch 表示对用户打开和关闭审计日志操作场景进行审计。 login_logout 表示对用户登录和登出操作场景进行审计。 system 表示对系统的启停、实例切换操作场景进行审计。 sql_parse 表示对SQL语句解析场景进行审计。 user_lock 表示对用户锁定和解锁操作的场景进行审计。 grant_revoke 表示对用户权限授予和回收操作场景进行审计。 violation 表示对用户访问存在越权的场景进行审计。 ddl 表示对DDL操作场景进行审计,因为DDL操作会根据操作对象进行更细粒度控制,仍然沿用审计开关audit_system_object,即由audit_system_object控制对哪些对象的DDL操作进行审计(此处不配置ddl,只要配置了audit_system_object,审计也会生效)。 dml 表示对DML操作场景进行审计。 select 表示对SELECT操作场景进行审计。 internal_event 表示对内部事件操作场景进行审计。 user_func 表示对用户自定义函数、存储过程、匿名块操作场景进行审计。 说明: 如果自定义函数、存储过程中有fetch语句,则审计fetch语句时,其中common_text字段记录的为其对应的CURSOR内容。 special_func 表示对特殊函数调用操作场景进行审计,特殊函数包括:pg_terminate_backend和pg_cancel_backend。 copy 表示对COPY操作场景进行审计。 set 表示对SET操作场景进行审计。 transaction 表示对事务操作场景进行审计。 vacuum 表示对VACUUM操作场景进行审计。 analyze 表示对ANALYZE操作场景进行审计。 cursor 表示对游标操作的场景进行审计。 anonymous_block 表示对匿名块操作场景进行审计。 explain 表示对EXPLAIN操作场景进行审计。 show 表示对SHOW操作场景进行审计。 lock_table 表示对锁表操作场景进行审计。 comment 表示对COMMENT操作场景进行审计。 preparestmt 表示对PREPARE、EXECUTE、DEALLOCATE操作场景进行审计。 cluster 表示对CLUSTER操作场景进行审计。 constraints 表示对CONSTRAINTS操作场景进行审计。 checkpoint 表示对CHECKPOINT操作场景进行审计。 barrier 表示对BARRIER操作场景进行审计。 cleanconn 表示对CLEAN CONNECTION操作场景进行审计。 seclabel 表示对安全标签操作进行审计。 notify 表示对通知操作进行审计。 load 表示对加载操作进行审计。 discard 表示对清理当前会话所有的全局临时表信息场景进行审计。 表3 audit_type审计类型项 审计类型 描述 audit_open/audit_close 表示审计类型为打开和关闭审计日志操作。 user_login/user_logout 表示审计类型为用户登录/退出成功的操作和用户。 system_start/system_stop/system_recover/system_switch 表示审计类型为系统的启停、实例切换操作。 sql_wait/sql_parse 表示审计类型为SQL语句解析。 lock_user/unlock_user 表示审计类型为用户锁定和解锁成功的操作。 grant_role/revoke__role 表示审计类型为用户权限授予和回收的操作。 user_violation 表示审计类型为用户访问存在越权的操作。 ddl_数据库对象 表示审计类型为DDL操作,因为DDL操作由会根据操作对象进行更细粒度控制,仍然沿用审计开关audit_system_object,即由audit_system_object控制对哪些对象的DDL操作进行审计(此处不配置ddl,只要配置了audit_system_object,审计也会生效)。 例如:ddl_sequence表示审计类型为序列相关操作。 dml_action_insert/dml_action_delete/dml_action_update/dml_action_merge/dml_action_select 表示审计类型为INSERT、DELETE、UPDATE、MERGE等DML操作。 internal_event 表示审计类型为内部事件。 user_func 表示审计类型为用户自定义函数、存储过程、匿名块操作。 说明: 如果自定义函数、存储过程中有fetch语句,则审计fetch语句时,其中common_text字段记录的为其对应的CURSOR内容。 special_func 表示审计类型为特殊函数调用操作,特殊函数包括:pg_terminate_backend和pg_cancel_backend。 copy_to/copy_from 表示审计类型为COPY相关操作。 set_parameter 表示审计类型为SET操作。 trans_begin/trans_commit/trans_prepare/trans_rollback_to/trans_release/trans_savepoint/trans_commit_prepare/trans_rollback_prepare/trans_rollback 表示审计类型为事务相关操作。 vacuum/vacuum_full/vacuum_merge 表示审计类型为VACUUM相关操作。 analyze/analyze_verify 表示审计类型为ANALYZE相关操作。 cursor_declare/cursor_move/cursor_fetch/cursor_close 表示审计类型为游标相关操作。 codeblock_execute 表示审计类型为匿名块。 explain 表示审计类型为EXPLAIN操作。 show 表示审计类型为SHOW操作。 lock_table 表示审计类型为锁表操作。 comment 表示审计类型为COMMENT操作。 prepare/execute/deallocate 表示审计类型为PREPARE、EXECUTE或DEALLOCATE操作。 cluster 表示审计类型为CLUSTER操作。 constraints 表示审计类型为CONSTRAINTS操作。 checkpoint 表示审计类型为CHECKPOINT操作。 barrier 表示审计类型为BARRIER操作。 cleanconn 表示审计类型为CLEAN CONNECTION操作。 seclabel 表示审计类型为安全标签操作。 notify 表示审计类型为通知操作。 load 表示审计类型为加载操作。 discard 表示审计类型为DISCARD操作。
  • gs_password_deadline() 描述:显示当前账户距离密码过期的时间。密码过期后提示用户修改密码。与GUC参数password_effect_time相关。 返回值类型:interval 示例: 1 2 3 4 5 SELECT gs_password_deadline(); gs_password_deadline ------------------------- 83 days 17:44:32.196094 (1 row)
  • gs_password_expiration() 描述:显示当前账户距离密码过期的时间。密码过期后用户无法登录数据库。与创建用户的DDL语句PASSWORD EXPIRATION period相关,函数返回值大于等于-1,如果创建用户时未指定PASSWORD EXPIRATION period,该函数的缺省值为-1,表示没有过期限制。 返回值类型:interval 示例: 1 2 3 4 5 SELECT gs_password_expiration(); gs_password_expiration ------------------------- 29 days 23:59:49.731482 (1 row)
  • login_audit_messages(flag boolean) 描述:查看登录用户的登录信息。 返回值类型:元组 示例: 查看上一次登录认证通过的日期、时间和IP等信息: 1 2 3 4 5 SELECT * FROM login_audit_messages(true); username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+---------------+--------+--------------------+----------------------------------------- dbadmin | gaussdb | 2017-06-02 15:28:34+08 | login_success | ok | gsql@[local] | 1663745305.140192538154752.coordinator1 (1 row) 查看上一次登录认证失败的日期、时间和IP等信息: 1 2 3 4 SELECT * FROM login_audit_messages(false) ORDER BY logintime desc limit 1; username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows) 查看自从最后一次认证通过以来失败的尝试次数、日期和时间: 1 2 3 4 SELECT * FROM login_audit_messages(false); username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows)
  • LEAD(value any [, offset integer [, default any ]]) 描述:LEAD函数为各组内对应值生成提前值。即当前值对应的行数向后偏移offset位后所得行的value值作为序号。若经过向后偏移后行数超过当前组内的总行数,则对应结果取为default值。若无指定,在默认情况下,offset取为1,default值取为NULL。 返回值类型:与参数数据类型相同。 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id,classid,score,LEAD(id,3) OVER(ORDER BY score DESC) FROM score; id | classid | score | lead ----+---------+-------+------ 1 | 1 | 95 | 3 2 | 2 | 95 | 4 5 | 2 | 88 | 6 3 | 2 | 85 | 4 | 1 | 70 | 6 | 1 | 70 | (6 rows)
  • LAG(value any [, offset integer [, default any ]]) 描述:LAG函数为各组内对应值生成滞后值。即当前值对应的行数往前偏移offset位后所得行的value值作为序号。若经过偏移后行数不存在,则对应结果取为default值。若无指定,在默认情况下,offset取为1,default值取为NULL。 返回值类型:与参数数据类型相同。 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id,classid,score,LAG(id,3) OVER(ORDER BY score DESC) FROM score; id | classid | score | lag ----+---------+-------+----- 1 | 1 | 95 | 2 | 2 | 95 | 5 | 2 | 88 | 3 | 2 | 85 | 1 4 | 1 | 70 | 2 6 | 1 | 70 | 5 (6 rows)
  • LAST_VALUE(value any) 描述:LAST_VALUE函数取各组内的最后一个值作为返回结果。 返回值类型:与参数数据类型相同。 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id,classid,score,LAST_VALUE(id) OVER(ORDER BY score DESC) FROM score; id | classid | score | last_value ----+---------+-------+------------ 1 | 1 | 95 | 2 2 | 2 | 95 | 2 5 | 2 | 88 | 5 3 | 2 | 85 | 3 4 | 1 | 70 | 6 6 | 1 | 70 | 6 (6 rows)
  • NTILE(num_buckets integer) 描述:NTILE函数根据num_buckets integer将有序的数据集合平均分配到num_buckets所指定数量的桶中,并将桶号分配给每一行。分配时应尽量做到平均分配。 返回值类型:INTEGER 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id,classid,score,NTILE(3) OVER(ORDER BY score DESC) FROM score; id | classid | score | ntile ----+---------+-------+------- 1 | 1 | 95 | 1 2 | 2 | 95 | 1 5 | 2 | 88 | 2 3 | 2 | 85 | 2 4 | 1 | 70 | 3 6 | 1 | 70 | 3 (6 rows)
  • FIRST_VALUE(value any) 描述:FIRST_VALUE函数取各组内的第一个值作为返回结果。 返回值类型:与参数数据类型相同。 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id,classid,score,FIRST_VALUE(id) OVER(ORDER BY score DESC) FROM score; id | classid | score | first_value ----+---------+-------+------------- 1 | 1 | 95 | 1 2 | 2 | 95 | 1 5 | 2 | 88 | 1 3 | 2 | 85 | 1 4 | 1 | 70 | 1 6 | 1 | 70 | 1 (6 rows)
  • NTH_VALUE(value any, nth integer) 描述:NTH_VALUE函数返回该组内的第nth行作为结果。若该行不存在,则默认返回NULL。 返回值类型:与参数数据类型相同。 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id,classid,score,NTH_VALUE(id,3) OVER(ORDER BY score DESC) FROM score; id | classid | score | nth_value ----+---------+-------+----------- 1 | 1 | 95 | 2 | 2 | 95 | 5 | 2 | 88 | 5 3 | 2 | 85 | 5 4 | 1 | 70 | 5 6 | 1 | 70 | 5 (6 rows)
  • RANK() 描述:RANK函数为各组内值生成跳跃排序序号,其中相同的值具有相同序号,但相同值占用多个编号。 返回值类型:BIGINT 示例: 给定表score(id, classid, score),每行表示学生id,所在班级id以及考试成绩。 使用RANK函数对学生成绩进行排序: 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE score(id int,classid int,score int); INSERT INTO score VALUES(1,1,95),(2,2,95),(3,2,85),(4,1,70),(5,2,88),(6,1,70); SELECT id, classid, score,RANK() OVER(ORDER BY score DESC) FROM score; id | classid | score | rank ----+---------+-------+------ 1 | 1 | 95 | 1 2 | 2 | 95 | 1 6 | 1 | 70 | 5 4 | 1 | 70 | 5 5 | 2 | 88 | 3 3 | 2 | 85 | 4 (6 rows)
  • ROW_NUMBER() 描述:ROW_NUMBER函数为各组内值生成连续排序序号,其中相同的值其序号也不相同。 返回值类型:BIGINT 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id, classid, score,ROW_NUMBER() OVER(ORDER BY score DESC) FROM score ORDER BY score DESC; id | classid | score | row_number ----+---------+-------+------------ 1 | 1 | 95 | 1 2 | 2 | 95 | 2 5 | 2 | 88 | 3 3 | 2 | 85 | 4 6 | 1 | 70 | 5 4 | 1 | 70 | 6 (6 rows)
  • CUME_DIST() 描述:CUME_DIST函数为各组内对应值生成累积分布序号。即根据公式(小于等于当前值的数据行数)/(该分组总行数totalrows)计算所得的相对序号。 返回值类型:DOUBLE PRECISION 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id,classid,score,CUME_DIST() OVER(ORDER BY score DESC) FROM score; id | classid | score | cume_dist ----+---------+-------+------------------ 1 | 1 | 95 | .333333333333333 2 | 2 | 95 | .333333333333333 5 | 2 | 88 | .5 3 | 2 | 85 | .666666666666667 4 | 1 | 70 | 1 6 | 1 | 70 | 1 (6 rows)
  • PERCENT_RANK() 描述:PERCENT_RANK函数为各组内对应值生成相对序号,即根据公式 (rank - 1) / (total rows - 1)计算所得的值。其中rank为该值依据RANK函数所生成的对应序号,totalrows为该分组内的总元素个数。 返回值类型:DOUBLE PRECISION 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id, classid, score,PERCENT_RANK() OVER(ORDER BY score DESC) FROM score; id | classid | score | percent_rank ----+---------+-------+-------------- 1 | 1 | 95 | 0 2 | 2 | 95 | 0 3 | 2 | 85 | .6 4 | 1 | 70 | .8 5 | 2 | 88 | .4 6 | 1 | 70 | .8 (6 rows)
  • 语法格式 窗口函数需要特殊的关键字OVER语句来指定窗口触发窗口函数。OVER语句用于对数据进行分组,并对组内元素进行排序。窗口函数用于给组内的值生成序号: 1 2 3 4 function_name ([expression [, expression ... ]]) OVER ( window_definition ) function_name ([expression [, expression ... ]]) OVER window_name function_name ( * ) OVER ( window_definition ) function_name ( * ) OVER window_name 其中window_definition子句option为: 1 2 3 4 [ existing_window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ] PARTITION BY选项指定了将具有相同PARTITION BY表达式值的行分为一组。 ORDER BY选项用于控制窗口函数处理行的顺序。ORDER BY后面必须跟字段名,若ORDER BY后面跟数字,该数字会被按照常量处理,对目标列没有起到排序的作用。 frame_clause子句option为: 1 2 [ RANGE | ROWS ] frame_start [ RANGE | ROWS ] BETWEEN frame_start AND frame_end 当需要指定一个窗口对分组内所有行结果进行计算时,我们需要指定窗口区间开始的行和结束的行。窗口区间支持RANGE、ROWS两种模式,ROWS以物理单位(行)指定窗口,RANGE将窗口指定为逻辑偏移量。 RANGE、ROWS中可以使用BETWEEN frame_start AND frame_end指定边界可取值。如果仅指定frame_start,则frame_end默认为CURRENT ROW。 frame_start和frame_end取值为: CURRENT ROW,当前行。 N PRECEDING,当前行向前第n行。 UNBOUNDED PRECEDING,当前PARTITION的第1行。 N FOLLOWING,当前行向后第n行。 UNBOUNDED FOLLOWING,当前PARTITION的最后1行。 需要注意,frame_start不能为UNBOUNDED FOLLOWING,frame_end不能为UNBOUNDED PRECEDING,并且frame_end选项不能比上面取值中出现的frame_start选项早。例如RANGE BETWEEN CURRENT ROW AND N PRECEDING是不被允许的。 LAST_VALUE函数支持IGNORE NULLS语法,该语法返回非NULL窗口中的最后一个值,如果所有值都为NULL,则返回NULL,具体格式为: 1 LAST_VALUE (expression [IGNORE NULLS]) OVER (window_definition) 当前IGNORE NULLS仅支持ROWS between CURRENT ROW and UNBOUNDED FOLLOWING和ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW两种窗口区间。
  • DENSE_RANK() 描述:DENSE_RANK函数为各组内值生成连续排序序号,其中相同的值具有相同序号,相同值只占用一个编号。 返回值类型:BIGINT 示例: 1 2 3 4 5 6 7 8 9 10 SELECT id, classid, score,DENSE_RANK() OVER(ORDER BY score DESC) FROM score; id | classid | score | dense_rank ----+---------+-------+------------ 1 | 1 | 95 | 1 2 | 2 | 95 | 1 5 | 2 | 88 | 2 3 | 2 | 85 | 3 6 | 1 | 70 | 4 4 | 1 | 70 | 4 (6 rows)
  • approx_count_distinct(col_name) 描述:使用HyperLogLog++ (HLL++) 算法进行基数(某一列去重后的行数)的估算。该函数仅8.3.0及以上集群版本支持。 入参说明:col_name指需要估算基数的列。 可通过GUC参数approx_count_distinct_precision调整误差率。 参数取值范围为[10,20],默认值为17,理论误差率为千分之三。 该参数表示HyperLogLog++ (HLL++)算法中分桶个数,参数越大时,分桶数则越大,理论误差率则越小。 该参数取值越大,相应的计算时间和内存资源开销越大,但依然远小于精确的count distinct语句对应的开销。推荐在估算基数较大时使用该函数替换count distinct。 示例: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint) WITH (ORIENTATION = COLUMN); INSERT INTO employeeinfo VALUES (7155, 'JACK', 'SALESMAN', '2018-12-01', 30); INSERT INTO employeeinfo VALUES (7003, 'TOM', 'FINANCE', '2016-06-15', 20); INSERT INTO employeeinfo VALUES (7357, 'MAX', 'SALESMAN', '2020-10-01', 30); SELECT APPROX_COUNT_DISTINCT(empno) from employeeinfo; approx_count_distinct ----------------------- 3 (1 row) SELECT COUNT(DISTINCT empno) FROM employeeinfo GROUP BY ename; count ------- 1 1 1 (3 rows)
  • UNIQ(col_name) 描述:计算非重复值个数的聚合函数,与COUNT DISTINCT类似,即计算某一列去重后的行数,结果返回一个去重值。该函数仅8.3.0及以上集群版本支持。 入参说明:col_name指需要计算去重后行数的列。支持SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、TEXT、VARCHAR、TIMESTAMP、TIMESTAMPTZ、DATE、TIMETZ、UUID类型。 GaussDB (DWS)目前仅列存表支持UNIQ函数。 使用UNIQ函数时,SQL需要包含GROUP BY,为了取得更好性能所选GROUP BY字段分布需要尽量均匀。 建议当SQL中需要去重的列大于等于3列时,使用UNIQ函数去重,以取得比COUNT DISTINCT更好的效果。 一般情况下UNIQ函数内存消耗低于COUNT DISTINCT,如果使用COUNT DISTINCT时遇到内存超限,可以使用UNIQ函数进行替换。 示例: 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint) WITH (ORIENTATION = COLUMN); INSERT INTO employeeinfo VALUES (7155, 'JACK', 'SALESMAN', '2018-12-01', 30); INSERT INTO employeeinfo VALUES (7003, 'TOM', 'FINANCE', '2016-06-15', 20); INSERT INTO employeeinfo VALUES (7357, 'MAX', 'SALESMAN', '2020-10-01', 30); SELECT UNIQ(deptno) FROM employeeinfo GROUP BY ename; uniq ------ 1 1 1 (3 rows)
  • checksum(expression) 描述:返回所有输入值的CHECKSUM值。使用该函数可以用来验证GaussDB(DWS)数据库(不支持GaussDB(DWS)之外的其他数据库)的备份恢复或者数据迁移操作前后表中的数据是否相同。在备份恢复或者数据迁移操作前后都需要用户通过手工执行SQL命令的方式获取执行结果,通过对比获取的执行结果判断操作前后表中的数据是否相同。 对于大表,CHECKSUM函数可能会需要很长时间。 如果某两表的CHECKSUM值不同,则表明两表的内容是不同的。由于CHECKSUM函数中使用散列函数不能保证无冲突,因此两个不同内容的表可能会得到相同的CHECKSUM值,存在这种情况的可能性较小。对于列进行的CHECKSUM也存在相同的情况。 对于时间类型timestamp, timestamptz和smalldatetime,计算CHECKSUM值时请确保时区设置一致。 若计算某列的CHECKSUM值,且该列类型可以默认转为TEXT类型,则expression为列名。 若计算某列的CHECKSUM值,且该列类型不能默认转为TEXT类型,则expression为列名::TEXT。 若计算所有列的CHECKSUM值,则expression为表名::TEXT。 可以默认转换为TEXT类型的类型包括:char, name, int8, int2, int1, int4, raw, pg_node_tree, float4, float8, bpchar, varchar, nvarchar2, date, timestamp, timestamptz, numeric, smalldatetime,其他类型需要强制转换为TEXT。 返回类型:numeric 示例: 表中可以默认转为TEXT类型的某列的CHECKSUM值: 1 2 3 4 5 SELECT CHECKSUM(inv_quantity_on_hand) FROM tpcds.inventory; checksum ------------------- 24417258945265247 (1 row) 表中不能默认转为TEXT类型的某列的CHECKSUM值(注意此时CHECKSUM参数是列名::TEXT): 1 2 3 4 5 SELECT CHECKSUM(inv_quantity_on_hand::TEXT) FROM tpcds.inventory; checksum ------------------- 24417258945265247 (1 row) 表中所有列的CHECKSUM值。注意此时CHECKSUM参数是表名::TEXT,且表名前不加Schema: 1 2 3 4 5 SELECT CHECKSUM(inventory::TEXT) FROM tpcds.inventory; checksum ------------------- 25223696246875800 (1 row)
  • group_concat(expression [ORDER BY {col_name | expr} [ASC | DESC]] [SEPARATOR str_val]) 描述:将列数据使用指定的str_val分隔符,按照ORDER BY子句指定的排序方式拼接成字符串,ORDER BY子句必须指定排序方式,不支持ORDER BY 1的写法。 expression:必选,指定列名或基于列的有效表达式,不支持DISTINCT关键字和VARIADIC参数。 str_val:可选,指定的分隔符,可以是字符串常数或基于分组列的确定性表达式。缺省时表示分隔符为逗号。 返回类型:text group_concat函数仅8.1.2及以上版本支持。 示例: 默认分隔符为逗号: 1 2 3 4 5 SELECT group_concat(sname) FROM group_concat_test; group_concat ------------------------------------------ ADAMS,FORD,JONES,KING,MILLER,SCOTT,SMITH (1 row) group_concat函数支持自定义分隔符: 1 2 3 4 5 SELECT group_concat(sname separator ';') from group_concat_test; group_concat ------------------------------------------ ADAMS;FORD;JONES;KING;MILLER;SCOTT;SMITH (1 row) group_concat函数支持ORDER BY子句,将列数据进行有序拼接: 1 2 3 4 5 SELECT group_concat(sname order by snumber separator ';') FROM group_concat_test; group_concat ------------------------------------------ MILLER;FORD;SCOTT;SMITH;KING;JONES;ADAMS (1 row)
  • stddev_samp(expression) 描述:样本标准差。 返回类型:对于浮点类型的输入返回double precision,其他输入返回numeric。 示例: 1 2 3 4 5 SELECT STDDEV_SAMP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; stddev_samp ------------------ 289.224359757315 (1 row)
  • stddev(expression) 描述:stddev_samp的别名。 返回类型:对于浮点类型的输入返回double precision,其他输入返回numeric。 示例: 1 2 3 4 5 SELECT STDDEV(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; stddev ------------------ 289.224359757315 (1 row)
  • stddev_pop(expression) 描述:总体标准差。 返回类型:对于浮点类型的输入返回double precision,其他输入返回numeric。 示例: 1 2 3 4 5 SELECT STDDEV_POP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; stddev_pop ------------------ 289.224294957556 (1 row)
  • var_samp(expression) 描述:样本方差(样本标准差的平方)。 返回类型:对于浮点类型的输入返回double precision类型,其他输入返回numeric类型。 示例: 1 2 3 4 5 SELECT VAR_SAMP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; var_samp -------------------- 83650.730277028768 (1 row)
  • bit_and(expression) 描述:所有非NULL输入值的按位与(AND),如果全部输入值皆为NULL,那么结果也为NULL 。 返回类型:和参数数据类型相同。 示例: 1 2 3 4 5 SELECT BIT_AND(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; bit_and --------- 0 (1 row)
  • bit_or(expression) 描述:所有非NULL输入值的按位或(OR),如果全部输入值皆为NULL,那么结果也为NULL。 返回类型:和参数数据类型相同 示例: 1 2 3 4 5 SELECT BIT_OR(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; bit_or -------- 1023 (1 row)
  • var_pop(expression) 描述:总体方差(总体标准差的平方)。 返回类型:对于浮点类型的输入返回double precision类型,其他输入返回numeric类型。 示例: 1 2 3 4 5 SELECT VAR_POP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1; var_pop -------------------- 83650.692793695475 (1 row)
共100000条