阿里云-ODPS SQL-日常开发日期、字符、数学运算、聚合函数函数使用技巧

1、背景

数据仓库是一个面向主题的、集成的、随时间变化的、信息本身相对稳定的数据集合。

数据仓库从Oracle单机、RAC)到MPP(Green plum)到Hadoop(Hive、Tez、Sprak)再到批流一体Flink/Blink、数据湖等SQL都是其主流的数据处理工具。
海量数据下的高效数据流转是数据分析朋友们必须直面的一个挑战。本文结合阿里自研的ODPS平台从自身工作出发总结日常使用过程中的SQL的一些日期、字符、数学运算、聚合函数函数使用技巧。

2、 数据处理

2.1、OLTP与OLAP概念

数据处理大致可以分成两大类

联机事务处理OLTPon-line transaction processing、联机分析处理OLAPOn-Line
Analytical Processing。

• OLTP是我们最为常见传统的关系型数据库的主要应用它主要用于基本的、日常的事务处理例如银行交易。
• OLAP是数据仓库系统的主要应用支持复杂的分析操作侧重决策支持并且提供直观易懂的查询结果。

2.2、OLTP与OLAP区别

• OLTP 系统强调数据库内存效率强调内存各种指标的命令率绑定变量并发操作等。
• OLAP 系统则强调数据分析强调SQL执行市场磁盘I/O分区等。
  

3、日常开发常用函数

3.1、日期函数

日期函数一般指日期与时间函数。 日期与时间函数是指在公式中用来分析和处理日期值和时间值的函数。也是我们经常遇到的函数之一。

GETDATE函数: 获取当前系统的时间。

SELECT GETDATE();
执行结果: 2023-01-15 20:33:23

DATEADD函数: 日期增减

SELECT DATEADD(GETDATE(),1,'dd');
执行结果: 2021-06-28 20:34:17
SELECT DATEADD(GETDATE(),-1,'dd');
执行结果: 2021-06-26 20:34:30

DATEDIFF函数: 计算返回时间差值

SELECT DATEDIFF('2023-01-15 20:33:23','2023-01-13 20:33:23','dd');
执行结果: 2
SELECT DATEDIFF('2023-01-15 20:33:23','2022-12-15 20:33:23','mm');
执行结果: 1
SELECT DATEDIFF('2023-01-15 20:33:23','2022-01-15 20:33:23','yyyy');
执行结果: 1
SELECT DATEDIFF('2023-01-15 20:33:23','2023-01-15 16:33:23','hh');
执行结果: 4
SELECT DATEDIFF('2023-01-15 20:33:23','2023-01-15 20:10:23','mi');
执行结果: 23
SELECT DATEDIFF('2023-01-15 20:33:23','2023-01-15 20:33:10','ss');
执行结果: 13

ADD_MONTHS函数:
计算月该函数将一个日期加上一指定的月份数所以日期中的日是不变的。然而如果开始日期是某月的最后一天那么结果将会调整以使返回值仍对应新的一月的最后一天。如果结果月份的天数比开始月份的天数少那么也会向回调整以适应有效日期。

SELECT ADD_MONTHS('2023-01-15',-1);
执行结果: 2022-12-15

DATEPART函数: 提取日期中part指定的部分

SELECT DATEPART('2023-01-15 20:33:23','yyyy');
执行结果: 2023
SELECT DATEPART('2023-01-15 20:33:23','mm');
执行结果: 1
SELECT DATEPART('2023-01-15 20:33:23','dd');
执行结果: 15
SELECT DATEPART('2023-01-15 20:33:23','hh');
执行结果: 20
SELECT DATEPART('2023-01-15 20:33:23','mi');
执行结果: 33
SELECT DATEPART('2023-01-15 20:33:23','ss');
执行结果: 23

DATETRUNC函数: 返回截取后的日期值

SELECT DATETRUNC('2023-01-15 20:33:23','yyyy');
执行结果: 2023-01-01 00:00:00

SELECT DATETRUNC('2023-01-15 20:33:23','mm');
执行结果: 2023-01-01 00:00:00

SELECT DATETRUNC('2023-01-15 20:33:23','dd');
执行结果: 2023-01-15 00:00:00

SELECT DATETRUNC('2023-01-15 20:33:23','hh');
执行结果: 2023-01-15 20:00:00

SELECT DATETRUNC('2023-01-15 20:33:23','mi');
执行结果: 2023-01-15 20:33:00

SELECT DATETRUNC('2023-01-15 20:33:23','ss');
执行结果: 2023-01-15 20:33:23

UNIX_TIMESTAMP函数: 将日期转化为整型的unix格式的日期时间值

SELECT UNIX_TIMESTAMP();
执行结果: 1624795597

FROM_UNIXTIME函数: 将数字型的unix 时间日期值转为DE日期值

SELECT FROM_UNIXTIME(1624795597);
执行结果: 2023-01-15 20:06:37

ISDATE函数: 判断一个日期字符串能否根据对应的格式串转换为一个日期值

SELECT ISDATE('2023-01-15 20:33:23','yyyy-mm-dd hh:mi:ss');
执行结果: true

LASTDAY函数: 取一个月的最后一天,截取到天

SELECT LASTDAY('2023-01-15 20:33:23');
执行结果: 2021-06-30 00:00:00

TO_DATE函数: 将一个字符串按照FORMAT指定的格式转成日期值

SELECT TO_DATE('2023-01-15 20:33:23','yyyy-mm-dd hh:mi:ss');
执行结果: 2023-01-15 20:33:23

TO_CHAR函数: 日期类型按照format指定的格式转成字符串

SELECT TO_CHAR(GETDATE(),'yyyy-mm-dd hh:mi:ss');
执行结果: 2023-01-15 20:17:26

WEEKDAY函数: 返回一个日期值是星期几

SELECT WEEKDAY(GETDATE());
执行结果: 6
星期一:0
星期二:1
星期三:2
星期四:3
星期五:4
星期六:5
星期天:6

WEEKOFYEAR函数: 返回一个日期位于那一年的第几周

SELECT WEEKOFYEAR(GETDATE());
执行结果: 25

3.2、数学运算函数

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

3.3、字符串处理函数

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('崇明在中国的上海','福建');

LENGTH函数: 返回一个字符串的长度

SELECT LENGTH('崇明在中国的上海');

LENGTHB函数: 返回一个字符串的以字节为单位的长度

SELECT LENGTHB('崇明在中国的上海');

MD5函数: 输入字符串的md5值

SELECT MD5('崇明在中国的上海');
执行结果: ac17f49e2f2e778634dee072e1dfc4ac

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 NOTE_INFO_TEST tt
WHERE tt.area = '340000';
执行结果: 2021002;2021004;2021001;2021005;2021003

3.4、聚合函数

AVG函数:主要用来计算包含在特定查询字段中的一组数值的算术平均值。

SELECT AVG(tt.money) AS RESULT FROM NOTE_INFO_TEST tt;

COUNT函数: 用来计算数据集中数值的个数.

SELECT COUNT(1) AS RESULT FROM NOTE_INFO_TEST tt;

MAX函数: 计算最大值

SELECT MAX(tt.money) AS RESULT FROM NOTE_INFO_TEST tt;

MEDIAN函数: 中位数,能够返回给定数值的中值中值是在一组数值中居于中间的数值如果参数集合中包含偶数个数字函数 MEDIAN 将返回位于中间的两个数的平均值。

SELECT MEDIAN(tt.money) AS RESULT FROM NOTE_INFO_TEST tt;

MIN函数: 计算最小值

SELECT MIN(tt.money) AS RESULT FROM NOTE_INFO_TEST tt;

SUM函数: 为值求和

SELECT SUM(tt.money) AS RESULT FROM NOTE_INFO_TEST tt;