1、为什么要建分区
在Hive Select查询中一般会扫描整个表内容,会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据,因此建表时引入了partition概念。partition就是辅助查询,缩小查询范围,加快数据的检索速度和对数据按照一定的规格和条件进行管理。
建表分区:单分区,多分区
create table day_hour_table (id int, content string) partitioned by (dt string, hour string);双分区表,按天和小时分区,在表结构中新增加了dt和hour两列。
查看分区:show partitions tablename
查看表结构:desc 表名
2、常用聚合函数
sum 列的总和
count 行的数目
avg、max、min
distinct 除重复的行
nvl(e1,e2)
nvl(e1,e2)函数讲解
如果e1的计算结果为NULL,则返回e2的值;如果e1的计算结果不是null值,返回e1。
例如,一个学生的成绩表,算总分,如果出现结果为null的,则用0表示
select student_id,nvl(sum(score),0) as score from student
3、ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
例子:
数据显示为
需求:根据部门分组,显示每个部门的工资等级
预期结果:
SQL脚本:
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee
4、Having讲解
having一般是与group by一起使用
HAVING 子句与 WHERE 子句类似,唯一的差别是WHERE过滤行,HAVING过滤组。
查询可同时包含 WHERE 子句和 HAVING 子句,但是必须在group by子句之前指定where子句
查询公司2010年入职的各个部门每个级别里的最高薪水
SELECT dept,edlevel,MAX(salary) AS MAXIMUM --有group by的查询语句,则查询结果只能是group by后的字段和聚合函数
FROM STAFF
WHERE hiredate > '2010-01-01'
GROUP BY dept,edlevel
查看结果表 不唯一的courier_id:
select * from
(select courier_id,count(*) as aa from edw.md_courier_portrait_data_ymd where dt_ymd = '20180515' group by courier_id ) t1
where t1.aa >1
等价于
select courier_id,count(*) as aa
from edw.md_courier_portrait_data_ymd
where dt_ymd='20180515'
group by courier_id
having aa>1
5、sql表的连接方式
外连接
1、左外连接 select * from TableA left join TableB on TableA.id=TableB.id
TableA(基表)中所有的行列都显示了,TableB条件不匹配的值为NULL
2、右外连接 select * from TableA right join TableB on TableA.id=TableB.id
TableB(基表)中所有的行列都显示了,TableA条件不匹配的值为NULL
3、全外连接 select * from TableA full join TableB on TableA.id=TableB.id
TableA和TableB的所有行列都显示了,条件不匹配的行的值为NULL
内连接
select * from TableA JOIN TableB on TableA.id=TableB.id
等价于select * from TableA,TableB where TableA.id=TableB.id
示例一:查询当天激活当天完成订单的司机数量
示例二:查询历史激活当天有完成单的司机数
PS:Every derived table must have its own alias:每一个派生出来的表都必须有一个自己的别名,不然会报错。
6、case when then讲解
计算每个城市闪送员的男女人数
select city_name,sum(case when gender='female' then 1 else 0 end) female,
sum(case when gender='male' then 1 else 0 end) male,
sum(case when gender is null then 1 else 0 end) nullgender
from edw.md_courier_portrait_data_ymd
where dt_ymd = '20180515'
group by city_name
7、count(*)与count(1)的区别
count(*)将返回表格中所有存在的行的总数包括值为null的行,然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入).
最近使用count函数比较多,当要统计的数量比较大时,发现count(*)花费的时间比较多,相对来说count(1)花费的时间比较少。
8、增量表和全量表
增量表是创建一个分区,可以查看历史中任意一天的表
全量表是所有的历史