基本语法

select 字段列表|表达式|子查询
from 表(子查询|视图|临时表|普通表)
where [not] 条件A and|or 条件B				--先：面向原始行进行筛选
group by 字段A[,字段B,...]					=> 分组【去重处理】
having 聚合条件(非原始字段条件)				--再：针对聚合后的字段进行二次筛选
order|sort|cluster by 字段A[,字段B,...]		--后：全局排序(非limit的最后一句)	走mapreduce
limit N(前N条记录) | M(行号偏移量),N(记录数)

1.where子句的条件格式

一：关系运算符

关系运算符：> , >= , < , <= , =【等值判断】 , <>【不等于】

• 延伸：between (>=)SMALL_VALUE and (<=)BIG_VALUE; 【面向于 数值或日期】

二：逻辑运算符

逻辑运算符：not【非】 , and【与】 , or【或】

• 延伸：

--if函数：
if(BOOLEN_EXPR,VALUE_IF_TRUE,VALUE_IF_FALSE_OR_NULL)
	案例：
        select user_id,`if`(order_amount < 1000,'low','high') as consumption
        from test1w
        where user_gender = '女'
        limit 100;
	结果展示：
		user_id	consumption
		652,high
        376,high
        537,high
        280,high
        23,high
  
--空值判断：
1.nvl(VALUE_A,VALUE_B)	=>	VALUE_A为空值(null),则返回VALUE_B。否则返回VALUE_A
2.isnull(VAL)		=>	如果 VAL 为 null,则返回 1 。否则返回 0

--case when函数：
case EXPR when V1 then VAL1 when V2 then VAL2 ... else VALN end <=> switch ... case
case when 条件1 then VAL1 when 条件2 then VAL2 ... else VALN end <=> if ... else if ...
	案例：
		select user_id,
			case when order_amount<1000 then '低消费人群' 
				when order_amount<5000 then '中等消费人群' 
				else '高消费人群' end as level
        from test1w
        where user_gender = '女'
        limit 100;
	结果展示：
		user_id	level
		652,高消费人群
        376,高消费人群
        537,低消费人群
        280,中等消费人群
		...

三：通配符

模糊查询：

基本语法：
	like '% | _'	【模糊匹配】
	
讲解：
	% => 任意个任意符号
	_ => 一个任意符号

案例：
	select "张无极" like '张%';		=> true
	select "张无极" like '张_';		=> false

正则匹配：

基本语法：
	rlike '正则表达式'
	如：'^//d+$'

案例：
	select "like" rlike '^[a-zA-Z]{2,4}$';	  =>true

2.排序

1、order by 表达式[field|func|case...when...]    		---【全局排序】：性能差
	优化：在order by B 之前，可以先对数据进行 distribute by A 与 sort by B
		=> 先部分排序，后全局排序
		
2、sort by FIELD_N 								  --在【每一个reducer端】排序
	解释：
		当reducer 的数量为1时，等同于 order by
		FIELD_N 必须是select字段列表中的一员
		一般和 distribute by 配合使用
	
3、cluster by 		--cluster by 字段A = distribute by 字段A + sort by 字段A

3.分组

1、group by 表达式(field|func|case...when) 	--为了聚合而分组，否则类似去重(代替distinct)
	目的：按照某些条件对数据进行分组并进行聚合操作，使用 group by
	多分组：
		1.group by A,B,C 		
		  grouping sets(B,(A,C),(B,C))	✔  --指定多个【分组】为：B,(A,C),(B,C)
		  
		2.group by cube(A,B,C) 		--排列组合后的所有分组：A,B,C,(A,B),(A,C),(B,C),(A,B,C)
		
		3.group by rollup(A,B,C)	--最左原则的所有分组：A,(A,B),(A,B,C)
		
2、distribute by 表达式(field|func|case...when)
	目的：为了将数据分区，仅仅将数据分发到多个节点上并行处理，使用 distribute by
	解释：
		1.不改变原始行数
		2.类似于 hadoop job 中的 Partitioner。 【默认是采用hash算法】
		3.指定按哪个字段的hashcode分区，配合【预先设置reducer数量】
	注意：
		distribute by【决定进哪个reducer】与sort by【在reducer中排序】一般搭配使用的
		distribute by通常使用在SORT BY语句之前

小型案例：

with product_total as ( 
    select order_item_product_id product_id,sum(order_item_subtotal) total
    from cb_order_items
    group by order_item_product_id
)
select product_id,total
from product_total
distribute by product_id
sort by total desc;

多分组案例

1.grouping sets 案例：✔
	create temporary table tmp_cb_order_ymbsc_sets as
	select year,month,dept_id,cate_id,prod_id
		grouping__id,
		sum(quantity) as quantity,
		round(sum(amount)) as amount
	from tmp_cb_order_ymbsc
	group by year,month,dept_id,cate_id,prod_id
	grouping sets(prod_id,(dept_id,cate_id),(year,month),(year,month,prod_id))
	order by grouping__id;
	-------------------------------------
	寻找哪几组【去重】：
		select grouping__id
		from tmp_cb_order_ymbsc_sets
		group by grouping__id;
	-------------------------------------
	-- grouping__id:
	6 :	year,month,prod_id
	7 :	year,month
	25 : dept_id,cate_id 	
	30 : prod_id
	
	
2.cube 案例：【不常用】
	select
		year(order_date) as year,
		month(order_date) as month,
		day(order_date) as day,
		count(*) as count,
		grouping__id
	from cb_orders
	group by cube (year(order_date),month(order_date),day(order_date))
	order by grouping__id;
	
	
	
3.rollup 案例：【不常用】
	select
		year(order_date) as year,
		month(order_date) as month,
		day(order_date) as day,
		count(*) as count,
		grouping__id
	from cb_orders
	group by rollup (year(order_date),month(order_date),day(order_date))
	order by grouping__id;