SQL 函数

特殊的语法形式

语法相当于描述
AT TIME ZONEtimezone()时区
CURRENT_CATALOGcurrent_catalog()现在使用的catalog
COLLATION FORpg_collation_for()排序
CURRENT_DATEcurrent_date()现在的日期(年,月,日)
CURRENT_ROLEcurrent_user()现在的用户
CURRENT_SCHEMAcurrent_schema()现在使用的schema
CURRENT_TIMESTAMPcurrent_timestamp()现在的时间戳
CURRENT_USERcurrent_user()现在的用户
EXTRACT(<part> FROM <value>)extract("<part>", <value>)从..中抽取部分内容
OVERLAY(<text1> PLACING <text2> FROM <int1> FOR <int2>)overlay(<text1>, <text2>, <int1>, <int2>)替换
OVERLAY(<text1> PLACING <text2> FROM <int>)overlay(<text1>, <text2>, <int>)替换
SESSION_USERcurrent_user()session用户
SUBSTRING(<text> FOR <int1> FROM <int2>))substring(<text>, <int2>, <int1>)截取
SUBSTRING(<text> FOR <int>)substring(<text>, 1, <int>))
SUBSTRING(<text> FROM <int1> FOR <int2>)substring(<text>, <int1>, <int2>)
SUBSTRING(<text> FROM <int>)substring(<text>, <int>)
TRIM(<text1> FROM <text2>)btrim(<text2>, <text1>)祛除字符或字符串。
TRIM(<text2>, <text1>)btrim(<text2>, <text1>)
TRIM(FROM <text>)btrim(<text>)
TRIM(LEADING <text1> FROM <text2>)ltrim(<text2>, <text1>)
TRIM(LEADING FROM <text> )ltrim(<text>)
TRIM(TRAILING <text1> FROM <text2>)rtrim(<text2>, <text1>)
TRIM(TRAILING FROM<text> )rtrim(<text>)
USERcurrent_user()查询使用用户
  • 展示当前用户
select current_user();
 current_user  
+--------------+
   hubble
  • 根据时间获取当前的时间戳
select extract(epoch from cast('2022-11-13 11:22:10'  as TIMESTAMP));
   extract  
+--------------+
1668338530
  • 时间戳转化为日期
select 1668338530::TIMESTAMP;
       timestamp
-----------------------
  2022-11-13 11:22:10
  • SUBSTRING()用于字符串截取

    从第1个位置开始截取,截取4个字符,返回结果:hubb

select  SUBSTRING('hubble', 1, 4);
   SUBSTRING  
+--------------+
   hubb

从8个位置开始截取,截取到最后一个字符

select SUBSTRING('information', 8);
   SUBSTRING  
+--------------+
   tion

正则表达式截取

select SUBSTRING('PostgreSQL' from 'gre' );
   SUBSTRING  
+--------------+
   gre

反向截取

select reverse(SUBSTRING(reverse('PostgreSQL'),1,2));
   reverse  
+--------------+
   QL
  • position(),子串在一字符串中的位置
select position('om' in 'Thomas');
  position  
+--------------+
   3
  • 获得客户端编码
select pg_client_encoding();
pg_client_encoding  
+--------------+
   UTF8
  • 利用正则表达式对字符串进行替换
select regexp_replace('Thomas', '.[mN]a.', 'M'); 
regexp_replace 
+--------------+
   ThM
  • 重复字符串(指定次数)
select repeat('hb', 4); 
    repeat 
+--------------+
   hbhbhbhb
  • 默认为去除空白字符,当然可以自己指定
select trim(both 'x' from 'xTomxx');
  btrim
+--------------+
  Tom
select trim('   xTomxxn    '); 
  btrim
+--------------+
  xTomxxn

条件运算符和类函数运算符

语法描述
ANNOTATE_TYPE(...)显式输入表达式
ARRAY(...)子查询结果转换为数组
ARRAY[...]将标量表达式转换为数组
CAST(...)类型转换
COALESCE(...)第一个非NULL短路表达式
EXISTS(...)子查询结果的存在性检验
IF(...)条件评估
IFNULL(...)COALESCE限制为两个操作数的别名
NULLIF(...)NULL有条件地返回
ROW(...)元组构造函数

语法:

ANNOTATE_TYPE()

<expr>:::<type>
ANNOTATE_TYPE(<expr>, <type>)

计算给定的表达式,要求表达式具有给定的类型。如果表达式没有给定的类型,则返回错误。

类型注释对于指导数值的算术特别有用。

例如:

SELECT (1 / 0):::FLOAT;
pq: division by zero

句法:

ARRAY( ... subquery ... )

计算子查询并将其结果转换为数组。任何选择查询都可以用作子查询。

语法:

ARRAY[ <expr>, <expr>, ... ]

评估为包含指定值的数组。

例如:

SELECT ARRAY[1,2,3] AS a;
+---------+
|    a    |
+---------+
| {1,2,3} |
+---------+

语法:

<expr> :: <type>
CAST (<expr> AS <type>)

计算表达式并将结果值转换为指定的类型。如果转换无效,将报告错误。

例如:

CAST(now() AS DATE)

语法:

<expr> COLLATE <collation>

计算表达式并将其结果转换为具有指定归类的归类字符串。

例如:

'a' COLLATE de

语法:

EXISTS ( ... subquery ... )
NOT EXISTS ( ... subquery ... )

评估子查询,然后返回TRUEFALSE取决于子查询是否返回任何行(用于EXISTS)或不返回任何行(用于NOT EXISTS)。任何选择查询 都可以用作子查询。

语法:

NULLIF ( <expr1>, <expr2> )

相当于:IF ( <expr1> = <expr2>, NULL, <expr1> )

语法:

IFNULL ( <expr1>, <expr2> )
COALESCE ( <expr1> [, <expr2> [, <expr3> ] ...] )

COALESCE首先计算第一个表达式。如果其值不是 NULL,则直接返回其值。否则,它将返回COALESCE对其余表达式应用的结果。如果所有表达式均为NULLNULL则返回。

不评估第一个非空参数右边的参数。

IFNULL(a, b)等同于COALESCE(a, b)

语法:

(<expr>, <expr>, ...)
ROW (<expr>, <expr>, ...)

评估为包含提供的表达式值的元组。

例如:

SELECT ('x', 123, 12.3) AS a;
+----------------+
|       a        |
+----------------+
| ('x',123,12.3) |
+----------------+

从值推断结果元组的数据类型。元组中的每个位置可以具有不同的数据类型。

数组函数

函数说明返回值类型
array_append(array: bool[], elem: bool) 将elem附加到数组中,返回结果。bool[]
array_append(array: bytes[], elem: bytes) 将elem附加到数组中,返回结果。bytes[]
array_append(array: date[], elem: date) 将elem附加到数组中,返回结果。date[]
array_append(array: decimal[], elem: decimal) 将elem附加到数组中,返回结果。decimal[]
array_append(array: float[], elem: float)将elem附加到数组中,返回结果。float[]
array_append(array: inet[], elem: inet) 将elem附加到数组中,返回结果。inet[]
array_append(array: int[], elem: int) 将elem附加到数组中,返回结果。int[]
array_append(array: interval[], elem: interval) 将elem附加到数组中,返回结果。interval[]
array_append(array: string[], elem: string) 将elem附加到数组中,返回结果。string[]
array_append(array: time[], elem: time) 将elem附加到数组中,返回结果。time[]
array_append(array: timestamp[], elem: timestamp)将elem附加到数组中,返回结果。timestamp[]
array_append(array: timestamptz[], elem: timestamptz) 将elem附加到数组中,返回结果。timestamptz[]
array_append(array: uuid[], elem: uuid) 将elem附加到数组中,返回结果。uuid[]
array_append(array: varbit[], elem: varbit) 将elem附加到数组中,返回结果。varbit[]
array_cat(left: bool[], right: bool[])追加两个数组。bool[]
array_cat(left: bytes[], right: bytes[]) 追加两个数组。bytes[]
array_cat(left: date[], right: date[]) 追加两个数组。date[]
array_cat(left: decimal[], right: decimal[]) 追加两个数组。decimal[]
array_cat(left: float[], right: float[]) 追加两个数组。float[]
array_cat(left: inet[], right: inet[]) 追加两个数组。inet[]
array_cat(left: int[], right: int[]) 追加两个数组。int[]
array_cat(left: interval[], right: interval[]) 追加两个数组。interval[]
array_cat(left: string[], right: string[]) 追加两个数组。string[]
array_cat(left: time[], right: time[]) 追加两个数组。time[]
array_cat(left: timestamp[], right: timestamp[]) 追加两个数组。timestamp[]
array_cat(left: timestamptz[], right: timestamptz[])追加两个数组。timestamptz[]
array_cat(left: uuid[], right: uuid[])追加两个数组。uuid[]
array_cat(left: varbit[], right: varbit[])追加两个数组。varbit[]
array_length(input: anyelement[], array_dimension: int) 计算input提供的上的长度array_dimension。但是,由于目前尚不支持多维数组,因此唯一支持的array_dimension是1。int
array_lower(input: anyelement[], array_dimension: int)计算input提供的上的最小值array_dimension。但是,由于目前尚不支持多维数组,因此唯一支持的array_dimension是1。int
array_position(array: bool[], elem: bool)返回数组中elem 第一次出现的索引 。int
array_position(array: bytes[], elem: bytes) 返回数组中elem 第一次出现的索引 。int
array_position(array: date[], elem: date) 返回数组中elem 第一次出现的索引 。int
array_position(array: decimal[], elem: decimal) 返回数组中elem 第一次出现的索引 。int
array_position(array: float[], elem: float) 返回数组中elem 第一次出现的索引 。int
array_position(array: inet[], elem: inet)返回数组中elem 第一次出现的索引 。int
array_position(array: int[], elem: int)返回数组中elem 第一次出现的索引 。int
array_position(array: interval[], elem: interval)返回数组中elem 第一次出现的索引 。int
array_position(array: string[], elem: string) 返回数组中elem 第一次出现的索引 。int
array_position(array: time[], elem: time) 返回数组中elem 第一次出现的索引 。int
array_position(array: timestamp[], elem: timestamp)返回数组中elem 第一次出现的索引 。int
array_position(array: timestamptz[], elem: timestamptz) 返回数组中elem 第一次出现的索引 。int
array_position(array: uuid[], elem: uuid) 返回数组中elem 第一次出现的索引 。int
array_position(array: oid[], elem: oid) 返回数组中elem 第一次出现的索引 。int
array_position(array: varbit[], elem: varbit) 返回数组中elem 第一次出现的索引 。int
array_positions(array: bool[], elem: bool)返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: bytes[], elem: bytes) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: date[], elem: date) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: decimal[], elem: decimal)返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: float[], elem: float) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: inet[], elem: inet)返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: int[], elem: int) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: interval[], elem: interval) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: string[], elem: string)返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: time[], elem: time)返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: timestamp[], elem: timestamp) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: timestamptz[], elem: timestamptz) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: uuid[], elem: uuid) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: oid[], elem: oid) 返回所有elem 在数组中出现的索引的数组。int[]
array_positions(array: varbit[], elem: varbit) 返回所有elem 在数组中出现的索引的数组。int[]
array_prepend(elem: bool, array: bool[]) elem 加入数组,返回结果。bool[]
array_prepend(elem: bytes, array: bytes[]) elem 加入数组,返回结果。bytes[]
array_prepend(elem: date, array: date[]) elem 加入数组,返回结果。date[]
array_prepend(elem: decimal, array: decimal[]) elem 加入数组,返回结果。decimal[]
array_prepend(elem: float, array: float[]) elem 加入数组,返回结果。float[]
array_prepend(elem: inet, array: inet[]) elem 加入数组,返回结果。inet[]
array_prepend(elem: int, array: int[]) elem 加入数组,返回结果。int[]
array_prepend(elem: interval, array: interval[])elem 加入数组,返回结果。interval[]
array_prepend(elem: string, array: string[]) elem 加入数组,返回结果。string[]
array_prepend(elem: time, array: time[]) elem 加入数组,返回结果。time[]
array_prepend(elem: timestamp, array: timestamp[]) elem 加入数组,返回结果。timestamp[]
array_prepend(elem: timestamptz, array: timestamptz[])elem 加入数组,返回结果。timestamptz[]
array_prepend(elem: uuid, array: uuid[]) elem 加入数组,返回结果。uuid[]
array_prepend(elem: varbit, array: varbit[]) elem 加入数组,返回结果。varbit[]
array_remove(array: bool[], elem: bool) 从数组中删除所有等于elem的元素。bool[]
array_remove(array: bytes[], elem: bytes) 从数组中删除所有等于elem的元素。bytes[]
array_remove(array: date[], elem: date) 从数组中删除所有等于elem的元素。date[]
array_remove(array: decimal[], elem: decimal) 从数组中删除所有等于elem的元素。decimal[]
array_remove(array: float[], elem: float) 从数组中删除所有等于elem的元素。float[]
array_remove(array: inet[], elem: inet)从数组中删除所有等于elem的元素。inet[]
array_remove(array: int[], elem: int) → int[]从数组中删除所有等于elem的元素。int[]
array_remove(array: interval[], elem: interval) 从数组中删除所有等于elem的元素。interval[]
array_remove(array: string[], elem: string) 从数组中删除所有等于elem的元素。string[]
array_remove(array: time[], elem: time)从数组中删除所有等于elem的元素。time[]
array_remove(array: timestamp[], elem: timestamp) 从数组中删除所有等于elem的元素。timestamp[]
array_remove(array: timestamptz[], elem: timestamptz) 从数组中删除所有等于elem的元素。timestamptz[]
array_remove(array: uuid[], elem: uuid)从数组中删除所有等于elem的元素。uuid[]
array_remove(array: varbit[], elem: varbit) 从数组中删除所有等于elem的元素。varbit[]
array_replace(array: bool[], toreplace: bool, replacewith: bool)用replacewith替换数组中出现的所有toreplace。bool[]
array_replace(array: bytes[], toreplace: bytes, replacewith: bytes)用replacewith替换数组中出现的所有toreplace。bytes[]
array_replace(array: date[], toreplace: date, replacewith: date) 用replacewith替换数组中出现的所有toreplace。date[]
array_replace(array: decimal[], toreplace: decimal, replacewith: decimal) 用replacewith替换数组中出现的所有toreplace。decimal[]
array_replace(array: float[], toreplace: float, replacewith: float) 用replacewith替换数组中出现的所有toreplace。float[]
array_replace(array: inet[], toreplace: inet, replacewith: inet) 用replacewith替换数组中出现的所有toreplace。inet[]
array_replace(array: int[], toreplace: int, replacewith: int) 用replacewith替换数组中出现的所有toreplace。int[]
array_replace(array: interval[], toreplace: interval, replacewith: interval)用replacewith替换数组中出现的所有toreplace。interval[]
array_replace(array: string[], toreplace: string, replacewith: string) 用replacewith替换数组中出现的所有toreplace。string[]
array_replace(array: time[], toreplace: time, replacewith: time) 用replacewith替换数组中出现的所有toreplace。time[]
array_replace(array: timestamp[], toreplace: timestamp, replacewith: timestamp)用replacewith替换数组中出现的所有toreplace。timestamp[]
array_replace(array: timestamptz[], toreplace: timestamptz, replacewith: timestamptz) 用replacewith替换数组中出现的所有toreplace。timestamptz[]
array_replace(array: uuid[], toreplace: uuid, replacewith: uuid) 用replacewith替换数组中出现的所有toreplace。uuid[]
array_replace(array: varbit[], toreplace: varbit, replacewith: varbit) 用replacewith替换数组中出现的所有toreplace。varbit[]
array_to_string(input: anyelement[], delim: string)用定界符将数组连接到字符串中。string
array_to_string(input: anyelement[], delimiter: string, null: string)用分隔符将数组连接到字符串中,用空字符串替换NULL。string
array_upper(input: anyelement[], array_dimension: int)计算input提供的上的最大值array_dimension。但是,由于目前尚不支持多维数组,因此唯一支持的array_dimension是1。int
string_to_array(str: string, delimiter: string)将字符串拆分为分隔符上的组件。string[]
string_to_array(str: string, delimiter: string, null: string)使用指定的字符串将字符串拆分为定界符上的组件,以将其视为NULL。string[]
  • 利用正则表达式将字符串分割成数组
select regexp_split_to_array('hello world', E'\\s+') 
  regexp_split_to_array              
+----------------------------+
      {hello,world} 

数据示例

CREATE TABLE test_array (a STRING[]);
INSERT INTO test_array VALUES (ARRAY['Monday', 'Tuesday', 'Wednesday']);
select * from test_array;
              a               
+----------------------------+
  {Monday,Tuesday,Wednesday}  
  • regexp_split_to_table
select  regexp_split_to_table('11,22,33',',');
  regexp_split_to_table
-------------------------
  11
  22
  33
  • append(array: x[], elem: y) → bool[]

数组拼接单个元素

select array_append(a,'Thursday') from test_array;
             array_append              
+-------------------------------------+

  {Monday,Tuesday,Wednesday,Thursday}
  • array_cat(array: x[], array y[])

两个数组拼接

select array_cat(a,ARRAY['Thursday', 'Friday', 'Saturday']) from test_array;
                       array_cat                       
+-----------------------------------------------------+
  {Monday,Tuesday,Wednesday,Thursday,Friday,Saturday}
  • array_length(array:x[],1)

数组大小,第二个参数暂时只支持1个间隔计算。

select array_length(a,1) from test_array;
  array_length  
+--------------+
          3
  • array_lower(array:x[],1)

返回指定的数组维数的下界,第二个参数暂时只支持1个间隔计算。

select array_lower(a,1) from test_array;
  array_lower  
+-------------+
          1
  • array_upper(array:x[],1)

返回指定数组维数的上界,第二个参数暂时只支持1个间隔计算。

select array_upper(a,1) from test_array;
  array_upper  
+-------------+
          3
  • array_position(array:x[],y)

返回元素的位置

select array_position(a,'Tuesday') from test_array;
  array_position  
+----------------+
          2 
  • array_prepend(y,array:x[])

数组之前插入元素

select array_prepend('Thursday',a) from test_array;
             array_prepend             
+-------------------------------------+
  {Thursday,Monday,Tuesday,Wednesday}
  • array_remove(array:x[],y)

数组中删除元素

select array_remove(a,'Tuesday') from test_array;
     array_remove     
+--------------------+
  {Monday,Wednesday}
  • array_replace(array:x[],y,z)

替换数组中的元素yz

select array_replace(a,'Tuesday','Thursday') from test_array;
         array_replace         
+-----------------------------+
  {Monday,Thursday,Wednesday}
  • array_to_string(array:x[],delim)

数组转字符串,按delim分隔

select array_to_string(a,',') from test_array;
      array_to_string       
+--------------------------+
  Monday,Tuesday,Wednesday
  • array_to_string(array:x[],delim,y)

数组转字符串,按delim分隔,y代替null,即保留null在字符串中的位置。

select array_to_string(ARRAY['Thursday', 'Friday', 'Saturday',null,'Friday'],',');
          array_to_string          
+---------------------------------+
  Thursday,Friday,Saturday,Friday
select array_to_string(ARRAY['Thursday', 'Friday', 'Saturday',null,'Friday'],',','y');
          array_to_string           
+----------------------------------+
  Thursday,Friday,Saturday,y,Friday
  • string_to_array(str:string,delim)

字符串转数组,按分隔符拆分

select string_to_array('Thursday,Friday,Saturday,Friday',',');
           string_to_array           
+-----------------------------------+
  {Thursday,Friday,Saturday,Friday}
  • string_to_array(str:string,delim,y)

字符串转数组,按分隔符拆分,将y视为null

select string_to_array('Thursday,Friday,Saturday,y,Friday',',','y');
             string_to_array              
+----------------------------------------+
  {Thursday,Friday,Saturday,NULL,Friday}

比较函数

函数说明返回值类型
greatest(anyelement...) 返回具有最大值的元素。anyelement
least(anyelement...) 返回具有最小值的元素。anyelement
  • greatest(anyelement...)

取元素中的最大值

select greatest(1,10,100,15);
  greatest  
+----------+
     100 
  • least(anyelement...)

取元素中的最小值

select least(1,2,10,100,122);
  least  
+-------+
      1 

时间函数

函数说明返回值类型
age(end: timestamptz, begin: timestamptz)计算beginend之间的时间间隔。interval
age(val: timestamptz) 计算val与当前时间之间的间隔。interval
clock_timestamp() 返回一个集群节点上的当前系统时间。timestamp
current_date()返回当前事务的日期。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。date
current_timestamp()返回当前事务的时间。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。date
current_timestamp() 返回当前事务的时间。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。timestamp
date_trunc(element: string, input: time) → intervalinput精确到截断element。将所有不重要的字段设置element为零。兼容元素:时,分,秒,毫秒,微秒。interval
date_trunc(element: string, input: timestamp) → timestampinput精确到截断element。将所有不重要的字段设置element为零(对于日和月,则为一)兼容的元素:年,季,月,周,小时,分钟,秒,毫秒,微秒。timestamp
date_trunc(element: string, input: timestamptz) input精确到截断element。将所有不重要的字段设置element为零(对于日和月,则为一)兼容的元素:年,季,月,周,小时,分钟,秒,毫秒,微秒。timestamptz
experimental_follower_read_timestamp() 返回一个时间戳,很可能可以安全地针对跟随者副本执行。此功能旨在与AS OF SYSTEM TIME子句一起使用,以对最近的时间执行历史读取,但时间要足够长,以便针对给定范围内的当前租户(相对于最近的租户)执行读取操作。请注意,此功能需要CCL发行版上的企业许可证才能无错误地返回。timestamptz
experimental_strftime(input: date, extract_format: string) 从中input提取和格式化extract_format使用标准strftime符号标识的时间(尽管并非所有格式都受支持)。string
experimental_strftime(input: timestamp, extract_format: string) 从中input提取和格式化extract_format使用标准strftime符号标识的时间(尽管并非所有格式都受支持)。string
experimental_strftime(input: timestamptz, extract_format: string) 从中input提取和格式化extract_format使用标准strftime符号标识的时间(尽管并非所有格式都受支持)。string
experimental_strptime(input: string, format: string) input使用format(使用标准strptime格式)作为时间戳返回。timestamptz
extract(element: string, input: date) element从中提取input。兼容的元素:年,季度,月,周,星期几,年日,小时,分钟,秒,毫秒,微秒int
extract(element: string, input: time) element从中提取input。兼容的元素:小时,分钟,秒,毫秒,微秒int
extract(element: string, input: timestamp)element从中提取input。兼容的元素:年,季度,月,周,星期几,年日,小时,分钟,秒,毫秒,微秒int
extract(element: string, input: timestamptz) element从中提取input。兼容的元素:年,季度,月,周,星期几,年日,小时,分钟,秒,毫秒,微秒int
now() 返回当前交易的时间。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。date
now() 返回当前交易的时间。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。timestamp
statement_timestamp() 返回当前语句的开始时间。timestamp
timezone(timestamp: timestamp, timezone: string) 将不带时区的给定时间戳视为位于指定时区中timestamp
transaction_timestamp() 返回当前交易的时间。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。date
transaction_timestamp() 返回当前交易的时间。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。timestamp

案例:

  • age(end: timestamptz, begin: timestamptz)

计算endbegin之间的时间间隔,结果体现为前值减去后值

select age(TIMESTAMPTZ '2022-03-26 14:22:10+08:00',TIMESTAMPTZ '2020-02-26 10:10:10-05:00');
            age     
+--------------------------+
  2 years 28 days 15:12:00 
  • age(val: timestamptz)

与当前时间的间隔

select age(TIMESTAMPTZ '2021-11-26 14:22:10+08:00');

               age         
+--------------------------------+
  10 mons 14 days 23:57:43.199154
  • current_date()

展示当前时间(年月日)

select current_date();
         current_date         
+-----------------------------+
          2022-10-11
  • localtime()

展示当前时间(时分秒)

select localtime();
    localtime        
+---------------+
     16:33:30
  • localtimestamp 显示时间(年月日时分秒)
select localtimestamp();
        localtimestamp
------------------------------
  2022-11-04 10:21:11.173667
  • current_timestamp()或者now()

展示当前完整时间

select current_timestamp();
       current_timestamp()         
+-----------------------------+
 2022-10-11 14:28:42.693 +0800

或者

select now();
            now()         
+-----------------------------+
 2022-10-11 14:32:38.070 +0800
  • date_trunc(x,input:date)

截断时间,后边清零,x(year, quarter, month, week, hour, minute, second, millisecond, microsecond)

select date_trunc('day',TIMESTAMPTZ '2022-11-12 14:22:10+08:00')  as  date_day;
         date_day          
+---------------------------+
 2022-11-12 00:00:00.000 +0800  
(1 row)
select date_trunc('month',TIMESTAMPTZ '2022-11-12 14:22:10+08:00') as date_month;
         date_month          
+---------------------------+
 2022-11-01 00:00:00.000 +0800  
(1 row)
  • extract(x,input:date)

input中提取x(year, quarter, month, week, dayofweek, hour, minute, second, millisecond, microsecond)

提取日期中的月份

select extract('month',TIMESTAMPTZ '2022-10-11 16:22:10+08:00');
  extract  
+---------+
    10   

根据日期提取今天是星期几,若返回0,则代表星期日

select extract('dayofweek',TIMESTAMPTZ '2022-10-16 16:22:10+08:00') as dayinweek;
 dayinweek  
+---------+
    0   
select extract('dayofweek',TIMESTAMPTZ '2022-10-11 16:22:10+08:00') as dayinweek;
 dayinweek  
+---------+
    2   
  • 获取当前时间是在年中的第多少周
select extract('week',TIMESTAMPTZ '2022-10-16 16:22:10+08:00') as weekofyear;
   weekofyear  
+------------+
      41  
  • 日期转字符串,用函数experimental_strftime()
select experimental_strftime(b,'%Y-%m-%d %H:%M:%S') from a;
  experimental_strftime
-------------------------
  2018-12-21 10:21:24
(1 row)
  • 字符串转日期,用函数experimental_strptime()
SELECT experimental_strptime(a,'%Y-%m-%d %H:%M:%S') FROM b;
  experimental_strptime
--------------------------
  2018-12-21 18:21:24+08
  • 时间的增减

增加一秒

SELECT now(), now() + interval '1s' as newnow;
           now()                       |          newnow
---------------------------------------+------------------------------
2022-10-11 15:47:23.380 +0800          | 2022-10-11 15:47:24.380 +0800

增加一天

SELECT now(), now() + interval '1 day' as newnow;
               now              |            newnow
--------------------------------+--------------------------------
  2022-10-18 11:06:25.522553+08 | 2022-10-19 11:06:25.522553+08
  • 获取本月的最后一天的日期
SELECT (date_trunc('month',now()) + INTERVAL '1 MONTH' + INTERVAL '-1 day')::DATE
           date
--------------------------
       2022-10-31
  • 或取当月最后一天的时间戳
SELECT (date_trunc('month',now()) + INTERVAL '1 MONTH' + INTERVAL '-1 second')::timestamp
          date
--------------------------
  2022-10-31 23:59:59.000

ID函数

函数说明返回值类型
gen_random_uuid()生成随机UUID并将其作为UUID类型的值返回。uuid
unique_rowid() 如果未为表定义主键,则返回用于生成唯一行ID的唯一ID。该值是插入时间戳记与执行该语句的节点的ID的组合,从而确保此组合在全局上是唯一的。但是,可能存在差距,不能完全保证顺序。int
  • gen_random_uuid()

生成随机uuid

select gen_random_uuid();
            gen_random_uuid             
+--------------------------------------+
  9c5b836c-486b-46ed-acb3-701c093f59fa  
  • unique_rowid()
select unique_rowid();
     unique_rowid     
+--------------------+
  514553266254184454  

生成唯一键,保证全局唯一,可用作主键。但由于集群性质,不能完全保证排序。

数学类函数

函数返回类型参数类型说明
absfloatfloat计算绝对值
absdecimaldecimal计算绝对值
absintint计算绝对值
acosfloatfloat计算反余弦
asinfloatfloat计算反正弦
atanfloatfloat计算反正切
atan2floatfloat,float计算入参的相除的反正切
cbrtfloatfloat计算立方根
ceildecimaldecimal计算最小整数
ceilfloatfloat计算最小整数
cosfloatfloat计算余弦值
cotfloatfloat计算余切
crc32cintbytescompute CRC(Castagnoli polynomial.)
crc32cintstringcompute CRC(Castagnoli polynomial.)
crc32ieeeintbytescompute CRC(IEEE polynomial)
crc32ieeeintstringcompute CRC(IEEE polynomial)
degreesfloatfloat将弧度值转换为度值
divdecimaldecimal,decimal计算x/y的整数商
divfloatfloat,float计算x/y的整数商
divintint,int计算x/y的整数商
expfloatfloate的幂次方
floordecimaldecimal计算不大于入参的最大整数
floorfloatfloat计算不大于入参的最大整数
fnv32intbytes计算32位的 FNV-1 hash值
fnv32intstring计算32位的 FNV-1 hash值
fnv32aintbytes计算32位的 FNV-1a hash值
fnv32aintstring计算32位的 FNV-1a hash值
fnv64intbytes计算64位的 FNV-1 hash值
fnv64intstring计算64位的 FNV-1 hash值
fnv64aintbytes计算64位的 FNV-1a hash值
fnv64aintstring计算64位的 FNV-1a hash值
isnanbooldecimal判断是否为NaN
isnanboolfloat判断是否为NaN
lndecimaldecimal计算自然对数
lnfloatfloat计算自然对数
logdecimaldecimal计算以10为底的对数
logfloatfloat计算以10为底的对数
moddecimaldecimal,decimal计算余数
modfloatfloat,float计算余数
modintint,int计算余数
pifloat3.141592653589793
powdecimaldecimal,decimalx^y
powfloatfloat,floatx^y
powintint,intx^y
radiansfloatfloat度值转换为弧度值
random()floatfloat0与1之间的随机数
rounddecimaldecimal,int四舍五入到给定的小数位数
roundfloatfloat,int四舍五入到给定的小数位数
rounddecimaldecimal四舍五入取整数
roundfloatfloat四舍五入取整数
signdecimaldecimal符号函数
signfloatfloat符号函数
signintint符号函数
sinfloatfloat计算正弦
sqrtdecimaldecimal计算平方根
sqrtfloatfloat计算平方根
tanfloatfloat计算正切
truncdecimaldecimal截断小数
truncfloatfloat截断小数
  • abs

绝对值

select abs(-11.19);
   abs
---------
  11.19
  • cbrt
select cbrt(27.0);
          cbrt
-------------------------
  3.0000000000000000000
  • ceil

向上取整

select ceil(23.7);
  ceil
--------
   24
select ceiling(-95.3);
  ceiling
-----------
      -95
  • degrees

把弧度转为角度

select degrees(0.5);
       degrees
---------------------
  28.64788975654116
  • div(y/x)

y/x的整数商

select div(9,4);
  div
-------
    2
  • exp

指数(e的幂次方)

select exp(1.0);
           exp
-------------------------
  2.7182818284590452354
  • floor

不大于参数的最近的整数

select floor(-42.8);
  floor
---------
    -43
  • ln

计算自然对数

select ln(2.0);
            ln
--------------------------
  0.69314718055994530942
  • log

以10为底的对数

select log(100.0);
           log
-------------------------
  2.0000000000000000000

以2为底的对数

select log(2.0, 64.0);
           log
-------------------------
  6.0000000000000000000
  • mod

计算余数

 select mod(8,3);
  mod
-------
    2
  • pi
select pi();
         pi
---------------------
  3.141592653589793
  • pow

ab次方

select pow(2,3);
 pow
-------
    8
  • random

0-1的随机数

select random();
        random
----------------------
  0.7155664026177302
  • round

四舍五入

select round(10.7);
  round
---------
     11
select round(10.1);
  round
---------
     10

四舍五入到给定的小数位数

select round(10.3455,2);
  round
---------
  10.35
  • sqrt

计算平方根

select sqrt(1.44);
  sqrt
--------
   1.2
  • trunc

截断小数,取整

select trunc(-45.71);
  trunc
---------
    -45
  • sind
select sind(45.0);
         sind
----------------------
  0.7071067811865475
  • sinh
select sinh(90.0);
          sinh
-------------------------
  6.102016471589204e+38
  • STDDEV_POP

数据准备

CREATE TABLE Player  
( 
PlayerName VARCHAR(100) NOT NULL,
RunScored INT NOT NULL,
WicketsTaken INT NOT NULL
);


INSERT INTO Player  
(PlayerName, RunScored,  WicketsTaken )
VALUES
('KL Rahul', 52, 0 ),
('Hardik Pandya', 30, 1 ),
('Ravindra Jadeja', 18, 2 ),
('Washington Sundar', 10, 1),
('D Chahar', 11, 2 ),  
('Mitchell Starc', 0, 3);

将找到WicketsTaken列的总体标准差

SELECT  STDDEV_POP(WicketsTaken) 
as Pop_Std_Dev_Wickets   
FROM Player ;
  pop_standard_deviation
--------------------------
   16.876183086099639597
  • uuid_generate_v4
select uuid_generate_v4();
            uuid_generate_v4
----------------------------------------
  165f8c68-d670-4708-aa50-ae6fc1bb72b0
(1 row)
  • VAR_POP
CREATE TABLE Player  
( 
PlayerName VARCHAR(100) NOT NULL,
RunScored INT NOT NULL,
WicketsTaken INT NOT NULL
);


INSERT INTO Player  
(PlayerName, RunScored,  WicketsTaken )
VALUES
('KL Rahul', 52, 0 ),
('Hardik Pandya', 30, 1 ),
('Ravindra Jadeja', 18, 2 ),
('Washington Sundar', 10, 1),
('D Chahar', 11, 2 ),  
('Mitchell Starc', 0, 3);

计算RunScored列的总体标准方差

SELECT  VAR_POP(RunScored ) as Run_POPVariance  
                          FROM Player ;
     run_popvariance
-------------------------
  284.80555555555555556
  • VAR_SAMP

数据准备同上一个示例

RunScored列的样本方差。

SELECT  VAR_SAMP(RunScored ) as Run_Variance 
FROM Player ;
      run_variance
-------------------------
  341.76666666666666667
  • TIMEOFDAY

返回当前日期和时间

select timeofday();
               timeofday
----------------------------------------
  Fri Nov 4 11:33:43.240953 2022 +0800
  • asind
select asind(1);
  asind
---------
     90
(1 row)

说明1的反正弦正好是90°

  • acosd
select acosd(0);
  acosd
---------
     90
(1 row)

确认0的反余弦正好是90°。

  • EVERY
create table book (id int,sal int ,bname string);

INSERT INTO book VALUES (1, 1, '1984');
INSERT INTO book VALUES (2, 1, 'Animal Farm');
INSERT INTO book VALUES (3, 2, 'O Alquimista');
INSERT INTO book VALUES (4, 2, 'Brida');

判断每本书的价格是否都小于10元

SELECT EVERY(sal < 10) FROM book;
  every
---------
  true
(1 row)

字符串函数

函数返回类型参数说明
asciiintstring返回第一个字符的asc编码
bit_lengthintbytes计算bits数
bit_lengthintstring计算bits数
btrimstringstring,string删除匹配的字符
btrimstringstring去除空格
char_lengthintbytes计算字节长度
char_lengthintstring计算字符长度
chrstringint返回ascii值
concatstringstring...拼接字符串
concat_wsstringstring...使用第一个参数拼接字符串
convert_fromstringbytes,string将字节转化为执行编码的字符串(支持UTF8与LATIN1)
convert_tobytesstring,string将字符串转化为指定编码的字节(支持UTF8与LATIN1)
decodebytesstring,string换化为字节码,支持hex,escape,base64
encodestringbytes,string换化为字符串,支持hex,escape,base64
from_ipstringbytes将IP的字节字符串表示形式转换为其字符串表示形式
from_uuidstringbytes将UUID的字节字符串表示形式转换为其字符串表示形式。
initcapstringstring第一个字母大写。
leftbytesbytes,int获取头几位字节
leftstringstring,int获取头几位字符串
lengthintbytes计算字节码的长度
lengthintstring计算字符串的长度
lowerstringstring将大写转化为小写
lpadstringstring,int通过在字符串的左侧添加' '来增加长度。如果字符串比长度长,就被截断。
lpadstringstring,int ,string通过在字符串的左侧添加传入参数来增加长度。如果字符串比长度长,就被截断。
ltrimstringstring,string递归从输入的开始(左侧)删除包含的所有字符
ltrimstringstring去除左边的空格
md5stringbytes计算MD5值
md5stringstring计算MD5值
octet_lengthintbytes计算字节数
octet_lengthintstring计算字节数
overlaystringstring,string,int从指定下标开始替换字符
overlaystringstring,string,int,int从指定下标开始替换字符
quote_identstringstring返回val作为SQL语句中的标识符。
quote_literalstringstring返回val作为SQL语句中的字符串文字适当引用。
quote_nullablestringstring将参数强制转换为字符串,然后将其作为文字引用。
regexp_extractstringstring,string返回输入中正则表达式的第一个匹配项。
regexp_replacestringstring,string,string将输入中的正则表达式regex的匹配项替换为正则表达式替换。
repeatstringstring,int返回输入指定重复次数的参数
replacestringstring,string,stringreplace替换输入的第三个参数替换匹配的第二个参数
reversestringstring反转字符串字符的顺序。
rightbytesbytes,int获取从右开始头几位字节
rightstringstring,int获取从右开始头几位字符
rpadstringstring,int通过在字符串的右侧添加' '来增加长度。如果字符串比长度长,就被截断。
rpadstringstring,int,string通过在字符串的右侧添加传入参数来增加长度。如果字符串比长度长,就被截断。
rtrimstringstring,string递归从输入的开始(右侧)删除包含的所有字符
rtrimstringstring去除右边的空格
sha1stringbytes计算SHA1值
sha1stringstring计算SHA1值
sha256stringbytes计算SHA256值
sha256stringstring计算SHA256值
sha512stringbytes计算SHA512值
sha512stringstring计算SHA512值
split_partstringstring,string,int分割分隔符上的输入并返回第几位
strposintstring,string计算输入中字符串开始的位置。
substrstringstring,string返回与正则表达式regex匹配的输入子字符串。
substrstringstring,string,string返回与正则表达式regex匹配的输入子字符串,使用第三个参数作为转义字符
substrstringstring,int从指定下标开始截取字符串
substrstringstring,int,int从指定下标开始截取字符串,并指定截取字符串长度
to_englishstringint此函数使用英语基数来声明其参数的值。
to_hexstringbytes将val转换为其十六进制表示形式。
to_hexbytesstring将val转换为其十六进制表示形式。
to_uuidbytesstring将UUID的字符串表示形式转换为其字节字符串表示形式。
translatestringstring,string,string指定替代字符串内容
upperstringstring将字符内容转为大写
  • ascii
select  ascii('a') ;
  ascii  
+--------------+
   97
  • 将字符串所有的单词进行格式化,首字母大写,其它为小写
select initcap('hi THOMAS');
  initcap  
+--------------+
 Hi Thomas
  • decode,对字符串按指定的类型进行解码
select decode('MTIzAAE=', 'base64'); 
  decode  
+--------------+
   123  
  • char_length()
select char_length('stfwwwwwfsf')
  char_length  
+--------------+
   11  
  • chr()
select chr(76);
  chr  
+--------------+
   L  
  • CONCAT字符串的连接
create table full_test(
first_name varchar(10),
last_name  varchar(10)
);

insert into full_test values ('zhang','sanfeng'),('liu','dehua'),('王','阳明');

select 
    CONCAT(first_name, ' ', last_name) AS "Full name"
from 
    full_test;
  Full name  
+--------------+
  zhang sanfeng
  liu dehua
    王 阳明 
  • concat_ws()

使用等号=拼接字段usernameaddress

select concat_ws('=', username, address) as info from user;
  Full name  
+--------------+
    张三=上海
    李四=上海
    王五=上海
  • 将一数值转换为字符串,并为其两边加上单引号
select squote_literal(42.5);
  quote_literal
+--------------+
   '42.5'
  • lower

将大写字母转化为小写

select  lower('ADDRESS') ;
    lower
+--------------+
   address
  • overlay()用于函数的替换

第一个起始位置参数,不是从0开始的,从1开始,第一个参数是起始位置,第二个是要被代替的字符长度

select overlay('Txxxxas' placing 'hom' from 2 for 4) ;
   overlay 
+--------------+
   Thomas

去掉for的情况,则会走默认代替的长度,故建议在使用时候选择语句中带有for的情况

select overlay('Txxxxas' placing 'hom' from 2 ) ;
   overlay  
+--------------+
   Thomxas
  • convert_from
select  convert_from('text_in_utf8', 'UTF8');
  • convert_to
select convert_to('some text', 'UTF8')
   convert_to  
+--------------+
   some text
  • encode
select encode(E'123\000\001', 'base64')
   encode  
+--------------+
   MTIzAAE=  
  • LEFT()

获取前5位字符串

select  LEFT('1234567890', 5);
  • upper()

小写字母转大写

select  upper('abcdef');
   upper  
+--------------+
   ABCDEF  
  • md5()

计算MD5

select  md5('hubble') ;
               md5  
+--------------------------------+
 90dc10ef0211b0088ac9430df0f6c158  
  • quote_nullable

可以将数字类型转化成字符串类型

select quote_nullable('address') ;
quote_nullable  
+--------------+
  'address'  
  • replace
select REPLACE (
        'http://www.baidu.cn',
        'http',
        'https'
    );
    replace 
+--------------------+
 https://www.baidu.cn  
  • sha256()加密
select sha256('hubble');
    sha256
+---------------------------------------------------------------+
 18d9486f99df32c58605e574ffae3773efe42294e95a1577c52a6f5d987ad44f 
  • split_part()
select split_part('add.ress', '.', 1);
   split_part
+--------------------+
   add
  • strpos

指定字符串在目标字符串的位置

select strpos('high', 'ig')
   strpos 
+--------------+
   2
  • substr()

三个参数情况,以下代表从第2位截取,截取3个字符

select substr('address', 2,3);
   substr 
+--------------+
   ddr

二个参数情况,代表从2位截取,默认截取到最后

select substr('address', 2);
   substr 
+--------------+
   ddress
  • to_english
select to_english(100);
 to_english 
+--------------+
 one-zero-zero
  • 将字符串中某些匹配的字符替换成指定字符串,目标字符与源字符都可以同时指定多个
select translate('abcde', 'ad', '14');
   translate 
+--------------+
   1bc4e
  • DIFFERENCE,值返回0-4,值越大相似性越大

用法:

DIFFERENCE(string, string)
SELECT SOUNDEX('poor') soundex_poor, SOUNDEX('pour') soundex_pour, 
DIFFERENCE('poor', 'pour') similarity;
  soundex_poor | soundex_pour | similarity
---------------+--------------+-------------
  P600         | P600         | 4

聚合函数

函数说明返回值类型
cume_dist() 计算当前行的相对排名:(当前行之前或与之对等的行数)/(总行数)。float
dense_rank() 计算当前行的排名,不留空格;此功能计算对等组。int
first_value(val: bool)返回val在窗口框第一行的那一行求值。bool
first_value(val: bytes) 返回val在窗口框第一行的那一行求值bytes
first_value(val: date)返回val在窗口框第一行的那一行求值。date
first_value(val:decimal)返回val在窗口框第一行的那一行求值。decimal
first_value(val: float)返回val在窗口框第一行的那一行求值。float
first_value(val: int) 返回val在窗口框第一行的那一行求值。int
first_value(val: string) 返回val在窗口框第一行的那一行求值。string
first_value(val: time) 返回val在窗口框第一行的那一行求值。time
first_value(val: timestamp) 返回val在窗口框第一行的那一行求值。timestamp
first_value(val: uuid) 返回val在窗口框第一行的那一行求值。uuid
first_value(val: jsonb) 返回val在窗口框第一行的那一行求值。jsonb
lag(val: bool) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。bool
lag(val: bool, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。bool
lag(val: bool, n: int, default: bool) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。bool
lag(val: date, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。date
lag(val: date, n: int, default: date) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。date
lag(val: decimal)返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。decimal
lag(val: decimal, n: int)返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。decimal
lag(val: decimal, n: int, default: decimal) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。decimal
lag(val: float) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。float
lag(val: float, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。float
lag(val: float, n: int, default: float) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。float
lag(val: inet) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。inet
lag(val: inet, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。inet
lag(val: inet, n: int, default: inet) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。inet
lag(val: int) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。int
lag(val: int, n: int)返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。int
lag(val: int, n: int, default: int) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。int
lag(val: interval) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。interval
lag(val: interval, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。interval
lag(val: interval, n: int, default: interval) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。interval
lag(val: string) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。string
lag(val: string, n: int) → string返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。string
lag(val: string, n: int, default: string) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。string
lag(val: time) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。time
lag(val: time, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。time
lag(val: time, n: int, default: time) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。time
lag(val: timestamp) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。timestamp
lag(val: timestamp, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。timestamp
lag(val: timestamp, n: int, default: timestamp) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。timestamp
lag(val: timestamptz) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。timestamptz
lag(val: timestamptz, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。timestamptz
lag(val: timestamptz, n: int, default: timestamptz) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。timestamptz
lag(val: uuid) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。uuid
lag(val: uuid, n: int) 返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。uuid
lag(val: uuid, n: int, default: uuid) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。uuid
lag(val: jsonb) 返回val在当前行分区内的前一行求值的结果;如果没有这样的行,则返回null。jsonb
lag(val: jsonb, n: int)返回在其分区中当前行之前的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。jsonb
lag(val: jsonb, n: int, default: jsonb) 返回在其分区中当前行之前的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。jsonb
last_value(val: bool) 返回val在窗框的最后一行的那一行求值。bool
last_value(val: bytes) 返回val在窗框的最后一行的那一行求值。bytes
last_value(val: date) 返回val在窗框的最后一行的那一行求值。date
last_value(val: decimal)返回val在窗框的最后一行的那一行求值。decimal
last_value(val: float)返回val在窗框的最后一行的那一行求值。float
last_value(val: inet) 返回val在窗框的最后一行的那一行求值。inet
last_value(val: int) 返回val在窗框的最后一行的那一行求值。int
last_value(val: interval) 返回val在窗框的最后一行的那一行求值。interval
last_value(val: string) 返回val在窗框的最后一行的那一行求值。string
last_value(val: time) 返回val在窗框的最后一行的那一行求值。time
last_value(val: timestamp) 返回val在窗框的最后一行的那一行求值。timestamp
last_value(val: timestamptz) 返回val在窗框的最后一行的那一行求值。timestamptz
last_value(val: uuid) 返回val在窗框的最后一行的那一行求值。uuid
last_value(val: jsonb) 返回val在窗框的最后一行的那一行求值。jsonb
lead(val: bool) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。bool
lead(val: bool, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。bool
lead(val: bool, n: int, default: bool) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。bool
lead(val: bytes) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。bytes
lead(val: bytes, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。bytes
lead(val: bytes, n: int, default: bytes)返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。bytes
lead(val: date) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。date
lead(val: date, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。date
lead(val: date, n: int, default: date) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。date
lead(val: decimal)返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。decimal
lead(val: decimal, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。decimal
lead(val: decimal, n: int, default: decimal) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。decimal
lead(val: float) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。float
lead(val: float, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。float
lead(val: float, n: int, default: float)返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。float
lead(val: inet) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。inet
lead(val: inet, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。int
lead(val: inet, n: int, default: inet) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。inet
lead(val: int)返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。int
lead(val: int, n: int)返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。int
lead(val: int, n: int, default: int) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。int
lead(val: interval) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。interval
lead(val: interval, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。interval
lead(val: interval, n: int, default: interval)返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。interval
lead(val: string) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。string
lead(val: string, n: int)返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。string
lead(val: string, n: int, default: string) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。string
lead(val: time) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。time
lead(val: time, n: int)返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。time
lead(val: time, n: int, default: time) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。time
lead(val: timestamp) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。timestamp
lead(val: timestamp, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。timestamp
lead(val: timestamp, n: int, default: timestamp)返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。timestamp
lead(val: timestamptz) → timestamptz返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。timestamptz
lead(val: timestamptz, n: int) → timestamptz返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。timestamptz
lead(val: timestamptz, n: int, default: timestamptz) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。timestamptz
lead(val: uuid) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。uuid
lead(val: uuid, n: int) 返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。uuid
lead(val: uuid, n: int, default: uuid) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。uuid
lead(val: jsonb) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。jsonb
lead(val: jsonb, n: int)返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。jsonb
lead(val: jsonb, n: int, default: jsonb) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。jsonb
lead(val: varbit) 返回val当前行分区中下一行的求值;如果没有这样的行,则返回null。varbit
lead(val: varbit, n: int) → varbit返回在其分区中当前行之后的行val处求值n;如果没有这样的行,则返回null。n针对当前行进行评估。varbit
lead(val: varbit, n: int, default: varbit) 返回在其分区中当前行之后的行val处求值n;如果没有,则返回行default(必须与类型相同val)。双方ndefault相对于当前行评估。varbit
nth_value(val: bool, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。bool
nth_value(val: bytes, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。bytes
nth_value(val: date, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。date
nth_value(val: decimal, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。decimal
nth_value(val: float, n: int)返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。float
nth_value(val: inet, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。inet
nth_value(val: int, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。int
nth_value(val: interval, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。interval
nth_value(val: string, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。string
nth_value(val: time, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。time
nth_value(val: timestamp, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。timestamp
nth_value(val: timestamptz, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。timestamptz
nth_value(val: uuid, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。uuid
nth_value(val: jsonb, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。jsonb
nth_value(val: varbit, n: int) 返回valn窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。varbit
ntile(n: int) 计算范围为1到的整数n,将分区尽可能平均地划分。int
percent_rank() 计算当前行的相对等级:(等级-1)/(总行-1)。float
rank() 计算带有间隙的当前行的排名;与第一个对等方的row_number相同。int
row_number() 计算分区中当前行的数量,从1开始计算。int
  • cume_dist

计算当前行的相对排名,统计小于等于当前工资的人数占总人数的比例

select ename, 
       deptno, 
       sal,
       cume_dist() OVER (ORDER BY sal) as cume_dist
from (table emp order by sal desc limit 10);
   ename  | deptno |   sal    | cume_dist
----------+--------+----------+------------
    TH    |     30 |  4801.79 |       0.1
  FORD    |     20 |  4831.53 |       0.3
  SCOTT   |     20 |  4831.53 |       0.3
  MILLER  |     10 |  4936.76 |       0.4
  KING    |     10 |  5000.00 |       0.5
  PAIABRS |     20 |  5381.50 |       0.8
  PAIRS   |     10 |  5381.50 |       0.8
  LEAERS  |     40 |  5381.50 |       0.8
  LAIHUI  |     10 | 18000.00 |         1
   LUCK   |     10 | 18000.00 |         1
  • PERCENT_RANK()

语法

PERCENT_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

分析以上语法:

  • 这个分区依据子句将行分为多个分区,PERCENT_RANK()函数已应用。
  • 这个排序子句指定应用该函数的每个分区中的行顺序。
  • 这个PERCENT_RANK()函数返回大于等于0且小于等于1的结果。
select
    ename,
    sal,
    PERCENT_RANK() OVER (
        ORDER BY sal
    )
from
    (table emp  order by sal desc limit 10);
   ename  |   sal    |    percent_rank
----------+----------+---------------------
  LUCK    |  4801.79 |                  0
  FORD    |  4831.53 | 0.1111111111111111
  SCOTT   |  4831.53 | 0.1111111111111111
  MILLER  |  4936.76 | 0.3333333333333333
  KING    |  5000.00 | 0.4444444444444444
  PAIABRS |  5381.50 | 0.5555555555555556
  PAIRS   |  5381.50 | 0.5555555555555556
  LEAERS  |  5381.50 | 0.5555555555555556
  LAIHUI  | 18000.00 | 0.8888888888888888
  WANG    | 18000.00 | 0.8888888888888888
  • 有关dense_rank()rank()row_number()的示例可以参考窗口函数

  • NTILE()

在hubble中,的NTILE()函数用于将分区中的有序行划分为指定数量的已排序存储,NTILE()函数允许用户将分区中的有序行划分为指定数量的分级组,并使其大小尽可能相等。这些排名的组称为存储桶。 NTILE()函数为每个组分配一个从1开始的存储桶号。对于组中的每一行,NTILE()函数分配一个存储桶号,该存储桶号代表该行所属的组。

语法

NTILE()函数的语法如下:

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    [ORDER BY sort_expression [ASC | DESC], ...]
)

语法分析语法:

  • 这个水桶代表排名组的数量。它可以是数字或表达式,每个分区的计算结果为正整数值(大于0)。的水桶不能为空。
  • PARTITION BY子句将行分布到应用了该函数的分区中。PARTITION BY子句是可选的。
  • 这个排序子句对应用该函数的每个分区中的行进行排序。

示例数据

CREATE TABLE sales_stats(
    name  VARCHAR(100) NOT NULL,
    year  INT          NOT NULL CHECK (year > 0),
    amount DECIMAL(10, 2) CHECK (amount >= 0)
   
);

INSERT INTO 
    sales_stats(name, year, amount)
VALUES
    ('Raju kumar', 2018, 120000),
    ('Alibaba', 2018, 110000),
    ('Gabbar Singh', 2018, 150000),
    ('Kadar Khan', 2018, 30000),
    ('Amrish Puri', 2018, 200000),
    ('Raju kumar', 2019, 150000),
    ('Alibaba', 2019, 130000),
    ('Gabbar Singh', 2019, 180000),
    ('Kadar Khan', 2019, 25000),
    ('Amrish Puri', 2019, 270000);

NTILE()用于将数据分配到3个存储桶中的函数:

SELECT 
    name,
    amount,
    NTILE(3) OVER(
        ORDER BY amount
    )
FROM
    sales_stats
WHERE
    year = 2018;
      name     |  amount   | ntile
---------------+-----------+--------
  Raju kumar   | 120000.00 |     2
  Alibaba      | 110000.00 |     1
  Gabbar Singh | 150000.00 |     2
  Kadar Khan   |  30000.00 |     1
  Amrish Puri  | 200000.00 |     3

NTILE()划分行的函数将表分为两个分区,每个分区有3个存储桶:

SELECT 
    name,
    amount,
    NTILE(3) OVER(
        PARTITION BY year
        ORDER BY amount
    )
FROM
    sales_stats;
      name     |  amount   | ntile
---------------+-----------+--------
  Raju kumar   | 120000.00 |     2
  Alibaba      | 110000.00 |     1
  Gabbar Singh | 150000.00 |     2
  Kadar Khan   |  30000.00 |     1
  Amrish Puri  | 200000.00 |     3
  Raju kumar   | 150000.00 |     2
  Alibaba      | 130000.00 |     1
  Gabbar Singh | 180000.00 |     2
  Kadar Khan   |  25000.00 |     1
  Amrish Puri  | 270000.00 |     3
  • FIRST_VALUE()

在hubble,FIRST_VALUE()函数用于在结果集的排序分区中返回第一个值。

语法

FIRST_VALUE ( expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

语法分析

  • 这个表达可以是根据结果集排序分区的第一行的值求值的表达式,列或子查询。的表达必须返回单个值。
  • PARTITION BY子句将结果集中的行划分为应用了FIRST_VALUE()函数的分区。当用户使用PARTITION BY子句时,FIRST_VALUE()函数将整个结果集视为单个分区。
  • 这个排序子句指定每个分区中行的排序顺序FIRST_VALUE()函数被应用。
  • 这个rows_range_clause通过定义分区的开始和结束来进一步限制分区中的行。

数据准备

CREATE TABLE product_groups (
    group_id int PRIMARY KEY,
    group_name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
    product_name VARCHAR (255) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL
);

INSERT INTO product_groups (group_name)
VALUES
    (1,'Smartphone'),
    (2,'Laptop'),
    (3,'Tablet');

INSERT INTO products (product_name, group_id, price)
VALUES
    ('Microsoft Lumia', 1, 200),
    ('HTC One', 1, 400),
    ('Nexus', 1, 500),
    ('iPhone', 1, 900),
    ('HP Elite', 2, 1200),
    ('Lenovo Thinkpad', 2, 700),
    ('Sony VAIO', 2, 700),
    ('Dell Vostro', 2, 800),
    ('iPad', 3, 700),
    ('Kindle Fire', 3, 150),
    ('Samsung Galaxy Tab', 3, 200);

使用FIRST_VALUE()函数返回所有产品以及价格最低的产品:

SELECT 
    product_name,
    group_id,
    price,
    FIRST_VALUE(product_name) 
    OVER(
        ORDER BY price
    ) lowest_price
FROM 
    products;
     product_name    | group_id |  price  | lowest_price
---------------------+----------+---------+---------------
  Microsoft Lumia    |        1 |  200.00 | Kindle Fire
  HTC One            |        1 |  400.00 | Kindle Fire
  Nexus              |        1 |  500.00 | Kindle Fire
  iPhone             |        1 |  900.00 | Kindle Fire
  HP Elite           |        2 | 1200.00 | Kindle Fire
  Lenovo Thinkpad    |        2 |  700.00 | Kindle Fire
  Sony VAIO          |        2 |  700.00 | Kindle Fire
  Dell Vostro        |        2 |  800.00 | Kindle Fire
  iPad               |        3 |  700.00 | Kindle Fire
  Kindle Fire        |        3 |  150.00 | Kindle Fire
  Samsung Galaxy Tab |        3 |  200.00 | Kindle Fire

使用FIRST_VALUE()函数返回按产品组分组的所有产品。对于每个产品组,它以最低的价格返回产品:

SELECT 
    product_name,
    group_id,
    price,
    FIRST_VALUE(product_name) 
    OVER(
    PARTITION BY group_id
        ORDER BY price
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) lowest_price
FROM 
    products;
     product_name    | group_id |  price  |  lowest_price
---------------------+----------+---------+------------------
  Microsoft Lumia    |        1 |  200.00 | Microsoft Lumia
  HTC One            |        1 |  400.00 | Microsoft Lumia
  Nexus              |        1 |  500.00 | Microsoft Lumia
  iPhone             |        1 |  900.00 | Microsoft Lumia
  HP Elite           |        2 | 1200.00 | Lenovo Thinkpad
  Lenovo Thinkpad    |        2 |  700.00 | Lenovo Thinkpad
  Sony VAIO          |        2 |  700.00 | Lenovo Thinkpad
  Dell Vostro        |        2 |  800.00 | Lenovo Thinkpad
  iPad               |        3 |  700.00 | Kindle Fire
  Kindle Fire        |        3 |  150.00 | Kindle Fire
  Samsung Galaxy Tab |        3 |  200.00 | Kindle Fire
  • LAST_VALUE()

LAST_VALUE()函数返回结果集的有序分区中的最后一个值。

语法

LAST_VALUE ( expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

语法分析

  • 表达式可以是根据结果集的有序分区中最后一行的值求值的表达式,列或子查询。表达式必须返回单个值。
  • 这个分区依据子句将结果集的行划分为分区,LAST_VALUE()函数已应用。
  • 这个排序子句指定每个分区中行的排序顺序LAST_VALUE()函数已应用。
  • 这个frame_clause定义当前分区中行的子集,LAST_VALUE()函数已应用。

使用LAST_VALUE()函数返回所有产品以及价格最高的产品:

SELECT 
    product_name,
    price,
    LAST_VALUE(product_name) 
    OVER(
        ORDER BY price
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) highest_price
FROM 
    products;
     product_name    |  price  | highest_price
---------------------+---------+----------------
  Microsoft Lumia    |  200.00 | HP Elite
  HTC One            |  400.00 | HP Elite
  Nexus              |  500.00 | HP Elite
  iPhone             |  900.00 | HP Elite
  HP Elite           | 1200.00 | HP Elite
  Lenovo Thinkpad    |  700.00 | HP Elite
  Sony VAIO          |  700.00 | HP Elite
  Dell Vostro        |  800.00 | HP Elite
  iPad               |  700.00 | HP Elite
  Kindle Fire        |  150.00 | HP Elite
  Samsung Galaxy Tab |  200.00 | HP Elite

使用LAST_VALUE()用于将所有产品连同每个产品组中最昂贵的产品一起退回的函数:

SELECT 
    product_name,
    group_id,
    price,
    LAST_VALUE(product_name) 
    OVER(
    PARTITION BY group_id
        ORDER BY price
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) highest_price
FROM 
    products;
     product_name    | group_id |  price  | highest_price
---------------------+----------+---------+----------------
  Microsoft Lumia    |        1 |  200.00 | iPhone
  HTC One            |        1 |  400.00 | iPhone
  Nexus              |        1 |  500.00 | iPhone
  iPhone             |        1 |  900.00 | iPhone
  HP Elite           |        2 | 1200.00 | HP Elite
  Lenovo Thinkpad    |        2 |  700.00 | HP Elite
  Sony VAIO          |        2 |  700.00 | HP Elite
  Dell Vostro        |        2 |  800.00 | HP Elite
  iPad               |        3 |  700.00 | iPad
  Kindle Fire        |        3 |  150.00 | iPad
  Samsung Galaxy Tab |        3 |  200.00 | iPad
  • ARRAY_AGG()

ARRAY_AGG()函数是一个聚合函数,它接受一组值并返回一个数组,其中将输入集中的每个值分配给该数组的元素。

用法

 ARRAY_AGG(expression [ORDER BY [sort_expression {ASC | DESC}], [...])

ORDER BY子句是自愿性子句。它指定集合中要处理的行的顺序,从而确定结果数组中元素的顺序。它通常与GROUP BY子句一起使用。

create table film (id int,pname  string,fname string);
insert into film values (1,'战狼二','wujing');
insert into film values (1,'断背山','lian');
insert into film values (3,'西游记','wuchengen');;
insert into film values (3,'水浒传','shinaian');
insert into film values (2,'亮剑','liyunlong');
insert into film values (2,'五号特工组','wanglikun');
SELECT
    id,
    ARRAY_AGG (pname || ' ' || fname) actors
FROM
    film 
group by id;
 id  |                actors
-----+----------------------------------------
   1 | {战狼二 wujing,断背山 lian}
   3 | {西游记 wuchengen,水浒传 shinaian}
   2 | {亮剑 liyunlong,五号特工组 wanglikun}
  • LEAD()

在hubble中,LEAD()函数用于以特定的物理偏移量访问当前行之后的行。LEAD()函数提供对以指定物理偏移量访问当前行之后的行的访问。这意味着从当前行开始,LEAD()函数可以访问下一行,下一行之后的行等等的数据。 LEAD()函数对于将当前行的值与当前行之后的行的值进行比较非常有用。

语法:

LEAD(expression [, offset [, default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

语法分析:

  • 这个表达根据与当前行的指定偏移量针对下一行进行评估。的表达可以是必须计算为单个值的列,表达式,子查询。
  • 偏移量是一个正整数,它指定从当前行转发访问的行数。偏移量可以是表达式,子查询或列。偏移量默认为1。
  • PARTITION BY子句将行划分为应用了LEAD()函数的分区。默认情况下,如果省略PARTITION BY子句,则整个结果集是单个分区。
  • 这个排序子句指定每个分区中行的排序顺序LEAD()函数已应用。

示例数据准备

CREATE TABLE sales(
    year INT CHECK(year > 0),
    group_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(year, group_id)
);

INSERT INTO 
    sales(year, group_id, amount) 
VALUES
    (2018, 1, 1474),
    (2018, 2, 1787),
    (2018, 3, 1760),
    (2019, 1, 1915),
    (2019, 2, 1911),
    (2019, 3, 1118),
    (2020, 1, 1646),
    (2020, 2, 1975),
    (2020, 3, 1516);

以下查询使用LEAD()函数返回当年和下一年的销售额:

WITH cte AS (
    SELECT 
        year, 
        SUM(amount) amount
    FROM sales
    GROUP BY year
    ORDER BY year
) 
SELECT
    year, 
    amount,
    LEAD(amount, 1) OVER (
        ORDER BY year
    ) next_year_sales
FROM
    cte;
  year | amount  | next_year_sales
-------+---------+------------------
  2018 | 5021.00 |         4944.00
  2019 | 4944.00 |         5137.00
  2020 | 5137.00 |         NULL

以下语句使用LEAD()函数将每个产品组的当年销售额与下一年的销售额进行比较

SELECT
    year, 
    amount,
    group_id,
    LEAD(amount, 1) OVER (
        PARTITION BY group_id
        ORDER BY year
    ) next_year_sales
FROM
    sales;
  year | amount  | group_id | next_year_sales
-------+---------+----------+------------------
  2018 | 1474.00 |        1 |         1915.00
  2019 | 1915.00 |        1 |         1646.00
  2020 | 1646.00 |        1 |         NULL
  2018 | 1787.00 |        2 |         1911.00
  2019 | 1911.00 |        2 |         1975.00
  2020 | 1975.00 |        2 |         NULL
  2018 | 1760.00 |        3 |         1118.00
  2019 | 1118.00 |        3 |         1516.00
  2020 | 1516.00 |        3 |         NULL
  • LAG()

在hubble中,LAG()函数用于访问一行那在当前行之前以特定的物理偏移量出现。LAG()函数提供对在指定物理偏移量的当前行之前的行的访问。换句话说,从当前行开始,LAG()函数可以访问前一行或前一行之前的行的数据,依此类推。 LAG()函数对于比较当前行和上一行的值非常有用。

语法

LAG(expression [, offset [, default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

语法分析

  • 这个表达将根据当前行之前指定偏移量的行进行评估。它可以是列,表达式或子查询。
  • 这个抵消是一个正整数,指定行数那 来在要从中访问数据的当前行之前。的抵消可以是表达式,子查询或列。默认为1。
  • 这个LAG()函数将返回default_value抵消超出了分区的范围。
  • PARTITION BY子句将行划分为应用了LAG()函数的分区。默认情况下,如果用户忽略PARTITION BY子句,该函数会将整个结果集视为一个分区。
  • 这个排序子句指定每个分区中行的顺序LAG()函数已应用。

示例数据准备

CREATE TABLE sales(
    year INT CHECK(year > 0),
    group_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(year, group_id)
);

INSERT INTO 
    sales(year, group_id, amount) 
VALUES
    (2018, 1, 1474),
    (2018, 2, 1787),
    (2018, 3, 1760),
    (2019, 1, 1915),
    (2019, 2, 1911),
    (2019, 3, 1118),
    (2020, 1, 1646),
    (2020, 2, 1975),
    (2020, 3, 1516);

LAG()函数返回当年和上一年的销售额:

WITH cte AS (
    SELECT 
        year, 
        SUM(amount) amount
    FROM sales
    GROUP BY year
    ORDER BY year
) 
SELECT
    year, 
    amount,
    LAG(amount, 1) OVER (
        ORDER BY year
    ) previous_year_sales
FROM
    cte;
  year | amount  | previous_year_sales
-------+---------+----------------------
  2018 | 5021.00 |             NULL
  2019 | 4944.00 |             5021.00
  2020 | 5137.00 |             4944.00

LAG()函数比较每个产品组的当年销售额与上一年的销售额:

SELECT
    year, 
    amount,
    group_id,
    LAG(amount, 1) OVER (
        PARTITION BY group_id
        ORDER BY year
    ) previous_year_sales
FROM
    sales;
  year | amount  | group_id | previous_year_sales
-------+---------+----------+----------------------
  2018 | 1474.00 |        1 |             NULL
  2019 | 1915.00 |        1 |             1474.00
  2020 | 1646.00 |        1 |             1915.00
  2018 | 1787.00 |        2 |             NULL
  2019 | 1911.00 |        2 |             1787.00
  2020 | 1975.00 |        2 |             1911.00
  2018 | 1760.00 |        3 |             NULL
  2019 | 1118.00 |        3 |             1760.00
  2020 | 1516.00 |        3 |             1118.00
  • NTH_VALUE()

NTH_VALUE()函数用于从获取值在结果集中的行。NTH_VALUE()函数从结果集的有序分区中返回一个值的第N行。

语法

NTH_VALUE(expression, offset) 
OVER (
    [PARTITION BY partition_expression]
    [ ORDER BY sort_expression [ASC | DESC]
    frame_clause ]
)

分析语法:

  • 这个表达是目标列或表达式NTH_VALUE()函数运行。
  • 这个分区依据子句将结果集的行分配到分区中,NTH_VALUE()函数适用。
  • 这个排序子句对应用该函数的每个分区中的行进行排序。
  • 这个frame_clause定义当前分区的子集或框架。

数据准备

CREATE TABLE products (
    product_name VARCHAR (255) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL
);

INSERT INTO products (product_name, group_id, price)
VALUES
    ('Microsoft Lumia', 1, 200),
    ('HTC One', 1, 400),
    ('Nexus', 1, 500),
    ('iPhone', 1, 900),
    ('HP Elite', 2, 1200),
    ('Lenovo Thinkpad', 2, 700),
    ('Sony VAIO', 2, 700),
    ('Dell Vostro', 2, 800),
    ('iPad', 3, 700),
    ('Kindle Fire', 3, 150),
    ('Samsung Galaxy Tab', 3, 200);

NTH_VALUE()函数返回每个产品组中价格第3贵的产品

SELECT 
    product_name,
    price,
    NTH_VALUE(product_name, 3) 
    OVER(
        ORDER BY price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    )
FROM 
    products;
     product_name    |  price  |  nth_value
---------------------+---------+--------------
  Microsoft Lumia    |  200.00 | Dell Vostro
  HTC One            |  400.00 | Dell Vostro
  Nexus              |  500.00 | Dell Vostro
  iPhone             |  900.00 | Dell Vostro
  HP Elite           | 1200.00 | Dell Vostro
  Lenovo Thinkpad    |  700.00 | Dell Vostro
  Sony VAIO          |  700.00 | Dell Vostro
  Dell Vostro        |  800.00 | Dell Vostro
  iPad               |  700.00 | Dell Vostro
  Kindle Fire        |  150.00 | Dell Vostro
  Samsung Galaxy Tab |  200.00 | Dell Vostro

NTH_VALUE()函数返回每个产品组中价格最贵的产品:

SELECT 
    product_name,
    price,
    group_id,
    NTH_VALUE(product_name, 1) 
    OVER(
        PARTITION BY group_id
        ORDER BY price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    )
FROM 
    products;
     product_name    |  price  | group_id | nth_value
---------------------+---------+----------+------------
  Microsoft Lumia    |  200.00 |        1 | iPhone
  HTC One            |  400.00 |        1 | iPhone
  Nexus              |  500.00 |        1 | iPhone
  iPhone             |  900.00 |        1 | iPhone
  HP Elite           | 1200.00 |        2 | HP Elite
  Lenovo Thinkpad    |  700.00 |        2 | HP Elite
  Sony VAIO          |  700.00 |        2 | HP Elite
  Dell Vostro        |  800.00 |        2 | HP Elite
  iPad               |  700.00 |        3 | iPad
  Kindle Fire        |  150.00 |        3 | iPad
  Samsung Galaxy Tab |  200.00 |        3 | iPad
  • AVG()

hubble提供了AVG()函数来计算集合的平均值。 AVG()函数是hubble中最常用的聚合函数之一。 AVG()函数使用户能够计算数字列的平均值。

语法

AVG(column)

它可以与SELECTHAVING子句一起使用。

查询员工平均支出

select avg(sal) from emp;
           avg
-------------------------
  4655.8677777777777778

按照员工部门计算平均支出

select deptno,avg(sal) from emp group by deptno;
  deptno |          avg
---------+------------------------
      10 | 7373.4033333333333333
      20 |              3490.455
      40 |               5381.50
      30 | 3010.2916666666666667
      50 |               1000.00

按照员工部门计算平均支出,且平均支出大于5000

select deptno,avg(sal) from emp group by deptno having(avg(sal))>5000;
  deptno |          avg
---------+------------------------
      10 | 7373.4033333333333333
      40 |               5381.50
  • COUNT()

COUNT()函数是一个聚合函数,使用户能够获取与查询的特定要求匹配的行数。根据用户要求,COUNT()函数可以具有以下语法:

用法1: COUNT(*)

All rows including NULL and Duplicates

用法2: COUNT(column)

All rows except NULL.

用法3: COUNT(DISTINCT column)

All rows without NULL and Duplicates

COUNT()函数与SELECT语句一起使用

查询员工的数量

select count(*) from emp;
  count
---------
     27

查询员工部门的数量

select count(DISTINCT deptno) from emp;
  count
---------
     5
  • SUM()

提供了一个SUM()函数,该函数用于获取数字列的值的加法

SUM(column)

使用上述函数时,请牢记以下几点:

  • 它忽略所有NULL值。
  • 如果与DISTINCT运算符一起用作SUM(DISTINCT),它将跳过重复的值。
  • SUM()函数与SELECT子句一起使用将返回NULL而不是零。

使用SUM()函数和GROUP BY子句计算每个部门支付的量

select deptno,sum(sal) from emp group by deptno ;
  deptno |   sum
---------+-----------
      10 | 66360.63
      20 | 34904.55
      40 |  5381.50
      30 | 18061.75
      50 |  1000.00

计算公司对员工总支出量

select sum(sal) from emp;
     sum
-------------
  125708.43
  • MAX()

MAX()函数是一个聚合函数,它返回一组值中的最大值。

语法

MAX(expression);

MAX()函数可以与SELECTWHEREHAVING子句一起使用。

查询支出最多的员工工资

select max(sal) from emp;
    max
------------
  18000.00

查询每个部门支出最多的员工工资

select deptno,max(sal) from emp group by deptno;
  deptno |   max
---------+-----------
      10 | 18000.00
      20 |  5381.50
      40 |  5381.50
      30 |  4801.79
      50 |  1000.00
  • MIN()

MIN()函数是一个聚合函数,它返回一组值中的最小值。

MIN(expression);

MIN()函数可以与SELECTWHEREHAVING子句一起使用。

查询支出最少的员工工资

select min(sal) from emp;
    min
------------
   1000.00

查询每个部门支出最少的员工工资

select deptno,min(sal) from emp group by deptno;
 deptno  |   min
---------+----------
      10 | 2134.00
      20 | 1000.00
      40 | 5381.50
      30 | 2165.00
      50 | 1000.00

JSON函数

函数说明返回值类型
array_to_json(array: anyelement[])将数组返回为JSON或JSONB。json
json_array_length(json: jsonb) → int返回最外面的JSON或JSONB数组中的元素数。int
json_build_array(anyelement...) 从可变参数列表中构建一个可能是异构类型的JSON或JSONB数组。json
json_build_object(anyelement...)从可变参数列表中构建JSON对象。json
json_extract_path(jsonb, string...) 返回可变参数所指向的JSON值。json
json_object(keys: string[], values: string[]) 这种形式的json_object从两个单独的数组中成对地获取键和值。在所有其他方面,它与单参数形式相同。json
json_object(texts: string[]) 从文本数组构建JSON或JSONB对象。数组必须具有一维且成员数为偶数,在这种情况下,它们被视为交替的键/值对。json
json_remove_path(val: jsonb, path: string[]) 从JSON对象中删除指定的路径。json
json_set(val: jsonb, path: string[], to: jsonb) 返回可变参数所指向的JSON值。json
json_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) 返回可变参数所指向的JSON值。如果create_missing为false,则不会将新键插入对象,也不会在值之前或之后添加值。json
json_strip_nulls(from_json: jsonb) 返回from_json,其中所有具有空值的对象字段都被省略。其他空值保持不变。json
json_typeof(val: jsonb) 以文字字串传回最外层JSON值的类型。string
jsonb_array_length(json: jsonb) 返回最外面的JSON或JSONB数组中的元素数。int
jsonb_build_array(anyelement...) 从可变参数列表中构建一个可能是异构类型的JSON或JSONB数组。int
jsonb_build_object(anyelement...) 从可变参数列表中构建JSON对象。json
jsonb_extract_path(jsonb, string...) 返回可变参数所指向的JSON值。json
jsonb_insert(target: jsonb, path: string[], new_val: jsonb)返回可变参数所指向的JSON值。new_val将在路径目标之前插入。jsonb
jsonb_insert(target: jsonb, path: string[], new_val: jsonb, insert_after: bool) 返回可变参数所指向的JSON值。如果insert_after为true(默认为false),new_val则将在路径目标之后插入。jsonb
jsonb_object(keys: string[], values: string[])这种形式的json_object从两个单独的数组中成对地获取键和值。在所有其他方面,它与单参数形式相同。jsonb
jsonb_object(texts: string[])从文本数组构建JSON或JSONB对象。数组必须具有一维且成员数为偶数,在这种情况下,它们被视为交替的键/值对。jsonb
jsonb_pretty(val: jsonb) 以缩进和换行符的形式返回给定的JSON值。string
jsonb_set(val: jsonb, path: string[], to: jsonb) 返回可变参数所指向的JSON值。jsonb
jsonb_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) 返回可变参数所指向的JSON值。如果create_missing为false,则不会将新键插入对象,也不会在值之前或之后添加值。jsonb
jsonb_strip_nulls(from_json: jsonb) 返回from_json,其中所有具有空值的对象字段都被省略。其他空值保持不变。jsonb
jsonb_typeof(val: jsonb)以文字字串传回最外层JSON值的类型。string
to_json(val: anyelement) 以JSON或JSONB的形式返回值。json
to_jsonb(val: anyelement)以JSON或JSONB的形式返回值。jsonb
  • to_json
select to_json('json type'::text);
    to_json
---------------
  "json type"
  • to_jsonb
select to_jsonb('json type'::text);
    to_jsonb
---------------
  "json type"
  • json_array_length
SELECT json_array_length('[1,2,3,4]');
  json_array_length
---------------------
                  4
  • json_build_array
 select json_build_array(1,2,'3',4,5);
  json_build_array
---------------------
  [1, 2, "3", 4, 5]
  • json_build_object
select json_build_object('1','2','3','4')
   json_build_object
------------------------
  {"1": "2", "3": "4"}
  • jsonb_pretty

数据准备

CREATE TABLE reports (rep_id int primary key, data json);


INSERT INTO reports (rep_id, data)
VALUES
(1, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barB.png", "pos": "top"}], "background":"background.png"}')
, (2, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barC.png", "pos": "top"}], "background":"bacakground.png"}')
, (3, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}')
, (4, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 3, "src":"barB.png", "pos": "top"}], "background":"backgroundA.png"}')
;
select jsonb_pretty( '{"name": "Alice", "agent": {"bot": true} }'::jsonb );
     jsonb_pretty
-----------------------
  {
      "agent": {
          "bot": true
      },
      "name": "Alice"
  }
  • json_object_keys

返回最外层的json对象中的键的集合

select * from json_object_keys('{"b":"1","a":"2"}');
  json_object_keys
--------------------
  a
  b
  • jsonb_set

json值的更新,jsonb_set函数,格式:jsonb_set(target jsonb,path text[],new_value jsonb[, create_missing boolean])target指源jsonb数据,path指路径,new_value指更新后的键值,create_missing值为true表示键不存在则添加,为false表示如果键不存在则不添加。

select jsonb_set('{"name":"bob","age":"27"}'::jsonb,'{age}','"28"'::jsonb,false); 
           jsonb_set
--------------------------------
  {"age": "28", "name": "bob"}
  • json_each

扩展最外层的json对象成为一组键值结果集

select * from json_each('{"b":"1","a":"2"}'); 
  key | value
------+--------
  a   | "2"
  b   | "1"
  • json_each_text以文本返回结果
select * from json_each_text('{"b":"1","a":"2"}');
  key | value
------+--------
  a   | 2
  b   | 1
  • json_object_keys

返回最外层的json对象中的键的集合

select * from json_object_keys('{"b":"1","a":"2"}');
  json_object_keys
--------------------
  a
  b
  • json_typeof

以文字字串传回最外层JSON值的类型

select * from json_typeof('{"b":"1","a":"2"}');
  json_typeof
---------------
  object
  • json键值的删除用-
select '{"b":"1","a":"2"}'::json - 'a';
   column
--------------
  {"b": "1"}
  • 删除嵌套json数据
select '["a","b","c"]'::jsonb - 0; 
  column  
------------
 ["b", "c"]
select '["a","b","c"]'::jsonb - 1; 
  column  
------------
 ["a", "c"]
  • json_strip_nulls
select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]');
     json_strip_nulls
---------------------------
  [{"f1": 1}, 2, null, 3]