Presto 与 Hive 语法学习
文章目录
1. Presto语法
参考文档:https://prestodb.io/docs/current/language/types.html
1.1 数据类型
Presto 有一组内置的数据类型,如下所述,插件可以提供其他类型。
布尔值
BOOLEAN
此类型捕获布尔值
true
和false
。
整数
TINYINT
一个 8 位有符号二进制补码整数,最小值为
-2^7
,最大值为2^7 - 1
SMALLINT
一个 16 位有符号二进制补码整数,最小值为
-2^15
,最大值为2^15 - 1
INTEGER
一个 32 位有符号二进制补码整数,最小值为
-2^31
,最大值为2^31 - 1
。名称为 INT 用的也是此类型。
BIGINT
一个 64 位有符号二进制补码整数,最小值为
-2^63
,最大值为2^63 - 1
浮点
REAL
real 是一个32位的不精确的、可变精度,实现了二进制浮点运算的IEEE标准754。
DOUBLE
double 是一个64位的不精确的、可变精度,实现了二进制浮点运算的IEEE标准754。
固定精度
DECIMAL
一个固定精度的十进制数。支持高达 38 位的精度,但性能最好高达 18 位。
十进制类型有两个文字参数:
- 精度:总位数
- scale:小数部分的位数。比例是可选的,默认为0。
示例类型定义:
DECIMAL(10.3),DECIMAL(20)
示例文字:
DECIMAL '10.3' DECIMAL '1234567890' 1.1
出于兼容性原因,没有显示类型说明符(例如
1.2
)的十进制文字在默认情况下被视为DOUBLE
类型的值,直到版本 0.198。在0.198 之后,它们被解析为 DECIMAL。
- 系统范围的属性:
parse-decimal-literals-as-double
- 会话范围的属性:
parse_decimal_literals_as_double
字符串
VARCHAR
具有可选最大长度的可变长度字符数据。
示例类型定义:
varchar
,varchar(20)
CHAR
固定长度的字符数据。未指定长度的
CHAR
类型的默认长度为1。CHAR(x) 值始终包含 x 个字符。例如,强制转换dog
为CHAR(7)
,会添加4个隐式尾随空格。前导和尾随空格包含在 CHAR值的比较中。结果,具有不同长度(CHAR(x)
andCHAR(y)
wherex != y
) 的两个字符值永远不会相等。示例类型定义:
char
,char(20)
VARBINARY
可变长度二进制数据。
注:尚不支持带长度的二进制字符串:
varbinary(n)
日期和时间
DATE
日历日期(年、月、日)
例子:
DATE '2002-03-21'
TIME
没有时区的一天中的时间(小时、分钟、秒、毫秒)。此类型的值在会话时区中解析和呈现。
例子:
TIME '01:02:03.456'
TIME WITH TIME ZONE
带时区的时间(小时、分钟、秒、毫秒)。此类型的值使用值中的时区呈现。
例子:
TIME '01:02:03.456 America/Los_Angeles'
TIMESTAMP
即时时间,包括没有时区的日期和时间。此类型的值在会话时区中解析和呈现。
例子:
TIMESTAMP '2001-08-22 03:04:05.321'
TIMESTAMP WITH TIME ZONE
即时时间,包括带有时区的日期和时间。此类型的值使用值中的时区呈现。
例子:
TIMESTAMP '2001-08-22 03:04:05.321 America/Los_Angeles'
INTERVAL YEAR TO MONTH
年和月的跨度。
例子:
INTERVAL '3' MONTH
INTERVAL DAY TO SECOND
天、小时、分钟、秒和毫秒的跨度。
例子:
INTERVAL '2' DAY
结构
ARRAY
给定组建类型的数组。
例子:
ARRAY[1,2,3]
MAP
给定组件类型之间的映射。
例子:
MAP(ARRAY['foo', 'bar'], ARRAY[1, 2])
ROW
由命名字段组成的结构。字段可以是任何SQL类型,并使用字段引用运算符访问。
例子:
CAST(ROW(1, 2.0) AS ROW(x BIGINT, y DOUBLE))
网络地址
IPADDRESS
可以表示IPv4 或 IPv6 地址的 IP 地址。
在内部,该类型是纯 IPv6 地址。使用IPv4 映射的 IPv6 地址范围(RFC 4291#section-2.5.5.2)。创建 时
IPADDRESS
,IPv4 地址将映射到该范围。格式化 时
IPADDRESS
,映射范围内的任何地址都将被格式化为IPv4地址。其他地址将使用中定义的规范格式化为IPv6RFC 5952。例子:
IPADDRESS '10.0.0.1'``IPADDRESS '2001:db8::1'
UUID
UUID
此类型表示一个 UUID (通用唯一标识符),也称为 GUID(全局唯一标识符)。使用中定义的格式RFC 4122。
例子:
UUID '12151fd2-7586-11e9-8f9e-2a86e4085a59'
IPPREFIX
可以表示 IPv4 或 IPv6 地址的 IP 路由前缀。
在内部,地址是纯 IPv6 地址。使用IPv4 映射的 IPv6 地址范围(RFC 4291#section-2.5.5.2)。创建 时
IPPREFIX
,IPv4 地址将映射到该范围。此外,地址将减少为网络的第一个地址。
IPPREFIX
值将以 CIDR 表示法格式化,写为 IP 地址、斜杠 (‘/’) 字符和前缀的位长度。IPv4 映射的 IPv6 地址范围内的任何地址都将被格式化为 IPv4 地址。其他地址将使用中定义的规范格式格式化为 IPv6RFC 5952。例子:,
IPPREFIX '10.0.1.0/24'``IPPREFIX '2001:db8::/48'
HyperLogLog
HyperLogLog
HyperLogLog 草图允许有效计算
approx_distinct()
. 它从稀疏表示开始,当它变得更有效时切换到密集表示。
P4HyperLogLog
HyperLogLog 草图允许有效计算
approx_distinct()
. 它从稀疏表示开始,当它变得更有效时切换到密集表示。
KHyperLogLog
KHyperLogLog
KHyperLogLog 是一个数据草图,可用于紧凑地表示两列的关联。请参阅KHyperLogLog 函数。
1.2 SQL 语句语法
ALTER FUNCTION --改变函数
概要
ALTER FUNCTION qualified_function_name [ ( parameter_type[, ...] ) ]
RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
描述
更改现有函数的定义。
如果指定的函数名称存在多个签名,则必须指定参数类型列表。如果指定的函数名称只存在一个签名,则可以省略参数类型列表。
目前只支持修改 null-call 子句。
示例
更改函数的null-call子句 example.default.tan(double)
:
ALTER FUNCTION prod.default.tan(double)
CALLED ON NULL INPUT
如果 只存在一个函数example.default.tan
,则可以省略参数类型列表:
ALTER FUNCTION prod.default.tan
CALLED ON NULL INPUT
ALTER SCHEMA --改变模式
概要
ALTER SCHEMA name RENAME TO new_name
描述
更改现有描述的定义。
示例
将模式web
重命名为traffic
:
ALTER SCHEMA web RENAME TO traffic
ALTER TABLE --改变表
概要
ALTER TABLE [ IF EXISTS ] name RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name ADD COLUMN [ IF NOT EXISTS ] column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
ALTER TABLE [ IF EXISTS ] name DROP COLUMN column_name
ALTER TABLE [ IF EXISTS ] name RENAME COLUMN [ IF EXISTS ] column_name TO new_column_name
描述
更改现有表的定义。
如果表不存在,可选(在表名之前使用)子句会导致错误被抑制。
IF EXISTS
如果列不存在,可选(在列名之前使用)子句会导致错误被抑制。
IF EXISTS
如果列已经存在,可选子句会导致错误被抑制。
IF NOT EXISTS
示例
将表users
重命名为 people
:
ALTER TABLE users RENAME TO people;
如果表users
存在,将表users
重命名为 people
:
ALTER TABLE IF EXISTS users RENAME TO people;
将列zip
添加到users
表中:
ALTER TABLE users ADD COLUMN zip varchar;
如果表users
存在,且列zip
不存在,则将列zip
添加到users
表中:
ALTER TABLE IF EXISTS users ADD COLUMN IF NOT EXISTS zip varchar;
从表users
中删除列zip
:
ALTER TABLE users DROP COLUMN zip;
如果表users
和列zip
都存在,则从表users
中删除列zip
:
ALTER TABLE IF EXISTS users DROP COLUMN IF EXISTS zip;
将表users
中的列id
重命名为user_id
:
ALTER TABLE users RENAME COLUMN id TO user_id;
如果表users
和列id
都存在,则将表users
中的列id
重命名为user_id
:
ALTER TABLE IF EXISTS users RENAME column IF EXISTS id to user_id;
ANALYZE --分析
概要
ANALYZE table_name [ WITH ( property_name = expression [, ...] ) ]
描述
收集给定表的表和列统计信息。目前,仅针对原始类型收集列统计信息。
可选
WITH
子句可用于提供特定连接器的属性,要列出所有可用属性,请运行以下查询:SELECT * FROM system.metadata.analyze_properties
目前,此语句仅售 Hive 连接器支持。
示例
分析表web
来收集表和列的信息:
ANALYZE web;
分析在目录hive
中和模式为default
的表stores
:
ANALYZE hive.default.stores;
分析Hive 分区表sales
中的分区:'1992-01-01', '1992-01-02'
ANALYZE hive.default.sales WITH (partitions = ARRAY[ARRAY['1992-01-01'], ARRAY['1992-01-02']]);
从 Hive 分区表customers
中分析具有复杂分区键(state
和city
列)的分区:
ANALYZE hive.default.customers WITH (partitions = ARRAY[ARRAY['CA', 'San Francisco'], ARRAY['NY', 'NY']]);
CALL – 调用过程
概要
CALL procedure_name ( [ name => ] expression [, ...] )
描述
调用一个过程。
连接器可以提供过程来执行数据操作或管理任务。例如,系统连接器定义了一个用于终止正在运行的查询的过程。
一些连接器,例如PostgreSQL 连接器,适用于拥有自己存储过程的系统。这些存储过程与此处讨论的连接器定义的过程是分开的,因此不能直接通过
CALL
。
示例
使用位置参数调用过程:
CALL test(123, 'apple');
使用命名参数调用过程:
CALL test(name => 'apple', id => 123);
使用完全限定名称调用过程:
CALL catalog.schema.test();
COMMIT – 提交
概要
COMMIT [ WORK ]
描述
提交当前事务。
示例
COMMIT;
COMMIT WORK;
CREATE FUNCTION – 创建函数
概要
CREATE [ OR REPLACE ] [TEMPORARY] FUNCTION
qualified_function_name (
parameter_name parameter_type
[, ...]
)
RETURNS return_type
[ COMMENT function_description ]
[ LANGUAGE [ SQL | identifier] ]
[ DETERMINISTIC | NOT DETERMINISTIC ]
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
[ RETURN expression | EXTERNAL [ NAME identifier ] ]
描述
创建具有指定定义的新函数。
指定
TEMPORARY
时,创建的函数在当前会话中有效且可见,但不会生成持久条目。每个永久函数都由其限定函数名称和参数类型列表唯一标识。
qualified_function_name
格式必须为catalog.schema.function_name
。每个临时函数都由函数名称唯一标识。该名称不能被限定,或与现有内置函数的名称冲突。
为了创建永久函数,相应的函数命名空间(格式为
catalog.schema
)必须首先由函数命名空间管理器管理。如果存在具有相同签名(具有参数类型列表的函数名称)的函数,则可选的
OR REPLACE
子句会导致查询悄悄地替换现有函数。
return_type
需要匹配例程体表达式的实际类型,而不执行类型强制。可以指定一组例程特征来修饰函数并指定其行为。每种常规特性最多可指定一次。
常规特征 默认值 描述 语言条款 SQL 定义函数的语言。 确定性特征 不确定的 函数是否是确定性的。意味着该函数可能是不确定的。 NOT DETERMINISTIC
调用子句 空输入时调用 null
作为至少一个参数的值提供的函数的行为。
示例
创建一个新函数example.default.tan(double)
:
CREATE FUNCTION example.default.tan(x double)
RETURNS double
DETERMINISTIC
RETURNS NULL ON NULL INPUT
RETURN sin(x) / cos(x)
如果表example.default.tan(double)
不存在,则创建表,添加函数描述并明确列出所有支持的例程特征:
CREATE OR REPLACE FUNCTION example.default.tan(x double)
RETURNS double
COMMENT 'tangent trigonometric function'
LANGUAGE SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
RETURN sin(x) / cos(x)
创建一个新的临时函数square
:
CREATE TEMPORARY FUNCTION square(x int)
RETURNS int
RETURN x * x
CREATE ROLE --创建角色
概要
CREATE ROLE role_name
[ WITH ADMIN ( user | USER user | ROLE role | CURRENT_USER | CURRENT_ROLE ) ]
描述
CREATE ROLE
在当前目录中创建指定角色。可选
WITH ADMIN
子句导致以指定用户作为角色管理员创建角色。角色管理员有权删除或授予角色。如果未指定可选WITH ADMIN
子句,则以当前用户为 admin 创建角色。
示例
创建角色admin
CREATE ROLE admin;
使用管理员bob
创建角色moderator
:
CREATE ROLE moderator WITH ADMIN USER bob;
限制
某些连接器不支持角色管理
CREATE SCHEMA – 创建模式
概要
CREATE SCHEMA [ IF NOT EXISTS ] schema_name
[ WITH ( property_name = expression [, ...] ) ]
描述
创建一个新的空模式。模式是包含表、视图和其他数据库对象的容器。
如果模式已经存在,可选子句
IF NOT EXISTS
会导致错误被抑制。可选
WITH
子句可用于在新创建的模式上设置属性。要列出所有可用的架构属性,请运行以下查询:SELECT * FROM system.metadata.schema_properties
示例
在当前目录中创建一个新模式web
:
CREATE SCHEMA web
在hive
目录中创建一个新模式sales
:
CREATE SCHEMA hive.sales
如果架构traffic
尚不存在,则创建它:
CREATE SCHEMA IF NOT EXISTS traffic
CREATE TABLE – 创建表
概要
CREATE TABLE [ IF NOT EXISTS ]
table_name (
{ column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
| LIKE existing_table_name [ { INCLUDING | EXCLUDING } PROPERTIES ] }
[, ...]
)
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]
描述
使用指定的列创建一个新的空表。使用CREATE TABLE AS创建包含数据的表。
如果表已经存在,可选子句会导致错误被抑制。
IF NOT EXISTS
可选
WITH
子句可用于在新创建的表或单个列上设置属性。要列出所有可用的表属性,请运行以下查询:SELECT * FROM system.metadata.table_properties
要列出所有可用的列属性,请运行以下查询:
SELECT * FROM system.metadata.column_properties
该
LIKE
子句可用于将现有表中的所有列定义包含在新表中。LIKE
可以指定多个子句,这允许从多个表中复制列。如果指定,则所有表属性都将复制到新表中。如果子句指定与复制的属性之一相同的属性名称,则将使用子句中的值。默认行为是. 最多可以为一个表指定该 选项。
INCLUDING PROPERTIES``WITH``WITH``EXCLUDING PROPERTIES``INCLUDING PROPERTIES
示例
创建一个新表orders
:
CREATE TABLE orders (
orderkey bigint,
orderstatus varchar,
totalprice double,
orderdate date
)
WITH (format = 'ORC')
如果表orders
不存在,则创建表,添加表注释和列注释:
CREATE TABLE IF NOT EXISTS orders (
orderkey bigint,
orderstatus varchar,
totalprice double COMMENT 'Price in cents.',
orderdate date
)
COMMENT 'A table to keep track of orders.'
使用orders
开头和结尾的附加列中的列创建表bigger_orders
:
CREATE TABLE bigger_orders (
another_orderkey bigint,
LIKE orders,
another_orderdate date
)
CREATE TABLE AS
概要
CREATE TABLE [ IF NOT EXISTS ] table_name [ ( column_alias, ... ) ]
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]
AS query
[ WITH [ NO ] DATA ]
描述
创建一个包含SELECT查询结果的信标。使用CREATE TABLE创建一个空表。
如果表已经存在,可选子句
IF NOT EXISTS
会导致错误被抑制。可选子句
WITH
可用于在新创建的表上设置属性。要列出所有可用的表属性,请运行以下查询:SELECT * FROM system.metadata.table_properties
示例
使用查询结果和给定的列名创建一个新表orders_column_aliased
:
CREATE TABLE orders_column_aliased (order_date, total_price)
AS
SELECT orderdate, totalprice
FROM orders
汇总orders
来创建一个新表orders_by_date
:
CREATE TABLE orders_by_date
COMMENT 'Summary of orders by date'
WITH (format = 'ORC')
AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate
如果表orders_by_date
不存在,则创建表:
CREATE TABLE IF NOT EXISTS orders_by_date AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate
创建一个与nation
具有相同模式但没有数据的新表empty_nation
:
CREATE TABLE empty_nation AS
SELECT *
FROM nation
WITH NO DATA
CREATE VIEW – 创建视图
概要
CREATE [ OR REPLACE ] VIEW view_name
[ SECURITY { DEFINER | INVOKER } ]
AS query
描述
创建SELECT 查询的新视图。该视图是一个逻辑表,可供将来的查询引用。视图不包含任何数据。相反,每次视图被另一个查询引用时,都会执行视图存储的查询。
如果视图已经存在,可选子句
OR REPLACE
会导致视图被替换,而不是引发错误。
安全
在默认
DEFINER
安全模式下,视图中引用的表是使用视图所有者(视图的创建者或 定义者)而不是执行查询的用户的权限来访问的。这允许提供对基础表的受限访问,可能不允许查询用户直接访问这些表。请注意,该current_user
函数将返回查询用户,而不是视图所有者,因此可用于根据当前访问视图的用户过滤行或以其他方式限制访问。在安全模式下,视图中引用的表是使用查询用户(视图的调用者
INVOKER
)的权限来访问的。在这种模式下创建的视图只是一个存储查询。
示例
在orders
表格上创建一个简单的视图test
:
CREATE VIEW test AS
SELECT orderkey, orderstatus, totalprice / 2 AS half
FROM orders
根据表orders
创建一个汇总的视图orders_by_date
:
CREATE VIEW orders_by_date AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate
创建一个替换现有视图的视图:
CREATE OR REPLACE VIEW test AS
SELECT orderkey, orderstatus, totalprice / 4 AS quarter
FROM orders
DEALLOCATEPREPARE --解除分配
概要
DEALLOCATE PREPARE statement_name
描述
从会话中的准备好的语句列表中删除具有
statement_name
名称的语句。
示例
取消分配一个名为 my_query
的语句:
DEALLOCATE PREPARE my_query;
DELETE – 删除
概要
DELETE FROM table_name [ WHERE condition ]
描述
从表中删除行。如果
WHERE
指定了子句,则仅删除匹配的行。否则,表中的所有行都将被删除。
示例
删除符合条件的数据行:
DELETE FROM lineitem WHERE shipmode = 'AIR';
删除低优先级数据的所有数据项:
DELETE FROM lineitem
WHERE orderkey IN (SELECT orderkey FROM orders WHERE priority = 'LOW');
删除所有数据:
DELETE FROM orders;
1.3 函数和运算符
1. 逻辑运算符
操作 | 描述 | 例子 |
---|---|---|
AND | 如果两个值都为true,则最终为true | a AND b |
OR | 如果有一个值为true,则最终为true | a OR b |
NOT | 如果值为False,则最终为true | NOT a |
NULL 对逻辑运算符的影响
AND
的一侧或两侧表达式是NULL则比较的结果是NULL。如果AND
运算符的至少一侧是False,则计算结果为False.
SELECT CAST(null AS boolean) AND true; -- null
SELECT CAST(null AS boolean) AND false; -- false
SELECT CAST(null AS boolean) AND CAST(null AS boolean); -- null
OR
的一侧或两侧表达式是NULL则比较的结果是NULL。如果OR
运算符的至少一侧是True,则计算结果为True.
SELECT CAST(null AS boolean) OR CAST(null AS boolean); -- null
SELECT CAST(null AS boolean) OR false; -- null
SELECT CAST(null AS boolean) OR true; -- true
如下是AND
和OR
遇到null的判断结果:
a | b | a AND b | a OR b |
---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE | TRUE |
TRUE | NULL | NULL | TRUE |
FALSE | TRUE | FALSE | TRUE |
FALSE | FALSE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL |
NULL | TRUE | NULL | TRUE |
NULL | FALSE | FALSE | NULL |
NULL | NULL | NULL | NULL |
如下为null与not 的判断结果
SELECT NOT CAST(null AS boolean); -- null
a | NOT a |
---|---|
TRUE | FALSE |
FALSE | TRUE |
NULL | NULL |
2. 比较函数和运算符
Operator | 描述 |
---|---|
< | 小于 |
> | 大于 |
<= | 小于等于 |
>= | 大于等于 |
= | 相等 |
<> | 不相等 |
!= | 不相等(非标准但流行的语法) |
范围运算符:BETWEEN
BETWEEN
运算符测试一个值是否在指定范围内,它使用以下语法:value BETWEEN min AND max
SELECT 3 BETWEEN 2 AND 6;
上述语句等效于以下语句:
SELECT 3 >= 2 AND 3 <= 6;
要测试一个值是否不在指定范围内,请使用:NOT BETWEEN
SELECT 3 NOT BETWEEN 2 AND 6;
上述语句等效于以下语句:
SELECT 3 < 2 OR 3 > 6;
BETWEEN
或NOT BETWEEN
语句中如果存在null,则结果也为null
SELECT NULL BETWEEN 2 AND 4; -- null
SELECT 2 BETWEEN NULL AND 6; -- null
BETWEEN
和NOT BETWEEN
运算符也可用于判断字符串参数。
SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true
并不是说,BETWEEN
和NOT BETWEEN
中value,min和max必须是相同的类型。例如,如果您询问:if John is between 2.3 and 35.2.presto会报错。
IS NULL 和 IS NOT NULL
IS NULL
和 IS NOT NULL
运算符测试一个值是否为空(未定义)。这两个运算符都适用于所有数据类型。
使用 null 和 IS NULL
计算结果为true:
select NULL IS NULL; -- true
但任何其他常数都不会:
SELECT 3.0 IS NULL; -- false
IS DISTINCT FROM 和 IS NOT DISTINCT FROM
在 SQL 中,NULL
值表示未知值,因此任何涉及NULL
的比较都会产生NULL
。IS DISTINCT FROM
和 IS NOT DISTINCT FROM
运算符将NULL
视为已知值, 即使存在输入null
,两个运算符也能保证结果为真或假:
SELECT NULL IS DISTINCT FROM NULL; -- false
SELECT NULL IS NOT DISTINCT FROM NULL; -- true
在上述例子中,’ NULL ‘值被认为与’ NULL ‘没有区别。当你比较可能包含’ NULL ‘的值时,使用这些操作符来保证结果是’ TRUE ‘或’ FALSE '。
The following truth table demonstrate the handling of NULL
in IS DISTINCT FROM
and IS NOT DISTINCT FROM
:
a | b | a = b | a <> b | a DISTINCT b | a NOT DISTINCT b |
---|---|---|---|---|---|
1 | 1 | TRUE | FALSE | FALSE | TRUE |
1 | 2 | FALSE | TRUE | TRUE | FALSE |
1 | NULL | NULL | NULL | TRUE | FALSE |
NULL | NULL | NULL | NULL | FALSE | TRUE |
GREATEST–最大 和 LEAST–最小
这些函数不在SQL标准中,而是一个创建的扩展。与Presto中的大多数其他函数一样,如果任何参数为null,它们将返回null。请注意,在某些其他数据库中,例如 PostgreSQL,它们仅在所有参数为 null 时才返回 null。
支持以下类型: DOUBLE
, BIGINT
, VARCHAR
, TIMESTAMP
, , TIMESTAMP WITH TIME ZONE``DATE
greatest(value1, value2, ..., valueN) → [same as input]
返回提供值中的最大值
least(value1, value2, ..., valueN) → [same as input]
返回提供值中的最小值
量化比较谓词:ALL、ANY和SOME
ALL
, ANY
和 SOME
量词 通过以下方式与比较运算符一起使用:
expression operator 量词 ( subquery )
例子:
SELECT 'hello' = ANY (VALUES 'hello', 'world'); -- true
SELECT 21 < ALL (VALUES 19, 20, 21); -- false
SELECT 42 >= SOME (SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43); -- true
以下是一些量词和比较运算符组合的含义:
Expression | 意义 |
---|---|
A = ALL (...) | 评估当A等于所有值时为true |
A <> ALL (...) | 评估当A不匹配任何值时为true |
A < ALL (...) | 评估当A小于最小值时为true |
A = ANY (...) | 评估当A等于任何值时为true。这种形式等价于:A IN (…) |
A <> ANY (...) | 评估当A不匹配一个或多个值时为true |
A < ANY (...) | 评估当A小于最大值时为true |
ANY
和 SOME
有相同的含义,可以互换使用。
LIKE
LIKE 运算符用于匹配字符串中的指定字符模式。模式可以包含常规字符以及通配符。可以使用为 ESCAPE 参数指定的单个字符对通配符进行转义。匹配区分大小写。
语法:
expression LIKE pattern [ ESCAPE 'escape_character' ]
如果 pattern
或 escape_character
为 null则表达式的计算结果为 null。
通配符 | 表示 |
---|---|
% | %表示零个、一个或多个字符 |
_ | _ 代表单个字符 |
示例:
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
WHERE name LIKE '%b%'
--returns 'abc' and 'bcd'
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
WHERE name LIKE '_b%'
--returns 'abc'
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
WHERE name LIKE 'b%'
--returns 'bcd'
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
WHERE name LIKE 'B%'
--returns nothing
SELECT * FROM (VALUES ('a_c'), ('_cd'), ('cde')) AS t (name)
WHERE name LIKE '%#_%' ESCAPE '#'
--returns 'a_c' and '_cd'
SELECT * FROM (VALUES ('a%c'), ('%cd'), ('cde')) AS t (name)
WHERE name LIKE '%#%%' ESCAPE '#'
--returns 'a%c' and '%cd'
3. 条件表达式
CASE
标准SQL CASE
表达式有两种形式。“简单”形式value
从左到右搜索每个表达式,直到找到一个等于expression
:
CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
result
返回匹配的value
。如果未找到匹配项,则返回ELSE
子句(如果存在),否则返回 null。例子:
SELECT a,
CASE a
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'many'
END
“搜索”形式condition
从左到右评估每个布尔值,直到一个为真并返回匹配项result
:
CASE
WHEN condition THEN result
[ WHEN ... ]
[ ELSE result ]
END
如果没有条件为真,则返回ELSE
子句(如果存在),否则返回 null。例子:
SELECT a, b,
CASE
WHEN a = 1 THEN 'aaa'
WHEN b = 2 THEN 'bbb'
ELSE 'ccc'
END
IF
IF 函数实际上是一种语法构造,它等价于以下 CASE 表达式:
CASE WHEN condition THEN true_value [ ELSE false_value ] END
-
if(condition, true_value)
true_value
如果为真,则计算并返回condition
,否则返回 null且true_value
不计算。 -
if(condition, true_value, false_value)
true_value
如果为真,则计算并返回condition
,否则返回 null且true_value
不计算。
COALESCE --合并
-
coalesce(value1, value2[, …])#
返回参数列表中的第一个非空值
value
。与CASE
表达式一样,仅在必要时才评估参数。
NULLIF
-
nullif(value1, value2)
如果
value1
与value2
相等返回null,否则返回value1
。
TRY
-
try(expression)
通过返回
null
来评估表达式并处理某些类型的错误。
如果查询产生’ NULL '或默认值,而不是在遇到损坏或无效数据时失败,则更好的情况是,TRY
函数可能很有用。要指定默认值,TRY
函数可以与该COALESCE
函数结合使用。
由 TRY
处理以下错误:
- 除以0
- 无效的强制转换参数或无效的函数参数
- 数值超出范围
例子
包含一些无效数据的源表:
SELECT * FROM shipping;
origin_state | origin_zip | packages | total_cost
--------------+------------+----------+------------
California | 94131 | 25 | 100
California | P332a | 5 | 72
California | 94025 | 0 | 155
New Jersey | 08544 | 225 | 490
(4 rows)
查询失败,没有 TRY
:
SELECT CAST(origin_zip AS BIGINT) FROM shipping;
Query failed: Can not cast 'P332a' to BIGINT
NULL
值 with TRY
:
SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;
origin_zip
------------
94131
NULL
94025
08544
(4 rows)
查询失败,没有TRY
:
SELECT total_cost / packages AS per_package FROM shipping;
Query failed: / by zero
带有默认值的 TRY
and COALESCE
:
SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping;
per_package
-------------
4
14
0
19
(4 rows)
4. Lambda 表达式
Lambda 表达式用 ->
编写:
x -> x + 1
(x, y) -> x + y
x -> regexp_like(x, 'a+')
x -> x[1] / x[2]
x -> IF(x > 0, x, -x)
x -> COALESCE(x, 0)
x -> CAST(x AS JSON)
x -> x + TRY(1 / 0)
大多数 SQL 表达式都可以在 Lambda 主体中使用,担忧一些例外:
Most SQL expressions can be used in a lambda body, with a few exceptions:
- 不支持子查询.
x -> 2 + (SELECT 3)
- 不支持聚合.
x -> max(y)
5. 转换函数
如果可以进行此类转换,Presto 将隐式地将数字和字符值转换为正确的类型。Presto 不会在字符和数字类型之间进行转换。例如,需要 varchar 的查询不会自动将 bigint 值转换为等效的 varchar。
必要时,可以将值显式转换为特定类型。
-
cast(value AS type) → type
将值显示转换为类型。这可用于将 varchar 转换为数值类型,反之亦然。
-
try_cast(value AS type) → type
Like
cast()
, 但如果转换失败则返回 null。
Data Size --数据大小
parse_presto_data_size
函数支持以下单位:
Unit | Description | Value |
---|---|---|
B | 字节 | 1 |
kB | 千字节 | 1024 |
MB | 兆字节 | 1024^2 |
GB | 千兆字节 | 1024^3 |
TB | 太字节 | 1024^4 |
PB | 牌子姐 | 1024^5 |
EB | 艾字节 | 1024^6 |
ZB | 泽字节 | 1024^7 |
YB | Yottabytes | 1024^8 |
-
parse_presto_data_size(string)
将’ value unit ‘格式的’ string ‘解析为一个数字,其中’ value ‘是’ unit '值的小数:
SELECT parse_presto_data_size('1B'); -- 1 SELECT parse_presto_data_size('1kB'); -- 1024 SELECT parse_presto_data_size('1MB'); -- 1048576 SELECT parse_presto_data_size('2.3MB'); -- 2411724
Miscellaneous
-
typeof(expr) → varchar
返回所提供表达式的类型名称:
SELECT typeof(123); -- integer SELECT typeof('cat'); -- varchar(3) SELECT typeof(cos(2) + 1.5); -- double`
6. 数学函数与运算符
数学运算符
Operator | Description |
---|---|
+ | 加法 |
- | 减法 |
* | 乘法 |
/ | 除法 (整数除法执行截断) |
% | 模数 (取余) |
数学函数
-
abs(x) → [same as input]
返回
x
的绝对值. -
cbrt(x) → double
返回
x
的立方根. -
ceil(x) → [same as input]
这是
ceiling()
的别名 -
ceiling(x) → [same as input]
返回
x
向上舍入到最接近的整数。 -
cosine_similarity(x, y) → double
返回稀疏向量’ x ‘和’ y '之间的余弦相似度:
SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0])); -- 1.0
-
degrees(x) → double
将
x
弧度中的角度转换为度数。 -
e() → double
返回常数欧拉数。
-
exp(x) → double
返回欧拉数的x次方。
-
floor(x) → [same as input]
返回
x
向下舍入到最接近的整数。 -
from_base(string, radix) → bigint
返回
string
解释为基数的值radix
。 -
ln(x) → double
返回
x
的自然对数. -
log2(x) → double
返回
x
以2为底的对数. -
log10(x) → double
返回
x
以10为底的对数. -
mod(n, m) → [same as input]
返回’ n ‘除以’ m '的模(余数)。
-
pi() → double
返回常数Pi.
-
pow(x, p) → double
这是
power()
的别名 -
power(x, p) → double
返回
x
的p
次方. -
radians(x) → double
将角度
x
以度为单位转换为弧度。 -
rand() → double
这是
random()
的别名 -
random() → double
返回 0.0 <= x < 1.0 范围内的伪随机值
-
random(n) → [same as input]
返回 0 到 n(不包括)之间的伪随机数。
-
secure_rand() → double
这是
secure_random()
的别名 -
secure_random() → double
返回 0.0 <= x < 1.0 范围内的加密安全随机值。
-
secure_random(lower, upper) → [same as input]
返回lower <= x < upper范围内的加密安全随机值,其中lower< upper。
-
round(x) → [same as input]
返回
x
四舍五入到最接近的整数。 -
round(x, d) → [same as input]
返回
x
四舍五入到d
小数位。 -
sign(x) → [same as input]
返回
x
的符号函数,即:- 如果参数为 0,则为0
- 如果参数大于 0,则为1
- 如果参数小于 0,则为-1
对于双参数,该函数还返回:
- 如果参数为 NaN,则为 NaN,
- 如果参数是 +Infinity,则为1
- 如果参数是 -Infinity,则为-1
-
sqrt(x) → double
返回
x
的平方根. -
to_base(x, radix) → varchar
返回
x
的radix
基本表示 -
truncate(x) → double
返回
x
通过删除小数点后的数字返回舍入为整数。 -
truncate(x, n) → double
返回
x
截断到n
小数位。n
可以为负数以截断n
小数点左侧的数字。例子:
truncate(REAL '12.333', -1)
-> result is 10.0truncate(REAL '12.333', 0)
-> result is 12.0truncate(REAL '12.333', 1)
-> result is 12.3
三角函数
所有三角函数参数都以弧度表示。请参阅单位转换函数degrees()
和radians()
。
-
acos(x) → double
返回
x
的反余弦. -
asin(x) → double
返回
x
的反正弦。 -
atan(x) → double
返回
x
的反正切。 -
atan2(y, x) → double
返回
y / x
的反正切。 -
cos(x) → double
返回
x
的余弦。 -
cosh(x) → double
返回
x
的双曲余弦。 -
sin(x) → double
返回
x
的正弦值。 -
tan(x) → double
返回
x
的正切。 -
tanh(x) → double
返回
x
的双曲正切。
浮点函数
-
infinity() → double
返回表示正无穷大的常数。
-
is_finite(x) → boolean
确定是否
x
是有限的。 -
is_infinite(x) → boolean
判断是否
x
无限。 -
is_nan(x) → boolean
确定是否
x
不是数字。 -
nan() → double
返回表示非数字的常量。
7. 位函数
bit_count(x, bits) → bigint
计算 2 的补码表示中设置的位数x
(视为bits
-bit 有符号整数):
SELECT bit_count(9, 64); -- 2
SELECT bit_count(9, 8); -- 2
SELECT bit_count(-7, 64); -- 62
SELECT bit_count(-7, 8); -- 6
bitwise_and(x, y) → bigint
返回’ x ‘和’ y '在2的补码表示法中的按位与。
bitwise_not(x) → bigint
返回’ x '在2的补码表示法中的按位非。
bitwise_or(x, y) → bigint
返回’ x ‘和’ y '在2的补码表示法中的按位或。
bitwise_xor(x, y) → bigint
返回’ x ‘和’ y '在2的补码表示法中的按位异或。
bitwise_shift_left(x, shift, bits) → bigint
左移操作x
(视为bits
-位整数)移位shift
:
SELECT bitwise_shift_left(7, 2, 4); -- 12
SELECT bitwise_shift_left(7, 2, 64); -- 28
bitwise_logical_shift_right(x, shift, bits) → bigint
x
对(被视为bits
-位整数)移位的逻辑右移操作shift
:
SELECT bitwise_logical_shift_right(7, 2, 4); -- 1
SELECT bitwise_logical_shift_right(-8, 2, 5); -- 6
bitwise_arithmetic_shift_right(x, shift) → bigint
以 2 的补码表示x
移位的算术右移操作:shift
SELECT bitwise_arithmetic_shift_right(-8, 2); -- -2
SELECT bitwise_arithmetic_shift_right(7, 2); -- 1
通用移位函数
这三个函数接受“TINYINT”、“SMALLINT”、“INTEGER”和“BIGINT”的整型值,并按“shift”给出的值对它们进行移位,返回相同的整型值。对于这三个函数,移位量由’ shift ‘参数的底部位给出,而’ shift '参数的较高位被忽略。
-
bitwise_left_shift(value, shift) → [same as value]
返回 的左移值
value
:SELECT bitwise_left_shift(TINYINT '7', 2); -- 28 SELECT bitwise_left_shift(TINYINT '-7', 2); -- -28
-
bitwise_right_shift(value, shift, digits) → [same as value]
返回 的逻辑右移值
value
:SELECT bitwise_right_shift(TINYINT '7', 2); -- 1 SELECT bitwise_right_shift(SMALLINT -8, 2); -- 16382
-
bitwise_right_shift_arithmetic(value, shift) → [same as value]
返回 的算术右移值
value
:SELECT bitwise_right_shift_arithmetic(BIGINT '-8', 2); -- -2 SELECT bitwise_right_shift_arithmetic(SMALLINT '7', 2); -- 1
8. 小数函数和运算符
十进制文字
使用语法定义 DECIMAL 类型的文字。
DECIMAL 'xxxxxxx.yyyyyyy'
文字的 DECIMAL 类型的精度将等于文字中的位数(包括尾随和前导零)。比例将等于小数部分的位数(包括尾随零)。
Example literal | 数据类型 |
---|---|
DECIMAL '0' | DECIMAL(1) |
DECIMAL '12345' | DECIMAL(5) |
DECIMAL '0000012345.1234500000' | DECIMAL(20, 10) |
二进制算数小数运算符
支持标准数学运算符。下表给出了结果的精度和比例尺计算规则。假设’ x ‘的类型是’ DECIMAL(xp, xs) ', ’ y ‘的类型是’ DECIMAL(yp, ys) '。
Operation | 结果类型 | Result type scale |
---|---|---|
x + y andx - y | min(38, 1 + min(xs, ys) + min(xp - xs, yp - ys) ) | max(xs, ys) |
x * y | min(38, xp + yp) | xs + ys |
x / y | min(38, xp + ys + max(0, ys-xs) ) | max(xs, ys) |
x % y | min(xp - xs, yp - ys) + max(xs, bs) | max(xs, ys) |
如果运算的数学结果不能用结果数据类型的精度和比例精确表示,则引发异常条件 -
Value is out of range
。当对具有不同比例和精度的小数类型进行操作时,这些值首先被强制转换为一个通用的超类型。对于接近最大可表示精度 (38) 的类型,当其中一个操作数不适合常见的超类型时,这可能会导致 Value is out of range 错误。比如decimal(38, 0)和decimal(38, 1)的常见超类型是decimal(38, 1),但是适合decimal(38, 0)的某些值不能表示为decimal(38, 1)。
比较运算符
所有标准比较运算符和
BETWEEN
运算符都适用于DECIMAL
类型。
一元小数运算符
-
运算符执行否定。结果的类型与参数的类型相同。
9. 字符串函数和运算符
字符串运算符
||
运算符执行连接。
字符串函数
这些函数假定输入字符串包含有效的 UTF-8 编码的 Unicode 代码点。没有对有效 UTF-8 的显式检查,并且函数可能会在无效 UTF-8 上返回不正确的结果。无效的 UTF-8 数据可以用
from_utf8()
.此外,这些函数在 Unicode 代码点上运行,而不是用户可见的字符(或字素簇)。一些语言将多个代码点组合成一个用户感知的字符,这是一种语言书写系统的基本单元,但函数会将每个代码点视为一个单独的单元。
-
chr(n) → varchar
将 Unicode 代码点
n
作为单个字符串返回。 -
codepoint(string) → integer
返回
string
的唯一字符的 Unicode 代码点。 -
concat(string1, …, stringN) → varchar
返回
string1
,string2
,...
,的串联stringN
。此函数提供与 SQL 标准连接运算符 (||
) 相同的功能。 -
hamming_distance(string1, string2) → bigint
返回
string1
和string2
的汉明距离,即对应字符不同的位置数。请注意,这两个字符串必须具有相同的长度。 -
length(string) → bigint
返回
string
字符的长度。 -
levenshtein_distance(string1, string2) → bigint
返回’ string1 ‘和’ string2 ‘的Levenshtein编辑距离,即将’ string1 ‘更改为’ string2 '所需的最小单字符编辑(插入、删除或替换)次数。
-
lower(string) → varchar
转换
string
为小写。 -
lpad(string, size, padstring) → varchar
string
用.向左填充size
字符padstring
。如果size
小于 的长度string
,则将结果截断为size
字符。size
不能为负,且padstring
不能为空。 -
ltrim(string) → varchar
从
string
中删除前导空格。 -
replace(string, search) → varchar
从’ string ‘中移除’ search '的所有实例。
-
replace(string, search, replace) → varchar
将’ string ‘中的’ search ‘的所有实例替换为’ replace ‘。如果’ search ‘是一个空字符串,在每个字符前面和’ string ‘的末尾插入’ replace '。
-
reverse(string) → varchar
用字符的倒序返回’ string '。
-
rpad(string, size, padstring) → varchar
右垫’ string ‘到’ size ‘字符与’ padstring ‘。如果’ size ‘小于’ string ‘的长度,结果将被截断为’ size ‘字符。’ size ‘不能为负数,’ padstring '不能为空。
-
rtrim(string) → varchar
删除’ string '中的尾随空格。
-
split(string, delimiter)
在“delimiter”上拆分“string”并返回一个数组。
-
split(string, delimiter, limit)
分隔’ delimiter ‘上的’ string ‘,并返回大小最多为’ limit ‘的数组。数组中的最后一个元素总是包含’ string ‘中剩下的所有内容。’ limit '必须是正数。
-
split_part(string, delimiter, index) → varchar
分隔’ delimiter ‘上的’ string ‘并返回字段’ index '。字段索引以“1”开头。如果索引大于字段的数量,则返回null。
-
split_to_map(string, entryDelimiter, keyValueDelimiter) → map<varchar, varchar>
用’ entryDelimiter ‘和’ keyValueDelimiter ‘分割’ string ‘,并返回一个映射。’ entryDelimiter ‘将’ string ‘拆分为键值对。’ keyValueDelimiter ‘将每个对拆分为键和值。注意’ entryDelimiter ‘和’ keyValueDelimiter '是按字面意思解释的,也就是说,作为完整的字符串匹配。
-
split_to_map(string, entryDelimiter, keyValueDelimiter, function(K, V1, V2, R)) → map<varchar, varchar>
用’ entryDelimiter ‘和’ keyValueDelimiter ‘分割’ string ‘,并返回一个映射。’ entryDelimiter ‘将’ string ‘拆分为键值对。’ keyValueDelimiter ‘将每个对拆分为键和值。注意’ entryDelimiter ‘和’ keyValueDelimiter ‘是按字面意思解释的,也就是说,作为完整的字符串匹配。’ function(K,V1,V2,R) '在键重复的情况下被调用,以解析应该在映射中的值。
SELECT(split_to_map(‘a:1;b:2;a:3’, ‘;’, ‘:’, (k, v1, v2) -> v1)); – {“a”: “1”, “b”: “2”} SELECT(split_to_map(‘a:1;b:2;a:3’, ‘;’, ‘:’, (k, v1, v2) -> CONCAT(v1, v2))); – {“a”: “13”, “b”: “2”}
-
split_to_multimap(string, entryDelimiter, keyValueDelimiter)
通过’ entryDelimiter ‘和’ keyValueDelimiter ‘分割’ string ‘,并返回包含每个唯一键值数组的映射。’ entryDelimiter ‘将’ string ‘拆分为键值对。’ keyValueDelimiter ‘将每个对拆分为键和值。每个键的值将在相同的顺序,因为他们出现在’字符串’。注意’ entryDelimiter ‘和’ keyValueDelimiter '是按字面意思解释的,也就是说,作为完整的字符串匹配。
-
strpos(string, substring) → bigint
返回’ string ‘中’ substring ‘的第一个实例的起始位置。位置以“1”开头。如果没有找到,则返回’ 0 '。
-
strpos(string, substring, instance) → bigint
返回’ string ‘中’子字符串’的第n个’实例’的位置。’ instance ‘必须为正数。位置以“1”开头。如果没有找到,则返回’ 0 '。
-
strrpos(string, substring) → bigint
返回’ string ‘中’ substring ‘最后一个实例的起始位置。位置以“1”开头。如果没有找到,则返回’ 0 '。
-
strrpos(string, substring, instance) → bigint
返回’ string ‘中’子字符串’的第n个’实例’的位置,从字符串的末尾开始。’ instance ‘必须为正数。位置以“1”开头。如果没有找到,则返回’ 0 '。
-
position(substring IN string) → bigint
返回’ string ‘中’ substring ‘的第一个实例的起始位置。位置以“1”开头。如果没有找到,则返回’ 0 '。
-
substr(string, start) → varchar
返回’ string ‘从起始位置’ start '开始的剩余部分。位置以“1”开头。负的起始位置被解释为相对于字符串的结尾。
-
substr(string, start, length) → varchar
从’ string ‘返回长度’ length ‘的子字符串,起始位置’ start '。位置以“1”开头。负的起始位置被解释为相对于字符串的结尾。
-
trim(string) → varchar
从’ string '中移除前导和尾随空格。
-
upper(string) → varchar
将’ string '转换为大写。
-
word_stem(word) → varchar
返回英语中’ word '的词干。
-
word_stem(word, lang) → varchar
返回’ lang ‘语言中的’ word '的词干。
10. 聚合函数
聚合函数对一组值进行运算以计算单个结果。
除了 count()
, count_if()
, max_by()
, min_by()
和approx_distinct()
, 所有这些聚合函数都忽略控制并在没有输入行或所有制为空时返回空值。例如, sum()
返回null 而不是零,并且 avg()
在计数中不包含空值。 coalesce
函数可用于将null 转换为零。
一些聚合函数如 arrag_agg() 根据输入值的顺序产生不同的结果。可以通过在聚合函数中编写 ORDER BY 子句来指定此排序:
array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z)
通用聚合函数
-
arbitrary(x) → [same as input]
如果存在,返回任意的非空值’ x '。
-
array_agg(x) → array<[same as input]>
返回由输入’ x '元素创建的数组。
-
avg(x) → double
返回所有输入值的平均值(算术平均值)。
-
avg(time interval type) → time interval type
返回所有输入值的平均间隔长度。
-
bool_and(boolean) → boolean
如果每个输入值都为’ TRUE ‘,则返回’ TRUE ‘,否则返回’ FALSE '。
-
bool_or(boolean) → boolean
如果输入值为’ TRUE ‘,则返回’ TRUE ‘,否则返回’ FALSE '。
-
checksum(x) → varbinary
返回给定值的不区分顺序的校验和。
-
count(***) → bigint
返回输入行数。
-
count(x) → bigint
返回非空输入值的数目。
-
count_if(x) → bigint
返回’ TRUE ‘输入值的个数。这个函数等价于’ count(CASE WHEN x THEN 1 END) '。
-
every(boolean) → boolean
这是’ bool_and() '的别名。
-
geometric_mean(x) → double
返回所有输入值的几何平均值。
-
max_by(x, y) → [same as x]
返回’ x ‘的值,该值与’ y '在所有输入值中的最大值相关联。
-
max_by(x, y, n) → array<[same as x]>
按“y”降序返回与“y”的所有输入值中最大的“n”相关联的“x”的“n”值。
-
min_by(x, y) → [same as x]
返回所有输入值中与’ y ‘的最小值相关联的’ x '的值。
-
min_by(x, y, n) → array<[same as x]>
按照“y”的升序,返回与“y”的所有输入值中最小的“n”相关联的“x”的“n”值。
-
max(x) → [same as input]
返回所有输入值的最大值。
-
max(x, n) → array<[same as x]>
返回’ x ‘的所有输入值中的’ n '个最大值。
-
min(x) → [same as input]
返回所有输入值的最小值。
-
min(x, n) → array<[same as x]>
返回’ x ‘的所有输入值中的’ n '个最小值。
-
reduce_agg(inputValue T, initialState S, inputFunction(S, T, S), combineFunction(S, S, S)) → S
将所有输入值减少为一个值。每个输入值都会调用’ inputFunction ‘。除了获取输入值之外,’ inputFunction ‘还获取当前状态,即最初的’ initialState ‘,并返回新状态。’ combineFunction '将被调用来将两个状态合并成一个新的状态。返回最终状态:
SELECT id, reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b) FROM ( VALUES (1, 2), (1, 3), (1, 4), (2, 20), (2, 30), (2, 40) ) AS t(id, value) GROUP BY id; -- (1, 9) -- (2, 90) SELECT id, reduce_agg(value, 1, (a, b) -> a * b, (a, b) -> a * b) FROM ( VALUES (1, 2), (1, 3), (1, 4), (2, 20), (2, 30), (2, 40) ) AS t(id, value) GROUP BY id; -- (1, 24) -- (2, 24000)
状态类型必须是布尔值、整数、浮点数或日期/时间/间隔。
-
set_agg(x) → array<[same as input]>
返回由不同输入’ x '元素创建的数组。
-
set_union(array(T)) -> array(T)
返回inputExample中每个数组中包含的所有不同值的数组:
SELECT set_union(elements) FROM ( VALUES ARRAY[1, 2, 3], ARRAY[2, 3, 4] ) AS t(elements);
返回ARRAY[1, 2, 3, 4]
-
sum(x) → [same as input]
返回所有输入值的和。
按位聚合函数
-
bitwise_and_agg(x) → bigint
以 2 的补码表示形式返回所有输入值的按位与。
-
bitwise_or_agg(x) → bigint
以 2 的补码表示形式返回所有输入值的按位或。
MAP 聚合函数
-
histogram(x)
返回一个映射,其中包含每个输入值出现的次数的计数。
-
map_agg(key, value)
返回由输入的“key”/“value”对创建的映射。
-
map_union(x(K, V)) -> map(K, V)
返回所有输入映射的并集。如果一个键在多个输入映射中找到,则结果映射中的该键值来自任意一个输入映射。
-
map_union_sum(x(K, V)) -> map(K, V)
返回所有输入映射的并集,并将所有映射中匹配键的值相加。原始映射中的所有空值都合并为0。
-
multimap_agg(key, value)
返回由输入的“key”/“value”对创建的multimap容器。每个键可以与多个值关联。
2.MySQL、Hive SQL、presto语法常见区别
-
hive 不支持join 的on 中用or 连接过个条件,但MySQL和presto支持
-
hive目前 in、not in 是不支持子查询的,MySQL和presto支持
sql:
select DISTINCT userid FROM TABLE_A AS a WHERE a.dt >= '20200209' AND a.userid NOT IN (SELECT DISTINCT userid FROM TABLE_B AS b WHERE b.dt >= '20200209');
使用join改写:
select DISTINCT a.userid FROM TABLE_A AS a left JOIN TABLE_B AS b on a.userid=b.userid WHERE b.userid is NULL;
使用EXISTS改写:
select DISTINCT a.userid FROM TABLE_A AS a WHERE a.dt >= '20200209' AND NOT EXISTS (SELECT DISTINCT b.userid FROM TABLE_B AS b WHERE b.dt >= '20200209' AND a.userid = b.userid);
-
hive 不支持limit。 在hive中,不支持
limit m-1, n
的方式,只能用 row_number() over(distribute by … sort by … ) rank where rank< (m+n+1) and rank > (m-1)实现。Mysql中可以用 limit m-1 ,n 的限制语法;表中下标从0开始,从m条记录开始取,一共取n条记录。
sql:
SELECT * FROM table LIMIT 5,10; //检索记录行6-15 SELECT * FROM table LIMIT 5,-1; // 检索记录行 6-last SELECT * FROM table LIMIT 5; //检索前 5 个记录行
hive改写:
select a.course,a.score from ( select course,score,row_number() over(partition by course order by score desc) as n from lesson )a where a.n<=2;
其中,row_number() over(partition by course order by score desc)
意思是以课程分组,按成绩递减排序,并为每组中的数据打上行号的标记,从1开始。然后在外层套一层过滤行号小于等于2的即可。
-
with as 创建临时表。presto和mysql支持with as 创建临时表,但hive不支持。
-
nullif函数。hive较低的版本暂时不支持nullif 函数,Mysql和presto支持
-
分母为0的问题。hive中分母为0和null均不报错,算出来结果为null。但presto中分母为0报错,为null不报错,用case when 判断
-
计算四分位数。hive具有 percentile(col,num) 函数,presto不支持
-
group by 与with rollup连用。mysql和hive支持该用法,presto不支持。
-
分组连接函数
MySQL的group_concat()函数使用如下:
Select fid, group_concat(name order by name desc) from test group by fid
hive可以用concat_ws函数和collect_list、collect_set 函数来实现该功能。collect_set转为数组并去重,concat_ws将数组用逗号间隔连接成字符串
select id, concat_ws(',',collect_set(content)) as con_con, concat_ws(',',collect_set(comment)) as con_com from db_name.test_tb group by id
在presto中无collect_set,可用array_join函数实现该功能。array_agg 转为数组,array_distinct 去重,array_join 将数组用逗号间隔连接成字符串
select id, array_join(array_distinct(array_agg(content)), ',') as con_con from db_name.test_tb group by id
- 日期转换
// presto
format_datetime(from_unixtime(cast(substr(crowd_create_time,1,10) as double)),‘yyyy-MM-dd HH:mm:ss’)// hive
from_unixtime(CAST(SUBSTR(crowd_create_time, 1, 10) AS INT), ‘yyyy-MM-dd HH:mm:ss’)
- cast()函数的使用将某种数据类型显式转换成另一种数据类型
eg : cast(‘12’ as integer)
hive cast(字段 as string)
presto cast(字段 as string)
presto和 Hive 差异
presto | Hive | |
---|---|---|
数组 | 数组有动态下标,下标从1开始 | 下标常量,下摆哦从0开始 |
标识符 | 数字开头用"", eg: from “2days” | 无 |
string | varchar | string |
运算 | 5/2=2 | 5/2=2.5 |
列传行 | unnset | laterval view explode() |
JSON处理 | json_extract_scalar | get_json_object |
date转string(隐式转换) | 不支持 | 支持 |
concat | char和varchar不支持连接 | 支持 |
semi join | 不支持 | 支持 |
cross join | 不支持on | 支持on |