SQL和HIVE语句的查询顺序、执行顺序以及一些实用小tips

查询顺序：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

SQL的执行顺序：
 

FROM ... JOIN ... ON ... WHERE ... GROUP BY ... AVG SUM 等聚合函数 ... HAVING ... 计算所有表达式 ... SELECT ... DISTINCT ... ORDER BY ... LIMIT ...

HIVE的执行顺序：

FROM ... WHERE ... SELECT ... GROUP BY ... HAVING ... ORDER BY ...

Hive的执行顺序也是MapReduce的执行顺序：

map阶段：

1. 执行from加载，进行表的查找与加载
2. 执行where过滤，进行条件过滤与筛选
3. 执行select查询：进行输出项的筛选
4. 执行group by分组：描述了分组后需要计算的函数
5. map端文件合并：map端本地溢出写文件的合并操作，每个map最终形成一个临时文件。 然后按列映射到对应的reduceReduce阶段：

reduce阶段：

1. group by：对map端发送过来的数据进行分组并进行计算。
2. select：最后过滤列用于输出结果
3. limit排序后进行结果输出到HDFS文件

---

SQL里使用正则表达式：

regexp_like(匹配)

regexp_instr(包含)

regexp_replace(替换)

regexp_substr(提取)

---

SQL中的函数：

1. 算法函数
2. 字符串函数
3. 日期函数
4. 转换函数

算术函数：

1. abs()：绝对值
2. mod()：取余
3. round()：四舍五入，2个参数：字段名,小数位

字符串函数：

1. concat()：合并多个字符串
2. length()：计算字段长度，汉字计3个，其他计1个
3. char_length()：计算字段长度，都计1个
4. lower()：小写
5. upper()：大写
6. replace()：3个参数：要替换的，被查找替换的，替换成哪个字符串
7. substring()：截取字符串，3个参数：被截取的字符串，开始截取的位置，想截取的字符串长度

SQL中的日期函数：

注意：date日期的格式必须是 yyyy-mm-dd，有的时候日期也会存储为字符串格式。

1. current_date()：当前日期
2. current_time()：当前时间
3. current_timestamp()：日期 + 时间
4. extract()：抽取具体的年月日
5. date()
6. year()
7. month()
8. day()
9. hour()
10. minute()
11. second()

SQL中的转换函数：

1. cast()：表达式通过 as 分为2个参数：原始数据和目标数据类型
2. coalesce()：返回第一个非空数值

注：presto中的转换函数：

1. cast(value as type)
2. try_cast(value as type) ：与cast相似，区别是转换失败返回null

例如：

select cast(123.456 as int)

select coalesce(null,1,2,3)

---

Mysql：

• 在Linux下严格区分大小写
• 在Windows下不区分

Hive：

• 不区分大小写

---

SQL99中的连接查询：

1. 交叉连接(cross join)

在SQL92中为笛卡尔乘积

select * from table1 cross join table2 

2. 自然连接(natural join)

在SQL92中为等值连接，查询两张表中所有相同字段，然后进行连接

select order_id,user_id from table1 natural join table2 

3. ON连接(join on)

on可以连接等值或者非等值的

(1) 等值的：

select order_id,user_id from table1 join table2 on table1.user_id = table2.user_id

(2) 非等值的：

select order_id,user_id from table1 join table2 on order_id between 1 and 99

4. USING连接(join using)

select order_id,user_id from table1 join table2 using(order_id)

5. 外连接

1. 左外连接：left join 或 left outer join
2. 右外连接：right join 或 right outer join 
3. 全外连接：full join 或full outer join(mysql不支持，oracle、sql server支持)
4. 自连接（能使用自连接，不要使用子查询，因为大部分DBMS对自连接做了优化）

注：多表连接：

select
from table1 
    join table 2 on table1.id = table2.id
        join table3 on table2.id = table3.id

---

SQL中的if、case when：

if表达式：

if(expr1,expr2,expr3)

和三目运算符的原理一样，如果expr1是true，则 if() 返回expr2，否则返回expr3。

 

case when表达式：

select case 列名
    when 1 then '结果1'
    when 2 then '结果2'
    else '结果3'
end 

或者

​
select case 
    when 列名 = 1 then '结果1'
    when 列名 = 2 then '结果2'
    else '结果3'
end