Hive SQL 学习

partition by

partition by关键字是分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组

example: 一个班有学生id，成绩，班级，现在将学生根据班级按照成绩排名。(partition by)

lateral view

explode / posexplode

explode 拆分一行称多行，而posexplode是根据多行匹配行号进行拆分多行。

窗口函数

a. first_value

​ 取分组内排序后，截止到当前行，第一个值

b.last_value

​ 取分组内排序后，截止到当前行，最后一个值

c.lead(col,n,default)

​ 用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

d.lag(col,n,default)

​ 与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

c.聚集函数 + over + (partition by col1 [order by col (rows | range) between (UNBOUNDED | [num]) preceding and (num FOLLOWING | current ROW))

d.ROW_NUMBER()

​ 从1开始，按照顺序，生成分组内记录的序列

e.RANK()

​ 生成数据项在分组中的排名，排名相等会在名次中留下空位

f.DENSE_RANK()

​ 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

g.CUME_DIST()

​ 小于等于当前值的行数/分组内总行数

h.PERCENT_RANK ()

​ 分组内当前行的RANK值-1/分组内总行数-1

i.NTILE(n)

​ 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布

Note:

• From子句：执行顺序自上而下，从左到右，从后往前，所以数据量少的表尽量放后
• where子句：执行顺序自下而上，从右到左，可以过滤掉大量记录的条件写在where子句的末尾
• group by子句：通过将不需要的记录在group by之前过滤掉，避免使用having来过滤
• having子句：尽量少用
• select子句：尽量少用*，取字段名称
• order by子句：执行顺序为从左到右排序
• join：尽量把数据量大的表放在最右边来进行关联