HQL笔记

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、增量表和全量表

增量表是创建一个分区，可以查看历史中任意一天的表

全量表是所有的历史