这两天断断续续整理出使用阿里云ODPS常用到的SQL语句，主要用于支持简单地数据查询。阿里云的SQL语句类似hive sql，具体使用规则可参照文档MaxCompute快速入门，此外导出一万行受限制，需要单独配置。
对于我目前的工作来说，主要使用的是SQL的查询功能，它帮我分担了很多Excel数据处理工作。怎样快速学习SQL呢？我的经验是找实例多练习，遇见好的代码做好笔记和累积。
因时间关系，下面我只整理了一些常用函数的注意事项和对比总结。小伙伴们感兴趣可进一步了解，欢迎大家指正！
常规操作
1.${bdp.system.bizdate}
系统参数，格式为yyyymmdd，日常调度实例定时时间的前一天（年月日）。节点每天自动调度实例定时时间年月日减1天。
2.表的常见操作：创建临时表，删除表
--新建临时表a（方式一）
CREATE TABLE IF NOT EXISTS a --创建表名为a的临时表(可进一步设置生命周期)
(
id STRING COMMENT 'ID'
,name STRING COMMENT '姓名'
)
--本地上传记录，检验行数
SELECT COUNT(*) FROM a;
--新建临时表a（方式二）
CREATE TABLE IF NOT EXISTS a AS
SELECT b.id id
,b.name 姓名
FROM b
drop TABLE a; --完全清除
truncate TABLE a; --只清空数据，不清除格式
3.select
如果表中有分区，查询时必须指定分区
select *
from orders
where ds=MAX_PT('orders') --最大日期分区
--where ds='20200321' --指定日期分区
4.join
join，left outer join，right outer join，full outer join
select * from orders a
LEFT OUTER JOIN promoter p
ON a.promoter_id=p.id
5.group by与having
- 如果 SELECT 子句中包含聚合函数，则除了聚合函数，表达式中所有属性名都必须包含在 GROUP BY内；
- where过滤行，having过滤分组。where语句在数据分组前进行过滤，having在数据分组后进行过滤，where排除的行不包括在分组中
6.case when&if表达式（用于数据分类）
if表达式更简洁，只包含两种情况；case when...then..else...end可应用多情况条件下分类
7.union&union all（组合查询）
- 将两个select语句的结果作为一个整体显示出来,union all 不去重
- 注意必须包含相同列
- cast函数，用于数据类型转换,常配合union时使用
8.nvl和coalesce(处理为空值)
- nvl只适合于两个参数的，COALESCE适合于多个参数
- COALESCE里的所有参数类型必须保持一致，nvl可以不一致
9.with...as(写长查询时使用，相当于创建临时表)
具体可参考教程with as 使用
10.ROW_NUMBER() OVER(分组排序，非常好用！)
11.to_date,to_char(格式转换)
TO_CHAR(u.bind_time,'yyyymmdd') =20190812 //将日期型转换为字符串
to_date('20080718', 'yyyymmdd') = 2008-07-18 00:00:00 // 字符串转换成时间
11.decode(将查询结果翻译成其他值)
12.datediff(计算时间差)
13.时间戳转换
FROM_UNIXTIME是将时间戳转换为日期类型
14.字符串相关函数
| 函数 | 功能 | 说明 |
|---|---|---|
| CONCAT | 字符串拼接 | CONCAT('\t',goods_code) --将0开头编码显示 |
| replace | 字符串替换 | REPLACE(id,-1,0) --把-1替换为0 |
| length | 字符串长度 | 直接用，飒！ |
| split | 字符串拆分 | 指定分隔符进行切片，返回分割后的字符串列表。像excel中的分列功能 |
