大数据数仓操作（二）

join：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

left outer join左边表的数据都列出，如果右边表没有对应的列，则写成了NULL值。

分区排序：

sort by：mapreduce内部排序

order by：全局排序

当distribute by和sorts by字段相同时，可以使用cluster by方式。

partition分区计算并可以搭配order by等，但distribute by只能搭配sort by

窗口函数：

针对over()前最后一个函数进行开窗

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

over()不同分区内排序使用sort by

列转行：

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

行转列：

lateral view和explode配合使用

select 
movie,
category_name 
from 
movie_info lateral view explode(category) table_tmp as category_name;

排名函数：

RANK() 排序相同时会重复，总数不会变   1，1，3，4

DENSE_RANK() 排序相同时会重复，总数会减少  1，1，2，3

ROW_NUMBER() 会根据顺序计算 1，2，3，4

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,

动态分区：

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

create table ori_partitioned_target(id bigint, time bigint, uid string,
 keyword string, url_rank int, click_num int, click_url string) PARTITIONED BY (p_time STRING) row format delimited fields terminated by '\t';
 
insert overwrite table ori_partitioned_target partition (p_time) 
select id, time, uid, keyword, url_rank, click_num, click_url, p_time from ori_partitioned;

查询前三行数据

select * from table LIMIT 3;