赞
踩
场景:使用MaxCompute常用函数
记录:NO.251
MaxCompute是一项大数据计算服务,它能提供快速、完全托管的PB级数据仓库解决方案。小北刚使用这个产品时,那会叫做ODPS(Open Data Processing Service)。为此在标题中添加了ODPS常用函数。
单行函数: 只处理单个行,并且为每行返回一个结果。
聚合函数: 同时对一组数据进行操作,返回一行结果。
窗口函数: 具有分组和排序的功能,返回结果不减少原表的行数。
语法:<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)。
一、日期函数
GETDATE函数: 获取当前系统时间
SELECT GETDATE();
执行结果: 2021-06-27 19:33:23
DATEADD函数: 日期增减
SELECT DATEADD(GETDATE(),1,'dd');
执行结果: 2021-06-28 19:34:17
SELECT DATEADD(GETDATE(),-1,'dd');
执行结果: 2021-06-26 19:34:30
DATEDIFF函数: 计算返回时间差值
SELECT DATEDIFF('2021-06-27 19:33:23','2021-06-25 19:33:23','dd');
执行结果: 2
SELECT DATEDIFF('2021-06-27 19:33:23','2021-05-27 19:33:23','mm');
执行结果: 1
SELECT DATEDIFF('2021-06-27 19:33:23','2020-06-27 19:33:23','yyyy');
执行结果: 1
SELECT DATEDIFF('2021-06-27 19:33:23','2021-06-27 15:33:23','hh');
执行结果: 4
SELECT DATEDIFF('2021-06-27 19:33:23','2021-06-27 19:10:23','mi');
执行结果: 23
SELECT DATEDIFF('2021-06-27 19:33:23','2021-06-27 19:33:10','ss');
执行结果: 13
ADD_MONTHS函数: 计算月
SELECT ADD_MONTHS('2021-6-27',-1);
执行结果: 2021-05-27
DATEPART函数: 提取日期中part指定的部分
SELECT DATEPART('2021-06-27 19:33:23','yyyy');
执行结果: 2021
SELECT DATEPART('2021-06-27 19:33:23','mm');
执行结果: 6
SELECT DATEPART('2021-06-27 19:33:23','dd');
执行结果: 27
SELECT DATEPART('2021-06-27 19:33:23','hh');
执行结果: 19
SELECT DATEPART('2021-06-27 19:33:23','mi');
执行结果: 33
SELECT DATEPART('2021-06-27 19:33:23','ss');
执行结果: 23
DATETRUNC函数: 返回截取后的日期值
SELECT DATETRUNC('2021-06-27 19:33:23','yyyy');
执行结果: 2021-01-01 00:00:00
SELECT DATETRUNC('2021-06-27 19:33:23','mm');
执行结果: 2021-06-01 00:00:00
SELECT DATETRUNC('2021-06-27 19:33:23','dd');
执行结果: 2021-06-27 00:00:00
SELECT DATETRUNC('2021-06-27 19:33:23','hh');
执行结果: 2021-06-27 19:00:00
SELECT DATETRUNC('2021-06-27 19:33:23','mi');
执行结果: 2021-06-27 19:33:00
SELECT DATETRUNC('2021-06-27 19:33:23','ss');
执行结果: 2021-06-27 19:33:23
UNIX_TIMESTAMP函数: 将日期转化为整型的unix格式的日期时间值
SELECT UNIX_TIMESTAMP();
执行结果: 1624795597
FROM_UNIXTIME函数: 将数字型的unix 时间日期值转为DE日期值
SELECT FROM_UNIXTIME(1624795597);
执行结果: 2021-06-27 20:06:37
ISDATE函数: 判断一个日期字符串能否根据对应的格式串转换为一个日期值
SELECT ISDATE('2021-06-27 19:33:23','yyyy-mm-dd hh:mi:ss');
执行结果: true
LASTDAY函数: 取一个月的最后一天,截取到天
SELECT LASTDAY('2021-06-27 19:33:23');
执行结果: 2021-06-30 00:00:00
TO_DATE函数: 将一个字符串按照FORMAT指定的格式转成日期值
SELECT TO_DATE('2021-06-27 19:33:23','yyyy-mm-dd hh:mi:ss');
执行结果: 2021-06-27 20:13:41
TO_CHAR函数: 日期类型按照format指定的格式转成字符串
SELECT TO_CHAR(GETDATE(),'yyyy-mm-dd hh:mi:ss');
执行结果: 2021-06-27 20:17:26
WEEKDAY函数: 返回一个日期值是星期几
SELECT WEEKDAY(GETDATE());
执行结果: 6
星期一:0
星期二:1
星期三:2
星期四:3
星期五:4
星期六:5
星期天:6
WEEKOFYEAR函数: 返回一个日期位于那一年的第几周
SELECT WEEKOFYEAR(GETDATE());
执行结果: 25
二、数学运算函数
ABS函数: 绝对值函数
SELECT ABS(-1.567);
执行结果: 1.567
ACOS函数: 反余弦函数
SELECT ACOS(0.789);
执行结果: 0.6616166568777674
ASIN函数: 反正弦函数
SELECT ASIN(0.789);
执行结果: 0.9091796699171293
ATAN函数: 反正切函数
SELECT ATAN(0.789);
执行结果: 0.6679975427240679
CONV函数: 进制转换函数
SELECT CONV(100,10,2);
执行结果: 1100100
SELECT CONV(10101100,2,10);
执行结果: 172
COS函数: 余弦函数,输入为弧度值
SELECT COS(0.789);
执行结果: 0.7045553168836329
COSH函数: 双曲余弦函数
SELECT COSH(0.789);
执行结果: 1.3277464991182242
COT函数: 余切函数
SELECT COT(0.789);
执行结果: 0.992822149200644
EXP函数: 指数函数(以e=2.718281828459045为底数)
SELECT EXP(3);
执行结果: 20.085536923187668
RAND函数: 随机数函数,返回double类型的随机数,返回值区间是的0~1
SELECT RAND(99);
执行结果: 0.9610280566667337
ROUND函数: 四舍五入到指定小数点位置
SELECT ROUND(0.789,2);
执行结果: 0.79
FLOOR函数: 向下取整
SELECT FLOOR(9.789);
执行结果: 9
SIN函数: 正弦函数
SELECT SIN(0.789);
执行结果: 0.7096490720426566
SINH函数: 双曲正弦函数
SELECT SINH(0.789);
执行结果: 0.8734476320425288
SQRT函数: 计算平方根
SELECT SQRT(9);
执行结果: 3.0
TAN函数: 正切函数
SELECT TAN(0.789);
执行结果: 1.0072297448290564
TANH函数: 双曲正切函数
SELECT TANH(0.789);
执行结果: 0.65784216537012
TRUNC函数: 截取函数,将输入值截取到指定小数点位置
SELECT TRUNC(987.789,1);
执行结果: 987.7
LN函数: 返回number的自然对数
SELECT LN(20.085536923187668);
执行结果: 3.0
LOG函数: 对数函数
SELECT LOG(2,3);
执行结果: SELECT LOG(2,3);
POW函数: 返回x的y次方,即x^y
SELECT POW(2,3);
执行结果: 8.0
CEIL函数: 向上取整
SELECT CEIL(9.789);
执行结果: 10
三、字符串处理函数
CHR函数: 将给定ASCII转换成字符,参数范围是0~255
SELECT CHR(90);
执行结果: Z
CONCAT函数: 字符串连接函数,将参数中的所有字符串连接在一起的结果
SELECT CONCAT('厦门在','福建');
执行结果: 厦门在福建
IN函数: 查看key是否在给定列表中出现
SELECT 99 IN(9,99,999,9999,99999);
执行结果: true
INSTR函数: 计算一个子串在字符串中的位置
SELECT INSTR('厦门在中国的福建','福建');
执行结果: 7
LENGTH函数: 返回一个字符串的长度
SELECT LENGTH('厦门在中国的福建');
执行结果: 8
LENGTHB函数: 返回一个字符串的以字节为单位的长度
SELECT LENGTHB('厦门在中国的福建');
执行结果: 24
MD5函数: 输入字符串的md5值
SELECT MD5('厦门在中国的福建');
执行结果: ebf6fc3da626d922aa687c899232ab87
SPLIT_PART函数: 拆分字符串,返回指定的部分
SELECT SPLIT_PART('福州;厦门;泉州',';',2);
执行结果: 厦门
TO_CHAR函数: 返回对应值的字符串
SELECT TO_CHAR(99.98);
执行结果: 99.98
SUBSTR函数: 返回字符串string1从start_position开始长度为length的子串
SELECT SUBSTR('厦门在中国的福建',7,2);
执行结果: 福建
TOLOWER函数: 字符串转换为小写,输入字符串对应的小写字符串
SELECT TOLOWER('abcDEFGH');
执行结果: abcdefgh
TOUPPER函数: 字符串转换为大写,输入字符串对应的大写字符串
SELECT TOUPPER('abcDEFGH');
执行结果: ABCDEFGH
TRIM函数: 截取两端空格,将输入字符串去除左右空格
SELECT TRIM(' 厦门在中国的福建 ');
执行结果: 厦门在中国的福建
WM_CONCAT函数: 用指定的spearator做分隔符,做字符串类型的连接操作
- SELECT WM_CONCAT(';',tt.id) as result
- FROM info_test tt
- WHERE tt.area = '厦门';
执行结果: 2021002;2021004;2021001;2021005;2021003
四、聚合函数
AVG函数: 计算平均值
SELECT AVG(tt.money) AS RESULT FROM INFO_TEST tt;
执行结果: 33.03333333333333
COUNT函数: 计算数目
SELECT COUNT(1) AS RESULT FROM INFO_TEST tt;
执行结果: 15
MAX函数: 计算最大值
SELECT MAX(tt.money) AS RESULT FROM INFO_TEST tt;
执行结果: 55.5
MEDIAN函数: 中位数
SELECT MEDIAN(tt.money) AS RESULT FROM INFO_TEST tt;
执行结果: 32.5
MIN函数: 计算最小值
SELECT MIN(tt.money) AS RESULT FROM INFO_TEST tt;
执行结果: 10.5
STDDEV函数: 总体标准差
SELECT STDDEV(tt.money) AS RESULT FROM INFO_TEST tt;
执行结果: 14.582485689651435
STDDEV_SAMP函数: 样本标准差
SELECT STDDEV_SAMP(tt.money) AS RESULT FROM INFO_TEST tt;
执行结果: 15.094306716046791
SUM函数: 求和
SELECT SUM(tt.money) AS RESULT FROM INFO_TEST tt;
执行结果: 495.5
五、窗口函数
COUNT函数: 计算条数
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,COUNT(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
SUM函数: 求和(同一个分组内,当前行和排在当前行之前的行累计求和)
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,SUM(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
AVG函数: 求平均数
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,AVG(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
MAX函数: 计算最大值
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,MAX(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
MIN函数: 最小值
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,MIN(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
MEDIAN函数: 计算中位数
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,MEDIAN(tt.money) OVER( PARTITION BY tt.area )
- FROM info_test tt;
执行结果:
RANK函数: 计算排名
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,RANK() OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
DENSE_RANK函数: 连续排名
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,DENSE_RANK() OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
PERCENT_RANK函数: 计算一组数据中某行的相对排名
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,PERCENT_RANK() OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
ROW_NUMBER函数: 返回行号,从1开始,(可以做去重,根据行号)
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,ROW_NUMBER() OVER( PARTITION BY tt.id ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
STDDEV函数: 总体标准差
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,STDDEV(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
STDDEV_SAMP函数: 样本标准差
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,STDDEV_SAMP(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
LAG函数: 按偏移量取当前行之前第几行的值
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,LAG(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
LEAD函数: 按偏移量取当前行之后第几行的值
- SELECT tt.area
- ,tt.id
- ,tt.money
- ,LEAD(tt.money) OVER( PARTITION BY tt.area ORDER BY tt.money DESC)
- FROM info_test tt;
执行结果:
六、其它函数
CAST函数: 将表达式的结果转换成目标类型
SELECT CAST('99.98' AS BIGINT);
执行结果: 99
COALESCE函数: 返回列表中第一个非null的值,如果列表中所有的值都是null则返回null
SELECT COALESCE(NULL,NULL,'12','34',NULL,'56');
执行结果: 12
DECODE函数: 分支选择的功能
SELECT DECODE(99, 1, '一', 9, '九', 99, '九十九', '大于一百');
执行结果: 九十九
GREATEST函数: 返回输入参数中最大的一个
SELECT GREATEST(9,99,999,9999,99999);
执行结果: 99999
ORDINAL函数: 将输入变量按从小到大排序后,返回nth指定的位置的值
SELECT ORDINAL(3,9999,9,99,999,99999);
执行结果: 999
LEAST函数: 返回输入参数中最小的一个
SELECT LEAST(9,99,999,9999,99999);
执行结果: 9
UNIQUE_ID函数: 返回一个随机的唯一id,32位字符串
SELECT UNIQUE_ID();
执行结果: 9989cef2-97af-46e8-91ae-287812b149b7_0
UUID函数: 返回一个随机的唯一id,32位字符串
SELECT UUID();
执行结果: b0796115-3011-451f-8a6f-4ad4740d7532
七、附件
建表语句:
- CREATE TABLE IF NOT EXISTS info_test
- (
- id BIGINT COMMENT '主键标识',
- money DOUBLE COMMENT '金额',
- area STRING COMMENT '城市'
- )
- COMMENT '验证表';
数据:
- INSERT INTO info_test(id,money,area) values(2021001,10.5,'厦门');
- INSERT INTO info_test(id,money,area) values(2021003,20.5,'厦门');
- INSERT INTO info_test(id,money,area) values(2021004,30.5,'厦门');
- INSERT INTO info_test(id,money,area) values(2021002,40.5,'厦门');
- INSERT INTO info_test(id,money,area) values(2021005,50.5,'厦门');
- INSERT INTO info_test(id,money,area) values(2021006,15.5,'福州');
- INSERT INTO info_test(id,money,area) values(2021009,25.5,'福州');
- INSERT INTO info_test(id,money,area) values(2021007,35.5,'福州');
- INSERT INTO info_test(id,money,area) values(2021010,45.5,'福州');
- INSERT INTO info_test(id,money,area) values(2021009,55.5,'福州');
- INSERT INTO info_test(id,money,area) values(2021013,55.5,'福州');
- INSERT INTO info_test(id,money,area) values(2021012,12.5,'泉州');
- INSERT INTO info_test(id,money,area) values(2021011,22.5,'泉州');
- INSERT INTO info_test(id,money,area) values(2021014,32.5,'泉州');
- INSERT INTO info_test(id,money,area) values(2021015,42.5,'泉州');
以上,感谢。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。