华为云用户手册

WHERE子句使用约束 对于WHERE子句的LIKE操作符，当LIKE中要查询特殊字符“%”、“_”、“\”的时候需要使用反斜杠“\”来进行转义。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 CREATE TABLE tt01 (id int,content varchar(50)); INSERT INTO tt01 values (1,'Jack say ''hello'''); INSERT INTO tt01 values (2,'Rose do 50%'); INSERT INTO tt01 values (3,'Lilei say ''world'''); INSERT INTO tt01 values (4,'Hanmei do 100%'); SELECT * FROM tt01; id | content ----+------------------- 3 | Lilei say 'world' 4 | Hanmei do 100% 1 | Jack say 'hello' 2 | Rose do 50% (4 rows) SELECT * FROM tt01 WHERE content like '%''he%'; id | content ----+------------------ 1 | Jack say 'hello' (1 row) SELECT * FROM tt01 WHERE content like '%50\%%'; id | content ----+------------- 2 | Rose do 50% (1 row) WHERE子句中可以通过指定"(+)"操作符的方法将表的连接关系转换为外连接。但是不建议用户使用这种用法，因为这并不是SQL的标准语法，在做平台迁移的时候可能面临语法兼容性的问题。使用"(+)"有很多限制如下： "(+)"只能出现在where子句中。 如果from子句中已经有指定表连接关系，那么"(+)"不能在where子句中使用。 "(+)"只能作用在表或者视图的列上，不能作用在表达式上。 如果表A和表B有多个连接条件，那么必须在所有的连接条件中指定"(+)"，否则"(+)"将不会生效，表连接会转化成内连接，并且不给出任何提示信息。 "(+)"作用的连接条件中的表不能跨查询或者子查询。如果"(+)"作用的表，不在当前查询或者子查询的from子句中，则会报错。如果"(+)"作用的对端的表不存在，则不报错，同时连接关系会转化为内连接。 "(+)"作用的表达式不能直接通过"OR"连接。 如果"(+)"作用的列是和一个常量的比较关系， 那么这个表达式会成为join条件的一部分。 同一个表不能对应多个外表。 "(+)"只能出现"比较表达式"，"NOT表达式"，“ANY表达式”，“ALL表达式”，“IN表达式”，“NULLIF表达式”，“IS DISTINCT FROM表达式”，“IS OF”表达式。"(+)"不能出现在其他类型表达式中，并且这些表达式中不允许出现通过“AND”和“OR”连接。 "(+)"只能转化为左外连接或者右外连接，不能转化为全连接，即不能在一个表达式的两个表上同时指定"(+)"。

参数说明 表1 SELECT参数说明 参数 描述 取值范围 WITH [ RECURSIVE ] with_query [, ...] 用于声明一个或多个在主查询中通过名字引用的子查询，相当于临时表。 如果声明了RECURSIVE，则允许SELECT子查询通过名字引用它自己。 其中with_query的详细格式为：with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )。 默认被主查询多次引用的with_query通常只被执行一次，并将其结果集进行物化，供主查询多次查询其结果集； 被主查询引用一次的with_query，则不再单独执行，而是将其子查询直接替换到主查询中的引用处，随主查询一起执行。 每个子查询可以是SELECT，VALUES，INSERT，UPDATE或DELETE语句。 如下几种情况会忽略NOT MATERIALIZED： 子查询中含有volatile函数。 子查询为含有FOR UPDATE/FOR SHARE的SELECT/VALUES语句。 子查询为INSERT/UPDATE/DELETE等语句。 with_query为RECURSIVE。 被引用次数大于1的with_query2引用了外层自引用的with_query1，则with_query2不能被替换到引用处。 例如下面示例中，tmp2被引用了两次，tmp2因为引用了外层自引用的tmp1，所以即使tmp2指定了NOT MATERIALIZED也会被物化。 1 2 3 4 5 with recursive tmp1(b) as (values(1) union all (with tmp2 as not materialized (select * from tmp1) select tt1.b + tt2.b from tmp2 tt1, tmp2 tt2)) select * from tmp1; with_query_name指定子查询生成的结果集名称，可使用该名称访问子查询的结果集。 [ NOT ] MATERIALIZED，指定该参数可改变默认行为： 指定MATERIALIZED时，将子查询执行一次，并将其结果集进行物化。 指定NOT MATERIALIZED时，则将其子查询替换到主查询中的引用处。 column_name指定子查询结果集中显示的列名。 plan_hint子句 以/*+ */的形式在SELECT关键字后，用于对SELECT对应的语句块生成的计划进行hint调优 ，详细用法请参见使用Plan Hint进行调优。 - ALL 声明返回所有符合条件的行，是默认行为，可以省略该关键字。 - DISTINCT [ ON ( expression [, ...] ) ] 从SELECT的结果集中删除所有重复的行，确保结果集中每行都是唯一的。 ON ( expression [, ...] ) 只保留那些在给出的表达式上运算出相同结果的行集合中的第一行。 须知： DISTINCT ON表达式是使用与ORDER BY相同的规则进行解释的。除非使用了ORDER BY来保证需要的行首先出现，否则，"第一行" 是不可预测的。 - SELECT列表 指定查询表中列名，可以是部分列或者是全部（使用通配符*表示）。 通过使用子句AS output_name可以为输出字段取个别名，这个别名通常用于输出字段的显示。 列名可以用下面几种形式表达： 手动输入列名，多个列之间用英文逗号（,）分隔。 可以是FROM子句里面计算出来的字段。 FROM子句 为SELECT声明一个或者多个源表。具体请参见表2。 - WHERE子句 WHERE子句构成一个行选择表达式，用来缩小SELECT的查询范围。 LIKE操作符 "(+)"操作符，不建议使用。 具体使用约束请参见WHERE子句使用约束。 GROUP BY子句 将查询结果按某一列或多列的值分组，值相等的为一组。 详情请参见表4。 HAVING子句 与GROUP BY子句配合用来选择特殊的组。HAVING子句将组的一些属性与一个常数值比较，只有满足HAVING子句中的逻辑表达式的组才会被提取出来。 - WINDOW子句 一般形式为WINDOW window_name AS ( window_definition ) [， ...]，window_name是可以被随后的窗口定义所引用的名称，window_definition可以是以下的形式： [ existing_window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ] frame_clause为窗函数定义一个窗口框架window frame，窗函数（并非所有）依赖于框架，window frame是当前查询行的一组相关行。frame_clause可以是以下的形式： [ RANGE | ROWS ] frame_start [ RANGE | ROWS ] BETWEEN frame_start AND frame_end frame_start和frame_end可以是： UNBOUNDED PRECEDING value PRECEDING（RANGE不支持） CURRENT ROW value FOLLOWING（RANGE不支持） UNBOUNDED FOLLOWING 须知： 对列存表的查询目前只支持row_number窗口函数，不支持frame_clause。 - UNION子句 UNION计算多个SELECT语句返回行集合的并集。 UNION子句有如下约束条件： 除非声明了ALL子句，否则缺省的UNION结果不包含重复的行。 同一个SELECT语句中的多个UNION操作符是从左向右计算的，除非用圆括弧进行了标识。 FOR UPDATE不能在UNION的结果或输入中声明。 一般表达式： select_statement UNION [ALL] select_statement select_statement可以是任何没有ORDER BY、LIMIT、FOR UPDATE子句的SELECT语句。 如果用圆括弧包围，ORDER BY和LIMIT可以附着在子表达式里。 - INTERSECT子句 INTERSECT计算多个SELECT语句返回行集合的交集，不含重复的记录。 INTERSECT子句有如下约束条件： 同一个SELECT语句中的多个INTERSECT操作符是从左向右计算的，除非用圆括弧进行了标识。 当对多个SELECT语句的执行结果进行UNION和INTERSECT操作的时候，会优先处理INTERSECT。 一般形式： select_statement INTERSECT select_statement select_statement可以是任何没有FOR UPDATE子句的SELECT语句。 - EXCEPT子句 EXCEPT子句有如下的通用形式： select_statement EXCEPT [ ALL ] select_statement select_statement是任何没有FOR UPDATE子句的SELECT表达式。 EXCEPT操作符计算存在于左边SELECT语句的输出而不存在于右边SELECT语句输出的行。 EXCEPT的结果不包含任何重复的行，除非声明了ALL选项。使用ALL时，一个在左边表中有m个重复而在右边表中有n个重复的行将在结果中出现max(m-n,0) 次。 除非用圆括弧指明顺序，否则同一个SELECT语句中的多个EXCEPT操作符是从左向右计算的。EXCEPT和UNION的绑定级别相同。 目前，不能给EXCEPT的结果或者任何EXCEPT的输入声明FOR UPDATE子句。 - MINUS子句 与EXCEPT子句具有相同的功能和用法。 - ORDER BY子句 对SELECT语句检索得到的数据进行升序或降序排序。对于ORDER BY表达式中包含多列的情况： 首先根据最左边的列进行排序，如果这一列的值相同，则根据下一个表达式进行比较，以此类推。 如果对于所有声明的表达式都相同，则按随机顺序返回。 ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。 须知： 如果未指定ORDER BY，则按数据库系统最快生成的顺序返回。 可以选择在ORDER BY子句中的任何表达式之后添加关键字ASC（升序）或DESC（降序）。如果未指定，则默认使用ASC。 如果要支持中文拼音排序和不区分大小写排序，需要在初始化数据库时指定编码格式为UTF-8或GBK。 命令如下: initdb –E UTF8 –D ../data –locale=zh_CN.UTF-8或initdb –E GBK –D ../data –locale=zh_CN.GBK。 - [ { [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] } | { LIMIT start, { count | ALL } } ] LIMIT子句由两个独立的Limit子句、Offset子句和一个多参Limit子句构成： LIMIT { count | ALL } OFFSET start [ ROW | ROWS ] LIMIT start, { count | ALL } 其中，count声明返回的最大行数，而start声明开始返回行之前忽略的行数。如果这两个参数都指定了，会在开始计算count个返回行之前先跳过start行。多参Limit子句不可和单参的Limit子句或Offset子句共同出现。 - FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY FETCH子句限定返回查询结果从第一行开始的总行数。 如果不指定count，默认值为1。 FOR UPDATE子句 FOR UPDATE子句将对SELECT检索出来的行进行加锁，这样避免在当前事务结束前被其它事务修改或者删除。即执行UPDATE、 DELETE、 SELECT FOR UPDATE这些行的事务将被阻塞，直到当前事务结束。 如果在FOR UPDATE/SHARE中明确指定了表名，则只有这些指定的表被锁定，其他在SELECT中使用的表将不会被锁定。否则，将锁定该命令中所有使用的表。 如果FOR UPDATE/SHARE应用于一个视图或者子查询，它同样将锁定所有该视图或子查询中使用到的表。 多个FOR UPDATE/SHARE子句可以用于为不同的表指定不同的锁定模式。 如果一个表中同时出现（或隐含同时出现）在FOR UPDATE/SHARE子句中，则按照FOR UPDATE处理。类似的，如果影响一个表的任意子句中出现了NOWAIT，该表将按照NOWAIT处理。 须知： 对于FOR UPDATE/SHARE，执行计划不能下推的SQL，直接返回报错信息；对于执行计划可以下推的，下推到DN执行。 对列存表的查询不支持FOR UPDATE/SHARE。 NOWAIT选项：避免操作等待其它事务提交，如果被选择的行不能立即被锁，执行SELECT FOR UPDATE NOWAIT将会立即报错。 FOR SHARE/UPDATE：是对每个检索出来的行要求一个共享锁，而不是排他锁。一个共享锁阻塞其它事务执行UPDATE、DELETE、SELECT，而不阻塞SELECT FOR SHARE。 NLS_SORT 指定某字段按照特殊方式排序。目前仅支持中文拼音格式排序和不区分大小写排序。 SCHINESE_PINYIN_M，按照中文拼音排序（目前只支持GBK字符集内的一级汉字排序）。如果要支持此排序方式，在创建数据库时需要指定编码格式为“GBK”，否则排序无效。 generic_m_ci，不区分大小写排序。 PARTITION子句 查询某个分区表中相应分区的数据。 - 表2 FROM子句相关参数说明 参数 描述 取值范围 table_name 表名或视图名，名称前可加上模式名，如：schema_name.table_name。 字符串，要符合标识符命名规范。 具体请参见标识符命名规范。 alias 给表或复杂的表引用起一个临时的表别名，以便被其余的查询引用。 别名用于缩写或者在自连接中消除歧义。如果提供了别名，它就会完全隐藏表的实际名字。 字符串，要符合标识符命名规范。 具体请参见标识符命名规范。 column_alias 列别名。 字符串，要符合标识符命名规范。 具体请参见标识符命名规范。 PARTITION 查询分区表的某个分区的数据。 - partition_name 分区名。 字符串，要符合标识符命名规范。 具体请参见标识符命名规范。 partition_value 指定的分区键值。在创建分区表时，如果指定了多个分区键，可以通过PARTITION FOR子句指定的这一组分区键的值，唯一确定一个分区。 字符串，要符合标识符命名规范。 具体请参见标识符命名规范。 subquery FROM子句中可以出现子查询，创建一个临时表保存子查询的输出。 - with_query_name WITH子句同样可以作为FROM子句的源，可以通过WITH查询的名字对其进行引用。 字符串，要符合标识符命名规范。 具体请参见标识符命名规范。 function_name 函数名称。函数调用也可以出现在FROM子句中。 字符串，要符合标识符命名规范。 具体请参见标识符命名规范。 join_type JOIN的类型。 支持五种JOIN类型： [ INNER ] JOIN LEFT [ OUTER ] JOIN RIGHT [ OUTER ] JOIN FULL [ OUTER ] JOIN CROSS JOIN 详情请参见表3。 ON join_condition 连接条件，用于限定连接中的哪些行是匹配的。如：ON left_table.a = right_table.a。 - USING(join_column[，...]) ON left_table.a = right_table.a AND left_table.b = right_table.b ... 的简写。要求对应的列必须同名。 - NATURAL NATURAL是具有相同名称的两个表的所有列的USING列表的简写。 - from item 用于连接的查询源对象的名称。 - 表3 join_type的五种类型 参数 描述 [ INNER ] JOIN 一个JOIN子句由两个FROM项组合而成。可使用圆括弧以决定嵌套的顺序。如果没有圆括弧，JOIN从左向右嵌套。在任何情况下，JOIN都比逗号分隔的FROM项绑定得更紧。 LEFT [ OUTER ] JOIN 返回笛卡尔积中所有符合连接条件的行，再加上左表中通过连接条件没有匹配到右表行中的那些行。这样，左边的行将扩展为生成表的全长，方法是在那些右表对应的字段位置填充上NULL。 需要注意的是，只在计算匹配的时候，才使用JOIN子句的条件，外层的条件是在计算完成之后施加的。 RIGHT [ OUTER ] JOIN 返回所有内连接的结果行，加上每个不匹配的右边行（左边用NULL扩展）。这只是一个符号上的方便，因为总是可以将其转换成一个LEFT OUTER JOIN，只需要把左边和右边的输入互换位置即可。 FULL [ OUTER ] JOIN 返回所有内连接的结果行，加上每个不匹配的左边行（右边用NULL扩展），再加上每个不匹配的右边行（左边用NULL扩展）。 CROSS JOIN CROSS JOIN等效于INNER JOIN ON（TRUE） ，即没有被条件删除的行。这种连接类型只是符号上的方便，与简单的FROM和WHERE的效果相同。 说明： 必须为INNER和OUTER连接类型声明一个连接条件，即NATURAL ON，join_condition，USING (join_column [， ...]) 之一。但是它们不能出现在CROSS JOIN中。 CROSS JOIN和INNER JOIN生成一个简单的笛卡尔积，和在FROM的顶层列出两个项的结果相同。 表4 GROUP BY子句参数说明 参数 描述 ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) ROLLUP是计算一个有序的分组列在GROUP BY中指定的标准聚集值，然后从右到左进一步创建高层次的部分和，最后创建了累积和。一个分组能够看做一系列的分组集。例如： 1 GROUP BY ROLLUP (a,b,c) 等价于： 1 GROUP BY GROUPING SETS((a,b,c), (a,b), (a), ( )) ROLLUP子句中的元素可以是单独的字段或表达式，也可以是使用括号包含的列表。如果是括号中的列表，产生分组集时它们必须作为一个整体。例如： 1 GROUP BY ROLLUP ((a,b), (c,d)) 等价于： 1 GROUPING SETS ((a,b,c,d), (a,b), (c,d ), ( )) CUBE ( { expression | ( expression [, ...] ) } [, ...] ) CUBE是自动对group by子句中列出的字段进行分组汇总，结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚合值。它会为每个分组返回一行汇总信息， 用户可以使用CUBE来产生交叉表值。比如，在CUBE子句中给出三个表达式（n = 3），运算结果为2n = 23 = 8组。 以n个表达式的值分组的行称为常规行，其余的行称为超级聚集行。例如： 1 GROUP BY CUBE (a,b,c) 等价于： 1 GROUP BY GROUPING SETS((a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ( )) CUBE子句中的元素可以是单独的字段或表达式，也可以是使用括号包含的列表。如果是括号中的列表，产生分组集时它们必须作为一个整体。例如： 1 GROUP BY CUBE (a, (b, c), d) 等价于： GROUP BY GROUPING SETS ((a,b,c,d), (a,b,c), (a), ( )) GROUPING SETS ( grouping_element [, ...] ) GROUPING SETS子句是GROUP BY子句的进一步扩展，它可以使用户指定多个GROUP BY选项。选项用于定义分组集，每个分组集都需要包含在单独的括号中，空白的括号（()）表示将所有数据当作一个组处理。 这样做可以通过裁剪用户不需要的数据组来提高效率。 用户可以根据需要指定所需的数据组进行查询。 须知： 如果SELECT列表的表达式中引用了那些没有分组的字段，则会报错，除非使用了聚集函数，因为对于未分组的字段，可能返回多个数值。

语法格式 查询数据 1 2 3 4 5 6 7 8 9 10 11 12 13 [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] { * | {expression [ [ AS ] output_name ]} [, ...] } [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] [ WINDOW {window_name AS ( window_definition )} [, ...] ] [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ] [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ] [ { [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] } | { LIMIT start, { count | ALL } } ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ]} [...] ]; condition和expression中可以使用输出列中表达式的别名。 只能同一层引用。 只能引用输出列中的别名。 只能是后面的表达式引用前面的表达式。 不能包含volatile函数。 不能包含Window function函数。 不支持在join on条件中引用别名。 输出列中有多个要应用的别名则报错。 其中子查询with_query为： 1 2 with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} ) 其中指定查询源from_item为： 1 2 3 4 5 6 {[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |( select ) [ AS ] alias [ ( column_alias [, ...] ) ] |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] |function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]} 其中group子句为： 1 2 3 4 5 6 ( ) | expression | ( expression [, ...] ) | ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) | CUBE ( { expression | ( expression [, ...] ) } [, ...] ) | GROUPING SETS ( grouping_element [, ...] ) 其中指定分区partition_clause为： 1 2 PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } 指定分区只适合普通表。 其中设置排序方式nlssort_expression_clause为： 1 NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' ) 简化版查询语法，功能相当于select * from table_name。 1 TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};

参数描述 表1 DROP SYNONYM参数说明 参数 描述 取值范围 IF EXISTS 如果指定的同义词不存在，则发出一个notice而不是抛出一个错误。 - synonym_name 要删除的同义词名字，可以带模式名。 已存在的同义词名字。 CASCADE | RESTRICT 当执行删除操作时，如何处理依赖对象中的相关数据。 CASCADE：级联删除依赖同义词的对象（比如视图）。 RESTRICT：如果有依赖对象存在，则拒绝删除同义词。此选项为缺省值。

示例代码 计算所有商品库存（items）的 0.5 百分位，精确度100。命令示例如下： select PERCENTILE_APPROX(items,0.5，100) from warehouse; 返回结果如下： +------------+ | _c0 | +------------+ | 521 | +------------+ 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，并计算同组商品库存（items）的 0.5 百分位，精确度100。命令示例如下： select warehouseId, PERCENTILE_APPROX(items, 0.5, 100) from warehourse group by warehouseId; 返回结果如下： +------------+------------+ | warehouseId| _c1 | +------------+------------+ | city1 | 499 | | city2 | 354 | | city3 | 565 | +------------+------------+

日期函数概览 DLI 所支持的日期函数如表1所示。 表1 日期/时间函数 函数 命令格式 返回值 功能简介 add_months add_months(string start_date, int num_months) STRING 返回start_date在num_months个月之后的date。 current_date current_date() DATE 返回当前日期，如2016-07-04。 current_timestamp current_timestamp() TIMESTAMP 返回TIMESTAMP类型的时间戳。 date_add date_add(string startdate, int days) STRING或DATE 给定时间，在此基础上加上指定的时间段。 dateadd dateadd(string date, bigint delta, string datepart) STRING或DATE dateadd函数用于按照指定的单位datepart和幅度delta修改date的值。 date：必填。日期值，string类型。 使用的时间格式为yyyy-mm-dd hh:mi:ss，例如2021-08-28 00:00:00。 delta：必填。修改幅度，BIGINT类型。 datepart：必填。指定修改的单位，STRING类型常量。 date_sub date_sub(string startdate, int days) STRING 给定时间，在此基础上减去指定的时间段。 date_format date_format(string date, string format) STRING 用于将date按照format指定的格式转换为字符串。 datediff datediff(string date1, string date2) BIGINT datediff函数用于计算两个时间date1、date2的日期差值。 datediff1 datediff1(string date1, string date2, string datepart) BIGINT datediff1函数用于计算两个时间date1、date2的差值，将差值以指定的时间单位datepart表示。 datepart datepart（string date，string datepart） BIGINT 取日期中符合指定时间单位的字段值。 datetrunc datetrunc (string date, string datepart) STRING datetrunc函数用于计算将日期date按照datepart指定的时间单位进行截取后的日期值。 date：必填。格式为yyyy-mm-dd或yyyy-mm-dd hh:mi:ss。 datepart：必填。STRING类型常量。支持扩展的日期格式。 day/dayofmonth day(string date)、 dayofmonth(string date) INT 返回指定时间的日期。 from_unixtime from_unixtime(bigint unixtime) STRING 将时间戳转换为时间格式，格式为“yyyy-MM-dd HH:mm:ss”或“yyyyMMddHHmmss.uuuuuu”。 例如：select FROM_UNIXTIME(1608135036,'yyyy-MM-dd HH:mm:ss') from_utc_timestamp from_utc_timestamp(string timestamp, string timezone) TIMESTAMP 将UTC的时间戳转化为timezone所对应的时间戳。 getdate getdate() STRING 获取当前系统时间。 hour hour(string date) INT 返回指定时间的小时，范围为0到23。 isdate isdate(string date , string format) BOOLEAN date：必填。STRING类型。会隐式转换为STRING类型后参与运算。 format：必填。STRING类型常量，不支持日期扩展格式。如果format中出现多余的格式串，则只取第一个格式串对应的日期数值，其余的会被视为分隔符。例如isdate("1234-yyyy", "yyyy-yyyy")，会返回True。 last_day last_day(string date) DATE 返回date所在月份的最后一天，格式为yyyy-MM-dd，如2015-08-31。 lastday lastday(string date) STRING lastday函数用于返回date所在月的最后一天，返回值STRING类型，格式为yyyy-mm-dd hh:mi:ss。 minute minute(string date) INT 返回指定时间的分钟，范围为0到59。 month month(string date) INT 返回指定时间的月份，范围为1至12月。 months_between months_between(string date1, string date2) DOUBLE 返回date1与date2之间的月份差。 next_day next_day(string start_date, string day_of_week) DATE 返回start_date之后最接近day_of_week的日期，格式为yyyy-MM-dd，day_of_week表示一周内的星期（如Monday、FRIDAY）。 quarter quarter(string date) INT 返回该date/timestamp/string所在的季度，如quarter('2015-04-01')=2。 second second(string date) INT 返回指定时间的秒，范围为0到59。 to_char to_char(string date, string format) STRING 将日期按照指定格式转换为字符串。 to_date to_date(string timestamp) DATE 返回时间中的年月日，例如：to_date("1970-01-01 00:00:00") = "1970-01-01" to_date1 to_date1(string date, string format) STRING 返回STRING类型，格式为yyyy-mm-dd hh:mi:ss。date或format值为NULL时，返回NULL。 将指定格式的字符串转换为日期值。 to_utc_timestamp to_utc_timestamp(string timestamp, string timezone) TIMESTAMP 将timezone所对应的时间戳转换为UTC的时间戳。 trunc trunc(string date, string format) DTAE 将date按照特定的格式进行清零操作，支持格式为MONTH/MON/MM, YEAR/YYYY/YY，如trunc('2015-03-17', 'MM') = 2015-03-01。 unix_timestamp unix_timestamp(string timestamp, string pattern) BIGINT 如果不带参数的调用，返回一个Unix时间戳（从“1970-01-01 00:00:00”到现在的秒数）为无符号整数。 weekday weekday (string date) INT 返回日期值是当前周的第几天。 weekofyear weekofyear(string date) INT 返回指定日期是一年中的第几周，范围为0到53。 year year(string date) INT 返回指定日期中的年份。 父主题： 日期函数

示例代码 计算所有商品库存（items）和价格（price）的协方差。命令示例如下： select covar_pop(items，price) from warehouse; 返回结果如下： _c0 1.242355 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，并计算同组商品库存（items）和价格（price）的协方差。命令示例如下： select warehouseId, covar_pop(items，price) from warehourse group by warehouseId; 返回结果如下： warehouseId _c1 city1 1.13124 city2 1.13344 city3 1.53425

示例代码 计算所有商品库存（items）的 0.5 百分位。命令示例如下： select percentile(items,0.5) from warehouse; 返回结果如下： +------------+ | _c0 | +------------+ | 500.6 | +------------+ 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，并计算同组商品库存（items）的 0.5 百分位。命令示例如下： select warehouseId, percentile(items, 0.5) from warehourse group by warehouseId; 返回结果如下： +------------+------------+ | warehouseId| _c1 | +------------+------------+ | city1 | 499.6 | | city2 | 354.8 | | city3 | 565.7 | +------------+------------+

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 start_date 是 DATE 或 STRING 代表起始日期。 支持以下格式： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 days 是 BIGINT 代表需要增加天的数量。 days大于0，则为增加天数。 days小于0，则减去天数。 days等于0，即加0天，日期不变。 days值为NULL时，返回NULL。

示例代码 返回2023-03-01。加1天，结果超出当年2月份的最后1天，实际值为下个月的第1天。 select date_add('2023-02-28 00:00:00', 1); 返回2023-02-27。减1天。 select date_add(date '2023-02-28', -1); 返回2023-03-20。 select date_add('2023-02-28 00:00:00', 20); 假设当前时间为2023-08-14 16:00:00，返回2023-08-13。 select date_add(getdate(),-1); 返回NULL。 select date_add('2023-02-28 00:00:00', null);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 str 是 STRING 待搜索的目标字符串。 如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算，其他类型会返回报错。 substr 是 STRING 待匹配的子串。 如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算，其他类型会返回报错。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 source 是 STRING 待替换的字符 pattern 是 STRING STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。pattern为空串时返回报错。 replace_string 是 STRING 将匹配pattern的字符串替换后的字符串。 occurrence 否 BIGINT 必须大于等于1，表示将第occurrence次匹配的字符串替换为replace_string，为1时表示替换所有匹配的子串。为其他类型或小于1时，返回报错。默认值为1。

示例代码 返回 2222。 select regexp_replace1('abcd', '[a-z]', '2'); 返回 2bcd。 select regexp_replace1('abcd', '[a-z]', '2', 1); 返回 a2cd。 select regexp_replace1('abcd', '[a-z]', '2', 2); 返回 ab2d。 select regexp_replace1('abcd', '[a-z]', '2', 3); 返回 abc2。 select regexp_replace1('abcd', '[a-z]', '2', 4);

示例代码 计算所有商品库存（items）的方差。命令示例如下： select variance(items) from warehouse; --等效于如下语句。 select var_pop(items) from warehouse; 返回结果如下： _c0 203.42352 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，并计算同组商品库存（items）的方差。命令示例如下： select warehouseId, variance(items) from warehourse group by warehouseId; --等效于如下语句。 select warehouseId, var_pop(items) from warehourse group by warehouseId; 返回结果如下： 1 2 3 4 warehouseId _c1 city1 19.23124 city2 17.23344 city3 12.43425

语法格式 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE [IF NOT EXISTS] TABLE_NAME( FIELDNAME1 FIELDTYPE1, FIELDNAME2 FIELDTYPE2) USING CSS OPTIONS ( 'es.nodes'='xx', 'resource'='type_path_in_ CS S', 'pushdown'='true', 'strict'='false', 'batch.size.entries'= '1000', 'batch.size.bytes'= '1mb', 'es.nodes.wan.only' = 'true', 'es.mapping.id' = 'FIELDNAME');

示例 1 2 3 4 5 6 7 8 CREATE TABLE IF NOT EXISTS dli_to_css (doc_id String, name string, age int) USING CSS OPTIONS ( 'es.nodes' ='to-css-1174404703-LzwpJEyx.datasource.com:9200', 'resource' ='/dli_index/dli_type', 'pushdown' ='false', 'strict' ='true', 'es.nodes.wan.only' ='true', 'es.mapping.id' ='doc_id');

示例代码 计算所有商品的最低库存（items）。命令示例如下： select min(items) from warehouse; 返回结果如下： _c0 600 与group by配合使用，求每个仓库的最低库存。命令示例如下： select warehouseId, min(items) from warehouse group by warehouseId; 返回结果如下： warehouseId _c1 city1 15 city2 10 city3 19

示例代码 返回2023-08-12。减2天。 select date_sub('2023-08-14 17:00:00', 2); 返回2023-08-15。增1天。 select date_sub(date'2023-08-14', -1); 假设当前时间为2023-08-14 17:00:00，返回2023-08-13。 select date_sub(getdate(),1); 返回NULL。 select date_sub('2023-08-14 17:00:00', null);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 start_date 是 DATE 或 STRING 代表起始日期。 支持以下格式： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 days 是 BIGINT 代表需要减少的天数。 当days大于0时，从startdate中减少指定的天数。 当days小于0时，向startdate中增加指定的天数。 当days等于0时，即加0天，日期不变。 当days值为NULL时，返回NULL。

分析窗口函数概览 DLI所支持的分析窗口函数如分析窗口函数介绍所示。 表1 分析窗口函数介绍 函数 命令格式 返回值 功能简介 cume_dist cume_dist() DOUBLE 用于求累计分布，相当于求分区中大于等于或小于等于当前行的数据在分区中的占比。 first_value first_value(col) 参数的数据类型 返回结果集中某列第一条数据的值。 last_value last_value(col) 参数的数据类型 返回结果集中某列最后一条数据的值。 lag lag (col,n,DEFAULT) 参数的数据类型 用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）。 lead lead (col,n,DEFAULT) 参数的数据类型 用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）。 percent_rank percent_rank() DOUBLE 为窗口的ORDER BY子句所指定列中值的返回秩，但以介于0和1之间的小数形式表示，计算方法为 (RANK - 1)/(- 1)。 rank rank() INT 计算一个值在一组值中的排位。如果出现并列的情况，RANK函数会在排名序列中留出空位。 row_number row_number() over (order by col_1[,col_2 ...]) INT 为每一行指派一个唯一的编号。 父主题： 分析窗口函数

示例代码 例如 table1 是分区表，该表对应的分区为20120801和20120802，且都有数据。则以下语句中max_pt返回值为‘20120802’。DLI SQL语句会读出pt=‘20120802’分区下的数据。 命令示例如下。 select * from tablel where pt = max_pt('dbname.table1'); 等效于如下语句。 select * from table1 where pt = (select max(pt) from dbname.table1);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 source 是 STRING 待替换的字符。 pattern 是 STRING STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。pattern为空串时返回报错。 replace_string 是 STRING 将匹配pattern的字符串替换后的字符串。 occurrence 否 BIGINT 必须大于等于1，表示将第occurrence次匹配的字符串替换为replace_string，为1时表示替换所有匹配的子串。为其他类型或小于1时，返回报错。默认值为1。 说明： 该字段仅Spark3.1.1版本的功能适用。

说明 separator指语法中的分隔符或替代符，仅支持CHAR类型。 FIELDS TERMINATED BY指定表中字段级别的分隔符，仅支持CHAR类型。 COLLECTION ITEMS TERMINATED BY指定集合级别的分隔符，仅支持CHAR类型 MAP KEY TERMINATED BY仅用于指定MAP类型中的key与vaule之间的分隔符号，仅支持CHAR类型。 LINES TERMINATED BY指定行与行之间的分隔符，目前只支持“\n”。 使用NULL DEFINED AS子句可以指定NULL的格式。 SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]可利用以下语句实现NULL值转换为空字符串。 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' with serdeproperties('serialization.null.format' = '')

格式 ROW FORMAT DELIMITED [FIELDS TERMINATED BY separator] [COLLECTION ITEMS TERMINATED BY separator] [MAP KEYS TERMINATED BY separator] [LINES TERMINATED BY separator] [NULL DEFINED AS separator] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

示例代码 去除字符串 yxabcxx 的左右空格。命令示例如下。 返回字符串yxabcxx。 select trim(' yxabcxx '); 等效于如下语句。 select trim(both from ' yxabcxx '); select trim(from ' yxabcxx '); 去除字符串yxabcxx左右两端所有字符都在集合xy中的子串。 返回abc，只要左右两端遇到x或者y就会被去掉。 select trim('xy', 'yxabcxx'); 等效于如下语句。 select trim(both 'xy' from 'yxabcxx'); 输入参数为NULL。命令示例如下。 返回NULL。 select trim(null); select trim(null, 'yxabcxx');

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date1 是 DATE 或 STRING 计算两个时间date1、date2的日期差值中的被减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 date2 是 DATE 或 STRING 计算两个时间date1、date2的日期差值的减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3

示例代码 返回14400。 select datediff1('2023-06-30 00:00:00', '2023-06-20 00:00:00', 'mi'); 返回10。 select datediff1(date '2023-06-21', date '2023-06-11', 'dd'); 返回NULL。 select datediff1(date '2023-05-21', date '2023-05-10', null); 返回NULL。 select datediff1(date '2023-05-21', null, 'dd');

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date1 是 DATE 或 STRING 计算两个时间date1、date2的日期差值中的被减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 date2 是 DATE 或 STRING 计算两个时间date1、date2的日期差值的减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 datepart 是 STRING 代表需要返回的时间单位。 参数datepart支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。 yyyy代表年份。 MM代表月份。 dd代表天。 hh代表小时。 mi代表分钟。 ss代表秒。

示例代码 计算所有商品库存（items）的样本方差。命令示例如下： select var_samp(items) from warehouse; 返回结果如下： _c0 294.342355 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，并计算同组商品库存（items）的样本方差。命令示例如下： select warehouseId, var_samp(items) from warehourse group by warehouseId; 返回结果如下： warehouseId _c1 city1 18.23124 city2 16.23344 city3 11.43425

示例代码 计算所有商品库存（items）和价格（price）的样本协方差。命令示例如下： select covar_samp(items,price) from warehouse; 返回结果如下： _c0 1.242355 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，并计算同组商品库存（items）和价格（price）的样本协方差。命令示例如下： select warehouseId, covar_samp(items,price) from warehourse group by warehouseId; 返回结果如下： warehouseId _c1 city1 1.03124 city2 1.03344 city3 1.33425