华为云用户手册

示例代码 假设列int_test中的元素为1、2、3、4，类型为INT类型。 返回3。 select percentile_approx(int_test,0.7) FROM int_test; 返回3。 select percentile_approx(int_test,0.75) FROM int_test; 返回2。 select percentile_approx(int_test,0.5) FROM int_test; 返回[1,2,2,3,4]。 select percentile_approx (int_test,ARRAY(0.1,0.3,0.5,0.6,0.9)) FROM int_test;

示例代码 假设列int_test中的元素为1、2、3、4，类型为INT类型。 返回3.0999999999999996。 select percentile(int_test,0.7) FROM int_test; 返回3.997。 select percentile(int_test,0.999) FROM int_test; 返回2.5。 select percentile(int_test,0.5) FROM int_test; 返回[1.3,1.9,2.5,2.8,3.7]。 select percentile (int_test,ARRAY(0.1,0.3,0.5,0.6,0.9)) FROM int_test;

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

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

示例代码 示例数据 为便于理解函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表salary，并添加数据，命令示例如下： CREATE EXTERNAL TABLE salary ( dept STRING, -- 部⻔名称 userid string, -- 员⼯ID sal INT -- 薪⽔ ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile; 添加数据如下： d1,user1,1000 d1,user2,2000 d1,user3,3000 d2,user4,4000 d2,user5,5000 示例：计算员工薪水在部门内的百分比排名。 select dept, userid, sal, percent_rank() over(partition by dept order by sal) as pr2 from salary; -- 结果分析： d1 user1 1000 0.0 -- (1-1)/(3-1)=0.0 d1 user2 2000 0.5 -- (2-1)/(3-1)=0.5 d1 user3 3000 1.0 -- (3-1)/(3-1)=1.0 d2 user4 4000 0.0 -- (1-1)/(2-1)=0.0 d2 user5 5000 1.0 -- (2-1)/(2-1)=1.0

示例代码 返回example.com。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'HOST'); 返回/over/there/index.dtb。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'PATH'); 返回animal。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'QUERY', 'type'); 返回nose。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'REF'); 返回file。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'PROTOCOL'); 返回 username@example.com:8042。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'AUTHORITY'); 返回username。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'USERINFO');

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 urlString 是 STRING URL链接。无效URL链接会返回报错。 partToExtract 是 STRING 取值包含：HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE和USERINFO，不区分大小写。 keyToExtract 否 STRING 当part取值为QUERY时，根据key值取出对应的Value值。

示例代码 返回2023-08-22。 select next_day('2023-08-16','TU'); 返回2023-08-22。 select next_day('2023-08-16 10:54:00','TU'); 返回2023-08-23。 select next_day('2023-08-16 10:54:00','WE'); 返回NULL。 select next_day('20230816','TU'); 返回NULL。 select next_day('20230816 20:00:00',null);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 start_date 是 DATE 或 STRING 代表需要处理的日期。 start_date取值为STRING类型格式时，至少要包含yyyy-mm-dd且不含多余的字符串。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 day_of_week 是 STRING 一个星期前2个或3个字母，或者一个星期的全名。例如TU代表星期二。

示例代码 返回0.0563172。 select months_between('2023-08-16 10:54:00', '2023-08-14 17:00:00'); 返回0.06451613。 select months_between('2023-08-16','2023-08-14'); 返回NULL。 select months_between('2023-08-16',null);

返回值说明 返回DOUBLE类型的值。 date1、date2非DATE或STRING类型时，返回报错，错误信息：data type mismatch。 date1、date2为DATE或STRING类型，但不符合日期值的入参格式时，返回NULL。 当date1晚于date2时，返回值为正。当date2晚于date1时，返回值为负。 当date1和date2分别对应两个月的最后一天，返回整数月；否则计算方式为date1减去date2的天数除以31天。 date1或date2值为NULL时，返回NULL。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date1 是 DATE 或 STRING 代表被减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 date2 是 DATE 或 STRING 代表减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3

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

示例代码 例如 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);

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

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

示例代码 用字符串ZZ将字符串abcdefgh向左补足到10位。命令示例如下。 返回ZZabcdefgh。 select lpad('abcdefgh', 10, 'ZZ'); 用字符串ZZ将字符串abcdefgh向左补足到5位。命令示例如下。 返回abcde。 select lpad('abcdefgh', 5, 'ZZ'); length为0。命令示例如下。 返回空串。 select lpad('abcdefgh', 0, 'ZZ'); 任一输入参数为NULL。命令示例如下。 返回NULL。 select lpad(null ,0, 'ZZ');

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 base 是 DOUBLE、BIGINT、DECIMAL、STRING类型。 参数base的格式包括浮点数格式、整数格式、字符串格式。 参数base非DOUBLE类型时，会隐式转换为DOUBLE类型后参与运算。 a 是 DOUBLE、BIGINT、DECIMAL、STRING类型。 参数a的格式包括浮点数格式、整数格式、字符串格式。 参数a非DOUBLE类型时，会隐式转换为DOUBLE类型后参与运算。

示例代码 查找字符串ab在字符串abhiab中的位置。命令示例如下。 返回1。 select locate('ab', 'abhiab'); 返回5。 select locate('ab', 'abhiab', 2); 返回0。 select locate('ab', 'abhiab', null); 查找字符串hi在字符串hanmeimei and lilei中的位置。命令示例如下。 返回0。 select locate('hi', 'hanmeimei and lilei');

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

示例代码 示例数据 为便于理解函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表logs，并添加数据，命令示例如下： create table logs( cookieid string, createtime string, url string ) STORED AS parquet; 添加数据如下： cookie1 2015-04-10 10:00:02 url2 cookie1 2015-04-10 10:00:00 url1 cookie1 2015-04-10 10:03:04 url3 cookie1 2015-04-10 10:50:05 url6 cookie1 2015-04-10 11:00:00 url7 cookie1 2015-04-10 10:10:00 url4 cookie1 2015-04-10 10:50:01 url5 cookie2 2015-04-10 10:00:02 url22 cookie2 2015-04-10 10:00:00 url11 cookie2 2015-04-10 10:03:04 url33 cookie2 2015-04-10 10:50:05 url66 cookie2 2015-04-10 11:00:00 url77 cookie2 2015-04-10 10:10:00 url44 cookie2 2015-04-10 10:50:01 url55 将所有记录根据cookieid分组，并按createtime升序排列，返回窗口内往下第2行和第1行的值。命令示例如下 SELECT cookieid, createtime, url, LEAD(createtime, 2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time, LEAD(createtime, 1, '1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time FROM logs; -- 返回结果： cookieid createtime url next_2_time next_1_time cookie1 2015-04-10 10:00:00 url1 2015-04-10 10:03:04 2015-04-10 10:00:02 cookie1 2015-04-10 10:00:02 url2 2015-04-10 10:10:00 2015-04-10 10:03:04 cookie1 2015-04-10 10:03:04 url3 2015-04-10 10:50:01 2015-04-10 10:10:00 cookie1 2015-04-10 10:10:00 url4 2015-04-10 10:50:05 2015-04-10 10:50:01 cookie1 2015-04-10 10:50:01 url5 2015-04-10 11:00:00 2015-04-10 10:50:05 cookie1 2015-04-10 10:50:05 url6 NULL 2015-04-10 11:00:00 cookie1 2015-04-10 11:00:00 url7 NULL 1970-01-01 00:00:00 cookie2 2015-04-10 10:00:00 url11 2015-04-10 10:03:04 2015-04-10 10:00:02 cookie2 2015-04-10 10:00:02 url22 2015-04-10 10:10:00 2015-04-10 10:03:04 cookie2 2015-04-10 10:03:04 url33 2015-04-10 10:50:01 2015-04-10 10:10:00 cookie2 2015-04-10 10:10:00 url44 2015-04-10 10:50:05 2015-04-10 10:50:01 cookie2 2015-04-10 10:50:01 url55 2015-04-10 11:00:00 2015-04-10 10:50:05 cookie2 2015-04-10 10:50:05 url66 NULL 2015-04-10 11:00:00 cookie2 2015-04-10 11:00:00 url77 NULL 1970-01-01 00:00:00

参数说明 表1 参数说明 参数 是否必选 说明 expr 是 待计算返回结果的表达式。 offset 否 偏移量，BIGINT类型常量，取值大于等于0。值为0时表示当前行，为1时表示前一行，以此类推。默认值为1。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。 default 是 常量，默认值为NULL。 当offset指定的范围越界时的缺省值，需要与expr对应的数据类型相同。如果expr非常量，则基于当前行进行求值。 partition_clause 否 指定分区。分区列的值相同的行被视为在同一个窗口内。 orderby_clause 否 指定数据在一个窗口内如何排序。

参数说明 表1 参数说明 参数 是否必选 说明 expr 是 待计算返回结果的表达式。 ignore_nulls 否 BOOLEAN类型，表示是否忽略NULL值。默认值为False。 当参数的值为True时，返回窗口中第一条非NULL的值。 partition_clause 否 指定分区。分区列的值相同的行被视为在同一个窗口内。 orderby_clause 否 指定数据在一个窗口内如何排序。 frame_clause 否 用于确定数据边界。

示例代码 为便于理解函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表logs，并添加数据，命令示例如下： create table logs( cookieid string, createtime string, url string ) STORED AS parquet; 添加数据如下： cookie1 2015-04-10 10:00:02 url2 cookie1 2015-04-10 10:00:00 url1 cookie1 2015-04-10 10:03:04 url3 cookie1 2015-04-10 10:50:05 url6 cookie1 2015-04-10 11:00:00 url7 cookie1 2015-04-10 10:10:00 url4 cookie1 2015-04-10 10:50:01 url5 cookie2 2015-04-10 10:00:02 url22 cookie2 2015-04-10 10:00:00 url11 cookie2 2015-04-10 10:03:04 url33 cookie2 2015-04-10 10:50:05 url66 cookie2 2015-04-10 11:00:00 url77 cookie2 2015-04-10 10:10:00 url44 cookie2 2015-04-10 10:50:01 url55 示例：将所有记录根据cookieid分组，并按createtime升序排列，返回每组中的最后一行数据。命令示例如下 SELECT cookieid, createtime, url, LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last FROM logs; -- 返回结果： cookieid createtime url last cookie1 2015-04-10 10:00:00 url1 url1 cookie1 2015-04-10 10:00:02 url2 url2 cookie1 2015-04-10 10:03:04 url3 url3 cookie1 2015-04-10 10:10:00 url4 url4 cookie1 2015-04-10 10:50:01 url5 url5 cookie1 2015-04-10 10:50:05 url6 url6 cookie1 2015-04-10 11:00:00 url7 url7 cookie2 2015-04-10 10:00:00 url11 url11 cookie2 2015-04-10 10:00:02 url22 url22 cookie2 2015-04-10 10:03:04 url33 url33 cookie2 2015-04-10 10:10:00 url44 url44 cookie2 2015-04-10 10:50:01 url55 url55 cookie2 2015-04-10 10:50:05 url66 url66 cookie2 2015-04-10 11:00:00 url77 url77 截止到当前行的最后一个值，其实就是它本身。

示例代码 示例数据 为便于理解函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表logs，并添加数据，命令示例如下： create table logs( cookieid string, createtime string, url string ) STORED AS parquet; 添加数据如下： cookie1 2015-04-10 10:00:02 url2 cookie1 2015-04-10 10:00:00 url1 cookie1 2015-04-10 10:03:04 url3 cookie1 2015-04-10 10:50:05 url6 cookie1 2015-04-10 11:00:00 url7 cookie1 2015-04-10 10:10:00 url4 cookie1 2015-04-10 10:50:01 url5 cookie2 2015-04-10 10:00:02 url22 cookie2 2015-04-10 10:00:00 url11 cookie2 2015-04-10 10:03:04 url33 cookie2 2015-04-10 10:50:05 url66 cookie2 2015-04-10 11:00:00 url77 cookie2 2015-04-10 10:10:00 url44 cookie2 2015-04-10 10:50:01 url55 将所有记录根据cookieid分组，并按createtime升序排列，返回窗口内往上第2行的值。命令示例如下 示例1： SELECT cookieid, createtime, url, LAG(createtime, 2) OVER (PARTITION BY cookieid ORDER BY createtime) AS last_2_time FROM logs; -- 返回结果： cookieid createtime url last_2_time cookie1 2015-04-10 10:00:00 url1 NULL cookie1 2015-04-10 10:00:02 url2 NULL cookie1 2015-04-10 10:03:04 url3 2015-04-10 10:00:00 cookie1 2015-04-10 10:10:00 url4 2015-04-10 10:00:02 cookie1 2015-04-10 10:50:01 url5 2015-04-10 10:03:04 cookie1 2015-04-10 10:50:05 url6 2015-04-10 10:10:00 cookie1 2015-04-10 11:00:00 url7 2015-04-10 10:50:01 cookie2 2015-04-10 10:00:00 url11 NULL cookie2 2015-04-10 10:00:02 url22 NULL cookie2 2015-04-10 10:03:04 url33 2015-04-10 10:00:00 cookie2 2015-04-10 10:10:00 url44 2015-04-10 10:00:02 cookie2 2015-04-10 10:50:01 url55 2015-04-10 10:03:04 cookie2 2015-04-10 10:50:05 url66 2015-04-10 10:10:00 cookie2 2015-04-10 11:00:00 url77 2015-04-10 10:50:01 说明：因为没有设置默认值，当没有上两行时显示为NULL。 示例2： SELECT cookieid, createtime, url, LAG(createtime,1,'1970-01-01 00:00:00') OVER (PARTITION BY cookieid ORDER BY createtime) AS last_1_time FROM cookie4; -- 结果： cookieid createtime url last_1_time cookie1 2015-04-10 10:00:00 url1 1970-01-01 00:00:00 (显示默认值) cookie1 2015-04-10 10:00:02 url2 2015-04-10 10:00:00 cookie1 2015-04-10 10:03:04 url3 2015-04-10 10:00:02 cookie1 2015-04-10 10:10:00 url4 2015-04-10 10:03:04 cookie1 2015-04-10 10:50:01 url5 2015-04-10 10:10:00 cookie1 2015-04-10 10:50:05 url6 2015-04-10 10:50:01 cookie1 2015-04-10 11:00:00 url7 2015-04-10 10:50:05 cookie2 2015-04-10 10:00:00 url11 1970-01-01 00:00:00 (显示默认值) cookie2 2015-04-10 10:00:02 url22 2015-04-10 10:00:00 cookie2 2015-04-10 10:03:04 url33 2015-04-10 10:00:02 cookie2 2015-04-10 10:10:00 url44 2015-04-10 10:03:04 cookie2 2015-04-10 10:50:01 url55 2015-04-10 10:10:00 cookie2 2015-04-10 10:50:05 url66 2015-04-10 10:50:01 cookie2 2015-04-10 11:00:00 url77 2015-04-10 10:50:05

参数说明 表1 参数说明 参数 是否必选 说明 expr 是 待计算返回结果的表达式。 offset 否 偏移量，BIGINT类型常量，取值大于等于0。值为0时表示当前行，为1时表示前一行，以此类推。默认值为1。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。 default 是 常量，默认值为NULL。 当offset指定的范围越界时的缺省值，需要与expr对应的数据类型相同。如果expr非常量，则基于当前行进行求值。 partition_clause 否 指定分区。分区列的值相同的行被视为在同一个窗口内。 orderby_clause 否 指定数据在一个窗口内如何排序。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 str 是 STRING 待拆分的字符串。 split1、split2 否 STRING 用于作为分隔符的字符串，按照指定的两个分隔符拆分源字符串。如果表达式中没有指定这两项，默认split1为";"，split2为":"。当某个被split1拆分后的字符串中有多个split2时，返回结果未定义。 key 否 BIGINT 将字符串按照split1和split2拆分后，返回key值对应的Value。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date 是 DATE 或 STRING 代表需要判断的字符串。 如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，会隐式转换为STRING类型后参与运算 格式为任意字符串。 format 是 STRING 代表需要转换的目标日期格式。 STRING类型常量，不支持日期扩展格式。 format:格式为代表年月日时分秒的时间单位与任意字符的组合，其中： yyyy代表年份。 mm代表月份。 dd代表天。 hh代表小时。 mi代表分钟。 ss代表秒。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 str1 是 STRING 待搜索的目标字符串。 如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算，其他类型会返回报错。 str2 是 STRING 待匹配的子串。 如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算，其他类型会返回报错。 start_position 否 BIGINT 表示从str1的第几个字符开始搜索，默认起始位置是第一个字符位置1。 当start_position为负数时表示开始位置是从字符串的结尾往前倒数，最后一个字符是-1，依次往前倒数。 nth_appearance 否 BIGINT 表示str2在str1中第nth_appearance次匹配的位置。 如果nth_appearance为其他类型或小于等于0，则返回报错。

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