基本信息
源码名称：阿里云_大数据计算服务_MaxCompute（原ODPS）_SQL使用指南.pdf
源码大小：2.57M
文件格式：.pdf
开发语言：SQL
更新时间：2021-01-21
   友情提示：（无需注册或充值，赞助后即可获取资源下载链接）

     嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：813200300

本次赞助数额为： 2 元　
   源码介绍 

目录 I

1 概要.........................................................................................................................1
1.1 使用场景...................................................................................................................1
1.2 保留字.......................................................................................................................1
1.3 分区表.......................................................................................................................2
1.4 类型转换...................................................................................................................3
1.4.1 显式类型转换................................................................................................................3
1.4.2 隐式类型转换及其作用域........................................................................................4
1.4.3 SQL 内建函数................................................................................................................8
1.4.4 CASE WHEN....................................................................................................................8
1.4.5 分区列...............................................................................................................................9
1.4.6 UNION ALL......................................................................................................................9
1.4.7 String 类型与 Datetime 类型之间的转换 .........................................................9
2 运算符 .................................................................................................................11
2.1 关系操作符 ............................................................................................................11
2.2 算术操作符 ............................................................................................................13
2.3 位运算操作符........................................................................................................14
2.4 逻辑操作符 ............................................................................................................14
3 DDL 语句............................................................................................................16
3.1 表操作.....................................................................................................................16
3.1.1 创建表(CREATE TABLE)........................................................................................ 16
3.1.2 删除表(DROP TABLE)............................................................................................. 19
目录

3.1.3 重命名表(RENAME TABLE).................................................................................. 20
3.1.4 修改表的注释............................................................................................................. 21
3.1.5 修改表的生命周期属性.......................................................................................... 21
3.1.6 禁止生命周期............................................................................................................. 22
3.1.7 修改表的修改时间.................................................................................................... 23
3.1.8 清空非分区表里的数据.......................................................................................... 23
3.1.9 备份表的数据............................................................................................................. 24
3.2 视图操作.................................................................................................................26
3.2.1 创建视图(CREATE VIEW)...................................................................................... 26
3.2.2 删除视图(DROP VIEW).......................................................................................... 27
3.2.3 重命名视图(RENAME VIEW)............................................................................... 28
3.3 列及分区操作........................................................................................................28
3.3.1 添加分区(ADD PARTITION)................................................................................. 28
3.3.2 删除分区(DROP PARTITION)............................................................................... 30
3.3.3 添加列............................................................................................................................ 30
3.3.4 修改列名....................................................................................................................... 31
3.3.5 修改列、分区注释.................................................................................................... 31
3.3.6 修改分区的修改时间............................................................................................... 31
3.3.7 修改分区值.................................................................................................................. 32
4 DML 语句 ...........................................................................................................33
4.1 Insert 语句.................................................................................................................33
目录

4.1.1 更新表中的数据(INSERT OVERWRITE/INTO)............................................. 33
4.1.2 多路输出(MULTI INSERT)..................................................................................... 35
4.1.3 输出到动态分区(DYNAMIC PARTITION)........................................................ 37
4.2 Select 语句.................................................................................................................39
4.2.1 SELECT 操作............................................................................................................... 39
4.2.2 子查询............................................................................................................................ 45
4.3 Union 语句.................................................................................................................45
4.3.1 UNION ALL................................................................................................................... 45
4.4 Join 语句.....................................................................................................................47
4.4.1 JOIN 操作...................................................................................................................... 47
4.4.2 MAPJOIN HINT........................................................................................................... 49
4.5 Explain 语句..............................................................................................................50
5 内建函数.............................................................................................................55
5.1 数学运算函数........................................................................................................55
5.1.1 ABS.................................................................................................................................. 55
5.1.2 ACOS............................................................................................................................... 56
5.1.3 ASIN................................................................................................................................ 57
5.1.4 ATAN............................................................................................................................... 58
5.1.5 CEIL................................................................................................................................. 58
5.1.6 CONV.............................................................................................................................. 59
5.1.7 COS.................................................................................................................................. 60
目录

5.1.8 COSH............................................................................................................................... 61
5.1.9 COT.................................................................................................................................. 61
5.1.10 EXP............................................................................................................................. 62
5.1.11 FLOOR....................................................................................................................... 62
5.1.12 LN............................................................................................................................... 63
5.1.13 LOG ............................................................................................................................ 63
5.1.14 POW........................................................................................................................... 64
5.1.15 RAND ........................................................................................................................ 65
5.1.16 ROUND..................................................................................................................... 65
5.1.17 SIN.............................................................................................................................. 67
5.1.18 SINH.......................................................................................................................... 67
5.1.19 SQRT.......................................................................................................................... 68
5.1.20 TAN............................................................................................................................ 68
5.1.21 TANH......................................................................................................................... 69
5.1.22 TRUNC...................................................................................................................... 69
5.2 字符串处理函数....................................................................................................71
5.2.1 CHAR_MATCHCOUNT.............................................................................................. 71
5.2.2 CHR................................................................................................................................. 71
5.2.3 CONCAT......................................................................................................................... 72
5.2.4 GET_JSON_OBJECT.................................................................................................... 73
5.2.5 INSTR............................................................................................................................. 75
5.2.6 IS_ENCODING.............................................................................................................. 76
目录

5.2.7 KEYVALUE.................................................................................................................... 77
5.2.8 LENGTH......................................................................................................................... 78
5.2.9 LENGTHB...................................................................................................................... 79
5.2.10 MD5........................................................................................................................... 80
5.2.11 PARSE_URL............................................................................................................. 80
5.2.12 REGEXP_EXTRACT .............................................................................................. 82
5.2.13 REGEXP_INSTR..................................................................................................... 83
5.2.14 REGEXP_REPLACE............................................................................................... 84
5.2.15 REGEXP_SUBSTR.................................................................................................. 86
5.2.16 REGEXP_COUNT................................................................................................... 87
5.2.17 SPLIT_PART............................................................................................................ 88
5.2.18 SUBSTR.................................................................................................................... 89
5.2.19 TOLOWER............................................................................................................... 90
5.2.20 TOUPPER................................................................................................................. 91
5.2.21 TO_CHAR................................................................................................................. 91
5.2.22 TRIM.......................................................................................................................... 92
5.2.23 URL_ENCODE......................................................................................................... 93
5.2.24 URL_DECODE......................................................................................................... 94
5.3 日期处理函数........................................................................................................95
5.3.1 DATEADD ..................................................................................................................... 95
5.3.2 DATEDIFF..................................................................................................................... 98
5.3.3 DATEPART.................................................................................................................... 99
目录

5.3.4 DATETRUNC................................................................................................................ 99
5.3.5 FROM_UNIXTIME....................................................................................................100
5.3.6 GETDATE....................................................................................................................101
5.3.7 ISDATE.........................................................................................................................101
5.3.8 LASTDAY.....................................................................................................................102
5.3.9 TO_DATE.....................................................................................................................102
5.3.10 TO_CHAR...............................................................................................................104
5.3.11 UNIX_TIMESTAMP.............................................................................................105
5.3.12 WEEKDAY .............................................................................................................105
5.3.13 WEEKOFYEAR.....................................................................................................106
5.4 窗口函数..............................................................................................................106
5.4.1 COUNT.........................................................................................................................107
5.4.2 AVG................................................................................................................................109
5.4.3 MAX...............................................................................................................................110
5.4.4 MIN ...............................................................................................................................111
5.4.5 MEDIAN ......................................................................................................................112
5.4.6 STDDEV.......................................................................................................................112
5.4.7 STDDEV_SAMP.........................................................................................................113
5.4.8 SUM...............................................................................................................................114
5.4.9 DENSE_RANK............................................................................................................115
5.4.10 RANK.......................................................................................................................115
5.4.11 LAG ..........................................................................................................................116
目录

5.4.12 LEAD .......................................................................................................................117
5.4.13 PERCENT_RANK.................................................................................................117
5.4.14 ROW_NUMBER....................................................................................................118
5.4.15 CLUSTER_SAMPLE ............................................................................................119
5.5 聚合函数..............................................................................................................121
5.5.1 COUNT.........................................................................................................................122
5.5.2 AVG................................................................................................................................124
5.5.3 MAX...............................................................................................................................126
5.5.4 MIN ...............................................................................................................................127
5.5.5 MEDIAN ......................................................................................................................128
5.5.6 STDDEV.......................................................................................................................128
5.5.7 STDDEV_SAMP.........................................................................................................129
5.5.8 SUM...............................................................................................................................129
5.5.9 WM_CONCAT.............................................................................................................130
5.6 其他函数..............................................................................................................131
5.6.1 ARRAY..........................................................................................................................131
5.6.2 ARRAY_CONTAINS ..................................................................................................132
5.6.3 CAST .............................................................................................................................132
5.6.4 COALESCE ..................................................................................................................133
5.6.5 DECODE ......................................................................................................................133
5.6.6 EXPLODE ....................................................................................................................135
5.6.7 GET_IDCARD_AGE...................................................................................................136
目录

5.6.8 GET_IDCARD_BIRTHDAY .....................................................................................137
5.6.9 GET_IDCARD_SEX ...................................................................................................137
5.6.10 GREATEST.............................................................................................................138
5.6.11 INDEX .....................................................................................................................138
5.6.12 MAX_PT..................................................................................................................139
5.6.13 ORDINAL ...............................................................................................................140
5.6.14 LEAST .....................................................................................................................141
5.6.15 SIZE..........................................................................................................................141
5.6.16 SPLIT.......................................................................................................................142
5.6.17 TRANS_ARRAY....................................................................................................143
5.6.18 TRANS_COLS........................................................................................................145
5.6.19 UNIQUE_ID ...........................................................................................................147
5.6.20 UUID........................................................................................................................147
5.6.21 SAMPLE..................................................................................................................147
5.6.22 CASE WHEN 表达式.........................................................................................148
5.7 分词函数..............................................................................................................150
5.7.1 UNIFORM....................................................................................................................150
5.7.2 UDF_NORMALIZE....................................................................................................150
5.7.3 SEGMENT ...................................................................................................................151
5.7.4 SYNONYM...................................................................................................................151
6 UDF ....................................................................................................................152
目录

6.1 概要.......................................................................................................................152
6.1.1 参数与返回值类型..................................................................................................153
6.2 UDF............................................................................................................................154
6.3 UDAF.........................................................................................................................154
6.4 UDTF.........................................................................................................................160
6.4.1 概要说明.....................................................................................................................160
6.4.2 UDTF 使用说明........................................................................................................163
6.5 Python UDF ............................................................................................................168
6.5.1 受限环境.....................................................................................................................168
6.5.2 第三方库.....................................................................................................................171
6.5.3 参数与返回值类型..................................................................................................171
6.5.4 UDF ...............................................................................................................................173
6.5.5 UDAF ............................................................................................................................173
6.5.6 UDTF.............................................................................................................................175
6.5.7 引用资源.....................................................................................................................176
7 附录....................................................................................................................179
7.1 转义字符..............................................................................................................179
7.2 LIKE 字符匹配........................................................................................................180
7.3 正则表达式规范.................................................................................................180
7.4 保留字..................................................................................................................184