实例介绍
【实例简介】
【实例截图】
【核心代码】
目录 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
好例子网口号：伸出你的我的手 — 分享！
相关软件
小贴士
感谢您为本站写下的评论，您的评论对其它用户来说具有重要的参考价值，所以请认真填写。
- 类似“顶”、“沙发”之类没有营养的文字，对勤劳贡献的楼主来说是令人沮丧的反馈信息。
- 相信您也不想看到一排文字/表情墙，所以请不要反馈意义不大的重复字符，也请尽量不要纯表情的回复。
- 提问之前请再仔细看一遍楼主的说明，或许是您遗漏了。
- 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦，又没人会搭理，于人于己都无利。
关于好例子网
本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享，对搜索内容的合法性不具有预见性、识别性、控制性，仅供学习研究，请务必在下载后24小时内给予删除，不得用于其他任何用途，否则后果自负。基于互联网的特殊性，平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查；无论平台是否已进行审查，用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场，基于网友分享，根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定，若资源存在侵权或相关问题请联系本站客服人员，点此联系我们。关于更多版权及免责申明参见 版权及免责申明
网友评论
我要评论