语法 | 相当于 | 描述 |
---|---|---|
AT TIME ZONE | timezone() | 时区 |
CURRENT_CATALOG | current_catalog() | 现在使用的catalog |
COLLATION FOR | pg_collation_for() | 排序 |
CURRENT_DATE | current_date() | 现在的日期(年,月,日) |
CURRENT_ROLE | current_user() | 现在的用户 |
CURRENT_SCHEMA | current_schema() | 现在使用的schema |
CURRENT_TIMESTAMP | current_timestamp() | 现在的时间戳 |
CURRENT_USER | current_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_USER | current_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>) | |
USER | current_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 ... )
评估子查询,然后返回TRUE
或FALSE
取决于子查询是否返回任何行(用于EXISTS
)或不返回任何行(用于NOT EXISTS
)。任何选择查询 都可以用作子查询。
语法:
NULLIF ( <expr1>, <expr2> )
相当于:IF ( <expr1> = <expr2>, NULL, <expr1> )
语法:
IFNULL ( <expr1>, <expr2> )
COALESCE ( <expr1> [, <expr2> [, <expr3> ] ...] )
COALESCE
首先计算第一个表达式。如果其值不是 NULL
,则直接返回其值。否则,它将返回COALESCE
对其余表达式应用的结果。如果所有表达式均为NULL
,NULL
则返回。
不评估第一个非空参数右边的参数。
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)
替换数组中的元素y
为z
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) | 计算begin 和end 之间的时间间隔。 | interval |
age(val: timestamptz) | 计算val 与当前时间之间的间隔。 | interval |
clock_timestamp() | 返回一个集群节点上的当前系统时间。 | timestamp |
current_date() | 返回当前事务的日期。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。 | date |
current_timestamp() | 返回当前事务的时间。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。 | date |
current_timestamp() | 返回当前事务的时间。该值基于事务开始时选择的时间戳记,并且在整个事务中保持不变。此时间戳与并发事务的提交顺序没有关系。 | timestamp |
date_trunc(element: string, input: time) → interval | input 精确到截断element 。将所有不重要的字段设置element 为零。兼容元素:时,分,秒,毫秒,微秒。 | interval |
date_trunc(element: string, input: timestamp) → timestamp | input 精确到截断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)
计算end
和begin
之间的时间间隔,结果体现为前值减去后值
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
函数 | 说明 | 返回值类型 |
---|---|---|
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
生成唯一键,保证全局唯一,可用作主键。但由于集群性质,不能完全保证排序。
函数 | 返回类型 | 参数类型 | 说明 |
---|---|---|---|
abs | float | float | 计算绝对值 |
abs | decimal | decimal | 计算绝对值 |
abs | int | int | 计算绝对值 |
acos | float | float | 计算反余弦 |
asin | float | float | 计算反正弦 |
atan | float | float | 计算反正切 |
atan2 | float | float,float | 计算入参的相除的反正切 |
cbrt | float | float | 计算立方根 |
ceil | decimal | decimal | 计算最小整数 |
ceil | float | float | 计算最小整数 |
cos | float | float | 计算余弦值 |
cot | float | float | 计算余切 |
crc32c | int | bytes | compute CRC(Castagnoli polynomial.) |
crc32c | int | string | compute CRC(Castagnoli polynomial.) |
crc32ieee | int | bytes | compute CRC(IEEE polynomial) |
crc32ieee | int | string | compute CRC(IEEE polynomial) |
degrees | float | float | 将弧度值转换为度值 |
div | decimal | decimal,decimal | 计算x/y的整数商 |
div | float | float,float | 计算x/y的整数商 |
div | int | int,int | 计算x/y的整数商 |
exp | float | float | e的幂次方 |
floor | decimal | decimal | 计算不大于入参的最大整数 |
floor | float | float | 计算不大于入参的最大整数 |
fnv32 | int | bytes | 计算32位的 FNV-1 hash值 |
fnv32 | int | string | 计算32位的 FNV-1 hash值 |
fnv32a | int | bytes | 计算32位的 FNV-1a hash值 |
fnv32a | int | string | 计算32位的 FNV-1a hash值 |
fnv64 | int | bytes | 计算64位的 FNV-1 hash值 |
fnv64 | int | string | 计算64位的 FNV-1 hash值 |
fnv64a | int | bytes | 计算64位的 FNV-1a hash值 |
fnv64a | int | string | 计算64位的 FNV-1a hash值 |
isnan | bool | decimal | 判断是否为NaN |
isnan | bool | float | 判断是否为NaN |
ln | decimal | decimal | 计算自然对数 |
ln | float | float | 计算自然对数 |
log | decimal | decimal | 计算以10为底的对数 |
log | float | float | 计算以10为底的对数 |
mod | decimal | decimal,decimal | 计算余数 |
mod | float | float,float | 计算余数 |
mod | int | int,int | 计算余数 |
pi | float | 3.141592653589793 | |
pow | decimal | decimal,decimal | x ^y |
pow | float | float,float | x ^y |
pow | int | int,int | x ^y |
radians | float | float | 度值转换为弧度值 |
random() | float | float | 0与1之间的随机数 |
round | decimal | decimal,int | 四舍五入到给定的小数位数 |
round | float | float,int | 四舍五入到给定的小数位数 |
round | decimal | decimal | 四舍五入取整数 |
round | float | float | 四舍五入取整数 |
sign | decimal | decimal | 符号函数 |
sign | float | float | 符号函数 |
sign | int | int | 符号函数 |
sin | float | float | 计算正弦 |
sqrt | decimal | decimal | 计算平方根 |
sqrt | float | float | 计算平方根 |
tan | float | float | 计算正切 |
trunc | decimal | decimal | 截断小数 |
trunc | float | float | 截断小数 |
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
a
的b
次方
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)
函数 | 返回类型 | 参数 | 说明 |
---|---|---|---|
ascii | int | string | 返回第一个字符的asc编码 |
bit_length | int | bytes | 计算bits数 |
bit_length | int | string | 计算bits数 |
btrim | string | string,string | 删除匹配的字符 |
btrim | string | string | 去除空格 |
char_length | int | bytes | 计算字节长度 |
char_length | int | string | 计算字符长度 |
chr | string | int | 返回ascii值 |
concat | string | string... | 拼接字符串 |
concat_ws | string | string... | 使用第一个参数拼接字符串 |
convert_from | string | bytes,string | 将字节转化为执行编码的字符串(支持UTF8与LATIN1) |
convert_to | bytes | string,string | 将字符串转化为指定编码的字节(支持UTF8与LATIN1) |
decode | bytes | string,string | 换化为字节码,支持hex,escape,base64 |
encode | string | bytes,string | 换化为字符串,支持hex,escape,base64 |
from_ip | string | bytes | 将IP的字节字符串表示形式转换为其字符串表示形式 |
from_uuid | string | bytes | 将UUID的字节字符串表示形式转换为其字符串表示形式。 |
initcap | string | string | 第一个字母大写。 |
left | bytes | bytes,int | 获取头几位字节 |
left | string | string,int | 获取头几位字符串 |
length | int | bytes | 计算字节码的长度 |
length | int | string | 计算字符串的长度 |
lower | string | string | 将大写转化为小写 |
lpad | string | string,int | 通过在字符串的左侧添加' '来增加长度。如果字符串比长度长,就被截断。 |
lpad | string | string,int ,string | 通过在字符串的左侧添加传入参数来增加长度。如果字符串比长度长,就被截断。 |
ltrim | string | string,string | 递归从输入的开始(左侧)删除包含的所有字符 |
ltrim | string | string | 去除左边的空格 |
md5 | string | bytes | 计算MD5值 |
md5 | string | string | 计算MD5值 |
octet_length | int | bytes | 计算字节数 |
octet_length | int | string | 计算字节数 |
overlay | string | string,string,int | 从指定下标开始替换字符 |
overlay | string | string,string,int,int | 从指定下标开始替换字符 |
quote_ident | string | string | 返回val作为SQL语句中的标识符。 |
quote_literal | string | string | 返回val作为SQL语句中的字符串文字适当引用。 |
quote_nullable | string | string | 将参数强制转换为字符串,然后将其作为文字引用。 |
regexp_extract | string | string,string | 返回输入中正则表达式的第一个匹配项。 |
regexp_replace | string | string,string,string | 将输入中的正则表达式regex 的匹配项替换为正则表达式替换。 |
repeat | string | string,int | 返回输入指定重复次数的参数 |
replace | string | string,string,string | 用replace 替换输入的第三个参数替换匹配的第二个参数 |
reverse | string | string | 反转字符串字符的顺序。 |
right | bytes | bytes,int | 获取从右开始头几位字节 |
right | string | string,int | 获取从右开始头几位字符 |
rpad | string | string,int | 通过在字符串的右侧添加' '来增加长度。如果字符串比长度长,就被截断。 |
rpad | string | string,int,string | 通过在字符串的右侧添加传入参数来增加长度。如果字符串比长度长,就被截断。 |
rtrim | string | string,string | 递归从输入的开始(右侧)删除包含的所有字符 |
rtrim | string | string | 去除右边的空格 |
sha1 | string | bytes | 计算SHA1值 |
sha1 | string | string | 计算SHA1值 |
sha256 | string | bytes | 计算SHA256值 |
sha256 | string | string | 计算SHA256值 |
sha512 | string | bytes | 计算SHA512值 |
sha512 | string | string | 计算SHA512值 |
split_part | string | string,string,int | 分割分隔符上的输入并返回第几位 |
strpos | int | string,string | 计算输入中字符串开始的位置。 |
substr | string | string,string | 返回与正则表达式regex 匹配的输入子字符串。 |
substr | string | string,string,string | 返回与正则表达式regex 匹配的输入子字符串,使用第三个参数作为转义字符 |
substr | string | string,int | 从指定下标开始截取字符串 |
substr | string | string,int,int | 从指定下标开始截取字符串,并指定截取字符串长度 |
to_english | string | int | 此函数使用英语基数来声明其参数的值。 |
to_hex | string | bytes | 将val转换为其十六进制表示形式。 |
to_hex | bytes | string | 将val转换为其十六进制表示形式。 |
to_uuid | bytes | string | 将UUID的字符串表示形式转换为其字节字符串表示形式。 |
translate | string | string,string,string | 指定替代字符串内容 |
upper | string | string | 将字符内容转为大写 |
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()
使用等号=
拼接字段username
,address
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 )。双方n 并default 相对于当前行评估。 | bool |
lag(val: date, n: int) | 返回在其分区中当前行之前的行val 处求值n ;如果没有这样的行,则返回null。n 针对当前行进行评估。 | date |
lag(val: date, n: int, default: date) | 返回在其分区中当前行之前的行val 处求值n ;如果没有,则返回行default (必须与类型相同val )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | 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 )。双方n 并default 相对于当前行评估。 | varbit |
nth_value(val: bool, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | bool |
nth_value(val: bytes, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | bytes |
nth_value(val: date, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | date |
nth_value(val: decimal, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | decimal |
nth_value(val: float, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | float |
nth_value(val: inet, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | inet |
nth_value(val: int, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | int |
nth_value(val: interval, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | interval |
nth_value(val: string, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | string |
nth_value(val: time, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | time |
nth_value(val: timestamp, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | timestamp |
nth_value(val: timestamptz, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | timestamptz |
nth_value(val: uuid, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | uuid |
nth_value(val: jsonb, n: int) | 返回val 在n 窗框第th行(从1开始)处求值的值;如果没有这样的行,则返回null。 | jsonb |
nth_value(val: varbit, n: int) | 返回val 在n 窗框第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], ...]
)
语法分析语法:
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], ...
)
语法分析:
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], ...
)
语法分析
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)
它可以与SELECT
和HAVING
子句一起使用。
查询员工平均支出
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()
函数可以与SELECT
,WHERE
和HAVING
子句一起使用。
查询支出最多的员工工资
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()
函数可以与SELECT
,WHERE
和HAVING
子句一起使用。
查询支出最少的员工工资
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
函数 | 说明 | 返回值类型 |
---|---|---|
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]